Microsoft Excel Dashboards for Business and Industry Class

How our class will boost your Excel skills.

Our 3-day class shows you how to develop innovative dashboards that allow you to quickly visualize your data and KPIs. It covers the essentials like how to analyze and calculate data using Excel's data tools, functions, PivotTables, and formulas. It also covers interface design and how to integrate them with your dashboards.

Our class then discusses in depth how to create the various dashboard displays that organize your strategic, tactical, analytical… and KPI information. It shows you how to assemble charts, controls, shapes, pictures, Conditional Formatting, and cells into the custom displays you need to view your data.

In addition, it shows how to use Power Query to access and shape external dashboard data and access weekly, daily, and hourly data downloads.

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

What you'll learn in our class.

Learn to use formulas, Conditional Formatting, filter functions, and spill over arrays to create adaptive data displays that auto format.

Learn to perform complex table lookups, find and fill tables using logic, calculate dates, and analyze statistics.

Learn to create project timelines and use logic functions, concatentation, and text functions to sum and analyze dashboard data.

Learn to create user interfaces from ActiveX, Data Validation, Charts, and shapes that control your dashboard displays and formulas.

Learn to use Pivot Tables, Pivot Charts, and slicers to be rapidly group, sum, and count data in your dashboards.

Learn to create maps and use Power Query to link, clean, merge, and shape text, workbook, and database data.

Learn about the different dashboard types (scorecard, tactical...) and how to create their KPIs using Excel functions and formulas.

Learn to create various chart gauges by combining doughnut, pie, bar... charts with shapes and Conditional Formatting.

Learn to build dynamic dashboard displays from ranges, shapes, and names that toggle pictures and fill table data in shapes.

Learn to toggle between tables in formulas; process complex datasets with array formulas; and design status and calendar displays.

Key dashboard formula design, creating names, and linking workbooks and worksheets
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...
How to build custom worksheet functions using the LAMBDA function and VBA
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, ActiveX, 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

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

Day-1

Formula Design, Names, Linking Data, and Table Design

Important uses of absolute, relative, and mixed reference cell notation in dashboard formula design
Designing worksheet tables and Excel tables to store your dashboard data
Review of using Excel's Filters, Sort, and Remove Duplicates to preprocess your dashboard data
How to use structured references and names to access your dashboard tables
Different ways to rapidly link data between workbooks and worksheets
Using Excel tables, structured references, and spill over arrays to parse dashboard data
Using functions like UNIQUE, FILTER, CHOOSECOLS, SORT... to parse dashboard data

Processing Text, Dates, and Performing Lookups for Your Dashboard

Parsing date and time data using functions like TEXT, YEAR, TODAY, NOW...
Parsing text data using functions like LEFT, MID, SUBSTITUTE, TRIM, CLEAN...
How to use string concatenation to create lookup values, merge text data, and create date / times
How to use the VLOOKUP, XLOOKUP, and HLOOKUP functions to perform table lookups
How to use the INDEX function to return entire table rows and columns
Using a structured reference to return part of an Excel table row or column in a lookup
Using formulas and concatenation to toggle tables, columns, and rows in your formulas

Designing Logic Formulas and Calculating Statistics in Your Dashboards

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: <, >, <=, >=, < >, = and the IF function
Using the IF, IFS, AND, OR,... functions to build multi-decision and multi-condition logic for your dashboard
Developing advanced logical tests for your IF, DSUM... functions that analyze parts of text, dates, numbers, spaces...
Using the DSUM, DMIN, DMAX... functions to quickly calculate dashboard data
Calculating key statistical parameters using formulas and STDEV.S, VAR.S, FREQUENCY...

Designing User Interfaces to Control Your Dashboard Displays

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 ActiveX and Form controls to control user input on the worksheet
Creating, setting, and linking ActiveX and Form controls with worksheet cells
Integrating UI settings with dashboard formulas and displays using logic, concatenation, and functions

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 Lambda to Create Custom Worksheet Functions

Using LAMBDA to create custom functions that replace your complex technical formulas
How to translate your technical formula into a LAMBDA function
What are parameters and how to use them in a LAMBDA function
How to add a LAMBDA function to Name Manager
Using your custom worksheet function in a formula

Day-2

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

How to use Power Query to link with and process data in a workbook, text file, or database
Performing various Power Query shaping tasks like merging, filtering, sorting, trimming, cleaning... data
How to use Excel tables with Power Query to append or merge multiple Excel tables together
How to group and calculate data in Power Query
Using Power Query to filter data through a series of tables so the final table can be used in calculations, charts, reports...

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

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 PivotTables and Pivot Charts in Your Excel Dashboards

Laying out and organizing the Excel table that your PivotTable will use
How to create a PivotTable / Pivot Chart and create running totals, counts, ranks, % totals...
Using PivotTables in unique ways to process your dashboard data and perform calculations
Using the GetPivotData function to extract pivot table data
Setting up a dashboard using slicers, timelines, Pivot Charts, and formatting

Additional Class Information

Prerequisites for our Excel dashboard 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 basic charts and drawing shapes

Class time, 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: 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 >

Microsoft Excel Dashboard Design for Business and Industry - Class