Class for Business and Industry
More Dates >
Learn to assemble Excel's "pre-made" features to create powerful hi-tech business / industry dashboards
that integrate, analyze, and visualize data.
View your Data - front & center.
How our training can help you.
Our 3-day workshop shows you how to create adaptive Excel dashboards that allow you to interact with and visualize your data in innovative ways.
It will show you the Excel components you need to analyze and integrate your dashboard data like key functions, filters, links, Excel tables, Power Query, and UIs. Then it will
show you how to build various dashboard elements that you can organize to display your strategic, tactical, analytical… information.
Join us and we'll show you how to put all the
Excel pieces together to produce that hi-tech dashboard that fits your exact needs.
Key Excel dashboard topics covered.
- Key dashboard formula design, creating
names, and linking workbooks and worksheets
- Storing your dashboard data in worksheet
and Excel tables and filtering them
- Designing logic formulas to process
dashboard data and interpret UI selections using functions like IF,
- Summing, counting, and analyzing
worksheet data using functions like SUMIFS, COUNTIFS, LEFT, YEAR...
- Using ActiveX controls, Form controls,
and Data Validation to build user interfaces (UIs) that control your
- Performing advanced lookups in your
dashboard data using concatenation, text functions, and functions
like VLOOKUP, XLOOKUP...
- Automatically importing, integrating,
and shaping external dashboard data using Power Query
- Creating array formulas that process complex data patterns
and perform multi-value lookups
- A review of the major dashboard types,
benchmarks, and KPI's and how to use various Excel elements to build
- Creating and formatting various
dashboard chart types and controlling them with Slicers, Excel
tables, controls, and Data Validation
- Creating adaptive shape, picture, and
worksheet cell displays using linked shapes, linked pictures, and
- Quickly creating dashboard maps from
linked pictures, linked shapes, and Bing maps
- How to use PivotTables, Pivot Charts, slicers, and timelines to
calculate and build adaptive dashboard displays
Click to enlarge, scroll < > if hidden.
Detailed training syllabus.
Virtual >, Onsite >
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.
Dashboard Formula Design,
Using Names, and Linking Data
- A review of critical dashboard formula construction
and debugging techniques
- Important uses of absolute, relative, and mixed reference cell notation in formulas
and Excel's tools
- What is string concatenation and how is it used in formulas?
- How to create, manage, name, and use
cell and range names in your formulas
- Efficiently passing data between
data sheets and your dashboard using names, cell references, and
Storing, Filtering, and
- 4 Rules for designing worksheet tables,
primary key and foreign key review
- Creating Excel tables and how to
use them to create self-adjusting Dashboards
- How to use Excel's Filters, Sort,
and Remove Duplicates to process your worksheet data
- Creating names for worksheet and Excel
tables for use in Excel's tools and formulas
- How to use structured references in your dashboard formulas so they can automatically adapt to changing data
- Using slicers to filter your Excel tables, dynamically adjust your
dashboard formulas, and charts
Designing Logic Formulas for
Your Excel Dashboard
- How is logic used to control
dashboard data and its user interfaces (UIs)?
- Strategies for detecting logic triggers in
your dashboard data
- Building simple logic formulas using the relational operators: <, >, <=, >=, < >, =
- Building single and
multi-condition logic formulas using the IF, AND, OR, and IS logical functions
- Creating IF...ELSEIF decisions using nested IF's and the IFS function
- Creating logic by combining key worksheet functions
with relational operators
Processing Date, Time, and Text
- Parsing date and time data using
functions like TEXT, YEAR, NETWORKDAYS.INTL, TODAY, NOW...
- Parsing text data using functions
like LEFT, RIGHT, MID, SUBSTITUTE, TRIM, CLEAN...
- Using string concatenation to
merge text data and to create date / times
Designing UIs to
Control Your Dashboard Formulas and Displays
- Why use the worksheet to construct dashboard user interfaces (UIs)?
- Creating, setting, and linking ActiveX
and Form controls with worksheet cells
- Integrating control outputs with
dashboard formulas and displays using logic, concatenation, and
- Why use Data Validation instead of controls to create a
- Using Data Validation to control what number, text, or date is typed in your dashboard UI
- How to use Data Validation and
names to create a self-adjusting cell dropdown list
- Designing formulas for your Data Validation that allow it to adjust to changing data conditions
Using Dynamic Names to Track
- What is a dynamic cell and range name and how
are they used in dashboard construction?
- How to build dynamic cell and range names using
functions like INDIRECT, ADDRESS, OFFSET...
- Using dynamic names to track
expanding and contracting data, alternate to structured
- Using dynamic names to go after
data subsets within a column so they can be summed, counted,
used in charts...
- Integrating UI selections into
your dynamic names
Using Table Lookups to Access
and Display Your Dashboard Data
- Using the VLOOKUP, XLOOKUP, and
HLOOKUP functions to perform table lookups
- Using string UI selections, concatenation, and
text functions to build your lookup value
- Finding a lookup item's # position
within a row or column using the XMATCH and MATCH functions
- How to use the INDEX function to
return entire table rows and columns
- Using a structured reference to
return part of a table
- Using INDIRECT,
concatenation, names, and UI selections to toggle tables,
columns, and rows in your formulas
Using Power Query to Access,
and Shape Your External Data
- What is Power Query and how is it
used to import and rapidly shape
- 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, filtering, sorting,
trimming, cleaning... data
- Using Power Query to filter data through a series of tables so the final table can be used in calculations, charts,
- How to perform formula calculations inside a Power Query table
Dashboard Type, KPI, and Metric
- What is the difference between a dashboard and a scorecard?
- Review of 4 major dashboard types: operational, strategic, analytical, and tactical
- What Excel elements are needed to create these different style dashboards
- What is a benchmark, metric, and a KPI?
- Review of popular kinds of metric formulas
and Analyzing Dashboard Data Using Logic
- How to build logic for functions
like SUMIFS... and determine logic triggers
in your dashboard data
- How to sum, count, and analyze
your dashboard data using logic functions like SUMIF, SUMIFS,
- Using string concatenation to
create dynamic logic for functions like SUMIFs... due to changes
in data and UI selections
- Detecting if data is identical in
two different worksheet tables using COUNTIFS
Using Array Formulas to Analyze
Complex Dashboard Data
- Overview of how you can use array formulas to analyze complex worksheet data
- Creating Excel array formulas that process complex datasets using the IF function
- Using simple array formulas to
pre-process data before being used in functions like VLOOKUP, IF, MAX, AVERAGE...
- Creating Excel array formulas that perform advanced lookups using functions like MATCH, VLOOKUP, INDEX, ROW...
How to Use Linked Drawing Shapes and Pictures to Build Adaptive Dashboard Displays
- Creating various drawing shapes and how to import a picture onto a worksheet
- Formatting, positioning, and layering shapes and pictures in the worksheet z-layer
- Design methods for linking shapes to worksheet cell data
and grouping them
- Creating a linked picture that dynamically displays a worksheet range and the elements that float above it
- How to
toggle worksheet pictures using dynamic names
- Combining linked shapes and dynamic pictures to produce various dashboard displays
Chart Construction, Formatting,
and Constructs for Excel Dashboards
- Creating and formatting chart types like
column, line, pie, tree map, sunburst, waterfall...
- Creating combination charts that can display two types of data at once
- Creating secondary axes and curve fits for your chart data
- Building vertical and horizontal limit lines in your chart
- Dynamically adjusting a chart's
data series using Excel tables, Slicers, and UI selections
- How to change chart data points, limit lines,
and labels using UI selections
Creating Speedometer, Linear, and Doughnut Gauges for Your Dashboards
- Laying out and building the formulas and
tables that speedometer, linear, and doughnut style gauge charts will use
- Creating a speedometer style gauge using a pie chart and drawing shapes
- Creating a linear style gauge from a bar or column chart and drawing shapes
- Creating a doughnut style gauge from a doughnut chart and drawing shapes
Using Conditional Formatting
to Build Various Status and Project / Timeline Displays
- The different uses of Conditional Formatting in dashboards
- A review of the different Conditional Formatting rules and how to set them up
- Using Conditional Formatting to hide or display data in a dashboard
- Using Conditional Formatting to create icon sets and data bars for dashboard data sets
- How to use Conditional Formatting to create status displays using logic and functions
- Using Conditional Formatting to color complete table areas based on logic
- Building dynamic project, timeline, and schedule Gantt charts on the worksheet using Conditional Formatting
Building Milestone Charts for Your Excel Dashboards
- Laying out and building the formulas and table that your milestone chart will use
- Creating a milestone chart and its labels
- Formatting a milestone chart
Excel Dashboard Design Strategies for Map Construction
- How to import map pictures and format them for use in your dashboard
- Placing linked drawing shapes and pictures over your map to display cell data
- How to use Conditional Formatting in linked pictures to make your map light up
- Creating dynamic Bing Maps on your worksheet dashboards
Using Pivot Charts and PivotTables in Your Excel Dashboards
- Laying out and organizing the
Excel table that your PivotTable will use
- How to build a PivotTable and
Pivot Chart and set their various features
- How to link PivotTables to different data sources
- How to create slicers and timelines that will filter your PivotTables and Pivot Charts
- Formatting your Pivot Charts, slicers, and timelines for that unique dashboard look
- Using Conditional Formatting in your PivotTables
- How to create running totals, ranks, % totals... fields in your PivotTables
Excel skills needed for our training.
Select this Excel training if you or your group have:
- Constructed general worksheet formulas like =A1+A2
- Constructed cell references and range references like A1 or A1:A10
- Used worksheet functions like SUM, MAX, MIN...before
- Used Microsoft Excel's data tools before
- Formatted a worksheet before
- Built charts and drawing shapes
The training details.
- Audience: Business, government, industry, military, engineering, science, techs... general audience
- Duration: 9:00 - 5:00 CT
- # of Days: 3-days
- Instruction Type: Hands-on, live, instructor taught training
Full Manual Included: 2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Choice of manuals:
The Complete Excel Manual > or Microsoft Excel-Aided Engineering and Science >
- Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
- Free Repeats: Repeat your Excel training.
- Personal Examples: We review our attendees personal examples in our training instruction
- Available Training Formats:
If you need to contact us about our training.
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
You can email us at email@example.com >
Contact us by form.
Need Help? Please call us at 1.805.498.7162
Copyright © 2002-2022
EMAGENIT All Rights Reserved