Dashboarding Basics

A Dashboard is a visual display of the most important information needed to achieve one or more objectives. It fits on a single screen so it can be monitored at a glance.

1. The Golden Rules of Dashboarding

1. Keep it Simple (KISS): Don't clutter. Less is more.
2. 5-Second Rule: The user should understand the key message in 5 seconds.
3. Choose the Right Chart:
   • Trend over time: Line Chart.
   • Comparison: Bar Chart.
   • Composition: Pie Chart (Use sparingly! Max 3-4 slices).
   • Distribution: Histogram.

2. Setting Up in Excel

Step 1: Data Sheet

• Keep your raw data here. Never touch it.
• Format as an Excel Table (Ctrl + T). This makes it dynamic.

Step 2: Calculation Sheet (The Engine)

• Use Pivot Tables to summarize the raw data.
• Example: "Total Sales by Region".
• Link your charts to these Pivot Tables.

Step 3: Dashboard Sheet (The Face)

• This is what the user sees.
• Hide Gridlines (View > Uncheck Gridlines).
• Align charts perfectly.
• Add "Slicers" for interactivity.

3. Key Excel Tools for Dashboards

• Pivot Tables: The backbone.
• Slicers: Buttons to filter data visually. (Insert > Slicer).
• Sparklines: Tiny charts inside a cell. (Insert > Sparklines).
• Conditional Formatting: Color scales to highlight High/Low values.
• GETPIVOTDATA: A function to pull data from Pivot Tables safely.

4. Design Tips

• Color Palette: Use 2-3 main colors. Don't make it a rainbow.
• Consistency: All fonts and titles should look the same.
• White Space: Leave empty space between charts. It reduces cognitive load.

7-Day Action Plan

Day 1: Find a dataset (e.g., "Superstore Sales"). Clean it.
Day 2: Create 3 Pivot Tables answering key questions (e.g., Sales by Month, Top 5 Products).
Day 3: Insert Pivot Charts for each table.
Day 4: Create a new "Dashboard" sheet. Turn off gridlines.
Day 5: Move charts to the Dashboard. Align them.
Day 6: Add Slicers (Region, Year). Connect Slicers to ALL Pivot Tables (Right Click Slicer > Report Connections).
Day 7: Format it. Add a title. Save as PDF to see how it looks.

Quiz

💡 Final Wisdom: A dashboard is a story. Don't just show numbers; show what they mean.