Advanced Microsoft Excel Training

Our 1-day hands-on advanced Microsoft Excel training shows you advanced Excel skills that apply to creating smart adaptive Excel business tools. Our Excel training focuses on creating analysis and data processing tools using advanced formulas, array formulas, logic, ActiveX controls, Conditional Formatting, Data Validation, Pivot Tables, Pivot Charts, Excel tables, relationships, Power Query and MS Query. Our advanced Excel training also discusses the advanced aspects of these elements and how to assemble them in sophisticated ways that we use in our software designs. Our training also stresses how to create the advanced logic that allows these tools to operate autonomously and adapt to changing data. It also provides an introduction to VBA macros.

Key advanced Excel skills learned:

• Advanced methods for formula design, cell naming, table construction and workbook/worksheet linking
• How to fully leverage Excel tables and their structured references in advanced formulas and various Excel data tools
• How to add and control ActiveX controls on the worksheet and tie them into formulas
• Advanced uses of Conditional Formatting and Data Validation in Excel including how to create their adjustable logic
• How to create advanced logic for your formulas
• How to create powerful data processing formulas that use adjustable logic and ranges that adapt to changing data
• How to use array formulas to locate and process data
• How to control Excel tables and external ranges that are linked to external data
• How to create advanced business displays by combining formulas, charts, drawing shapes, Data Validation, Conditional Formatting and ActiveX controls
• How to link with and integrate business data in Excel using MS Query and Power Query
• How to create powerful Excel Pivot Tables and Pivot Charts using calculated fields, calculated items and styles
• How to create and use relationships to build Pivot Tables and Pivot Charts
• How to build Pivot Tables and Pivot Charts directly from external data sources like Access, SQL Server and text files
• An introduction to VBA macros including creating your own custom worksheet functions to replace large cumbersome worksheet mega formulas, the real calculation power house

Select this Excel training if you or your group have:

• Formatted a worksheet using colors and fonts before
• Typed in general formulas and used basic worksheet functions like VLOOKUP, IF, MAX, MIN, COUNTIF...
• Have used Conditional Formatting and Data Validation before
• Built a table before and performed drag and fill operations on formulas (little black dot bottom right hand-side of a cell)
• Used logic before in general and know the relational operators <, >, <=, >=, <>, =
• Used basic Excel features like Sort, AutoFilter and have built charts
• A desire to push Excel to its limit and know what you can do

Advanced Excel Formula, Cell Naming, Table Construction and Workbook Linking Review

• Review of advanced formula construction techniques, cell naming and debugging
• Advanced Microsoft Excel methods for naming cells and ranges, the key to making advanced Excel tools
• How to manage your cell and range names
• How to manage and link information in your workbook and between different workbooks using cell references and names
• Passing large amounts of information using array techniques, the key to building large models
• How to create proper tables in Excel that can be used with its advanced data analysis tools like Excel tables
• How to create and manage Excel tables on the worksheet and use their structured references in formulas

Creating Advanced Logic and Data Processing Formulas on the Microsoft Excel Worksheet

• Logic review including relational operators: <, >, <=, >=, < >, = and the IF, AND, OR logical worksheet functions
• How to use advanced string concatenation to build self-adjusting relational logic for functions like SUMIFS, COUNTIFS...
• How to use advanced Excel logic in your data processing formulas including how to make nested IF formulas and use NOT
• How to use advanced Microsoft Excel logic to control when formulas start and stop on your worksheet
• How to use INDIRECT, INDEX, ADDRESS, MATCH, ROW, COLUMN, OFFSET, COUNTA, cell names and string concatenation to construct formulas that adjust to changing data
• How to create dynamic adjustable cell and range names that react to changing data size using COUNTA, OFFSET and INDIRECT
• How to construct advanced Excel formulas that can automatically toggle between tables
• How to create useful formulas that merge and lookup table data

Using Microsoft Query and Power Query to Access External 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 data, replacing characters, splitting dates, cleaning text,...

Creating Visual Displays on the Excel Worksheet Using ActiveX Controls, Drawing Shapes, Data Validation and Conditional Formatting

• The difference between ActiveX controls and Form controls
• How to create and use combo boxes, spinners, scroll bars, option buttons, check boxes and toggle buttons
• How to integrate these controls with your advanced Excel formulas, charts and drawing shapes
• How to use Conditional Formatting feature in different ways to create advanced Excel displays
• How to create advanced logic formulas for use in the Data Validation and Conditional Formatting features
• How to use drawing shapes and pictures to create advanced Excel displays
• For advanced displays, see our Dashboards workshop

How to Create and Use Array Formulas to Process Data on the Excel Worksheet

• Overview of array formulas and their use
• How to construct simple array formulas that use basic relational operators and basic math
• How to create array formulas that find, return and track table information
• How to create array formulas that use logic functions like IF to sum, count, lookup, find ranges,...etc

Advanced Excel Methods for Creating Pivot Tables and Pivot Charts

• How to create a connection to a database, text file, Excel table... for use in a Pivot Table
• How to use text files and databases in your Pivot Tables
• How to create running totals, % OF, differences and ranks in your Pivot Tables
• How to create calculated fields in a Pivot Table
• How to group and ungroup in a Pivot Table
• How to create advanced formulas that reference Pivot Table data including how to use GETPIVOTDATA
• How to create custom table styles for a Pivot Table
• How to create a data model for use in a Pivot Table (Excel 2013 and above)
• How to create and use relationships from Excel tables and external data sources to build a Pivot Table and Pivot Chart
• How to create advanced Pivot Charts

Introduction to Excel VBA Macros

• What is VBA?
• What are a Sub and Function procedure and how are they used?
• How to command Excel using VBA
• What are variables, loops and logic and how do you use them in your computer code
• How to create custom worksheet functions that replace your mega formulas on the worksheet