Advanced Microsoft Excel Training for Engineers and Scientists

Our 1-day hands-on advanced Microsoft Excel training for engineers and scientists shows you how to use Microsoft Excel to build advanced 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 advanced Excel formulas, how to build advanced Excel array formulas, how to build advanced Excel logical formulas and how to build advanced displays using ActiveX controls, charts and graphics. Also demonstrated is how to use advanced Excel data processing features including advanced pivot tables, Microsoft Query and Excel tables. It also provides an introduction to Excel VBA macros. Taught by an engineer.

Our advanced Excel training public events are held in Thousand Oaks, CA. Start time is 8:30 AM PT and end time is 4:30 PM PT. Thousand Oaks is in the vicinity of Simi Valley, CA; Oxnard, CA; Ventura, CA; Santa Barbara, CA; Carpentaria, CA; Calabasas, CA; Agoura Hills, CA; Studio City, CA; Sherman Oaks, CA; Encino, CA; Woodland Hills, CA; Westwood, CA, Santa Paula, CA; Valencia, CA; Santa Monica, CA and Los Angeles, CA. Santa Barbara county, Ventura county, Los Angeles county and Orange county are within range of Thousand Oaks. Our Advanced Excel training is also held live online and is broadcast all over the US, Canada, Mexico and the Caribbean. We can also broadcast to any part of the globe as an onsite. Our in person onsites are conducted in the US, Canada and Europe.

Key advanced Excel skills learned:

• A review of how to construct advanced 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 advanced Excel models and data processing tools
• How to use advanced Excel for engineers and scientists methods to correctly organize and store large amounts of data on worksheets, a key component in your advanced Excel models and data tools
• Advanced Excel for scientists and engineers methods for using ActiveX controls with advanced Excel logical formulas to control data tools and models
• How engineers and scientists can build advanced Excel displays using ActiveX controls, Data Validation, Conditional Formatting, drawing shapes, pictures and charts
• How scientists and engineers can create and use advanced Excel logic in data processing tools and models
• Advanced Excel for engineers and scientists methods for using the IF, AND, OR, IS, MATCH, INDEX, INDIRECT... functions to create advanced Excel formulas that can think, adapt and find changing data and model information
• How engineers and scientists can create advanced Excel array formulas that analyze vast amounts of Excel data
• Advanced Excel for scientists and engineers methods for developing advanced 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
• Advanced Excel for engineers and scientists methods for building advanced Excel Pivot Tables and Pivot Charts
• How engineers and scientists can use advanced Excel features to dynamically link Excel with Word and PowerPoint, the key to building simple but effective report generation tools
• Advanced Excel for scientists and engineers methods for performing numerical analysis on the worksheet and for using Solver
• 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 consistently
• Formatted a worksheet
• Typed in general formulas and used basic worksheet functions like IF, SUM, SUMIF....
• Built a table before and performed drag and fill operations on formulas
• Have a desire to use Excel in ways you never thought possible

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

• Advanced Excel formula construction, debugging and worksheet linking review
• Guidelines for engineers and scientists when naming cells and ranges
• Advanced 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
• Advanced 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 advanced 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
• Advanced Excel methods for large model layout and organization

Advanced Excel Formula Design for Scientists and Engineers

• Logic, the key to advanced 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 advanced 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 advanced 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 advanced Excel array formulas that process large amounts of data using the IF function
• How engineers and scientists can create advanced Excel array formulas that perform advanced lookups and table merging using MATCH, VLOOKUP, HLOOKUP, INDEX, ROW, COLUMN...
• How to write advanced 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 advanced Excel formulas that can automatically toggle between tables and other worksheet information using the INDIRECT functions
• How to create advanced Excel formulas that extract information from cell and range names using INDEX and MATCH
• How to use advanced string concatenation in Excel formulas, the key to creating adaptable displays for models and data tools

Advanced Excel Dashboard Design for Engineers and Scientists

• In advanced 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 advanced Excel formulas integrate ActiveX controls using logic
• How to use advanced Excel formulas and Data Validation to control user input in Excel models and data tools
• How engineers and scientists can build advanced Excel visual displays using Conditional Formatting and sophisticated logic
• How scientists and engineers can build advanced 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 advanced Excel displays that react to and display data and model information
• How to assemble advanced 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 advanced 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 Advanced Excel Pivot Tables for Engineering and Science

• Advanced 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 advanced Excel formulas that use pivot table data
• How to use advanced Excel Pivot Table features to create running totals, ranks and %of calculations in pivot tables
• How engineers and scientists can use Advanced Excel pivot charts, slicers and timelines to build advanced 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

• Audience: Engineers, Scientists and Technicians
• Time: 8:30 AM - 4:30 PM
• # of Days: 1-day
• Type: Hands-on, live, instructor taught training
• Manual: 2000 + page Microsoft Excel Aided Engineering and Science manual full of Excel tips and how-to-do topics included with the training
• Platform: Windows 7 - Windows 10, Mac users welcome
• Excel Versions: 2007-2016, Windows and Mac
• Free Repeats: Repeat your Excel training as many times as you like, no date or limit, click here for details
• Examples: We review our attendees personal examples in our training instruction
• Training Formats:
  • Public Training Workshop
  • Live Online Course. We broadcast our Excel public training as a live hands-on online courses so even though you cannot travel to us, you can still take the course as if sitting in the room with us
  • Company Onsite
  •  For self-study, purchase Microsoft Excel Aided Engineering and Science. For company onsite information, please contact us 1.866.924.6244
• Additional Training:
  • Beginning Excel VBA
  • Intermediate Excel VBA
  • Advanced Excel VBA
  • Microsoft Excel Dashboards