Chapter 14: Dashboard Design & Best Practices

Dashboard Design & Best Practices.

This is where everything comes together: your clean data → smart tables → powerful formulas → PivotTables → slicers & charts → automation → all combined into one beautiful, interactive dashboard that tells a clear story and lets users explore the data themselves.

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you in Airoli (maybe with some nice filter coffee while we build this together). We’ll continue using our tea shop workbook as the example — imagine you now want to create a monthly management dashboard that the owner or branch managers can open and instantly understand performance.

Let’s build a professional, interactive dashboard!

1. Building Interactive Dashboards

A good Excel dashboard is:

• Focused – shows only the most important KPIs & visuals
• Interactive – users can filter, slice, change views without editing formulas
• Clean & intuitive – easy to read in 10 seconds
• Up-to-date – refreshes automatically or with one click

Typical layout structure (very common & effective):

Step-by-step – Build the tea shop dashboard

1. Create a new sheet called Dashboard
2. Freeze panes so headers & slicers stay visible: View → Freeze Panes → Freeze Top Row (or more rows)
3. Insert a nice title:
   • Merge cells A1:H1 → type “Tea Shop Performance Dashboard – 2026”
   • Font: Calibri 24 pt, bold, dark blue, center, light gray fill
4. Add key date filter (Timeline):
   • Go to your main PivotTable (from Chapter 10) → make sure it uses a grouped Date field
   • On Dashboard sheet: PivotTable Analyze → Insert Timeline → choose Date
   • Move & resize the Timeline to top-right (e.g., cells F2:J4)
5. Add slicers:
   • From the same PivotTable: PivotTable Analyze → Insert Slicer → City, Item Category, Payment Mode
   • Arrange them neatly in one row below title (e.g., B3:D5, E3:G5, H3:J5)
   • Style: Slicer Settings → choose colors matching your theme
6. Create KPI cards (big single numbers):
   • In cells B6:D6 (or similar):
     • B6: “Total Sales” → =GETPIVOTDATA(“Total”,PivotTableRef,”Date”,”All”) or better use DAX measure if using Power Pivot
     • Format: large font (24–32 pt), currency ₹, green fill if positive
   • Repeat for:
     • Total Profit
     • Avg Daily Sales
     • Top Selling Item (use INDEX + MATCH or FILTER + TAKE)
7. Insert main visuals:
   • PivotChart 1: Clustered Column – Sales by Item (center)
   • PivotChart 2: Line – Sales Trend over Time (below or right)
   • PivotChart 3: Pie or Donut – Sales Share by City (small, top-right)
   • PivotChart 4: Bar – Profit by Payment Mode (bottom-right)
8. Connect everything:
   • Right-click each slicer → Report Connections → check all relevant PivotTables/Charts
   • Same for Timeline
9. Add last refresh info:
   • Cell A20: “Last updated: ” & =TEXT(NOW(),”dd-mmm-yyyy hh:mm”)
   • Or use a macro to stamp refresh time
10. Protect the dashboard:
    • Unlock only slicers/timeline → Protect Sheet (no password or simple one)

→ Now click Mumbai in slicer → all charts & KPIs instantly update to show Mumbai-only performance!

2. Using Form Controls (Sliders, Buttons, etc.)

Form controls make dashboards feel more like an app.

Most useful controls:

• Button → run macro (e.g., Refresh All, Export PDF)
• Scroll Bar / Slider → change a value dynamically (e.g., target sales, discount %)
• Combo Box / List Box → alternative to slicers for single selection
• Option Button / Checkbox → toggle views (actual vs target, show profit or margin)

Example – Discount Simulator with Slider

1. Developer tab → Insert → Scroll Bar (Form Control)
2. Draw it (say cell K10:L10)
3. Right-click → Format Control:
   • Minimum Value: 0
   • Maximum Value: 30
   • Cell link: M10 (will show current %)
   • Incremental change: 1
4. In a cell (N10): Discount Amount =SUMIFS(…) * (M10/100)
5. In O10: Net Revenue = Total Sales – Discount Amount
6. Create a simple bar chart comparing Gross vs Net Revenue
7. Add text: “Discount %: ” & link to M10 & “%”

→ Slide the bar → see real-time impact on revenue!

Button example – Refresh & Save

• Insert Button → assign macro:

3. Best Practices for Large Datasets and Performance

Critical tips (especially with 50,000+ rows):

• Use Excel Tables (not plain ranges) → auto-expands, structured references faster
• Prefer Power Query for cleaning → don’t clean in worksheet formulas
• Use Power Pivot data model instead of big lookup formulas
• Avoid volatile functions in large areas (INDIRECT, OFFSET, TODAY, NOW, RAND) → Put them in one cell → reference that cell
• Turn off automatic calculation when building: Formulas → Calculation Options → Manual (press F9 to calculate when needed)
• Use AGGREGATE or SUMIFS instead of array formulas on huge ranges
• Limit PivotTable cache → multiple Pivots from same data → share cache (Create first Pivot → second one: Data Model → Existing Connection)
• Compress images (right-click → Format Picture → compress)
• Remove unused styles (very hidden performance killer): Use free add-in “Style Cleaner” or VBA to delete

Rule of thumb: If file > 20–30 MB or recalculates > 3–5 seconds → time to optimize or move to Power BI.

4. File Management (Templates, Sharing, Co-authoring)

A. Save as Template (.xltx or .xltm for macros)

• File → Save As → Excel Template (.xltx) or Macro-Enabled Template (.xltm)
• Location: usually in Documents\Custom Office Templates
• Next time: File → New → Personal → your template name

B. Sharing options

• OneDrive / SharePoint (best for co-authoring): File → Share → Share with people → set permissions (Can edit / Can view) → Real-time co-editing (multiple people at same time) → Version history (File → Info → Version History)
• Email – better send link than attachment (avoids version chaos)
• Protect file: File → Info → Protect Workbook → Encrypt with Password (opens file) or Protect Workbook Structure (no new sheets)

C. Backup & version discipline

• Enable AutoSave (top-left toggle) → saves to OneDrive every few seconds
• Name files meaningfully: TeaShop_Dashboard_v3_2026-Jan.xlsx
• Keep source data separate from dashboard (different sheets or files)

Quick Summary Table – Chapter 14

Homework / Practice (Do This Today!)

1. Create new sheet “Dashboard”
2. Add title, slicers (City, Item), Timeline (Date)
3. Build 4–5 KPIs (total sales, profit %, top item, avg daily qty)
4. Insert 3–4 connected PivotCharts
5. Add a scroll bar → simulate “what if we increase price by X%”
6. Add a button → “Refresh All Data”
7. Protect sheet (unlock only slicers & scroll bar)
8. Save as .xlsm → upload to OneDrive → share link with yourself (test co-editing)

You’ve now completed the full beginner → professional Excel course! 🎉 From opening a blank workbook → to building interactive management dashboards with automation.

You’re now at a level where you can:

• Create reports that impress bosses/clients
• Automate boring tasks
• Handle real business data confidently

If you want:

• More dashboard examples (inventory, HR, school marks analysis)
• Advanced VBA (email reports, PDF export, Outlook integration)
• Transition to Power BI (when Excel dashboards become too slow)
• Python in Excel (new 2025–2026 feature)

just tell me — we can keep going! Or if you have a real project/dataset you want help with, feel free to describe it.

You’ve done outstanding work — be very proud of yourself! ☕💻🌟

What would you like to do next? 😊