Microsoft Excel Essentials Training

Our 1-day hands-on Excel essentials training for engineers and scientists shows you how to use Excel to solve analysis problems, analyze data, build technical displays, build models and create custom VBA functions to solve tough logic and computational problems. The training starts with a complete review of technical formula construction, formula debugging, cell naming and how to effectively transmit information between worksheets and workbooks. It then discusses how to construct proper tables and use key worksheet functions like IF, VLOOKUP, MAX, SUMIF, COUNTIF, COS, STDDEV.P,... to build formulas that analyze data, calculate models and solve analysis problems. After formulas and tables are discussed, it demonstrates how to build proper worksheet forms and data entry fields using data validation and ActiveX controls. Entering and storing data in a standardized consistent format is the key to developing powerful models and data analysis systems in Excel. It also demonstrates how to use shapes and pictures to develop technical diagrams that dynamically update and how to color cell data dynamically using conditional formatting. Our training also covers how to import text files into worksheets and use text functions to process text data on the worksheet. Last it discusses how to build technical charts, how to build Pivot Tables that summarize and organize data, and provides an introduction to Excel VBA macros by demonstrating how to build custom worksheet functions, the ultimate formulas.

Key Excel skills learned:

• A review of how to construct engineering and science related worksheet formulas including debugging
• How to construct worksheet cell and range names, the key to building and managing adaptive models, analysis and data processing tools
• How to construct worksheet tables and Excel tables, a key component in building adjustable engineering and science tools
• How to use basic functions like SUM, COUNTA, AVERAGE, MAX, MIN, SUBTOTAL,... in your formulas
• How to use trig, math, imaginary, complex, and statistical worksheet functions like COS, SIN, ROUND, TAN, SQRT, LN, LOG, COMPLEX, IMAGINARY, STDDEV.P, RANK.EQ ... in your formulas
• How to use the IF, AND, OR logical worksheet functions and the comparison operators <, >, <=, >=, < >, = to make decisions in model and analysis formulas
• How to use the SUMIF, COUNTIF, SUMIFS and COUNTIFS worksheet functions to summarize and count instances in your data
• How to look up tables and merge worksheet data using key Excel worksheet functions like VLOOKUP, MATCH and INDEX
• How to parse text on a worksheet using Text to Columns, Text Wizard and the Remove Duplicates features
• How to use the LEFT, RIGHT, MID, SEARCH, SUBSITUTE, LEN, REPLACE, TRIM and CLEAN functions to process worksheet text
• How to design and build standardized engineering and science worksheet forms
• How to use Excel features like data validation and ActiveX controls to protect cell data entry in your models, analysis tools and data systems
• How to use conditional formatting to dynamically color cells based on logic
• How to build dynamically updating technical displays using cells, drawing shapes and pictures
• How to use Group, Sort, Advanced Filter, AutoFilter and Subtotal to manipulate engineering and science worksheet data
• How to build engineering and science charts including curve fits and limit lines
• How to create, control and format Pivot Tables and Pivot Charts including slicers and timelines
• Introduction to Excel VBA macros and building your own custom worksheet functions to solve tough logic and computational problems

Select this Excel training if you or your group:

• Know how to move around Excel's Ribbon interface and have used Excel on a regular basis
• Know how to control a workbook like opening and saving
• Know what a worksheet and cell reference are
• Have performed basic cell formatting tasks like coloring a cell, bolding, aligning...
• Have typed in Excel before
• Have had limited formula construction experience
• Have a desire to use Excel in ways you never thought possible

Review of Excel Formula Construction, Cell Naming and Data Distribution

• Excel engineering and science related formula construction and debugging review
• Cell and range name construction and guidelines for engineers and scientists
• How to use and manage cell and range names in your formulas to track model and data information
• How to organize, manage and transmit large amounts of model information between worksheets and workbooks
• How to organize and layout large models in Excel

How to Build Engineering and Science Tables on the Worksheet

• What is a worksheet table and how can Excel and VBA utilize them in data processing schemes
• Worksheet table layout rules that allow it to act like a database table
• How to name and update your worksheet tables
• How to create Excel tables, name them and manage them on the worksheet

Worksheet Function Review for Engineers and Scientists

• What is a worksheet function, what is an argument list and how does it all work
• How to use range references, cell references, names, constants and Excel table structured references in your worksheet function formulas
• How to use basic functions like SUM, COUNTA, AVERAGE, MAX, MIN, SUBTOTAL,... in your formulas
• How to use trig functions and math functions like COS, SIN, ROUND, TAN, SQRT, LN, LOG,... in your formulas
• How to use imaginary and complex functions in your formulas (class vote)
• How to use statistical functions in your formulas like STDDEV.P, RANK.EQ,... in your formulas
• How to nest Microsoft Excel worksheet functions to create powerful formulas
• How to create string concatenation formulas for use in functions

