How to Create KPI Cards in Excel (With Arrows, Targets and Sparklines)

KPI cards are the big numbers across the top of a dashboard: revenue this month, orders, average order value, progress to target. They're the first thing anyone looks at, and often the only thing. A good card answers three questions in about a second: what's the number, is it good, and which way is it moving?

This guide shows how to create KPI cards in Excel, step by step: the calculations behind them, a card layout built from cells, arrows and colors that change by themselves, number formats that keep big values short, and a sparkline or progress bar for context. Everything is driven by formulas, so the cards update with your data.

What makes a good KPI card

Every card needs the same four parts:

1. A label that says exactly what the number is and the period it covers: "Revenue, October" rather than "Sales".
2. The value, rounded so it reads at a glance: $48.2K, not $48,217.36.
3. A comparison that says whether the value is good: against a target ("92% of target"), against last period ("▲ 8.2% vs September") or both.
4. A trend for context, such as a 12-month sparkline, so a dip can be seen against the months around it.

Pick four to six KPIs, the numbers that would change a decision. If you're not sure which ones matter in your business, look at the KPIs used in your industry. Our guide to nail salon KPIs shows how that works for one type of business.

How to create KPI cards in Excel in 6 steps

The examples assume an Excel table called Sales with a Month column (the first day of each order's month) and an Amount column. Our guide to building an Excel dashboard without pivot tables shows how to set that up.

Step 1: Calculate the numbers on a Calc sheet

Keep the arithmetic off the dashboard. On a Calc sheet, for each KPI, work out the current value, the value to compare it with and the change. With the current month's first day in B1:

The IF wrappers stop a #DIV/0! error from appearing on the card when there's no comparison value yet.

Step 2: Lay out the card in cells

Build each card in a block of cells about three columns wide and four rows tall on the Dashboard sheet:

• Row 1, the label: 10 or 11 pt, in a muted gray. Type it, or link it to a cell (for example ="Revenue, "&TEXT(Calc!B1,"mmmm")) so the month updates.
• Row 2, the value: =Calc!B2, in 24 to 28 pt bold.
• Row 3, the comparison: =Calc!B5, formatted with arrows in step 3.
• Row 4, the trend: a sparkline, added in step 5.

Give the block a light fill color and leave an empty column between cards so they read as separate tiles. To center text across the card without merging cells, select the cells in a row, open Format Cells > Alignment and choose Center Across Selection. Merged cells cause trouble when you copy or sort, so avoid them.

Step 3: Add arrows and colors that change by themselves

A custom number format can show a green up arrow for positive changes and a red down arrow for negative ones, with no formulas or conditional formatting. Select the comparison cell, press Ctrl+1 (Cmd+1 on a Mac), choose Custom and enter:

[Color10]"▲ "0.0%;[Red]"▼ "0.0%;"– "0.0%

A custom format has up to four sections separated by semicolons: positive numbers; negative numbers; zero; text. Here, positive changes show in green with ▲, negative ones in red with ▼ (and no minus sign, because the arrow already says it), and zero shows as – 0.0%. [Color10] is a darker, more readable green than the bright [Green]. If your Excel doesn't accept [Color10], use [Green] or color the cell with conditional formatting instead.

For KPIs where down is good, such as costs, refunds or no-shows, swap the colors:

[Red]"▲ "0.0%;[Color10]"▼ "0.0%;"– "0.0%

Prefer icons? Select the cell and choose Home > Conditional Formatting > Icon Sets > 3 Arrows (Colored). In Manage Rules > Edit Rule, set the thresholds to Number > 0 and Number >= 0 so the arrow follows the sign of the change.

Don't rely on color alone. Red and green are the hardest pair to tell apart for people with the most common types of color blindness, which is why the arrows matter.

Step 4: Format big numbers so they fit

A comma at the end of a number format divides the display by 1,000, and two commas divide it by a million. The cell keeps its full value for any formula that uses it.

The last format uses conditions in square brackets, so one format works whatever size the number is. It's built for positive values, which suits most headline KPIs.

Step 5: Add a sparkline for the trend

A sparkline is a tiny chart that fits inside one cell. On the Calc sheet, keep the last 12 months of the KPI in a row (for example C2:N2). Then click the trend cell on the card, choose Insert > Sparklines > Line, set the data range to the 12 months and click OK.

On the Sparkline tab, tick Last Point (and perhaps High Point) so the latest month stands out, and pick a color that matches your dashboard. Use Column sparklines for things counted in whole units, like orders, and Line sparklines for trends like revenue.

Step 6: Show progress toward a target

For a KPI with a target, a progress bar shows at a glance how far you've come. Three ways to build one:

• Data bar. Select the share-of-target cell and choose Home > Conditional Formatting > Data Bars. In Manage Rules > Edit Rule, set Minimum to Number 0 and Maximum to Number 1, so a full bar means 100%.
• Text bar. A formula that draws the bar with characters, ten blocks for 100%:
  =REPT("█",ROUND(MIN(1,N(Calc!B6))*10,0))&REPT("░",10-ROUND(MIN(1,N(Calc!B6))*10,0))
• Progress ring. Put the share of target in one cell and the remainder, =MAX(0,1-N(Calc!B6)), in the next. Select both and choose Insert > Insert Pie or Doughnut Chart > Doughnut. Color the remainder light gray, then right-click the ring, choose Format Data Series and set Doughnut Hole Size to about 75%. Add the percentage in the middle with a text box linked to the cell.

Once one card works, copy the block across, change the cell references and adjust the labels. Keep every card the same size and line them up in one row.

KPI cards in practice: net revenue, gross profits, debts and corporation tax at the top of our Profit and Loss Dashboard (dark version).

Cards built from shapes instead of cells

For a more polished look, you can build cards from shapes. Insert a rounded rectangle (Insert > Shapes) as the card background, then add text boxes on top for the label, value and change. To make a text box show a cell's value, select it, click in the formula bar, type =, click the cell (for example Calc!B2) and press Enter. The text box now updates with the cell.

The trade-offs: a linked text box shows the cell's formatted value, but it can't run a formula of its own, so build any text you need, such as "92% of target", in a cell first. And shapes always sit on top of cells, so a sparkline in a cell under the card is hidden. Use a small line chart on the card instead.

Example KPI cards for a small business

Frequently asked questions

What is a KPI card?

A KPI card is a small panel on a dashboard that shows one key performance indicator: its name, its current value, how it compares with a target or the last period, and often a small trend chart.

How many KPI cards should a dashboard have?

Four to six is a good range for one screen. With more than that, nothing stands out. Choose the numbers that would change a decision, and move the rest to a detail sheet.

How do I show up and down arrows in Excel?

Use a custom number format such as [Color10]"▲ "0.0%;[Red]"▼ "0.0%;"– "0.0%, which shows a green up arrow for positive values and a red down arrow for negative ones. Or use Home > Conditional Formatting > Icon Sets > 3 Arrows.

How do I make a KPI card change color automatically?

Color the text with a custom number format, as above, or select the card's cells and add a conditional formatting rule (Home > Conditional Formatting > New Rule) that fills them red or green depending on the value.

Do KPI cards work in older versions of Excel?

Yes. Custom number formats, conditional formatting and sparklines all work in Excel 2016 and later on Windows and Mac. Sparklines date back to Excel 2010 on Windows and Excel 2011 on Mac.

Does Excel have a built-in KPI feature?

Yes, in Power Pivot on Windows. A Power Pivot KPI has a base value, a target value and status thresholds, and shows a status icon in PivotTables built from the Data Model. For most small-business dashboards, the cell-based cards in this guide are simpler and work on Mac too.

What's the difference between a KPI card and a scorecard?

A KPI card shows one measure in detail. A scorecard is a table that lists many KPIs side by side, each with its target and a status, so you can scan them all at once.

Sources

• Microsoft Support: Review guidelines for customizing a number format
• Exceljet: Excel custom number formats
• Microsoft Support: Analyze trends in data using sparklines
• Microsoft Support: Use conditional formatting to highlight information
• Microsoft Support: Present your data in a doughnut chart
• Microsoft Support: Link a chart title, label or text box to a worksheet cell
• Microsoft Support: Key Performance Indicators (KPIs) in Power Pivot