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

13. Excel Dashboards

13.4 KPI Cards

By Last updated:

KPI cards from formulas with example — in 30 seconds

What: A KPI card is one big number with a short label, fed by a formula or GETPIVOTDATA.

Why: A manager reads four big numbers in five seconds. A table of 50 rows takes five minutes.

Tiny example: Sales ₹2,870 ÷ 10 orders = AOV ₹287. Against a target of ₹3,000, achievement is 95.7%.

First look at the data (before → after):

KPI cards from formulas with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — KPI cards on the Dashboard

Card Shows
Sales ₹2,870
Orders 10
AOV ₹287
Target achieved 95.7%

About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Neha Shah) are made up for this lesson on kpi cards.

Before table and formula as text

Before — Calc sheet inputs

Cell Meaning Value
N3 Total sales (₹) 2,870
N4 Orders 10
N5 Monthly target (₹) 3,000

About this data: Demo table only — not real customer or company data. Built to practise kpi cards.

Formula / click path

AOV: =IFERROR(N3/N4,0)
Target achievement: =IFERROR(N3/N5,0)   (format %)
Card label: ="Sales ₹"&TEXT(N3,"#,##0")

Do it step by step:

  1. Put the inputs on a hidden Calc sheet: sales in N3, orders in N4, target in N5.
  2. Type =IFERROR(N3/N4,0) for AOV and =IFERROR(N3/N5,0) for achievement.
  3. On the Dashboard, draw a rectangle with Insert › Shapes.
  4. Click the shape, type = in the formula bar, click the Calc cell and press Enter.
  5. Make the number large (28 pt) and add a small label above it.

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

About this data: Made-up rows so you can copy the pattern for kpi cards. Currencies may use ₹, £, € or $.

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.

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 kpi cards on a blank sheet for GreenField Dairy.

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

2. Change one input for Sana Sheikh 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 James O'Neill.

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 kpi cards to a 5-row made-up GreenField Dairy 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 kpi cards.

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