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

Introduction

Engineering and Excel

Spreadsheet Design Best Practices

Knowing when (and when not) to use Excel
Designing a workbook
Documenting your work
Formula habits
Working with engineering units
Improving data quality with data validation
Working with tables

Essential Excel Functions

Date and time functions
Range names
XLOOKUP and other lookup functions
Statistical functions - AVERAGE, STDEV, MIN, and MAX
String functions
Working with dynamic arrays

Solving Complex Problems

Using Excel for process simulations
Goal seek
Excel Solver

Working with Charts

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

Importing Data

Importing text files with Power Query
Importing sets of files
Importing the most recent file in a folder

Data Analysis

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

VBA Basics

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

User-Defined Functions (VBA)

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

Conclusion

What have you learned