Excel Data Analysis for Engineers / Scientists Class

Next Class: 12/10/2022

More Dates >

See the must know topics you will learn.

No time to attend, get a manual instead.

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, basically all the above manuals.
Covers the Excel topics needed to perform tasks like data processing, automated reporting, modeling, numerical analysis, design optimization, and dashboard design.
Includes our complete Excel VBA write-up that shows you how to build automated calculations; models; data processing tools; advanced UIs; and database tools.
100's real world examples and 1000's of lines of useful VBA code included. Learn More >
Buy a manual now, apply the price towards a class later. Manuals are included with our classes.

Key Excel topics covered in class.

Data analysis formula design, cell naming, and worksheet linking review
Using Excel's Filters, Sort, Slicers, Analysis Toolpak, and Excel tables to rapidly filter and process data
Using Conditional Formatting to automatically flag worksheet data based on logic
Controlling worksheet data entry and creating cell drop downs using Data Validation
Creating charts, curve fits, and using Slicers to control chart views
Creating key math, trig, date time, and statistical worksheet functions to analyze data
Using Excel table structured references to create adaptive formulas
Looking up data in worksheet tables using the VLOOKUP, XLOOKUP, INDEX... functions
Using logic functions like SUMIFS, COUNTIFS, IF, AND... to summarize, count, and analyze worksheet data
Importing text files with Text Wizard and analyzing worksheet text with functions like LEFT, MID, CLEAN...
Building array formulas to analyze complex engineering and science data patterns
How to use PivotTables and Pivot Charts to process, summarize, and visualize data
How to use Power Query to import and shape (i.e., filter, sort, delete, clean...) data automatically
Creating dynamic reports by linking Excel to Word and PowerPoint

Class examples.

Pricing and class options.

Online Virtual Learning

Live, hands-on, talk to the instructor training
Bring your own problems to class
Online Details >

Dates / Signup >

Public In-Person Learning

Live hands-on training
Bring your own problems to class
We can supply a computer

Dates / Signup >

Onsite Hands-On Learning

We come to you and solve your problems
We cover in person in the USA and Canada
We cover the world virtually using your time zone

Details >

Detailed 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.

Data Analysis Problem Construction on the Worksheet

Using relative, mixed, and absolute cell references along with various operators to build analysis formulas
How to use relative and mixed references to drag and fill formulas in 1-d and 2-d data tables
How Excel really stores text, numbers, and dates in cells and how they affect functions and formulas
Using cell and range names in your formulas to easily track problem inputs and data
How to efficiently pass data between worksheets and workbooks using cell references and names
Building simple logic formulas to analyze data using the relational operators: <, >, <=, >=, < >, =

Building Tables, Filtering / Sorting Data, and Using the Analysis Toolpak

The difference between an Excel table and a regular worksheet table
The 4 basic construction rules for worksheet and Excel tables, primary and foreign key discussion
Using Sort, AutoFilter, and Advanced Filter in unique ways to sort and filter worksheet data for reports
How to use Copy / PasteSpecial to paste select parts of filtered data to a new report worksheet
Using the Remove Duplicates feature to retrieve unique values from a column
Using an Excel table's quick analysis tools and slicers to rapidly filter and calculate data
What is in the Analysis Toolpak and how to use its tools like moving average, regression analysis, sampling... (optional discussion)

Using Conditional Formatting / Data Validation to Flag and Control Data

Why control what a user types in a data table or form?
Using Data Validation to create cell drop down lists and control number, date, and text entry in your data
What is Conditional Formatting and how is it used to automatically flag worksheet data based on logic?
Setting up a Conditional Formatting logic rule to format worksheet data based on a cell value or formula
Setting up a Conditional Formatting logic rule to place arrows, circles, bars... in cells
How to edit and delete a Conditional Formatting rule

Chart Design, Curve Fitting, and Controlling Chart Views with Slicers

Creating and professionally formatting engineering and science charts like XY Scatter, Column, Line, Pie...
Combining two or more chart types into one chart (i.e., combo chart)
Creating horizontal and vertical limit lines on your engineering and science charts
Combining multiple worksheet tables into a single chart, great for trade studies, varying x-axis tables...
Laying out multiple charts on the same worksheet to create chart reports
How to curve fit your chart data
Using Excel tables and Slicers to dynamically adjust and filter chart data

Creating and Using Key Worksheet Functions to Analyze Data