Key Lookup Worksheet Functions for Engineers and Scientists

• Review of the VLOOKUP, MATCH and INDEX functions
• How to use VLOOKUP to lookup table information and merge data between multiple tables
• How to use MATCH and INDEX to perform table lookups VLOOKUP cannot
• How to use INDEX to parse large amounts of model information passed between worksheets and workbooks

Key Logical Worksheet Functions for Engineers and Scientists

• A review of logic and how to figure out logical triggers in engineering and science problems
• How to use the relational operators: <, >, <=, >=, < >, = to build logic formulas
• How to use the IF, AND, OR logical worksheet functions to build logic in engineering and science formulas
• How to use the IF and IS functions to provide error protection in your formulas
• How to create formulas that appear and disappear when needed using the IF and IS functions

How to Summarize and Count Instances in Your Technical Data Based on Logic

• How to use SUMIF and SUMIFS to summarize your worksheet data
• How to use COUNTIF and COUNTIFS to count how many instances you have in your worksheet data
• How to use string concatenation to build adaptable logic in the SUMIF(S) and COUNTIF(S) worksheet functions

How to Use Data Validation and ActiveX to Control Data Entry on a Worksheet

• What is data validation and how to use it in engineering and science
• How to control numbers, text and date entries in cells using data validation
• How to create a list in a worksheet cell using data validation, great for standardized cell entries
• What is the difference between using ActiveX and Form controls, why use them on a worksheet to control data entry
• How to create and use ActiveX combo boxes, spinners, scroll bars, option buttons, check boxes and toggle buttons on the worksheet
• How to integrate ActiveX control values in your engineering and science formulas

Worksheet Formatting and Form Construction for Engineers and Scientists

• How to use cell formatting, cell merging, pictures and drawing shapes to develop technical displays and worksheet layouts
• How to use drawing shapes to create different types of technical displays that update when cell values do
• How to design and format worksheet forms that can be used to store consistent technical data, the key to large scale data processing
• How to use worksheet protection to lock down your worksheet information ensuring data integrity

How to Import and Analyze Text Files for Engineers and Scientists

• How to analyze, parse and clean worksheet text using the text worksheet functions like RIGHT, MID, LEFT, SUBSTITUTE, TRIM, CLEAN, SEARCH and REPLACE
• How to use the Text to Columns feature to separate delimited data into separate worksheet columns
• How to use Text Wizard to import text file data on to a worksheet
• How to use the Remove Duplicates feature to retrieve unique names from a worksheet column

Using the Group, Advance Filter, AutoFilter and Sort Tools on Engineering and Science Data

• How to group worksheet rows allowing them to be hidden and displayed using the Group feature
• How to use the Sort feature to sort tables based on value, date/times and colors
• 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

How to Use Conditional Formatting to Flag Your Data

• The uses of conditional formatting
• How to use the conditional formatting feature to flag data in tables and in models
• How to use the conditional formatting feature as a dashboard system (i.e. graphical bars, icons...)

How to Create Engineering and Science Charts in Excel

• How to organize data on a worksheet that an Excel chart can read
• How to create engineering and science charts like XY Scatter, Log, Column, Line and Pie charts
• How to create limit lines on your charts
• Chart formatting strategies including overall look and color, data labels, error limits and curve fits
• How to combine two or more chart types in one chart

How to Create and Use Pivot Tables in Engineering and Science

• What are Pivot Tables and how can they be leveraged in engineering and science
• How to layout engineering and science data for use in Pivot Tables
• How to build a summary Pivot Table
• How to build an information Pivot Table
• How to use running totals, ranks and %of calculations in Pivot Tables
• How scientists and engineers can use Pivot Tables to filter data then use formulas and logic to look up their information
• How to build and use Excel Pivot Charts, slicers and timelines to quickly build visual displays

Introduction to Excel VBA Macros for Engineers and Scientists

• What is Visual Basic for Applications (VBA)?
• What are a Sub and Function procedure and how are they used in VBA?
• Overview of how to command Microsoft Excel using VBA
• What roles do variables, loops and logic play in your VBA code
• How to replace clumsy formulas and large drag and fill areas on the worksheet with custom Excel VBA worksheet functions (UDF), the key to building high end models and data tools