Microsoft Excel Dashboard Training

Since 1998

In Person / Live Online

Next Date: Click Dates|Signup

All Dates | Signup

*Our live, hands-on, instructor led Excel online training is running as scheduled above. Excel in person training will hopefully resume in the near future.

Learn how to design cost effective powerful Microsoft Excel dashboards.

Our Microsoft Excel Dashboard training shows you how to create powerful Excel dashboards that let you select, import, visualize and integrate your Excel data.

Questions? 1.805.498.7162   Customers >

Excel Worksheet Layout
Scroll Picture > > >
< 1 > Delete

Take a tour of our Microsoft Excel dashboard training.

Take Visual Tour

Take a visual tour of our class.

Download Visual Tour

Download the visual tour in PDF form.

Our 3-day hands-on Microsoft Excel dashboard training shows you the complete mechanics and strategies of how to create powerful Microsoft Excel dashboards that rival anything on the market, but at a fraction of the cost. Our Excel dashboard training starts off with a review of the key formula construction methods, name construction methods, worksheet functions, table designs and logic that you will need to analyze your dashboard metrics. It then discusses dashboard types, metrics and KPI's and shows in detail how to design various dashboard displays including status and summary; chart gauges; bubble, tree map, funnel and radar charts; project management; timeline; milestone; map; pivot tables and pivot charts. Last our training discusses how to link Excel with external data and filter it using MS Query and Power Query and how to build powerful custom VBA worksheet functions that allow you to perform tasks that normal formulas cannot.

The skills you need for our Excel dashboard training.

Select this Excel training if you or your group have:

  • Constructed general worksheet formulas (=A1+A2) and have used worksheet functions before like SUM
  • Used Microsoft Excel's basic data tools before
  • Formatted a worksheet before
  • Built basic charts
  • A desire to push Microsoft Excel to the limit

Our Microsoft Excel dashboard training syllabus.

Important: We focus our Excel training on what our customers need. When training begins, we analyze those needs and shift our Excel 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

Excel Dashboard Formula Design, Debugging and Linking Review

  • A review of critical excel dashboard formula construction techniques
  • How to use absolute, relative and mixed reference cell notation in your Excel dashboard formulas
  • How to use Excel's formula debugging tools
  • What is string concatenation and how is it used in formulas
  • Naming strategies for cell/range names that aid in formula construction and tracking data
  • How to manually create and manage cell and range names
  • How to use cell and range names in worksheet formulas
  • How to efficiently pass data between data sheets and your Excel dashboard using cell and range names

Designing Logic for Your Excel Dashboard Formulas

  • How is logic used in Excel dashboard design
  • Strategies for detecting logic triggers in worksheet data
  • How to build logic formulas for Excel dashboards using the relational operators: <, >, <=, >=, < >, =
  • How to build logic formulas for Excel dashboards using the IF, AND, OR logical functions
  • How to use key worksheet functions with relational operators to create logic conditions
  • How to use the IS worksheet functions to detect different cell inputs and formula problems
  • How to build multi-conditional logic formulas and use them in your Excel dashboards

Table Design Methods for Your Excel Dashboard

  • Rules for designing a proper table on the Excel worksheet
  • What is a primary key and foreign key and how are they used in table design and formulas
  • How to use data validation to enforce standardized data entry in a worksheet table
  • What is an Excel table and how to use them to create self adjusting Excel Dashboards
  • How to use structured references in your dashboard formulas so they can automatically adapt to changing data

