13.4 KPI Cards
In short: A KPI card is a large number with a label and a comparison, usually made with a shape or a few formatted cells.
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
- On
Calc, create the KPI cells. To make them follow the slicers, read the numbers frompvtKPIwith 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)
- Net Sales
- Build display text in a cell, for example
Calc!O3:="₹"&TEXT(N3/100000,"0.00")&" L"→ ₹49.50 L. - On
Dashboard: Insert › Illustrations › Shapes › Rectangle: Rounded Corners. Draw the card, remove the outline, choose a light fill. - Select the shape, click in the formula bar, type
=Calc!O3and press Enter – the shape now shows the live value. Format it large and bold (28–32 pt). - 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". - 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
Many students type the number into a card by hand – then next month the data changes but the card still shows the old number. Always link the card to a cell (=Calc!O3), and build that cell with GETPIVOTDATA or SUMIFS, so when the slicer changes the card changes too. Show the number in lakhs (L) – ₹4950000 is hard to read.
Ravindra Bagale's Tip – मराठी
बरेच students card मध्ये आकडा हाताने type करतात – मग पुढच्या महिन्यात data बदलतो पण card वर जुनाच आकडा राहतो. Card नेहमी cell ला link करा (=Calc!O3), आणि तो cell GETPIVOTDATA किंवा SUMIFS ने बनवा, म्हणजे slicer बदलला की card पण बदलतो. आकडा लाख (L) मध्ये दाखवा – ₹4950000 वाचायला कठीण आहे.
Ravindra Bagale's Tip – हिंदी
बहुत से students card में number हाथ से type कर देते हैं – फिर अगले महीने data बदल जाता है पर card पर पुराना number ही रहता है. Card को हमेशा cell से link करो (=Calc!O3), और उस cell को GETPIVOTDATA या SUMIFS से बनाओ, ताकि slicer बदलने पर card भी बदले. Number को लाख (L) में दिखाओ – ₹4950000 पढ़ना मुश्किल है.
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.