Microsoft Excel Dashboard Training

Next Date: Click Dates|Signup

View All Dates | Signup

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 >

We cover Excel for Mac.

Learn More >

US Military, DOD Discounts

Learn More >

Key topics covered in our Microsoft Excel dashboards training.

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 key skills learned in our Excel dashboard training.

Key Excel skills learned:

Excel dashboard formula design, debugging and linking review
Key logic formulas and functions that your Excel dashboards will use
Constructing, managing and using range and cell names in your Excel dashboard
How to create proper tables and use Excel tables in your Excel dashboards
How to create dynamic names that automatically adjust to changing data and use them in various Excel features
Key worksheet functions and array formulas used in Excel data processing
Metric formula construction techniques for Excel dashboards
Learn about the different dashboard types, benchmarks and KPI's
Learn how to use ActiveX controls and data validation to create Excel dashboard interfaces
Learn how to link drawing shapes with cells and pictures with ranges to create adaptive dashboard displays
Learn how to create and format key Excel dashboard charts like sunburst, tree map, bubble and radar
Learn how to create speedometer, linear and doughnut gauge charts
Learn how to create milestone charts
How to use conditional formatting on the worksheet to build various status, project and timeline displays
How to create custom map displays on the worksheet
Learn how to use Microsoft Query, Power Query and various OLEDB tools to automatically query and filter external dashboard data
Learn how to utilize pivot tables, pivot charts, slicers, timelines and various pivot table calculations to build dashboard displays
Learn how to create custom VBA worksheet functions that can perform amazing calculations

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