Microsoft Excel Data Analysis Training for Engineers and Scientists

Our 1-day hands-on Excel training will show you how to use Excel's data tools, charts, worksheet functions, formulas and array formulas to supercharge your engineering and science data analysis tasks. Key topics include:

• A review of data analysis formula design, debugging, cell naming, and worksheet/workbook linking
• Designing worksheet tables and Excel tables for data analysis problems
• Using cell names, range names, dynamic ranges and Excel tables to track changing data and build adaptive reporting tools
• Using key functions like VLOOKUP, MATCH, COUNTIFS, SUMIFS, STDDEV.S, IF... to process worksheet data
• Using array formulas and logic to process complex data patterns
• Advanced data filtering, parsing and cleaning worksheet text, and opening text files
•  Building charts, visualizing data limits in charts, and obtaining curve fit equations from charts
• Designing data user interfaces (UI's), using Data Validation to control worksheet input, and using Conditional Formatting to dynamically color cell data based on logic
•  Using PivotTables to summarize and organize engineering and science data
•  Using Power Query to link to, manipulate, and filter external data in workbooks, text files, and databases
• Linking Excel to Word and PowerPoint for reporting purposes.

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

Data Analysis Formula Design, Debugging, Cell Naming, and Worksheet Linking

• Formula construction and debugging techniques for data analysis problems
• Using various cell reference types in your data analysis formulas
• How Excel really stores data including text, Booleans, and dates and how they affect data processing formulas
• Using cell and range names in your formulas; the key to building adaptive tools that quickly respond to data changes
• Passing data between worksheets and workbooks using names and cell references

Worksheet Table Design, Table Filtering and Using the Analysis Toolpak

• The 4 basics rules for worksheet table layout that will allow it to act like a database table and be used in data analysis problems
• Creating Excel tables, naming them, managing them and using their built-in data analysis capabilities
• Using a slicer to filter Excel tables rapidly and create reports
• Leveraging Excel tables when making report templates that dynamically adjust to new data sets
• Primary and foreign key discussion; how lookups work in different data analysis problems
• Using Excel table structured references in formulas that adapt to changing data size
• Using Excel's Sort feature in new ways when analyzing engineering and science data
• Using AutoFilter and the Advanced Filter to filter engineering and science data
• Using the Remove Duplicates feature to retrieve unique values from a data column
• What is in the Analysis Toolpak and how to use its tools like moving average, regression analysis, sampling... on data

Key Analytical Methods and Worksheet Functions Used in Processing Data

• Learn the analysis methods and formula approaches for different data table patterns
• Using basic functions like SUM, COUNTA, AVERAGE, MAX, MIN, SUBTOTAL... in new ways in your data analysis formulas
• Creating logic conditions by looking for triggers in your engineering and science data
• Building logic formulas that analyze data using the relational operators: <, >, <=, >=, < >, =
• Building logic formulas that analyze data using ISBLANK, ISNUMBER, ISTEXT, ISNA, COUNTIF, COUNTIFS, LEFT, RIGHT, MID,  TEXT...
• Using string concatenation to create dynamic arguments in worksheet functions that adjust to changing inputs and logic
• Using SUMIFS, COUNTIFS, MAXIFS, AVERAGEIFS, MINIFS... and logic to analyze your data
• How to use the IF, AND, OR worksheet functions to process engineering and science worksheet data
• Using key statistical functions like STDEV.S, SKEW, FREQ, RANK... to process engineering and science data
• How to use the VLOOKUP function to perform powerful lookups and merge data between worksheet tables
• Using the INDEX and MATCH functions to perform lookups and return entire rows and columns from worksheet tables
• Managing dates using worksheet functions like MONTH, DAY, YEAR, EOMONTH, NETWORKDAYS, WORKDAYS, TEXT...
• How to use the INDIRECT function, string concatenation and names to toggle worksheet tables in formulas
• Creating dynamic ranges using ADDRESS, OFFSET, INDIRECT and MATCH that pull data subsets in formulas
• Creating names that can be assigned dynamic ranges

Using Array Formulas to Analyze Complex Data Patterns

• Overview of how scientists and engineers can use array formulas to analyze complex worksheet data
• How scientists and engineers can use some simple matrix algebra operations on the worksheet
• Creating Excel array formulas that process large complex data sets using the IF function
• How simple array formulas can be used to process data before being used in standard 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...

Importing Text Files and Analyzing Worksheet Text Data

• How to trim, substitute and clean worksheet text using the text worksheet functions like SUBSTITUTE, TRIM, and CLEAN
• Parsing and modifying worksheet text using the RIGHT, MID, LEFT, TEXT, and SEARCH worksheet functions
• Using Text to Columns to parse worksheet text and Text Wizard to import text files

Chart Design and Curve Fitting for Data Problems

• Creating and formatting engineering and science charts like XY Scatter, Column, Line and Pie charts
• Combining two or more chart types into one chart
• Creating horizontal and vertical limit lines on your engineering and science charts
• Combining different x-axis value data sets into a single chart
• Laying out multiple charts on the same worksheet to create reports
• Using an xy scatter chart to obtain the curve fit equation for worksheet data

User Interface (UI) Design for Data Analysis Problems

• Laying out user interfaces (UI's) for data analysis problems
• Using Data Validation to control user inputs, construct drop-down lists in your UI's
• Using ActiveX and Form controls to control user inputs in your UI's
• Using shapes to dynamically display cell values in your UI's
• How to use Conditional Formatting to flag engineering and science data on the worksheet
• Building the 2 most important Conditional Formatting rules that flag data based on logic formulas and cell values

Using PivotTables to Analyze Engineering and Science Data

• What is a PivotTable?
• Arranging engineering and science data for PivotTable use
• Creating and controlling PivotTables from worksheet ranges and Excel tables
• Calculating various statistics in a PivotTable
• Formatting a PivotTable
• Constructing a PivotChart from a PivotTable
• Using slicers and timelines to control PivotTables and PivotCharts

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

• Using Power Query to link to internal range data
• Using Power Query to link to external data like workbooks, text files and databases
• Performing various key shaping tasks in Power Query like merging and filtering data, replacing characters, splitting dates, cleaning text...
• Editing and updating power query based tables in Excel

Linking Excel with Word and PowerPoint to Quickly Build Reports

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