Microsoft Excel Essentials Training

Our 1-day hands-on Microsoft Excel essentials training for engineers and scientists shows you how to use Microsoft Excel to build data processing tools, visual tools and models for engineering and science. In the areas of both modeling and data processing it demonstrates how to organize and transfer information using names, use key worksheet functions to build Excel formulas, how to build Excel array formulas, how to build Excel logical formulas and how to build displays using ActiveX controls, charts and graphics. Also demonstrated is how to use Excel data processing features including pivot tables, Microsoft Query and Excel tables. It also provides an introduction to Excel VBA macros by demonstrating how to build custom worksheet functions.

Key Excel skills learned:

• A review of how to construct Excel worksheet formulas and debug them
• Guidelines for engineers and scientists to follow when naming worksheet cells and ranges, the key to building and managing Excel models and data processing tools
• Methods to correctly organize and store large amounts of engineering and science data on worksheets, a key component in your Excel models and data tools
• Methods for using ActiveX controls with Excel logical formulas to control data tools and models
• How to build Excel displays using ActiveX controls, Data Validation, Conditional Formatting, drawing shapes, pictures and charts
• How to create and use logical formulas in data processing tools and models
• Methods for using the IF, AND, OR, IS, MATCH, INDEX, INDIRECT... functions to create Excel formulas that can think, adapt and find changing data and model information
• How to create Excel array formulas that analyze vast amounts of engineering and science data
• Methods for developing Excel table lookup formulas including formulas that can toggle between tables, key capabilities for modeling and data processing
• How to import various text file formats into Excel and deal with less than ideal text
• How to use MS Query and Excel tables to link with databases and access Excel workbooks and text files using SQL
• How to build Excel Pivot Tables and Pivot Charts that analyze engineering and science data
• How engineers and scientists can use Excel features to dynamically link Excel with Word and PowerPoint, the key to building simple but effective report generation tools
• How to deploy numerical analysis methods on the worksheet
• How to use Solver to find an optimum solution for an engineering or science model
• Introduction to Excel VBA including how to build custom worksheet functions (UDFs)

Select this Excel training if you or your group have:

• Know how to move around Excel's Ribbon interface and have used Excel on a regular basis
• Know what a worksheet an cell reference are
• Formatted a worksheet before
• Typed in basic formulas and have been exposed to basic worksheet functions like SUM, AVERAGE....
• Have a desire to use Excel in ways you never thought possible

Excel Methods for Naming, Table Construction, Modeling and Data Distribution

• Excel formula construction, debugging and worksheet linking review
• Guidelines for engineers and scientists when naming cells and ranges
• Excel methods for naming cells and ranges and using them to track model and data information
• How to manage your cell and range names in your models and data tools
• Excel methods for organizing, managing and transmitting large amounts of information in your workbooks
• Guidelines for scientists and engineers to follow when designing and building Excel tables that will store vast amounts of data
• Methods for proper cell and range naming that will help you later create VBA tools that can manage your Excel data tools and models
• Excel methods for large model layout and organization

Excel Formula Design for Scientists and Engineers

• Logic, the key to Excel data and modeling tool construction
• Overview of how to use logic in your tool construction, figure out the strategies and figure out logical triggers
• A review of how to build logic conditions for IF functions using the relational operators (<, >, <=, >=, < >, =) and the IS, COUNTIF, COUNTIFS,LEFT,RIGHT,MID and other various worksheet functions
• How engineers and scientists can build multi-condition Excel logic formulas using the IF, AND, OR worksheet functions and nesting techniques
• Overview of how scientists and engineers can use array formulas to analyze large amounts of 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 data using the IF function
• How engineers and scientists can create Excel array formulas that perform lookups and table merging using MATCH, VLOOKUP, HLOOKUP, INDEX, ROW, COLUMN...
• How to write Excel formulas that adjust to changing data using Excel tables and adjustable name formulas, the key to developing self-adjusting Excel data tools, displays and models
• How engineers and scientists can construct Excel formulas that can automatically toggle between tables and other worksheet information using the INDIRECT functions
• How to create Excel formulas that extract information from cell and range names using INDEX and MATCH
• How to use string concatenation in Excel formulas, the key to creating adaptable displays for models and data tools

Excel Dashboard Design for Engineers and Scientists

• In Excel visual displays, what is the difference between using ActiveX controls and Form controls
• How to create and use ActiveX combo boxes, spinners, scroll bars, option buttons, check boxes and toggle buttons on the worksheet
• How Excel formulas integrate ActiveX controls using logic
• How to use Excel formulas and Data Validation to control user input in Excel models and data tools
• How engineers and scientists can build Excel visual displays using Conditional Formatting and sophisticated logic
• How scientists and engineers can build Excel formulas directly in charts making them adaptive to changing data and ActiveX control inputs
• How engineers and scientists can use drawing shapes and pictures to create Excel displays that react to and display data and model information
• How to assemble Excel dashboards using Data Validation, Conditional Formatting, drawing shapes, charts and ActiveX controls together

Numerical Analysis Worksheet Construction Methods for Engineers and Scientists (Class Vote on Topics)

• How engineers and scientists can perform integration and differentiation on the worksheet
• How scientists and engineers can solve a system of linear equations on the worksheet
• How to solve boundary value problems on the worksheet
• How scientists and engineers can find the roots of a non-linear equation using Goal Seek
• How engineers and scientists can solve a system of first order differential equations on the worksheet
• How to use Solver for design optimization purposes
• How engineers and scientists can use the Analysis Toolpak

Linking Excel to External Data, Text Files, Word, and PowerPoint

• Overview of using Excel features to integrate different data sources
• How to import various data text file formats into Microsoft Excel using Text Wizard and use the Text functions LEFT, RIGHT, MID, SUBSTITUTE, REPLACE, TEXT, SEARCH and LEN to parse
• How to use Text to Columns to parse text information copied onto Excel worksheets
• How to use Microsoft Query and Power Query along with Excel tables to connect with workbooks, text files and databases
• How to dynamically link Excel with Word and PowerPoint to build simple but effective report generation tools

How to Create Excel Pivot Tables for Engineering and Science

• Excel pivot table construction for engineers and scientists
• How scientists and engineers can use pivot tables to filter data then use formulas and logic to look up their information
• How engineers and scientists can build Excel formulas that use pivot table data
• How to use Excel Pivot Table features to create running totals, ranks and %of calculations in pivot tables
• How engineers and scientists can use Excel pivot charts, slicers and timelines to 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