Excel Dashboard Design Class for Business and Industry

Class overview.

What makes our training different?

Live, hands-on, industry focused topics
Bring your own problems, ask tough questions
Taught by an Excel software developer
Limited class size

Our 3-day class focuses on developing industry grade dashboards by combining Excel elements like charts, ActiveX controls, shapes...etc. It covers how to create various strategic, tactical… dashboard types that organize your KPI information.

Our class also discusses how to process and calculate your data using various functions / formulas, Excel tables, array formulas, and Pivot Tables.

Emphasis is also placed on accessing, integrating, and formatting downloaded external data in your dashboards using Power Query. Join us and we'll show you how to put all the Excel pieces together to produce that hi-tech dashboard that fits your needs. See the Video >

Some key topics we cover in class.

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, AND, IS...
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 dashboard
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 them
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 Conditional Formatting
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

Prerequisites for our Excel training.

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

Class time, duration, manual, and repeats.

Audience: Business, government, industry, military, engineering, science, techs...
Duration: 9:00 - 5:00 CT
# of Days: 3-days
Instruction Type: Live, hands-on
Class Manual: Our complete Excel manual for business and industry (TCEM). Details >
Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

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.

Day-1

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 formulas

Storing, Filtering, and Referencing Your Dashboard Data

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 Data

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 functions
Why use Data Validation instead of controls to create a dashboard interface?
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 Dashboard Data

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 references
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

Day-2

Using Power Query to Access, Integrate, and Shape Your External Data

What is Power Query and how is it used to import and rapidly shape 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, 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, reports...
How to perform formula calculations inside a Power Query table

Dashboard Type, KPI, and Metric Review

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

Summarizing, Counting, 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, COUNTIFS, MAXIFS...
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 and logic
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

Day-3

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

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 >

Excel Dashboard Design for Business and Industry

Build that customized hi-tech dashboard that fits your exact data needs!