Build reports that adapt to chainging data.
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,
- Building report templates with
formulas and charts that self-adjust to typed, pasted, or
- Using Excel tables, slicers,
structured references, and charts to quickly build report
- Building self-adjusting logic for
SUMIFS, COUNTIFS... that process data with varying logic
- Processing data with
multi-decision and multi-condition logic using the IF, IFS, AND,
- 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...
- Performing advanced lookups that
toggle between tables and columns using INDIRECT, OFFSET, and
- 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
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...
- 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
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, training, and price options.
Building Self-Adjusting Formulas, Tables, and
Filters to Link and
- 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
Using Logic Formulas and LAMBDA to Quickly
- 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,
- 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,
- 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
displaying cell values in shapes and creating linked pictures that
- How to combine these features to
quickly control and view report data
Building Powerful Lookups to Track and Find
- 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,
- Using structured references to
lock onto part of an Excel table, great for partial table lookups
- Using INDIRECT, OFFSET, and string
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
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
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
- Setting up a dashboard using slicers, timelines, Pivot Charts,
If you need to contact us about our
Phone Number: 1.805.498.7162
Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT
You can email us at email@example.com >
Need Help? Please call us at 1.805.498.7162
Copyright © 2002-2023
EMAGENIT All Rights Reserved