Microsoft Excel Data Analysis
for Engineers and Scientists Training

Our 1-day Excel data analysis training will show you how to effectively use Excel to process engineering and scientific data. Key topics include using data validation to control worksheet input, conditional formatting to dynamically color cell data based on logic, cell and range names and Excel tables to track data, using key functions like VLOOKUP, MATCH, COUNTIF, SUMIF and IF in data analysis formulas; using array formulas and logic to process data; organizing and storing data on the worksheet; analyzing worksheet data with various Excel data tools; building charts; pre-processing worksheet data and working with text files; and linking Excel to other programs like Word and PowerPoint for reporting purposes. Also discussed is how to use Pivot Tables and Power Query to manipulate and filter data.

Select this Excel training if you or your group:

• Have opened and saved a workbook file
• Know the basics about worksheets, cells and using cell references like A1
• Have copied and pasted worksheet data
• Have performed basic cell formatting tasks like coloring a cell, bolding, aligning...
• Have typed data in worksheet cells and built basic formulas like =A1+A2
• Have used a worksheet function like =Sum(A1:A10) in a formula or have seen it before

Review of Formula Design, Debugging, Cell Naming, Cell Formats, Data Validation and Sheet Linking

• Excel engineering and science related formula construction and formula debugging
• Review of how Excel really stores data including text, Booleans and dates and how they affect data processing formulas
• How to use cell and range names in your formulas, the key to building adaptive worksheets that respond to data changes
• How to manage and use cell and range names in data processing formulas
• How to use data validation and shapes to control user input on a worksheet and create visual displays
• How to pass information between worksheets and workbooks using names and cell references
• How to create a string concatenation formula that calculates a text value, essential to know when constructing logic in formulas and building reference columns

How to Build Proper Engineering and Science Tables on the Worksheet

• Overview of what makes a table on a worksheet and how Excel and later VBA can utilize them in mass data processing schemes
• The 4 basics rules for worksheet table layout that will allow it to act like a database table
• The different types of table configurations and how they impact what worksheet functions and formulas you use
• How to create Excel tables, name them, manage them and use their built-in data analysis capabilities on the worksheet
• How to use a slicer to filter Excel tables rapidly and create reports
• How to leverage Excel tables when making report templates that dynamically adjust to new data

Excel Worksheet Function Operation, Argument Lists and Nesting for Engineers and Scientists

• What is a worksheet function (coding example), what is an argument list and how to run them in formulas
• Review of how to analyze worksheet data using basic functions like SUM, MAX, MIN, COUNTA, AVERAGE and SUBTOTAL in your formulas and some new ways to use them
• How to use statistical functions in your formulas like STDDEV.P, RANK.EQ,... in your formulas
• How to use cell references, names, constants and Excel table structured references in worksheet function argument lists
• Overview of worksheet function nesting and its importance in analyzing engineering and science data
• Helpful methods for assembling nested worksheet function structures in formulas

Looking Up and Merging Engineering and Science Worksheet Table Data

• Primary and foreign key discussion, how lookups work in certain data tables
• How to nest VLOOKUP functions to perform powerful table lookups and merge data between tables
• How to use MATCH to locate data positions in a range
• How to use INDEX and MATCH to return scalar data and arrays from tables VLOOKUP cannot analyze

Logic Overview, Condition Construction and Key Logic Worksheet Functions for Engineers and Scientists

• Review of logic and how can engineers and scientists use it in data processing formulas
• What are logic conditions and how to create them by looking for triggers in your engineering and science data
• How to build logic condition formulas that analyze data using just the relational operators: <, >, <=, >=, < >, =
• Review of how to use the IF worksheet function to process data using logic
• How to build logic conditions for IF functions using the relational operators (<, >, <=, >=, < >, =) and various worksheet functions like ISBLANK, ISNUMBER, ISNA, COUNTIF, COUNTIFS, LEFT, RIGHT, MID,  TEXT,...
• How to use the AND and OR worksheet functions with IF to build multi-condition data processing formulas

