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 Day
Contact us for pricing
Prerequisites
Participants should have at least attended Intermediate Excel training or have comparable skills.Introduction
The Benefits of Dashboard ReportingDashboard 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 PyramidExcel Reports Offer Infinite Scope
Graphic Design
Use Sound Data Visualization Techniques
How to Create Mini-Charts for Dashboard Reporting
Getting StartedHow 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 ChartMultiple 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 FormattingChart 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 ObjectsShort 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
Shutters
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 DashboardsCommon Types of Funnel Methods
Build-From-Scratch Spreadsheet Reporting
Spreadsheet Databases
PivotTables
Excel-Surly OLAPs
Excel-Friendly OLAPs
How to Use Spreadsheet Databases for Dashboard Reporting
How to Set Up the Excel DatabaseThe 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 WorkbookThe 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 PlaceExpand or Contract the Figures
Set Up Alternate Figures
How to Update Your Report Each Period
Magazine-Quality Dashboard Designs
Online Business Week FiguresOther Online Sources
Figures Clipped from Magazines & Newspapers
Full-Page Magazine Displays
Full-Page Excel Dashboards
Advanced ExcelDashboard ReportingExcel DashboardsMicrosoft ExcelMS ExcelExcel 365Excel 2021Microsoft Excel 2021