Excel for Engineering Professionals

Course details

While Microsoft Excel can be a powerful tool for engineers, most engineers barely scratch the surface with its full functionality. In this course, designed for advanced Excel users, Microsoft MVP Jan Karel Pieterse gives you an overview of some of the most robust features in Excel that can help you complete many common tasks as a mechanical, chemical, or structural engineer. Get a primer on what tasks are best suited for Excel in relation to other functions and tools as well as the most useful functions for engineering projects, from ranges and lookups to strings, arrays, and more. Get ready to use Excel to solve complex problems, work with charts, and import and analyze data sets. By the end of this course, you’ll be ready to start leveraging workbooks, sheets, cells, arrays, conditions, loops, and events for VBA programming.

Concepts

0. Introduction

01 - Engineering and Excel

1. Spreadsheet Design Best Practices

02 - Knowing when (and when not) to use Excel
03 - Designing a workbook
04 - Documenting your work
05 - Formula habits
06 - Working with engineering units
07 - Improving data quality with data validation
08 - Working with tables

2. Essential Excel Functions

09 - Date and time functions
10 - Range names
11 - XLOOKUP and other lookup functions
12 - Statistical functions - AVERAGE, STDEV, MIN, and MAX
13 - String functions
14 - Working with dynamic arrays

3. Solving Complex Problems

15 - Using Excel for process simulations
16 - Goal seek
17 - Excel Solver

4. Working with Charts

18 - Which chart type to use
19 - Dos and don'ts
20 - Working with charts - Preparing your data
21 - Working with charts - Staging area
22 - Trends in charts - Trendlines

5. Importing Data

23 - Importing text files with Power Query
24 - Importing sets of files
25 - Importing the most recent file in a folder

6. Data Analysis

26 - When (and when not) to use PivotTables
27 - Regression analysis - Analysis Toolpak add-in
28 - Regression analysis - Helpful functions

7. VBA Basics

29 - Variables explained
30 - Working with workbooks, worksheets, and cells
31 - Working with arrays
32 - Conditions - If, Then, and Select Case
33 - Loops - ForNext and DoLoop
34 - Events - Automatically run VBA code

8. User-Defined Functions (VBA)

35 - How to write a proper user-defined function (UDF)
36 - Understanding LAMBDA functions
37 - Creating a LAMBDA function

Conclusion

38 - What have you learned