How to Create and Use Dynamic Names in Your Excel Dashboard Formulas

  • What is a dynamic cell and range name and how do they automatically adapt to changing data
  • Where are dynamic names used in Excel
  • How to build dynamic cell and range names using the INDIRECT, ADDRESS, OFFSET, COUNTA, COUNTIF and MATCH worksheet functions
  • How to create dynamic names using Excel tables

 Key Functions and Array Formulas Used in Dashboard Data Processing

  • How to summarize your Excel dashboard data using SUMIF, SUMIFS, COUNTIF, COUNTIFS, SUM, MAX, MIN, AVERAGE, RANK, SMALL, LARGE,...
  • How to lookup your Excel dashboard data using VLOOKUP, HLOOKUP, MATCH, INDEX, LOOKUP,...
  • How to search for and return entire data columns or rows for your Excel dashboard using ADDRESS, INDIRECT, INDEX, MATCH and string concatenation
  • How to deal with dates and time in your Excel dashboard using the TEXT, YEAR, DAY, MONTH, NETWORKDAYS.INTL, NOW, TODAY, TIME, TIMEVALUE, DATEVALUE, DAYS and SECONDS functions
  • How to process your worksheet text in your Excel dashboard using the LEFT, RIGHT, MID, SUBSTITUTE, REPLACE, SEARCH, LEN, TRIM, CLEAN and TEXT functions
  • How to detect data in different tables using COUNTIF and COUNTIFS
  • What is an array formula and how is one built
  • How to build simple array formulas in your Excel dashboards
  • How to create array formulas that perform powerful multi-conditional SUMIFS, MAXIFS, MINIFS and AVERAGEIFS, more powerful than the worksheet functions
  • How to create array formulas that perform multi-conditional lookups

Metric Formula Construction for Excel Dashboards

  • What is a metric and how are they used in dashboards
  • In class discussion on attendee's metric calculation needs
  • How to calculate various metric type formulas using key worksheet functions, array formulas and standard cell based formulas
  • How to identify and use a data subset range within a larger data range using OFFSET, INDIRECT, ADDRESS, MATCH, INDEX and COUNTA
  • Various ways to build metric formulas that count

Day-2

Dashboard Type Review

  • What is the difference between a dashboard and a scorecard
  • Review of the 4 major types of dashboards: operational, strategic, analytical and tactical
  • What is a benchmark?
  • What is a KPI?
  • A discussion of the Excel elements needed to create these different style dashboards

Designing Microsoft Excel Dashboard Interfaces Using ActiveX Controls and Data Validation

  • Why use the Excel worksheet to construct dashboard interfaces on?
  • Overview of ActiveX and Form controls, what is the difference?
  • How to create and use ActiveX combo boxes, spinners, scroll bars, option buttons, check boxes and toggle buttons
  • How to integrate these control outputs with your Excel dashboard formulas using logic, string concatenation and various worksheet functions
  • Why use data validation instead of controls to create an Excel dashboard interface
  • How to use data validation to control what number, text or date is typed in your Excel dashboard interface
  • How to create a list in a worksheet cell using data validation and use this feature as a selector for your Microsoft Excel dashboard views
  • How to use functions, formulas, names, logic and cell references in your data validation that allow it to adjust to changing Excel dashboard and data conditions
  • How to create cells that only allow certain date ranges to be entered

How to Use Linked Drawing Shapes and Pictures to Build Adaptive Dashboard Displays

  • How to create various drawing shapes and import a picture onto a worksheet
  • How to format, position and layer shapes and pictures on the worksheet
  • How to link drawing shapes to worksheet cell data
  • How to create a linked picture that dynamically displays a worksheet range and the items that float above it
  • How to design formulas and names that toggle the picture within a linked picture
  • How to combine linked drawing shapes and dynamic pictures to produce various Excel dashboard displays

 Chart Construction and Formatting for Excel Dashboards

  • Complete Excel chart type and formatting review
  • How to create and format unique Excel chart types like bubble, tree map, sunburst, radar, waterfall and funnel charts
  • How to create combination charts that can display two types of data at once
  • How to create secondary axes and curve fits for your chart data
  • How to build vertical and horizontal limit lines in your chart
  • How to dynamically adjust a chart's data series so it reacts to a dashboard interface start / finish or drop down selection
  • How to move data points, limit lines and labels within a chart using ActiveX controls

