Microsoft Excel Dashboards for Business and Industry

How our class will boost your Excel skills.

Our 3-day class shows you how to develop powerful Excel dashboards that allow you to quickly visualize your data. benchmarks, and KPIs. It covers the essentials like how to analyze and calculate data using Excel's data tools, functions, 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 how to use PivotTables in a dashboard setting.

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

See what you can learn.

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.

A review of the major dashboard types, benchmarks, and KPI's and how to use various Excel elements to build them
Dashboard formula design, creating names, and linking workbooks and worksheets
Processing text, dates, and performing various lookups for your dashboards
Designing logic formulas to process dashboard data and interpret user interface selections
Summing, counting, and analyzing dashboard data using logic functions like SUMIFS, COUNTIFS...
Creating array formulas that process complex data patterns, filter data, and perform multi-value lookups
How to build custom worksheet functions for your dashboards using the LAMBDA function
Using ActiveX controls, Form controls, and Data Validation to build user interfaces that control your dashboards
Creating and formatting various dashboard chart types and controlling them with Slicers, Excel tables, ActiveX, and Data Validationlicers, Excel tables, ActiveX, and Data Validation
Creating adaptive shape, picture, and worksheet cell displays using linked pictures, shapes, and ranges
Creating dashboard map, project, timeline, and status displays
Automatically importing, integrating, and shaping external dashboard data using Power Query
How to use PivotTables, Pivot Charts, slicers, and timelines to calculate and build adaptive dashboard displayss

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

Dashboard Type, KPI, and Metric Review

What is the difference between a dashboard and a scorecard?
Review of the major dashboard types: operational, strategic, analytical, tactical...
What is a benchmark, metric, and a KPI and what formula elements are used to determine them?
What Excel elements are needed to create different style dashboards and manage their data?

Formula Design, Naming Cells, Linking Data, and Table Design

Using absolute, relative, and mixed reference cell notation in formula design
Designing worksheet tables and Excel tables to store your dashboard data
Review of using Excel's Filters and Sort 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 structured references, spill over arrays, and functions 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, TRIM, CLEAN...
How to use string concatenation to create lookup values, merge text data, and create date / times
How to use the VLOOKUP, XLOOKUP, INDEX... functions to perform table lookups
Using a structured reference to return part of an Excel table row or column in a lookupp
Using formulas, logic, and concatenation to toggle tables, columns, and rows in your formulas
Using functions like INDIRECT, ADDRESS, OFFSET... to track dashboard data and go after data subsets

Analyzing Dashboard Data with Logic and Statistical Functions

Building logic formulas using the relational operators: <, >=... and the IF function
Using the IF, IFS, AND, OR,... functions to build multi-decision and multi-condition dashboard logic formulas
Using the DSUM, DMAX... functions to quickly calculate dashboard data
Calculating key statistical parameters with STDEV.S, VAR.S, FREQUENCY...
How to sum, count, and analyze your dashboard data using logic functions like SUMIFS, COUNTIFS...
Using string concatenation to create dynamic logic for functions like SUMIFS, MAXIFS...

Using Array Formulas to Analyze Complex Dashboard Data

How to tell when a worksheet function or formula can take an array and produce an array
Using simple array formulas to clean, replace... data for functions like VLOOKUP, SUM...
Creating array formulas that process complex datasets using logic
Creating array formulas that perform multi-condition lookups
How to use array formulas and spill over arrays to filter data for schedules, timelines...

Using Lambda to Create Custom Dashboard Functions

Using LAMBDA to create custom functions that replace your complex formulas
How to translate your 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

Designing User Interfaces (UIs) to Control Your Dashboards

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

Dashboard Chart Construction, Formatting, and Worksheet Integration

Creating and formatting chart types like column, line, xy scatter, combo...
Creating secondary axes and curve fits for your chart data
Building vertical and horizontal limit and control 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
Arranging charts, ranges, shapes, and pictures on a worksheet to form a dashbaord

Using Conditional Formatting to Build Status, Project and 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

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 shapes
Creating a linear style gauge from a bar or column chart and shapes
Creating a doughnut style gauge from a doughnut chart and shapes

Day-3

Using Linked Shapes and Pictures to Build Adaptive Dashboard Displays

Creating various 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

Excel Dashboard 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 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, 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...

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, %...
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, PivotTables, 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.629.207.9662

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 - Live Class