Microsoft Excel Data Analysis

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.

Key Excel skills learned:

• Review of how to build and debug worksheet data formulas and pass information between worksheets and workbooks
• Review of how Excel stores data on the worksheet, cell format discussion
• How to name worksheet cells and ranges, a key element in adaptable data formula design
• How to use data validation and formulas to control cell input on the worksheet
• How to use string concatenation to build cell names and references in formulas, a key element in adaptable data formula design
• How to build proper worksheet and Excel tables, the key to using Microsoft Excel's data tool, creating large data storage systems and designing adaptable data formulas
• How to use Excel worksheet functions like SUM, MIN, MAX, COUNTA, AVERAGE and SUBTOTAL in new ways to analyze data
• How to look up and merge worksheet data using key Excel worksheet functions like VLOOKUP, INDIRECT, OFFSET, MATCH and INDEX
• How to use Excel functions to summarize and count data instances like IF, AND, OR, SUMIF, SUMIFS, COUNTIF and COUNTIFS
• How to work with date and times on the worksheet along with their respective Excel functions
• How to process complex data sets using array formulas and logic
• How to use logical formulas in conditional formatting to flag data
• How to use Advanced Filter, AutoFilter and the Analysis Toolpak to manipulate Excel worksheet data
• How to parse text on a worksheet using Text to Columns and the Remove Duplicates features
• How to use Excel functions like LEFT, RIGHT, MID, SEARCH, SUBSITUTE, LEN, REPLACE, TRIM and CLEAN for parsing worksheet text data
• How to create engineering and science charts, format them, create limit lines, create curve fits and get the curve fit equation
• How to create, control and format Pivot Tables and Pivot Charts including slicers and timelines
• How to dynamically link a worksheet with a workbook file, text file or database in Excel
• Introduction to using Power Query and MS Query to filter and pre-process engineering and science data
• How to link Excel with PowerPoint and Word to create reports

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