Add Variance Arrows and Trend Indicators to a Dashboard

A dashboard figure on its own invites the question "is that good?". A small arrow beside it — green and pointing up when revenue rose, red and pointing down when it fell — answers before anyone reaches for last month's report. The details are where most implementations go wrong: arrows made by concatenating text break the numbers underneath, so the variance can no longer be summed or charted; the same green "up" is applied to costs and complaint counts, where up is bad; and a change of 0.1% gets the same bold arrow as a change of 30%. This page adds arrows with custom number formats, which change how a value looks but leave it a number, gives measures where a fall is good their own format, adds a dead band so trivial changes show as flat, builds a trend indicator over the last three periods, and checks the displayed result. It belongs to Building Multi-Sheet Excel Dashboards.

Prerequisites

pip install openpyxl pandas

LibreOffice (soffice) is used to recalculate the saved workbook and export what each cell displays, so the formats can be checked without opening Excel. The data is six KPIs with this month's and the three previous months' values; two of them — operating cost and complaints — are measures where a fall is the good direction.

Step 1 — The figures and the variance formula

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font

kpis = pd.DataFrame({
    "Measure": ["Revenue", "Orders", "Conversion", "Operating cost", "Complaints", "Avg order value"],
    "Good when": ["up", "up", "up", "down", "down", "up"],
    "Sep": [1284500, 3412, 0.0312, 412800, 37, 376.5],
    "Aug": [1215200, 3389, 0.0298, 398900, 52, 358.6],
    "Jul": [1180400, 3350, 0.0301, 401200, 49, 352.4],
    "Jun": [1150900, 3302, 0.0295, 405100, 44, 348.5],
})

wb = Workbook()
ws = wb.active
ws.title = "Dashboard"
ws.append(["Measure", "Sep", "Aug", "Jul", "Jun", "vs last month", "Trend"])
for c in ws[1]:
    c.font = Font(bold=True)
for i, row in enumerate(kpis.itertuples(index=False), start=2):
    ws.append([row.Measure, row.Sep, row.Aug, row.Jul, row.Jun])
    ws[f"F{i}"] = f"=IF(C{i}=0,NA(),B{i}/C{i}-1)"
print(ws.max_row - 1, "measures; variance in column F as a formula")

The variance is a formula — this month over last month, minus one — so the workbook stays live if someone corrects a figure, and it returns #N/A rather than a division error when last month was zero. Most importantly it is a number. Everything that follows changes only how it is displayed.

Step 2 — Arrows from a custom number format

A number format can have up to four sections separated by semicolons — positive; negative; zero; text — each with its own colour and literal characters. Put the arrow in the format:

UP_GOOD = '[Color10]▲ 0.0%;[Red]▼ 0.0%;[Color55]► 0.0%'
DOWN_GOOD = '[Red]▲ 0.0%;[Color10]▼ 0.0%;[Color55]► 0.0%'

for i, good in enumerate(kpis["Good when"], start=2):
    ws[f"F{i}"].number_format = UP_GOOD if good == "up" else DOWN_GOOD
print(ws["F2"].number_format)
print(ws["F5"].number_format)

The first section applies to positive values and shows a green up arrow; the second applies to negative values and shows a red down arrow — and because a negative section's format has no minus sign of its own, the value is shown without one, so a fall of 3.5% reads "▼ 3.5%" rather than "▼ -3.5%". The third section handles exactly zero with a neutral sideways arrow. [Color10] is a dark green from Excel's standard 56-colour palette — easier to read than the bright [Green] — and [Color55] a grey; [Red] is one of the eight named colours. The cell's value remains −0.035, so totals, charts and further formulas still work. Number format syntax in general is covered in Write Custom Number Format Codes in Excel with Python.

For measures where down is good — costs, complaints, days to pay, error rates — the arrow still shows the direction of change (up means it went up) but the colours swap. Keeping the arrow honest and moving the judgement into the colour avoids the confusing alternative of a down arrow for a cost that rose.

Step 3 — A dead band so small changes look flat

An arrow on a change of 0.1% suggests news where there is none. Number formats can test conditions, but only two at a time; the cleaner way to add a threshold is to round the variance into the band inside the formula, so values within ±1% become exactly zero and pick up the neutral section:

BAND = 0.01
for i in range(2, ws.max_row + 1):
    ws[f"F{i}"] = f"=IF(C{i}=0,NA(),IF(ABS(B{i}/C{i}-1)<{BAND},0,B{i}/C{i}-1))"
print(ws["F3"].value)

Orders rose from 3,389 to 3,412 — 0.7% — which is within the band and now shows as flat. Where the exact variance must still be available, keep it in a hidden column and put the banded version next to it for display. The width of the band is a business decision: 1% suits revenue, but a measure that normally moves by 0.1% a month, such as a conversion rate measured in basis points, needs a band of its own, so in practice the band is often a per-measure column in the data sheet rather than one constant.

