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

13. Excel Dashboards

Practice and Answer Key: KPI Cards for a City Dashboard

By Last updated:

← All Excel dashboards

Goal of this practice: Calculate five KPI card values and their labels for one city and one month.

Suppose Rani is building the Nashik November dashboard. The Calc sheet already has the totals below. She needs five KPI cards. All data is fictional.

Cell Meaning Value
B2 Sales Nov (₹) 1,84,500
B3 Orders Nov 615
B4 Delivered orders Nov 584
B5 Target Nov (₹) 2,00,000
B6 Sales Oct (₹) 1,62,000

Exercise (one under the other):

  1. In D2, calculate AOV (sales ÷ orders), safe from #DIV/0!.
  2. In D3, calculate the delivery rate (delivered ÷ orders) as %.
  3. In D4, calculate target achievement as %.
  4. In D5, calculate month-on-month growth as %.
  5. In D6, build a card label like "Sales ₹1,84,500".
  6. Link five shapes on the Dashboard to D2:D6 and B2.

Expected result (check your numbers):

Card Formula result Shows as
AOV 300.00 ₹300
Delivery rate 0.9496 95.0%
Target achieved 0.9225 92.2%
Growth vs Oct 0.1389 13.9%
Label — Sales ₹1,84,500

Check: 1,84,500 ÷ 615 = 300.00; 584 ÷ 615 = 95.0%; 1,84,500 ÷ 2,00,000 = 92.2%; (1,84,500 − 1,62,000) ÷ 1,62,000 = 13.9%.

Answer key (open after you try) 
D2: =IFERROR(B2/B3,0)            → format ₹#,##0
D3: =IFERROR(B4/B3,0)            → format 0.0%
D4: =IFERROR(B2/B5,0)            → format 0.0%
D5: =IFERROR(B2/B6-1,0)          → format 0.0%
D6: ="Sales "&TEXT(B2,"[>=100000]₹##\,##\,##0;₹##,##0")

The TEXT format in D6 uses the Indian lakh grouping from section 1.5.

Common mistakes:

What you see Why Fix
#DIV/0! on the AOV card Orders cell B3 is empty before the month starts Wrap the division in IFERROR(…,0)
Growth shows 0.1389 Percent format missing Format the cell as 0.0%
Label shows Sales 184500 Joined B2 without TEXT Wrap B2 in TEXT with a number format
Card does not update The shape text was typed, not linked Select the shape, type = in the formula bar, click the cell

Ravindra Bagale's rule: every card number comes from one Calc cell, never typed by hand.

Thodkyaat sangaycha tar (quick recap)

  • Aadhi audience, prashna aani KPI definitions – mag Excel.
  • Data, Calc aani Dashboard sheets vegle; sagle pivots ekach tblOrders var.
  • KPI cards = shape linked to a cell; cell = GETPIVOTDATA kiwa SUMIFS.
  • PivotCharts with field buttons hidden; insight titles; ekach highlight rang.
  • Slicers aani timeline → Report Connections → sagle pivots.
  • Refresh All, cross-check formulas, protect, aani ek screen madhe.

Aata pudhe jaauya – dashboard tayar zala, aata to surakshit kasa theva aani share/print kasa karaycha te bagha.