Excel Dashboard Design for Business and Industry - Class

Class overview.

Our live, hands-on, 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

Est 1998. If you haven't heard of us before, here's some of our customers from the past 25 years.

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

Get an instant class discount. Signup for our Exceleration newsletter and get a discount that you can apply instantly to any purchase.

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 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >

Class syllabus.

Class Syllabus

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 >