Advanced Microsoft Excel Training

Our 1-day hands-on advanced Microsoft Excel training for business professionals shows you advanced Excel skills that apply to creating smart adaptive Excel business tools. Our advanced Excel training focuses on creating Excel analysis and data processing tools using advanced formulas, array formulas, logic, ActiveX controls, conditional formatting, data validation, pivot tables, pivot charts, Excel tables, relationships, and MS Query. Our advanced Excel training also discusses the advanced aspects of these elements and how to assemble them in sophisticated ways including modeling. 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 macros (VBA). Excel for Mac users welcome.

Our Advanced Excel public training events are held in Thousand Oaks, CA. Our training starts at 8:30 AM PT and runs to 4:30 PM PT. We are 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. Our customers commute from Santa Barbara county, Ventura county, Los Angeles county and Orange county, anything farther it is recommended you stay the night before. We also hold our Advanced Excel training live online and broadcast it all over the US, Canada, Mexico and the Caribbean. We can also broadcast to any place in the world as an onsite. For our onsites, we travel all over the US, Canada and Europe.

Key advanced Excel skills learned:

• Advanced excel review of formula design, cell naming and workbook/worksheet linking
• Advanced excel techniques for constructing Microsoft Excel displays using ActiveX controls on the Excel worksheet
• Advanced methods for creating complex business displays using advanced formulas, charts, drawing shapes, Data Validation, Conditional Formatting and ActiveX controls
• How to create advanced data processing formulas that use advanced logic and can adjust to changing data automatically
• How to build advanced logic for Conditional Formatting and Data Validation
• Advanced Excel construction methods for building and using array formulas
• How to create and use Excel tables that can be used with advanced formulas and visual displays
• Advanced Excel methods for integrating business data in Excel by linking with external data using MS Query and Power Query
• How to create advanced Excel pivot tables and pivot charts including calculated fields, calculated items and styles
• How to create and use relationships to build advanced pivot tables and pivot charts
• Advanced Excel skills for building 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

Creating and Using Excel Tables to Process and Manage Data on the Worksheet

• What is an Excel table and how to use them to manage data and enhance data processing tasks
• How to create an Excel table using worksheet data
• How to add, edit and delete Excel table data
• How to control Excel tables and use their quick summary tools to analyze data
• How to format Excel tables including creating custom table styles
• How to create calculated columns in Excel tables
• How to create formulas that automatically adjust to data resizing using Excel tables
• How Excel tables are used with pivot tables and in relationships
• Advanced Excel strategies for storing data in workbooks

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 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 string concatenation to build self-adjusting relational logic (i.e. ">=" & A1)  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

Creating Visual Displays on the Excel Worksheet Using ActiveX Controls, Drawing Shapes 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

Advanced Excel Methods for Creating and Using 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

• Audience: Business Professionals
• Time: 8:30 AM - 4:30 PM
• # of Days: 1-day
• Type: Hands-on, live, instructor taught training
• Manual: 2000 + page Microsoft Excel-Aided Business 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 Business Essentials. 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