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

Khup students ekach sheet var data, pivots aani charts thevtat – mag pivot refresh zala ki to vadhto aani charts var overlap hoto (“PivotTable report cannot overlap” error). Pivots nehmi vegalya Calc sheet var theva, ekmekanpasun 2–3 rikami rows/columns antar thevun. Dashboard sheet var fakt charts, cards aani slicers.

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.