KPI Cards and Conditional Highlights

A KPI card is a single headline number that tells the reader the one thing they most need to know: total revenue, orders today, average order value. Dashboards usually open with a row of these cards.

A good card combines three parts: a clear label, the big number from a formula, and often a small comparison such as change versus last period.

In this lesson you will build KPI numbers with aggregation formulas, add period-over-period comparisons, and use conditional logic to highlight values that need attention.

Start with the core metric. Total revenue from a Sales sheet is just a SUM over the amount column.

Place this in the card's value cell:

For an order count, use COUNTA on a column that always has a value, like an order ID. The card label, a plain text cell above it, names what the number means. Keep each card to one number so it reads instantly.

Many KPIs are ratios. Average order value divides total revenue by order count. Build it from two aggregates.

One compact way uses AVERAGE directly:

If you prefer to derive it, divide your total card by your count card. Either way, this card now shows the typical sale size and updates whenever the data changes.

A KPI is more meaningful next to a goal. Suppose your monthly revenue target sits in cell B1. Compute how far above or below target you are.

The variance is current minus target:

You can also express it as a percentage of target with =SUM(Sales!C2:C500)/B1. Showing 1.12 tells the reader you hit 112 percent of goal. Comparison transforms a raw number into a story.

Readers love to see momentum. Compute the percentage change from a previous period. Say this period's total is in D2 and last period's is in D3.

The growth formula is:

If this period is 120 and last was 100, you get 0.2, meaning 20 percent growth. Format the cell as a percentage. A small up or down indicator like this turns a flat card into a trend at a glance.

You can show direction with words or symbols using IF. Read the change cell and pick a label.

This returns an up or down marker with the percentage:

When the change in E1 is positive you see an upward note; otherwise a downward one. Joining text and a formatted number keeps the card compact and readable without needing a chart.

Conditional formatting changes a cell's color based on a rule, so important values jump out. Select your KPI cells, open Conditional Formatting, and add a rule driven by a formula.

To flag any card below target in red, use a formula rule like:

Cells where the rule is TRUE get the format you choose. This is how a dashboard turns red when sales miss goal and green when they beat it, without anyone reading the numbers closely.

Sometimes you want the flag as text in the cell itself, not just color. IF with comparison operators creates a status word.

To label performance against target:

This shows On Track when you meet goal and Behind when you fall short. A status column like this pairs well with conditional formatting that colors the word, giving both a visual and a written signal.

Real KPIs often have more than two states: good, warning, critical. The IFS function checks conditions in order and returns the first match, which reads cleaner than nested IFs.

To grade performance into three bands:

IFS tests top to bottom, so put the strictest condition first. Pair each band with a color rule and your cards communicate health at a single glance.

Beyond single rules, color scales shade a range of numbers along a gradient so the highest and lowest values stand out without any formula. They are ideal for a column of regional totals or daily figures.

Select the range, open Conditional Formatting, and pick a color scale such as green to red.

• The largest values glow green, the smallest red, and the middle blends between.
• The scale recalculates automatically as data changes.

Color scales pair beautifully with KPI cards: the cards give the headline, the scaled column shows where each value sits in the spread.

Lay your cards out as a clean row at the top of the dashboard. Each card is a small block: a label cell, a value formula, and a comparison or status beneath it.

• Card 1: Total Revenue, =SUM(Sales!C2:C500), with a target variance.
• Card 2: Orders, =COUNTA(Sales!A2:A500).
• Card 3: Avg Order Value, =AVERAGE(Sales!C2:C500), with a growth percent.

Conditional formatting colors the comparison cells. The row gives an instant summary before the reader scrolls into detail.

Test your understanding of building a multi-level status flag.

You built the headline layer of a dashboard:

• Aggregation formulas like SUM, COUNTA, and AVERAGE produced the big KPI numbers.
• Variance and growth formulas added comparison context against a target and a prior period.
• IF and IFS turned numbers into status words like On Track, Watch, and Critical.
• Conditional formatting with formula rules colored cells automatically to flag what matters.

Combined with the summary tables, formula pivots, and interactive dropdowns from earlier lessons, you can now build a complete, live, formula-driven dashboard.