How to Create Speedometer Gauges, Linear Gauges and Doughnut Gauges for Your Excel Dashboards

  • How to layout and build the formulas and tables that speedometer and linear style gauge charts will use
  • How to create a speedometer style gauge using a pie chart and drawing shapes
  • How to layout and build the formulas and tables that your linear style gauge charts will use
  • How to create a linear style gauge from a bar or column chart and drawing shapes
  • How to layout and build the formulas and tables that your doughnut style gauge charts will use
  • How to create a doughnut style gauge from a doughnut chart and drawing shapes

How to Create Milestone Charts for Your Excel Dashboard

  • How to layout and build the formulas and table that your milestone chart will use
  • How to create the milestone chart and its labels
  • How to format a milestone chart

How to Use Conditional Formatting to Build Various Status, Project and Timeline Displays

  • The different uses of conditional formatting in Excel dashboards
  • A review of the different conditional formatting rules and how to set them up
  • How to use conditional formatting to hide or display data in a dashboard
  • How to use conditional formatting to create icon sets and data bars for dashboard data sets
  • How to use conditional formatting to create worksheet status displays using logic and functions
  • How to use conditional formatting to color complete table areas based on logic
  • How to build dynamic timelines and schedules on the worksheet using conditional formatting

Day-3

Excel Dashboard Design Strategies for Map Construction

  • How to import map pictures and format them for use in your Excel dashboard
  • How to place linked drawing shapes and linked range pictures over your map to display cell data
  • How to use conditional formatting in linked pictures to make your map light up

Accessing External Data in Your Excel Dashboards Using Power Query and MS Query

  • How to use Microsoft Query to access and integrate various external data sources
  • How to use Power Query to access and integrate various external data
  • How to use the various quick data access tools of Excel to integrate external data
  • How to access external workbook data and text file data like a relational database
  • How to import Microsoft Access data into Excel

Using Pivot Chart and Pivot Table Displays in Excel Dashboards

  • How to layout and organize the raw data that your pivot table and pivot chart will use
  • How to construct a pivot table for your pivot chart taking advantage of dynamic names and Excel tables
  • How to build a pivot chart for your pivot table
  • How to create slicers and timelines that will filter your pivot tables and charts
  • How to format your pivot charts, slicers and timelines for that Microsoft Excel dashboard look
  • How to use conditional formatting in your Excel dashboard pivot table
  • How to create running totals, ranks... in your pivot table

How to Build Custom VBA Worksheet Functions for Your Microsoft Excel Dashboards

  • Why build custom VBA worksheet functions?
  • Why custom VBA Worksheet functions easily surpass a hundred times over the most advanced worksheet array formula
  • How to use the VBA Editor
  • How to create a custom VBA worksheet function for your Excel dashboard
  • How to use logic, loops and variables inside your custom functions
  • How to use important worksheet functions and VBA functions inside your custom functions

The details about are Microsoft Excel dashboard training.

  • Audience:   Business professionals, engineers, scientists and technicians
  • Duration:   8:30 AM - 4:30 PM
  • # of Days:   3-days
  • Instruction Type:   Hands-on, live, instructor taught training
  • Full Manual Included:   2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Choice of manuals: Microsoft Excel-Aided Business > or Microsoft Excel-Aided Engineering and Science >
  • Excel Versions:   2007-2019, Microsoft 365, Windows and Mac
  • Free Repeats:   Repeat your Excel training. Details >
  • Personal Examples:   We review our attendees personal examples in our training instruction
  • Available Training Formats:
Microsoft Excel-Aided Business Self Study Manual
Learn More >
Excel Aided Business Self Study
Included with our training.
Excel-Aided Engineering & Science Self-Study Manual
Learn More >
Excel-Aided Engineering Self Study
Included with our training.

If you need to contact us about our Microsoft Excel dashboard training.

Contact us directly through email or phone.

Contact us by form.

Click to View Form
Contact Us Newsletter
Need Help? Please call us at 1.805.498.7162

Microsoft Excel Products

- Excel Self-Study Manuals

Copyright © 2002-2020

EMAGENIT All Rights Reserved