Advanced Excel for Business and Industry - Class

Class overview.

Our live, hands-on, 1-day class shows you the key Excel elements you need to start building powerful self-adjusting reporting tools right away.

It covers topics like auto-parsing report data using FILTER, UNIQUE... and spill over arrays and building report templates with formulas that auto-adjust to data. Also covered is using self-adjusting logic to quickly analyze data and performing lookups that track data across multiple workbooks and tables. Our class also discusses how to visualize data in new ways using shapes, charts, Conditional Formatting, and linked pictures.

Also discussed is how to use array formulas to solve multi-step data problems and use LAMBDA to create custom worksheet functions that can be rapidly entered to process data. Last it will show you how to use Power Query, PivotTables, Pivot Charts, Excel tables, and formulas to build automated reports that automatically filter, summarize, and update their data.

Join us and our class will show you how to put it all together and build those flexible report tools that will make your job a whole lot easier.

Some key topics we cover in class.

• Rapidly parsing reports using Excel tables, names, spill over arrays, and the UNIQUE, FILTER, Sort... functions
• Building report templates with formulas and charts that self-adjust to typed, pasted, or external data
• Using Excel tables, slicers, structured references, and charts to quickly build report dashboards
• Building self-adjusting logic for SUMIFS, COUNTIFS... that process data with varying logic conditions
• Processing data with multi-decision and multi-condition logic using the IF, IFS, AND, OR... functions
• Using the Text, Date/Time, Math... functions to build powerful logic formulas
• Using Conditional formatting to auto-format reports and build visual displays
• Creating worksheet interfaces to control your Excel data tools using Data Validation and ActiveX Controls
• Performing advanced lookups on partial and multiple tables using the VLOOKUP, XLOOKUP, INDEX... functions
• Performing advanced lookups that toggle between tables and columns using INDIRECT, OFFSET, and string concatenation
• Using Power Query to rapidly import, merge, and shape data from workbooks, text files, and databases
• Using array formulas to quickly analyze multi-step data problems and perform multi-condition lookups
• Using LAMBDA to create custom worksheet functions that can be rapidly entered to process data
• Using slicers, timelines, PivotTables, Pivot Charts, and Excel tables to construct adaptive reports

Est 1998. If you haven't heard of us before, here's some of our customers from the past 25 years.

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

Get an instant class discount. Signup for our Exceleration newsletter and get a discount that you can apply instantly to any purchase.

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 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >

Class syllabus.

Class Syllabus

Building Self-Adjusting Formulas, Tables, and Filters to Link and Report Data

• 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 Logic Formulas and LAMBDA to Quickly Analyze Data

• Review of using the relational operators (<, >,>=,<>,<=,=)  to build logic for formulas
• How to use string concatenation, relational operators, and names to build adaptive logic for SUMIFS, COUNTIFS...
• Using the IFS, AND, OR,... functions to build multi-decision and 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 for your formulas
• Developing advanced logical tests for your IF, DSUM... functions that analyze parts of text, dates, numbers, 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 displays
• Using logic formulas in Data Validation that automatically adjust to constrain user input
• Creating 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 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, replacing characters, 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 Array Formulas to Solve Multi-Step Data Problems in One Formula

• 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, split... worksheet data for functions like VLOOKUP, XLOOKUP, SUM, MAX,...
• Creating array formulas that process tough data sets using the IF worksheet function
• Creating array formulas that perform multi-condition lookups

How to use Power Query, 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 and build various report tables
• How to use the GetPivotData function to retrieve PivotTable data
• How to use Power Query to create relationships, perform calculations, and create a PivotTable
• Setting up a dashboard using slicers, timelines, Pivot Charts, and formatting

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 >