Ravindra BagaleCourses & study guides

13. Excel Dashboards

13.4 KPI Cards

By Last updated:

A KPI card is a large number with a label and a comparison, usually made with a shape or a few formatted cells.

Steps in Excel

  1. On Calc, create the KPI cells. To make them follow the slicers, read the numbers from pvtKPI with GETPIVOTDATA (type = and click a value inside the pivot – Excel writes the formula for you, Module 7.10):
    • Net Sales Calc!N3: =GETPIVOTDATA("Sum of Amount",$I$3)
    • Delivered Orders Calc!N4: =GETPIVOTDATA("Count of Order ID",$I$3)
    • AOV Calc!N5: =IFERROR(N3/N4,0)
    • vs Target Calc!N6: =N3/XLOOKUP("Mar-2026",tblTargets[Month],tblTargets[Target]) (Microsoft 365 / Excel 2021+; use INDEX-MATCH in older versions)
  2. Build display text in a cell, for example Calc!O3: ="₹"&TEXT(N3/100000,"0.00")&" L" → ₹49.50 L.
  3. On Dashboard: Insert › Illustrations › Shapes › Rectangle: Rounded Corners. Draw the card, remove the outline, choose a light fill.
  4. Select the shape, click in the formula bar, type =Calc!O3 and press Enter – the shape now shows the live value. Format it large and bold (28–32 pt).
  5. Add a second small text box for the label ("Net Sales") and a third linked to the comparison text, for example ="▲ "&TEXT(N6-1,"0.0%")&" vs target".
  6. Group the three objects (select all › right-click › Group) and copy the group for the next KPI.

Worked example – the four cards.

Card Main value Comparison text
Net Sales ₹49.50 L ▲ 3.1% vs target (₹48.00 L)
Delivered Orders 13,200 of 14,000 orders
AOV ₹375 ▲ ₹13 vs Feb (₹362)
Avg Delivery 11.6 min ✓ within 12-min promise

A shape can link only to a single cell (not a formula), which is why the text is built in a Calc cell first.

Ravindra Bagale's Tip

Khup students card madhe aakda haatane type kartat – mag pudhchya mahinyat data badalto pan card var junach aakda rahto. Card nehmi cell la link kara (=Calc!O3), aani to cell GETPIVOTDATA kiwa SUMIFS ne banva, mhanje slicer badalla ki card pan badalto. Aakda lakh (L) madhe dakhva – ₹4950000 vachayla kathin aahe.

Practice task

Build the Cancellation Rate card: add Count of Order ID for Status = Cancelled to a pivot, calculate 560 ÷ 14,000 = 4.0%, show "4.0%" with the comparison text "✓ below 5% target", and link it to a rounded rectangle.