# Excel Data Analysis for Engineers and Scientists - Live Class

Learn to analyze, report, and visualize data by combining Excel's data elements, formulas, and charts into adaptive data tools. Class Examples >

Next Class: Check Dates

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

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

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