Summarize a whole series’ direction in one symbol — compare the latest value to the prior (or to an average) and show ▲/▼/─. A compact trend cue beside a sparkline or KPI.
The example
Last point higher than the prior.
| A | B | |
|---|---|---|
| 1 | Series | Trend |
| 2 | …, 95, 102 | ▲ |
The formula
The formula:
How it works
How it works:
- Grab the latest and prior values — e.g.
INDEX(series, COUNT(series))and the one before. - A nested
IFreturns ▲ rising, ▼ falling, ─ flat. - For a steadier read, compare the latest to a moving average instead of a single prior point.
- Place it beside a sparkline so the symbol summarizes what the mini-chart shows.
Last-vs-prior is noisy; last-vs-average is steadier. A single down-tick can flip a last-vs-prior arrow even in a rising trend. Comparing the latest value to a short moving average — latest vs AVERAGE(last_n) — smooths the noise and better reflects the overall direction, which is usually what a dashboard trend cue should convey.
Try it: interactive demo
A series (comma-separated).
Variations
Latest value
Last point:
vs moving average
Smoother:
Slope sign
Overall trend:
Pitfalls & errors
Last-vs-prior is jumpy. Compare to a moving average or slope for a steadier signal.
Grab the real latest. Use INDEX with COUNT so it tracks the last filled point.
Flat needs a band. Exact equality is rare — allow a small tolerance for ─.
Practice workbook
Frequently asked questions
How do I show a trend arrow from a series in Excel?
How do I make the trend less noisy?
How do I get the latest value in a column?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class