How to Summarize and Count Worksheet Data Instances in Engineering and Science Data

• Review of how to use SUMIF and SUMIFS to summarize your worksheet data
• Review of how to use COUNTIF and COUNTIFS to count how many instances of a value you have in your data
• How to perform conditional lookups using SUMIFS and check for existing data using COUNTIFS
• How to use string concatenation to build adaptable logic in the SUMIF(S) and COUNTIF(S) worksheet functions

How to Use Array Formulas and Dynamic Names to Help Analyze Engineering and Science Data

• Overview of how scientists and engineers can use array formulas to analyze large amounts of difficult worksheet data
• How scientists and engineers can use simple matrix algebra operations on the worksheet
• How to create Excel array formulas that process large amounts of complex data in a single cell using the IF function
• How simple array formulas can be used to process data before being used in standard functions like VLOOKUP, SUM, IF, MAX, ....
• How engineers and scientists can create Excel array formulas that perform advanced lookups and table merging using MATCH, VLOOKUP, HLOOKUP, INDEX, ROW, COLUMN...
• How to create dynamic name formulas and assign them to names, the key to developing self-adjusting Excel data tools and displays
• How to create Excel formulas that bracket data ranges on the worksheet using MATCH, ADDRESS, OFFSET, ROW, COLUMN, and INDIRECT

Must Know Text Processing Techniques for Engineers and Scientists

• How to trim, substitute and clean worksheet text using the text worksheet functions like SUBSTITUTE, TRIM and CLEAN
• How to both parse and assemble strings using the RIGHT, MID, LEFT, SEARCH and REPLACE worksheet functions
• How to use the Text to Columns feature to separate delimited data into separate worksheet columns
• How to use Text Wizard to import downloaded text files
• How to use the Remove Duplicates feature to retrieve unique values from a column

Using the Advance Filter, AutoFilter and Analysis Toolpak on Engineering and Science Data

• How to use AutoFilter and its many features to quickly filter engineering and science data
• How to use the Advanced Filter to build advanced table filters for worksheet data using logic formulas
• What is in the Analysis Toolpak and how to use it
• How to use the Data Analysis tools in Excel like moving average, regression analysis, sampling,...

Using Conditional Formatting to Flag Your Engineering and Science Data

• The uses of conditional formatting in engineering and science problems
• How to use other conditional formatting rules (i.e. graphical bars, icons...)
• How to use the 2 most important conditional formatting rules that color cells based on logic formulas and cell values
• How to build logical formulas in the conditional formatting rules that flag data and can be used in visual displays in unique ways

Creating Engineering and Science Charts

• How to create engineering and science charts like XY Scatter, Column, Line and Pie charts
• Chart formatting strategies including overall look and color, data labels, error limits and curve fits
• How to combine two or more chart types into one chart
• How to create limit lines on your charts
• How to layout multiple charts on the same worksheet to create reports

How to Create Pivot Tables and Pivot Charts on the Worksheet

• What is a Pivot Table and Pivot Chart?
• How to arrange engineering and science data for Pivot Table use
• How to create Pivot Tables from worksheet ranges and Excel tables
• How to control Pivot Tables using filtering and grouping
• How to refresh and remap Pivot Table data, how Excel tables negate having to remap your changing data
• How to create different Pivot Table summary types
• How to format a Pivot Table using styles and the Format Cells dialog box
• How to make slicers and timelines for Pivot Tables
• How to format and control the options for timelines and slicers
• How to construct, edit and format Pivot Charts
• How to use the filters on a Pivot Chart and arrange them
• How to use the Pivot Tables, Pivot Charts, slicers and timelines to create selectable dashboards and reports

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

• How to use Microsoft Query to query external data and create an Excel table that is linked to its data source
• How to use Power Query to link with an Excel workbook, text file and database and create an Excel table
• How to perform various key tasks in Power Query like merging and filtering  data, replacing characters, splitting dates, cleaning text,...

Linking Excel with Word and PowerPoint

• Basic linking between the programs with copying and pasting
• How to link cells, ranges and charts with Word and PowerPoint