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 shows you how to develop industry grade dashboards that allow you to interact with and visualize your data in innovative ways.

It focuses on how to develop state of the art dashboards by combining key Excel elements like charts, ActiveX controls, shapes, pictures, Data Validation, Conditional Formatting, and worksheet cells. Also discussed is how to create the various dashboard types that organize your strategic, tactical, analytical… and KPI information.

Our class also covers how to process and calculate your data using various Excel functions, Excel tables, array formulas, Power Query, Power Pivot, and Pivot Tables. Emphasis is also placed on accessing, integrating, and formatting external data using Power Query and how to integrate weekly data downloads with your dashboards. It also shows how to create custom worksheet functions using LAMBDA or VBA to process your tough data.

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.

Some key topics we cover in class.

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, 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, Power Pivot, 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 basic 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

Formula Design, Names, Linking Data, and Table Design

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
Designing worksheet tables and Excel tables to store your dashboard data
Efficiently passing data in your dashboard workbook using names, cell references, formulas, and arrays
Review of using Excel's Filters, Sort, and Remove Duplicates to process your dashboard data
How to use structured references and names to access your dashboard tables

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 Text and Performing Lookups with Functions

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...
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
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 an Excel table row or column in a lookup
Using INDIRECT, concatenation, names, and UI selections to toggle tables, columns, and rows in your formulas

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

How to create your own worksheet function using the LAMBDA function
How to use parameters in your LAMBDA function
Adding a LAMBDA function to Name Manager
How to create a custom VBA Worksheet Function (UDF) including its argument list
Performing calculations, writing logic, and running Excel functions in your VBA UDFs
How to debug your custom worksheet functions

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 Pivot Charts, PivotTables, and Power Pivot 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
How to add Excel tables to the Data Model of a workbook
How to use Power Pivot to create relationships, perform calculations (DAX, Aggregate), and create a PivotTable
Setting up a dashboard using slicers, timelines, Pivot Charts, and formatting

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.