Advanced Excel for Business and Industry Class

How our class will boost your Excel skills.

Our 1-day class shows you how to build powerful self-adjusting Excel tools and reports. Our class starts off with how to use Power Query along with Excel tables; various filtering functions; and spill over arrays to quickly create autonomous reports.

In addition, our training shows how to build advanced lookups; logic formulas; and array formulas to solve tough data problems. Also discussed is how to use LAMBDA to create your own custom worksheet functions.

It also shows how to combine PivotTables, Pivot Charts, Conditional Formatting, shapes, and linked pictures to rapidly build dashboards and reports.

Read our detailed outline > below and see what you'll discover in our class.

Take a video tour of what you'll learn.

Rapidly parsing reports using Excel tables, names, spill over arrays, and the UNIQUE, SORT... functions
Building report templates with formulas and charts that self-adjust to typed, pasted, or external data
Using Power Query to rapidly import, merge, and shape data from workbooks, text files, and databases
Processing data with multiple conditions using the IFS, AND, OR... functions
Using the Text, Date/Time, Math... functions to build complex logic criteria for formulas
Using LAMBDA to create custom worksheet functions that replace large formulas
Using Excel tables, slicers, structured references, and charts to quickly build report dashboards
Building self-adjusting logic for the SUMIFS, COUNTIFS... worksheet functions
Creating worksheet interfaces to control your Excel data tools using Data Validation and ActiveX Controls
Using Conditional Formatting, shapes, charts, and formulas to build advanced data displays
Full, partial, and smart table lookups using the VLOOKUP, INDEX, INDIRECT... functions
Using array formulas to quickly complex datasets, perform multi-condition lookups, and filter data
Using slicers, timelines, PivotTables, Pivot Charts, and Excel tables to construct adaptive reports

Some key topics we cover in class.

4 ways to train.

Virtual

View the dates, signup, and pricing for our hands-on live virtual training.
Virtual Signup > Learn More >

In-Person

View the dates, signup, and pricing for our hands-on in-person training.
In-Person Signup > Learn More >

Manual

Can't take the class, buy the self-study manual, apply the price later.
View Manual >

Onsite

Learn how we can help your business save time and effort in Excel.
View Onsite Details >

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.

Download Class Brochure

Building Self-Adjusting Reports Using Functions, Excel Tables, Spill Over Arrays

Linking data from different sheets, the foundation to building adaptive reports
New ways to use cell reference notation in data formula construction to access data
Different ways to rapidly link data between workbooks and worksheets using cell references, names, and functions
Using Excel tables, structured references, and spill over arrays to rapidly parse report data
Using functions like UNIQUE, FILTER, CHOOSECOLS, VSTACK... to rapidly parse report data
Building report templates with formulas and charts that automatically adjust to pasted or external data

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

Using Power Query to link with and process data from a workbook, text file, database...
Performing various Power Query shaping tasks like filtering data, sorting, splitting dates, cleaning text...
How to merge and append different queries in Power Query
How to add existing Excel tables to Power Query, combine them, and process them
How to group data in Power Query and sum, count, average...
Performing formula calculations inside Power Query

Using Advanced Formulas, Logic, and LAMBDA to Quickly Analyze Data

Review of using the relational operators (<, >,>=,<>,<=,=) to build logic for formulas
How to use concatenation, operators, and names to build adaptive logic for SUMIFS, COUNTIFS...
Using the IFS, AND, OR... functions to build multi-condition logic for your data problems
Using the DSUM, DMIN, DMAX... functions to quickly calculate table data
Using Text, Date, Math... functions to build logic criteria for your formulas
Developing advanced logical tests for your IF, DSUM... functions that analyze text, dates, spaces...
Using LAMBDA to store large formulas that you can rapidly use as worksheet functions

Visualizing Your Report Data and Controlling Your Data Tools

Using slicers, Excel tables, and charts to setup an instant dashboard
Using Conditional Formatting to flag data, auto-format tables, and create dashboard displays
Using Data Validation and ActiveX controls to control user entry on a worksheet
Dynamically displaying cell values in shapes and creating linked pictures that display data
How to combine these features to quickly control and view report data

Building Powerful Lookups to Track and Find Your Data

Using concatenation and text functions to build adaptive lookup values for VLOOKUP, XLOOKUP...
How to clean up (TRIM, CLEAN...) data in a formula then use it in a lookup
How to build multi-table VLOOKUP, XLOOKUP... problems
Using structured references to lock onto part of an Excel table, great for partial table lookups and returns
Using INDIRECT, OFFSET, and string concatenation to automatically toggle between and find data in tables

Using Array Formulas to Solve Complex Worksheet Calculations

What is an array formula and how can they make your life easier?
How to tell when a worksheet function or formula can take an array and produce an array
Using simple array formulas to clean, replace... data for functions like VLOOKUP, SUM...
Creating array formulas that process complex datasets using logic
Creating array formulas that perform multi-condition lookups
How to use array formulas and spill over arrays to filter data for schedules, timelines...

Using PivotTables and Pivot Charts to Create Advanced Reports

Building the Excel table that your PivotTable will use
How to create various running totals, counts, ranks, % totals... for a PivotTable
Using PivotTables to perform calculations, statistics, and build various report tables
How to use the GetPivotData function to retrieve PivotTable data
Setting up a quick dashboard using slicers, timelines, Pivot Charts, and formatting

Additional Class Information

Prerequisites for our advanced Excel training.

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

Class time, 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: 2013-Present, Microsoft Excel 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

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 >

Form >

Advanced Excel for Business and Industry - Class