Advanced Microsoft Excel
for Business Training

Our 1-day hands-on advanced Microsoft Excel training shows you advanced Excel skills that apply to creating smart adaptive Excel business tools. Key topics include:

• Excel formula, cell naming, table construction, excel tables, and workbook linking review
• Creating advanced logic and data processing formulas for business data
• Using power query to access, merge, and shape business data
• Creating user interfaces (UI's) to visualize and control your business data
• Using array formulas to process complex business data sets
• Creating advanced PivotTables and PivotCharts that summarize and view business data
• Introduction to Excel VBA Macros and custom VBA worksheet functions (UDF's)

Select this Excel training if you or your group have:

• Formatted a worksheet using colors, fonts, borders, alignments, number formats...
• Typed in formulas and used worksheet functions like VLOOKUP, IF, MAX, MIN, COUNTIF...
• Built a worksheet table before and performed drag and fill operations on formulas
• Used logic before in formulas and know the relational operators <, >, <=, >=, <>, =
• Used basic Excel data tools before like Sort and AutoFilter
• Built and formatted charts
• Built basic PivotTables and PivotCharts

Advanced Formula Design, Cell Naming, Tables, Excel Tables, and Workbook Linking Review

• Advanced formula design, formula fill downs, nesting, and debugging methods
• How to create, edit, and delete cell and range names in Excel
• Managing and linking information between worksheets and workbooks using cell references and names
• Designing worksheet tables that can be used with various Excel data tools
• What are Excel tables and how are they used to make adaptable business formulas that analyze data?
• Creating and managing Excel tables on the worksheet and using their structured references in formulas
• Using slicers and quick data tools on Excel tables to rapidly filter their business data
• How to use string concatenation in formulas to calculate text reacting to user interface selections

Creating Advanced Logic and Data Processing Formulas for Business Data

• Logic review including relational operators: <, >, <=, >=, < >, = and using the IF, AND, and OR logical worksheet functions
• Review of using the SUMIFS, COUNTIFS, MAXIFS... worksheet functions to summarize and count business data based on logic
• Using the COUNTIFS and IFS worksheet functions to test and see if data exists in worksheet tables
• Using string concatenation to build self-adjusting logic criteria for functions like SUMIFS, COUNTIFS..., great for rapidly dragging and filling formulas in reports
• Developing advanced logical tests for your IF functions that analyze parts of text, parts of dates, test for data existence... in business data
• Creating advanced logical tests for your IF functions that control when formulas start and stop calculating over a worksheet range
• Creating advanced logical formulas using the IF and IS functions that determine if cells are blank, contain numbers, contain text, have errors...
• How to toggle table columns and entire tables in your business formulas thereby making one worksheet handle multiple business tables
• Using string concatenation, various worksheet functions and formulas to merge, clean, and parse business data in worksheet columns
• Performing advanced table lookups and nested lookups using the IFS, VLOOKUP, MATCH, INDEX, ADDRESS, OFFSET, COUNTA, ROW, and Column worksheet functions
• How to create a dynamic name from formulas, the key to developing adaptive visual dashboard tools in Excel

Using Power Query to Rapidly Access, Merge, and Shape Business Data

• What is Power Query and how is it used to rapidly shape (filter, parse, clean, trim, merge...) business data on the worksheet?
• How to refresh a Power Query worksheet table at the click of a button
• Using Power Query to link to business data within a workbook
• Using Power Query to link to business data in external workbooks, text files, and databases
• Performing various Power Query shaping tasks like merging business data, filtering data, replacing characters, splitting dates, cleaning text...
• Design strategies for using Power Query to filter data through a series of tables so the final table can be used in calculations, charts, and PivotTables
• Performing formula calculations inside a Power Query table

Creating User Interfaces (UI's) to Visualize and Control Your Business Data

• What is the difference between ActiveX controls and Form controls?
• Creating and using the combo box, spinner, scroll bar, option button, check box, and toggle button controls to control user entry
• Overview of using charts and UI's to visualize your business data
• Integrating controls with your Excel formulas and charts to create adaptive data visualization displays
• Using Data Validation to create a list in a cell and to control number, date, and text entry in a cell
• Using Conditional Formatting feature in different ways to create advanced business data displays
• Creating advanced logic formulas in Conditional Formatting, key to advanced business data displays
• Dynamically linking drawing shapes to cells and using linked pictures to create advanced business data displays

Using Array Formulas to Process Complex Business Data Sets

• What is an array formula and how are they used to process business data?
• How simple array formulas can be used to pre-process data for use in functions like VLOOKUP, MATCH, SUM, MAX, MIN, AVERAGE...
• Creating Excel array formulas that process complex logic data sets using the IF worksheet function
• Using array formulas to clean, replace, merge, and split business data while simultaneously processing it
• Creating Excel array formulas that perform advanced multi-condition lookups and table merges

Creating Advanced PivotTables and PivotCharts That Summarize and View Business Data

• Creating a connection to a database, text file, Excel table... for use in a PivotTable / PivotChart
• Creating running totals, % OF, differences, ranks... in your PivotTables
• Creating advanced formulas that reference PivotTable data including how to use GETPIVOTDATA
• Creating custom table formats for a PivotTable
• Creating a data model for use in a PivotTable (Excel 2013 and above)
• Creating and using relationships from Excel tables and external data sources to build PivotTables and PivotCharts
• Using slicers, timelines, PivotTables, and PivotCharts to rapidly construct simple dashboards

Introduction to Excel VBA Macros and Custom VBA Worksheet Functions (UDF's)

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