Step 4 — A trend indicator over three periods

Month-on-month arrows are noisy. A trend column compares this month with the average of the previous three, which smooths out one-off swings, and shows a symbol chosen by formula:

from openpyxl.formatting.rule import FormulaRule

for i, good in enumerate(kpis["Good when"], start=2):
    ws[f"G{i}"] = (f'=IF(B{i}>AVERAGE(C{i}:E{i})*(1+{BAND}),"↑",'
                   f'IF(B{i}<AVERAGE(C{i}:E{i})*(1-{BAND}),"↓","→"))')
green, red = Font(color="1A7F37", bold=True), Font(color="CF222E", bold=True)
last = ws.max_row
for good, up_font, down_font in [("up", green, red), ("down", red, green)]:
    rows = [i for i, g in enumerate(kpis["Good when"], start=2) if g == good]
    rng = " ".join(f"G{i}" for i in rows)
    ws.conditional_formatting.add(rng, FormulaRule(formula=[f'G{rows[0]}="↑"'], font=up_font))
    ws.conditional_formatting.add(rng, FormulaRule(formula=[f'G{rows[0]}="↓"'], font=down_font))
ws.column_dimensions["A"].width = 18
for col in "BCDEFG":
    ws.column_dimensions[col].width = 13
wb.save("variance.xlsx")

The trend arrows are text, so they are coloured with conditional formatting rather than a number format; the rule ranges list the up-is-good and down-is-good rows separately so each gets the right colours, and the relative reference G{rows[0]} is adjusted by Excel for every cell in the range. Using different glyphs for the two columns — triangles for the monthly variance, thin arrows for the trend — helps readers tell them apart. Icon sets are another way to show direction, with their own thresholds; they are covered in Add Icon Sets to Excel Cells with openpyxl.

Step 5 — Check what the cells display

Recalculate with LibreOffice and export to CSV with the "save cell contents as shown" option, which writes each cell as displayed — arrows and all — so the formats can be checked in a test:

import csv, subprocess

AS_SHOWN = "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true"     # 9th token: save as shown
subprocess.run(["soffice", "--headless", "--convert-to", AS_SHOWN, "--outdir", "calc", "variance.xlsx"], check=True, capture_output=True)
with open("calc/variance.csv", encoding="utf-8") as f:
    for row in csv.reader(f):
        print(f"{row[0]:<16} {row[5]:>13}  {row[6]:>5}")

Revenue shows "▲ 5.7%" and a rising trend. Orders, within the dead band month on month, show "► 0.0%" — yet the trend column still shows a rise, because 3,412 is 1.9% above the average of the three previous months: steady small gains add up, which is exactly what the trend column is for. Operating cost rose 3.5% — an up arrow, red in Excel because the format for costs swaps the colours; complaints fell 28.8%, a down arrow that is green for the same reason. Without the filter option LibreOffice writes the underlying values instead (5.70276…%), so the option matters. The CSV carries the characters but not the colours, so a test can assert both the text and, separately, the number_format string on each cell. Exporting the dashboard to PDF and looking at it remains the final check of colours; Convert Excel to PDF on Linux with LibreOffice shows how.

Common pitfalls

• Arrows concatenated into text. The variance stops being a number; use a number format.
• Green for every rise. Swap colours for measures where down is good.
• Arrows on noise. Add a dead band so tiny changes show as flat.
• Minus signs after arrows. Give the negative section its own format without a sign.
• Bright [Green]. Use a palette colour such as [Color10] for legibility.

Conclusion

Keep variances as numbers and let a custom number format add the arrow: [Color10]▲ 0.0%;[Red]▼ 0.0%;[Color55]► 0.0% for measures where up is good, with the colours swapped where down is good. Fold a dead band into the formula so small changes display as flat, add a trend column that compares with the average of recent periods, and check the displayed text by exporting to CSV with LibreOffice.

Frequently asked questions

How do I show an up or down arrow next to a percentage in Excel? Use a custom number format with sections for positive, negative and zero values, such as Color10▲ 0.0%;Red▼ 0.0%;► 0.0%. The value stays a number.

How do I show red for an increase in costs? Use a second format with the colours swapped, Red▲ 0.0%;Color10▼ 0.0%, and apply it to measures where a decrease is good.

How do I stop tiny changes showing an arrow? Return 0 from the variance formula when the absolute change is below a threshold, so the zero section of the format shows a neutral symbol.

Can I check number formats without opening Excel? Recalculate and export the workbook to CSV with LibreOffice using the option to save cell contents as shown; the CSV then contains the displayed text of each cell, including arrows.

Related

• Up one level: Building Multi-Sheet Excel Dashboards — dashboards from Python.
• Add a KPI Summary Block to an Excel Dashboard — headline tiles to put arrows on.
• Build a Monthly KPI Scorecard with Targets — comparing against targets.
• Lay Out a Dashboard Sheet on a Grid — arranging the dashboard.