Advanced Excel Class for Business and Industry

Next Class: 12/07/2022

More Dates >

See the must know topics you will learn.

No time to attend, get a manual instead.

Covers the Excel topics, strategies, and methods needed to solve your Excel workplace tasks quickly.

Our manual is a combination of beginning, intermediate, and advanced Excel topics and also covers hi-tech dashboard design.
Covers everything from typing data to using functions and formulas to automated reporting using Power Query, Excel tables, and Pivot Tables.
Also discusses must know topics like problem organization; using names to pass data efficiently; and data processing.
100's of real world examples included. Learn More >
Buy a manual now, apply the price towards a class later. Manuals are included with our classes.

Key Excel topics covered 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

Class examples.

Pricing and class options.

Online Virtual Learning

Live, hands-on, talk to the instructor training
Bring your own problems to class
Online Details >

Dates / Signup >

Public In-Person Learning

Live hands-on training
Bring your own problems to class
We can supply a computer

Dates / Signup >

Onsite Hands-On Learning

We come to you and solve your problems
We cover in person in the USA and Canada
We cover the world virtually using your time zone

Details >

Detailed class syllabus.

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

Excel skills needed for our class.

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

The class details.

Audience: Business, government, industry, military, engineering, science, techs... general audience
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Hands-on, live, instructor taught training
Full Excel Manual Included: Our 2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >
Personal Examples: We review our attendees personal examples in our training instruction
Available Training Formats:

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 >

Contact us by form.

Beginning Excel for Business / Industry	Intermediate Excel for Business / Industry	Advanced Excel for Business / Industry	Excel Dashboard Design for Business / Industry	Beginning Excel VBA for Business / Industry	Excel VBA Data Processing for Business / Industry	Advanced Excel VBA for Business / Industry