Ravindra BagaleCourses & study guides

13. Excel Dashboards

13.2 Structuring the Workbook: Data, Calc, Dashboard

By Last updated:

Steps in Excel

  1. Keep raw data on Data sheets as Tables: tblOrders, tblStores, tblTargets (Module 6). Add helper columns inside the Table if needed, for example Month = =TEXT([@[Order Date]],"mmm-yyyy") and IsDelivered = =--([@Status]="Delivered").
  2. Create a Calc sheet for PivotTables and KPI formulas. The user never needs to see it.
  3. Create a Dashboard sheet: View › Show › untick Gridlines, Headings and Formula Bar (the formula bar setting applies to the whole application).
  4. Set column widths to a narrow grid (for example 2.5) so you can place elements freely, or keep normal widths and use shapes on top.
  5. Colour the tabs: data = grey, calc = orange, dashboard = green. Put the Dashboard tab first.
  6. When the dashboard is final, right-click the Calc tab › Hide (and protect the workbook structure – Module 14).

Worked example – sheet plan.

Sheet Contains Visible to user?
Dashboard KPI cards, charts, slicers Yes
Calc 5 PivotTables, KPI formulas, target lookups Hidden
Orders tblOrders (all order lines) Yes (read only)
Stores / Targets tblStores, tblTargets Yes (read only)
Notes KPI definitions, refresh steps, data date Yes

Ravindra Bagale's Tip

Many students keep data, pivots and charts on one sheet – then when a pivot refreshes it grows and overlaps the charts (the “PivotTable report cannot overlap” error). Always keep pivots on a separate Calc sheet, with 2–3 empty rows/columns between them. Keep only charts, cards and slicers on the Dashboard sheet.

Practice task

Create the five sheets from the table above in Blinkit_Dashboard.xlsx, add the Month and IsDelivered helper columns to tblOrders, and turn off gridlines and headings on the Dashboard sheet.