Advanced Excel Class for Business and Industry

Class overview.

What makes our training different?

Live, hands-on, industry focused topics
Bring your own problems, ask tough questions
Taught by an Excel software developer
Limited class size

Our 1-day class covers must know topics like Excel tables; passing data between sheets / workbooks; key formulas / functions; and advanced data filtering. Our class also covers unique topics like adaptive data formulas; advanced logic; using ActiveX controls / UIs; and using array formulas to analyze corrupted datasets.

We’ll also show you how to combine Power Query, PivotTables, Pivot Charts, and all of the above to build automated reports that automatically filter, format, summarize, and update data from multiple sources. Join us and our class will show you how to build automated analysis tools that will make your job a whole lot easier. See the Video >

Some key topics we cover in class.

Using relative, mixed, and absolute references to perform various formula fills
How to manage and use cell / range names to rapidly build analysis formulas and link data
Using Excel tables, slicers, structured references, and charts to quickly build adaptive analysis tools
Creating worksheet UIs to control your Excel tools using Data Validation, ActiveX Controls, and shapes
Building adaptive formulas that react to changing logic and data using names, structured references, concatenation, and functions
Using Power Query to rapidly import, merge, and shape data from workbooks, text files, and databases
Dynamically changing cell format and placing bars, arrows, circles... in cells using Conditional Formatting and logic
Creating advanced logic for your formulas and Conditional Formatting
Using array formulas to analyze complex worksheet data sets and perform multi-condition lookups
Using slicers, timelines, PivotTables, Pivot Charts, and Excel tables to construct adaptive data reports
How to link Excel with Word and PowerPoint to create dynamic reports

Prerequisites for our Excel training.

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 Pivot Charts

Class time, duration, manual, and repeats.

Audience: Business, government, industry, military, engineering, science, techs...
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Live, hands-on
Class Manual: Our complete Excel manual for business and industry (TCEM). Details >
Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

How we run the class:

We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Advanced Formula Design, Names, and Linking Data

Using operators, relative, mixed, and absolute cell references to build analysis formulas
Building formulas that drag and fill horizontally and vertically using relative and mixed references
How does Excel really store numbers, text, and dates and how they affect your formulas
Using range and cell names in your formulas to easily track data
Passing data between workbooks and worksheets using cell references and names
Using the relational operators: <, >, <=, >=, < >, = to build basic logic formulas
How to use string concatenation to merge worksheet text data and date time values

Using Excel Tables to Quickly Build Automated Analysis Tools

Using an Excel table's quick analysis tools and slicers to report table data
How to create formulas in Excel tables that reference other worksheet and Excel tables
Using structured references to build formulas that adapt to changing data size and arrangement
Using structured references to lock onto part of an Excel table, great for lookups
Piggy backing names on top of Excel tables so they self-adjust, alternate to structured references
Designing Excel tables and Slicers to quickly control charts in a dashboard

Creating Worksheet UIs from Data Validation, ActiveX Controls, and Shapes

Using Data Validation to create cell drop down lists and control number, date, and text entry in a cell
Creating adjustable Data Validation cell drop lists using names
What is the difference between ActiveX controls and Form controls?
Creating and using check box, spinner, scrollbar... controls to control user entry on the worksheet
Dynamically displaying cell values in shapes and creating linked pictures that update

Building Adaptive Data Analysis Formulas that Lookup, Sum, or Count

How adaptive formulas will help you get rid of duplicated worksheets, extra columns, extra formulas...
Review of nesting formulas and functions to create self-adjusting formulas
What is string concatenation and how is it used to quickly build self-adjusting formulas?
Using concatenation to build names, cell references, and logic from user selections and data
Using concatenation and text functions to build adaptive lookup values for VLOOKUP, XLOOKUP...
Creating adaptive logic for SUMIFS, COUNTIFS... from selections, data, and concatenation; great for fill downs with changing logic
Using INDIRECT and OFFSET to automatically toggle between tables, columns, cells... in your formulas and functions

Using Power Query to Rapidly Access, Merge, and Shape Your Worksheet Data

What is Power Query and how is it used to import and rapidly shape (filter, parse, clean, trim, merge...) worksheet data?
How to refresh a Power Query table at the click of a button and update its source
Using Power Query to link with and process data in a workbook, text file, and database
Performing various Power Query shaping tasks like merging data, filtering data, replacing characters, sorting, 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, reports...
Performing formula calculations inside a Power Query table

Creating Advanced Logic Formulas and Using Conditional Formatting to Flag Data

Logic review including relational operators: <, >, <=, >=, < >, = and using the IF, AND, OR... logical worksheet functions
Creating logical formulas using the IF and IS functions that determine if cells are blank, contain numbers, contain text, have errors...
Using the IFS function to evaluate an IF, ELSEIF, ELSEIF... type of logic decision
Developing advanced logical tests for your IF functions that analyze parts of text, parts of dates, test for data existence... in worksheet data
How to use Conditional Formatting and logic to automatically flag worksheet data with colors, icons, bars....
How to create a Conditional Formatting rule that formats a cell based on value or formula
Modifying and deleting Conditional Formatting rules

Using Array Formulas to Process Complex Worksheet Data Sets

What is an array formula and how are they used to analyze worksheet data?
How to tell when a worksheet function or formula can take an array and produce an array
How simple array formulas can be used to pre-process data for use in functions like VLOOKUP, MATCH, SUM, MAX,...
Creating array formulas that process complex logic data sets using the IF worksheet function
Using array formulas to clean, replace, merge, and split worksheet data while simultaneously processing it
Creating array formulas that perform multi-condition lookups and return entire rows and columns from tables

Creating Advanced PivotTables and Pivot Charts That Summarize and View Your Data

Creating a connection to a database, text file, Excel table... for use in a PivotTable / Pivot Chart
Creating running totals, % OF, differences, ranks... in your PivotTables
Creating formulas that reference PivotTable data including how to use the GETPIVOTDATA function
Creating and using relationships from Excel tables and external data sources to build PivotTables and Pivot Charts
Using slicers, timelines, PivotTables, and Pivot Charts to rapidly construct dashboards
Getting creative with PivotTables and using them for other things besides summarizing data

Linking Excel with Word / PowerPoint to Quickly Build Reports

Using Excel with Word or PowerPoint to make dynamic reporting tools
Linking cell values, ranges, and charts to a Word document
Linking cell values, ranges, and charts to a PowerPoint presentation
How to save your linked files, open them, and break those links when emailing

If you need to contact us about our class.

Phone Number: 1.805.498.7162

Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT

You can email us at info@emagenit.com >

Advanced Microsoft Excel for Business and Industry

Stop manually making that weekly Excel report over and over again.