How to create and use basic functions like SUM, COUNTA, AVERAGE, MAX, SUBTOTAL... in your formulas
Using range references, names, and structured references as arguments to your functions
How to nest worksheet functions in your formulas to perform powerful analysis tasks
Using key statistical functions like STDEV.S, SKEW, FREQ, RANK.EQ... in your formulas
Using key math and trig functions like ROUNDUP, ABS, TAN, RADIANS... in your formulas
Using the TEXT, DAY, MONTH, YEAR, TODAY, DATE, TIME... functions to parse and create date time values
How to use the NETWORKDAYS.INTL and WORKDAY.INTL functions to calculate the # of workdays between dates or from a date

Looking Up Data in Worksheet Tables

How to use the VLOOKUP, HLOOKUP, and XLOOKUP functions to lookup data in a table
Using XMATCH and MATCH to find item position (i.e., 1, 2, 3...) within a row or column
How to use the INDEX function to perform advanced table lookups that return entire rows and columns
Using the INDIRECT function, string concatenation, and names to dynamically toggle worksheet tables in formulas

Importing Text Files and Analyzing Worksheet Text Data with Functions

Using Text to Columns to parse worksheet text and Text Wizard to import text files onto worksheets
How to trim, substitute, and clean worksheet text using the text worksheet functions like SUBSTITUTE, TRIM, CLEAN...
Parsing and cleaning worksheet text using functions like RIGHT, MID, LEFT, TEXT, SEARCH...
How to combine worksheet text using string concatenation

Using Logic to Summarize, Count, and Analyze Worksheet Data

The basics of building logic in formulas and determining logic triggers in your worksheet data
Using functions like SUMIF, SUMIFS, MAXIFS, COUNTIF, COUNTIFS ... to sum, count, and calculate your data based on logic
Using string concatenation to create dynamic logic for functions that adjust to changing inputs
Building logic formulas using the IF, AND, OR, NOT, and IS logical functions
Using IF to decide what formula to run or what value to output based on logic criteria
Using logic to look for pieces of text (i.e., substrings) within text data

Using PivotTables to Analyze Engineering and Science Data

Why use Excel tables to create the data that a PivotTable will use?
Creating, formatting, and controlling PivotTables
Calculating various statistics in a PivotTable
Constructing a Pivot Chart from a PivotTable
Using slicers and timelines to control your PivotTables and Pivot Charts

Using Array Formulas to Analyze Complex Engineering and Science Data Patterns

Overview of how scientists and engineers can use array formulas to analyze complex worksheet data
Creating Excel array formulas that process large complex data sets using the IF function
How simple array formulas can be used to preprocess data before being used in functions like VLOOKUP, IF, MAX, MIN,AVERAGE...
How engineers and scientists can create Excel array formulas that perform advanced lookups and table merging using functions like MATCH, VLOOKUP, HLOOKUP, INDEX, ROW, COLUMN...

Using Power Query to Access and Shape External Engineering and Science Data

Using Power Query to automatically import data and shape it (i.e., filter, sort...)
Using Power Query to link to internal and external data in workbooks, text files and databases
Performing various key shaping tasks in Power Query like merging and filtering data, replacing characters, splitting dates, cleaning text...
Refreshing, editing, and updating power query based tables and their sources

Linking Excel with Word and PowerPoint to Build Dynamic Reports

Using Excel with Word or PowerPoint to make powerful reporting tools
Linking cell values, ranges, and charts to Word documents
Linking cell values, ranges, and charts to PowerPoint presentations

Excel skills needed for the class.

Select this Excel training if you or your group have:

Controlled workbooks, worksheets, and entered worksheet data
Copied and pasted worksheet data
Performed basic cell formatting tasks like coloring a cell, bolding, aligning,...
Used cell and range references like A1 or A1:A10 in formulas
Built formulas like =.5*0.002377 *A1^2
Used basic worksheet functions like SUM, MAX, MIN...

The class details.

Audience: Engineers, scientists and technicians
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Hands-on, live, instructor taught training
Full Excel Manual Included: Our 2200 + page Microsoft Excel Aided Engineering and Science 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
Available Training Formats:

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 >

Contact us by form.

Microsoft Excel for Engineers / Scientists	Excel Data Analysis for Engineers / Scientists	Excel VBA for Engineers / Scientists	Excel VBA Data Analysis for Engineers / Scientists	Advanced Excel VBA Design for Engineers / Scientists	Mixed Language Programming with Excel VBA / Fortran