Excel Data Analysis for Engineers and Scientists

How our class will boost your Excel data skills.

Our 1-day class shows you how to rapidly analyze, report, and visualize data by combining Excel's data elements, formulas, and charts. It covers essential topics like how to use various formulas, functions, filters, and logic to process data. Data collection strategies between worksheets and workbooks are also explored. It also shows how to use charts, PivotTables, slicers, and Pivot Charts to rapidly create reports and visualize data.

In addition to the essentials, topics like curve fitting; parsing text / times; Conditional Formatting; and building array formulas to process data are also discussed. You'll also explore how to use Power Query to automatically import and filter data for your formulas, charts, and reports.

Read our detailed outline > below and see what you'll discover in our class.

See what you can learn.

Learn to use formulas, Excel tables, spill over arrays, and filter functions to track, organize, and consolidate data automatically.

Learn to use formula logic and functions to make data decisions and to sum, count, and analyze data based on criteria.

Learn to use logic, functions, and Conditional Formatting to flag data, create adaptive problem formatting, and design data visualization tools.

Learn to use Excel's data tools to filter data and the Analysis Toolpak to rapidly perform various statistical analyses on your data sets.

Learn how to perform hi-tech table look ups with various functions and return columns, rows, and parts of tables in your formulas.

Learn to create various technical charts, perform curve fits, and create visual displays by combining charts, shapes, slicers, and controls.

Learn to use array formulas to analyze and filter complex data, and use functions to perform various text, date/time, and statistical operations.

Learn to link and format external data with Power Query and use PivotTables to create tables and rapidly summarize data.

How to use cell references, names, and arrays to build data formulas and combine worksheet data
How to use Excel's Filters, Sort, Slicers, Analysis Toolpak, and Excel tables to rapidly filter and process data
Flagging worksheet data using Conditional Formatting and logic
Creating and formatting charts; curve fitting chart data
Using key math, trig, date-time, and statistical worksheet functions to analyze data
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 sets
How to use PivotTables and Pivot Charts to rapidly process, summarize, and visualize data
How to use Power Query to import and filter (i.e. sort, delete, clean, trim...) data automatically

Some key topics we cover in class.

4 ways to train.

Live Virtual

View the dates / signup for our hands-on live virtual training.
Virtual Signup > Learn More >

Public In-Person

View the dates, signup, and pricing for our hands-on in-person training.
In-Person Signup > Learn More >

Manual

Can't take the class, buy the self-study manual, apply the price later.
View Manual >

Onsite

Learn how we can help your business save time and effort in Excel.
View Onsite Details >

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 Brochure

Worksheet Formula, Linking, and Parsing Fundamentals

Designing worksheet and Excel tables to store technical data
Using different cell reference types and names to build data formulas
Consolidating and linking multi-worksheet / workbook data with formulas
Using Excel tables, structured references, and spill over arrays to track and assemble data
Rapidly parsing, filtering, and arranging report data using the UNIQUE, FILTER... functions
Review of using the PasteSpecial feature to paste parts of data

Using Logic to Analyze, Flag, and Display Worksheet Data

Summing and counting data with logic using the SUMIFS and COUNTIFS functions
Finding MAX, MINS... with logic using the MAXIFS, MINIFS... functions
Using the IF, IFS, AND... functions and <,>... operators to make data decisions
Creating dynamic logic in formulas using string concatenation
Using Conditional Formatting and logic to flag and display worksheet data

Analyzing Data with Filters and the Analysis Toolpak

How to use Sort, AutoFilter, Text to Columns, and the Advanced Filter to filter test data
Filtering and calculating data using an Excel table's quick analysis tools and slicers
Removing duplicate values from data columns with the Remove Duplicates feature
Using the Analysis Toolpak to analyze your data

Analyzing Dates, Times, Text, Statistics and, Performing Table Lookups

Analyzing dates, times, and milliseconds using the Date, Time, and Text functions
Trimming, parsing, and cleaning worksheet text using the SUBSTITUTE, MID, TRIM... functions
Performing table lookups using the INDIRECT, VLOOKUP, and XLOOKUP... functions
Using the INDEX, MATCH, and the XMATCH functions to return table rows or columns
Using key statistical functions like STDEV.P, VAR.P, T.INV... to analyze data

Using Power Query and Text Wizard to Import and Filter Data

Using Text Wizard to import text file data directly to a worksheet
How to use Power Query to link to external data (workbooks, text files, databases...) and import it
Performing various data tasks in Power Query like filtering, merging, replacing, splitting, trimming... data
Using Power Query to extract information from Excel tables to make report and chart tables
Refreshing, editing, and updating Power Query based Excel tables and their sources

Chart Design, Curve Fitting, and Visualizing Data

Creating and formatting XY Scatter, Column, Line, Pie... charts
How to create combo charts (i.e. line /column...) and combine multiple tables in a chart
Creating limit lines and secondary axes in your charts
Laying out multiple charts on the same worksheet to create chart reports
How to curve fit your chart data and extract the equation
Combining Excel tables, charts, slicers, ActiveX, and shapes to visualize data

Analyzing Data Sets Using Array Formulas

Using simple array formulas to correct and filter data for various functions
Combining the FILTER, SORT, CHOOSECOLS... functions to filter data
Creating array formulas that analyze complex data sets and perform advanced lookups

Using PivotTables and Pivot Charts to Analyze Data

Creating, formatting, and controlling PivotTables
Calculating various statistics and totals in a PivotTable
Constructing a Pivot Chart from a PivotTable
How to use the GETPIVOTDATA function to retrieve PivotTable data
Using slicers, timelines, PivotTables, and Pivot Charts to build visual displays

Additional Class Information

Prerequisites for our Excel training.

Controlled workbooks and worksheets
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...

Class time, manual, and repeats.

Audience: Engineers, scientists and technicians
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Live, hands-on, taught by engineer
Class Manual: Our complete Excel manual for engineering and science (EAE). Details >
Excel Versions: 2013-Present, Microsoft Excel 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

If you need to contact us about our class.

Phone Number: 1.629.325.9937

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

You can email us at info@emagenit.com >

Form >

Excel Data Analysis for Engineers and Scientists Live Class