Microsoft Excel for Engineers / Scientists Class

Onsite Classes Designed for Your Problems

Key Topics

Some key topics we cover in class.

Complete formula, debugging, cell reference, and cell naming review
How to layout model / analysis problems, build input / output ranges, and link team data in workbooks
Creating Excel and worksheet tables to store and filter data
Using Excel table slicers to rapidly filter table data
How to use key Excel worksheet functions in engineering and science problems
Performing table lookups and interpolation with the VLOOKUP, XLOOKUP, INDEX... functions
How to toggle between lookup tables in a formula
Creating formula logic that toggles equations, detects blank cells and errors, performs If...ElseIf logic...
Using Data Validation to create cell drop down lists and control worksheet cell entry in your UIs
Using ActiveX controls, shapes, and Conditional Formatting to build UIs and dynamic technical illustrations
Performing integration and differentiation, solving ODEs, solving linear / nonlinear equations, using Solver... on the worksheet
How to format and create charts, limit lines, secondary axis, carpet plots...

See What You'll Learn!

Can't Attend? Get the Manual.

Our "all-in-one" Excel manual covers the Excel and VBA topics needed to build powerful model, analysis, optimization, and data tools.

Includes our full Microsoft Excel and Excel VBA macro write-up. It is everything we have rolled into one manual.
Covers a variety of topics including data processing, automated reporting, modeling, numerical analysis, design optimization, and dashboard design.
Includes our complete Excel VBA write-up including automated calculations; models; data processing; userforms; and databases.
100's real world examples and 1000's of lines of useful VBA code included.
Buy the manual now and apply its price to a class later. Just let us know when purchasing a class. The manual comes with the class.

See the Manual >

Class Syllabus

How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Class PDF

Problem Formulation on the Worksheet

How to organize problem input and output ranges on the worksheet
Rapid formula construction and debugging methods for engineers and scientists
Using various relative, mixed, and absolute cell references effectively in formulas
Dragging and filling formulas over 1-d and 2-d range problems
How to manage and use cell / range names in your formulas to track model info and data
How to organize and transmit large amounts of data between problem / team worksheets and workbooks
Using string concatenation to assemble text "on-the-fly" in your formulas; used in lookups, logic conditions...

Creating Excel and Worksheet Tables to Store and Filter Data

Designing worksheet and Excel tables for easy lookups and data storage
How to create an Excel table and control its formats, filters, rows, columns...
Using Excel table structured references to build worksheet functions that adapt to changing data structure
Using Excel's Sort, Filter, Copy, and PasteSpecial features to rapidly organize data
Using an Excel table's quick analysis tools and Slicers to quickly calculate and filter report data

Using Key Excel Worksheet Functions in Engineering / Science Problems

How to create and use basic functions like SUM, COUNTA, AVERAGE, MAX, MIN, SUBTOTAL... in your formulas
How to nest worksheet functions in your formulas to perform advanced engineering / science calculations
Using functions like SUMIFS, COUNTIFS, MAXIFS... in your formulas to analyze data based on logic
Performing trig and math operations in your formulas using functions like RADIANS, COS, ATAN2, ROUND, SQRT, LN, LOG...
Using the IMAGINARY, COMPLEX... complex number functions in your formulas
How to use statistical functions like STDEV.S, SKEW, FREQ, RANK... in your engineering / science formulas
Designing formulas and using functions like MINVERSE, MMULT, TRANSPOSE... to perform array operations

Performing Table Lookups and Interpolation with Functions

Using VLOOKUP, XLOOKUP, and HLOOKUP to perform basic engineering / science table lookups
Using XMATCH and INDEX to perform complex table lookups as well as return entire rows and columns
Using functions to perform interpolation on a worksheet table
Using the INDIRECT function and string concatenation to toggle worksheet tables in formulas

Creating Logic Formulas for Model / Analysis Problems

Logic review for engineers and scientists including how to figure out logic triggers in problems
How to use the relational operators: <, >, <=, >=, < >, = and IF, AND, OR, NOT... to build logic formulas
Using simple logic to activate and deactivate a worksheet formula
Using the IF and IS functions to determine if cells are blank, contain numbers, contain text, have errors...
Creating formulas that appear and disappear when needed using the IF and IS functions
Creating IF...ELSEIF logic in formulas using nested IF's or the IFS function
Using the IF functions to toggle between different engineering and science formulas in a cell

Building Worksheet UIs and Dynamic Displays for Your Problems

Using Data Validation to create cell drop down lists and control number, date, and text entry in your UIs
How to create self-adjusting cell drop down lists using dynamic names and names piggy backed on Excel tables
Creating and integrating ActiveX and Form controls in worksheet UIs
Using Conditional Formatting to flag (i.e., format) cells in your problems based on logic triggers
Dynamically displaying cell values in shapes and creating linked pictures that update in your UIs

Performing Integration and Differentiation on the Worksheet

Performing numerical integration in Excel using the Trapezoid, Simpson's 1/3, and Simpsons 3/8 rule
Performing differentiation in Excel using the Forward Difference, Central Difference, and Backward Difference expressions

Solving Linear and Non-Linear Equations on the Worksheet

Performing Newton's method on the worksheet to solve a non-linear equation
Using Goal Seek to solve a non-linear equation on the worksheet
Performing the Gauss Seidel method on the worksheet to solve a system of linear equations
Using the Inverse Matrix method on the worksheet to solve a system of linear equations

Performing Simulations and Using Solver on the Worksheet

Performing the 4th Order Runge Kutta method on the worksheet to solve single and coupled ODE's
Using the Equilibrium method to solve a boundary value problem on the worksheet
Using Goal Seek to solve a Shooting method boundary value problem on the worksheet
What is Solver and how can it be used in engineering and science to perform design optimization?
Design optimization problem / constraint formulation on the worksheet
How to load and run Solver on single and multiple worksheets

Building and Formatting Engineering / Science Charts

How to create XY Scatter, Log, Column, Line, Pie... charts on a worksheet or chart sheet
Formatting chart data series, data labels, gridlines, axis limits...
Creating limit lines and a secondary axis for your charts
How to display multiple tables in a chart, great for trade studies, differing x axis increments...
How to combine two or more chart types in one chart (i.e., combo chart)

Required Class Skills

Select this Excel training if you or your group have:

Used Excel's Ribbon interface, dialog boxes, and shortcut menus
Opened and saved a workbook file
Entered data in a worksheet cell before
Copied and pasted data in Excel
Created worksheet formulas like =A1+(A2*A3)

Class Details

Duration, #days, and audience details about our class.

Audience: Engineers, scientists and technicians
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Hands-on, live, instructor taught training
Full EAE Manual Included: Our 2200 + page EAE manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >
Personal Examples: We review our attendees personal examples in our training instruction

If you need to contact us about our class.

Phone Number: 1.805.498.7162

Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT

You can email us at info@emagenit.com >

Microsoft Excel for Engineers & Scientists

Class overview.