Excel Data Analysis for Engineers / Scientists Class

Class overview.

What makes our training different?

Live, hands-on, industry topics
Bring your problems, ask tough questions
Taught by an engineer
Limited class size

Our 1-day class shows you how to take command of Excel's data tools, functions, and charts and use them to rapidly process and visualize engineering and science data. It covers essential topics like how to use functions, logic, charts, PivotTables, and various data filters to process your data.

Also discussed are unique topics like how to merge text values, parse date-times like milliseconds, curve fit chart data, and build array formulas to analyze complex datasets and lookups.

You'll also discover how to use Power Query and Excel tables to create reports that automatically import data, shape it, calculate it, and chart it at the click of a refresh button. Join us and our class will show you how to quickly combine Excel's critical data elements into tools that will save you time and effort.

Some key topics we cover in class.

Using different style references, names, and arrays to build formulas and combine worksheet data
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
Creating charts, curve fits, and using Slicers to control chart views
Using key math, trig, date time, and statistical worksheet functions to analyze data
Using Excel tables and structured references to create automated reports
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

Prerequisites for our Excel training.

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

Class time, duration, 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: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

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.

Using Formulas, Functions, String Concatenation, and Tables in Data Analysis

How Excel really stores text, numbers, and date/times in cells and how they affect your functions and formulas
Using relative, mixed, absolute, and named cell references to build key analysis formulas
Building simple logic formulas to analyze data using the relational operators: <, >, <=, >=, < >, =
Construction rules for worksheet tables; primary and foreign key discussion
Using Excel tables to create automated reports; using structured references, names, and ranges with functions
How to use key functions like SUM, AVERAGE, MAX, SUBTOTAL, IF, SUMIFS, COUNTIFS... to analyze data
Using cell references, names, dynamic arrays, and basic logic to combine and calculate data from different worksheets
How to use string concatenation in formulas to build text, merge text, and build logic that adapts to changing data

Quickly Analyzing Data Using Filters, the Analysis Toolpak, and Conditional Formatting

Using Sort, AutoFilter, Text to Columns, and the Advanced Filter in unique ways to filter download and test data
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 data column
Using an Excel table's quick analysis tools and slicers to rapidly filter and calculate data
Using the Analysis Toolpak tools like moving average, regression analysis, sampling... on the worksheet
Using Conditional Formatting to flag worksheet data based on a cell value or formula

Using Key Worksheet Functions to Lookup, Parse, and Analyze Your Data

Using key math and trig functions like ROUND, ABS, TAN, RADIANS... to analyze data
Using key statistical functions like STDEV.S, SKEW, FREQUENCY, RANK.EQ... to analyze data
Using key date and time functions like TODAY, EOMONTH, WEEKDAY, YEAR, DATE, WORKDAY.INTL... to analyze data
How to deal with milliseconds on the worksheet using functions, string concatenation, and number formats
How to trim, substitute, parse, and clean worksheet text using SUBSTITUTE, RIGHT, MID, TRIM, CLEAN, TEXT...
How to use the VLOOKUP, HLOOKUP, and XLOOKUP... functions to lookup data in a table
How to use the INDEX, MATCH, and XMATCH functions to perform advanced table lookups that return entire rows and columns
Using the IF, AND, OR, NOT, and IS logical functions to build advanced logic

Using Power Query and Text Wizard to Access and Shape Data

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

Chart Design, Curve Fitting, and Chart Displays

Creating and professionally formatting engineering and science charts like XY Scatter, Column, Line, Pie...
How to create combo charts in Excel like line / column charts; using the secondary axis to view data
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 and extract the equation
Using Excel tables, Slicers, shapes, and linked pictures to build dynamic chart displays

Using PivotTables to Analyze Engineering and Science Data

Creating, formatting, and controlling PivotTables
Calculating various statistics and totals 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 using 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 to create Excel array formulas that perform advanced lookups

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 and PowerPoint presentations

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 >

Excel Data Analysis for Engineers and Scientists

Unleash Excel's data abilities and rapidly automate multi-step engineering and science reports.