Dashboard Reporting with Excel

Course Description

Excel is an ideal tool for creating high-impact dashboards in businesses of all sizes. In this course, learn how to build Excel-based dashboards that extract information from your accounting application along with other databases and to present this data as actionable information in a dashboard setting.
1 days


Participants should have at least attended Intermediate Excel training or have comparable skills.


The Benefits of Dashboard Reporting
Dashboard Reporting and Business Size
Dashboards Are not only for Finance
Dashboards? Or Scorecards?
How Many Measures?
On-Screen or On-Paper Reporting?
Dashboard Reporting and Excel
Sample Dashboard Files

The Advantages of Dashboard Reporting With Excel

The Management-Reporting Pyramid
Excel Reports Offer Infinite Scope
Graphic Design
Use Sound Data Visualization Techniques

How to Create Mini-Charts for Dashboard Reporting

Getting Started
How to Manage Chart Titles and Legends
How to Manage the X Axis
How to Manage the Y Axis
Add and Format the Gridlines

Charting Techniques for Dashboard Reporting

Adding a Data Series to a Chart
Multiple Chart Types
Using the Primary Value Axis
Using the Secondary Value Axis
Overlaid Charts
Using Auto Shapes in Charts
Dynamic Text Boxes
Unit-of-Measure Text Boxes
Dynamic Currency Symbol
Down With Gauges!
Gauges Hide Trends
Excel, Gauges, and Charts
How to Create a Better Chart in Excel
Scaling Y-Axis Chart Data Dynamic Power-of-Ten Numbers
Displaying the Correct Number of Decimals
Using a Scaling Table
Set Up Your Range Names for Scaling
Set Up Your Scaling Formulas

Using Charts with Worksheets

Simple Formatting
Chart Formatting with Spreadsheet Cells
Minimal Chart Formatting
Sizing Charts with Spreadsheet Cells
How to Align Charts with Cells
How to Resize All Your Charts at Once
Forcing Charts to Work Together
How NOT to Set Up Y-Axis Common Scaling
The Easy Way to Set Up Y-Axis Common Scaling
How to Set Up X-Axis Common Scaling

How to Use Excel’s Camera Tool

How to Create Camera Objects
Short Examples of Using the Camera Tool
Display Tables with Different Column Widths and Row Heights
Rotate Your Reports
Mini Briefing Books
Using the Camera Tool Dynamically
Variable-Length Lists
Traffic Lights
Limitations of the Camera Tool
Printing Camera Objects Pointed at Charts
Don't Use Too Many Camera Objects

How to Funnel Data into Dashboard Reports

How Data Flows into Excel-Based Dashboards
Common Types of Funnel Methods
Build-From-Scratch Spreadsheet Reporting
Spreadsheet Databases
Excel-Surly OLAPs
Excel-Friendly OLAPs

How to Use Spreadsheet Databases for Dashboard Reporting

How to Set Up the Excel Database
The ActGL.xls Workbook
The BudGL.xls Workbook
The ActSales.xls Workbook
The FcstSales.xls Workbook
The Rolling-12 Worksheets
How to Manage Workbook Generations

How to Build Dashboard Reports in Excel

The Structure of a Dashboard Workbook
The Importance of Scope
Additional Worksheets
An Overview of the Report Page
Create a Mockup Report Page
Initialize the Page
Create and Position Your Mockup Figures
Set Up the Rows and Columns
Position the Textbox in its Cells
Complete the Mockup
An Alternate Method: Use Cell Borders
Determine Your Dashboard Formatting
Format the Mockup
Add the Control Sheet and the First Data Sheet
The Control Sheet
The First Data Sheet for Charts
Create the First Mini-Chart Figure
Set Up Data for Other Charts
Set Up the Other Chart Figures
Set Up the Other Figures
Create the Balance Sheet
Create the Income Statement
Create the Legend
Colour-Code the Sheet Tabs

Make It All Fit

Create a Parking Place
Expand or Contract the Figures
Set Up Alternate Figures
How to Update Your Report Each Period

Magazine-Quality Dashboard Designs

Online Business Week Figures
Other Online Sources
Figures Clipped from Magazines & Newspapers
Full-Page Magazine Displays
Full-Page Excel Dashboards

Advanced ExcelDashboard ReportingDashboard Reporting2010Dashboard Reporting2013Dashboard Reporting2016Dashboard ReportingExcel 2016Excel 2010Excel DashboardsMicrosoft ExcelMicrosoft Excel 2010Microsoft Excel 2016MS ExcelExcel 365Excel 2019