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

16. Practice Exercises with Answer Hints

16.12 Module 13: Dashboards

By Last updated:

← All Excel dashboards

  1. Write five KPIs with definitions for a Kolhapur store dashboard. Hint: numerator, denominator, filter, comparison, good direction (13.1).
  2. Build a KPI card that shows Net Sales in lakh and updates with slicers. Hint: GETPIVOTDATA in a Calc cell → text "₹"&TEXT(x/100000,"0.00")&" L" → shape linked with =Calc!O3.
  3. Connect City and Platform slicers to four pivots. Hint: Report Connections on each slicer.
  4. Fit the dashboard to one screen and hide gridlines/headings. Hint: View › Show; Zoom 100%.
  5. Add a manual check formula that must equal the Net Sales card. Hint: =SUMIFS(tblOrders[Amount],tblOrders[Status],"Delivered").

Ravindra Bagale's Tip

Many students build 10–12 charts for a dashboard. Write the questions first, then one chart for each question. Fewer charts, more clarity – the manager should get the answer in 30 seconds.

Loading practice tasks...

  1. Total sales (Easy): Add up Sales (₹) in B2:B11. (sheet "Data")
  2. Average margin (Easy): Average of Margin % in D2:D11, rounded to 1 decimal. (sheet "Data")
  3. Regions over 18% (Easy): How many rows have Margin % > 18? (sheet "Data")
  4. Max sales (Easy): Largest Sales (₹) in B2:B11. (sheet "Data")
  5. Total tickets (Easy): Add up Tickets in E2:E11. (sheet "Data")
  6. Total target (Medium): Add up Target (₹) in C2:C11. (sheet "Data")
  7. Avg NPS (Medium): Average of NPS in F2:F11, rounded to 0 decimals. (sheet "Data")
  8. Sales over 700000 (Medium): How many regions have Sales (₹) > 700000? (sheet "Data")
  9. Sales of Export (Medium): Sales (₹) for Region "Export". (sheet "Data")
  10. Sales / Target (Hard): Total sales / total target, 3 decimals. (sheet "Data")
  11. Max NPS (Hard): Largest NPS in F2:F11. (sheet "Data")
  12. Above avg sales? (Hard): "High" if B2 > average sales else "OK". (sheet "Data")

See all practice tasks in the Task Hub →

Worked example — module 13 dashboards

Why

Aamir Khan in Pune and Carlos Rivera abroad both need a clear, repeatable way to use module 13 dashboards on a QuickCart Delivery sheet without breaking totals.

What

Module 13 Dashboards is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for module 13 dashboards.
  2. Enter a small made-up value for Aamir Khan using $ where money appears.
  3. Add a second value for Carlos Rivera using £ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for QuickCart Delivery's sample row.
Meaning: Once module 13 dashboards is correct on one row, copy the same pattern down the column.

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 Module 13 Dashboards?
Card 1: What is Module 13 Dashboards? · Download PNG
Revision card 2: Tiny example — Module 13 Dashboards
Card 2: Tiny example — Module 13 Dashboards · Download PNG
Revision card 3: Checklist — Module 13 Dashboards
Card 3: Checklist — Module 13 Dashboards · Download PNG