Ravindra BagaleCourses & study guides मराठी Track your progress

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

About this data: Teaching sample for a fictional shop “BrightLedger Finance”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on structuring the workbook data calc dashboard.

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 tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of structuring the workbook data calc dashboard on a blank sheet for Skyline Clinics.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Aamir Khan and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Oliver Bennett.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply structuring the workbook data calc dashboard to a 5-row made-up Skyline Clinics table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with structuring the workbook data calc dashboard.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as SkylineClin_practice.xlsx.

Hint: Use a clear file name for class.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Structuring The Workbook Data Calc Dashboard?
Card 1: What is Structuring The Workbook Data Calc Dashboard? · Download PNG
Revision card 2: Tiny example — Structuring The Workbook Data Calc Dashboard
Card 2: Tiny example — Structuring The Workbook Data Calc Dashboard · Download PNG
Revision card 3: Checklist — Structuring The Workbook Data Calc Dashboard
Card 3: Checklist — Structuring The Workbook Data Calc Dashboard · Download PNG