Microsoft Excel Data Analysis for Engineers and Scientists Training

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 More >
• Designing worksheet tables and Excel tables to track changing data and build adaptive reporting tools  More >
• Using various worksheet filters and the Analysis Toolpak to analyze engineering and science data More >
• Using key worksheet functions like VLOOKUP, MATCH, COUNTIFS, SUMIFS, STDDEV.S, IF... to process technical data More >
• How to use logic in your worksheet functions to process engineering and science data More >
• Using array formulas and logic to process complex engineering and science data patterns More >
• Opening text files, cleaning, and parsing text on the worksheet More >
• Building engineering and science charts, visualizing data limits in charts, and obtaining curve fit equations from charts More >
• Designing user interfaces (UI's) to control your data tools and visualize their data More >
• Using PivotTables to summarize and group engineering and science data More >
• Using Power Query to fuse and shape external engineering and science data in workbooks, text files, and databases More >
• Linking Excel to Word and PowerPoint for technical reporting purposes More >

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

• 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 Back >

• 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 Engineering and Science Data Back >

• 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 Engineering and Science Data Patterns Back >

• 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 Back >

• 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 Engineering and Science Data Back >

• 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 Engineering and Science Data Tools Back >

• 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 Back >

• 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 Shape External Engineering and Science Data Back >

• 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 Technical Reports Back >

• 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