Using Spreadsheet Formulas for Sales Trend Analysis#

📊 Analyze Data 🧮 Calculations & Aggregation Lesson 020

Important

✨ AI-generated content. This page was written with the assistance of an AI language model and is provided as a learning aid. Despite careful review, it may still contain mistakes, omissions, or out-of-date information. Whether you are new to the topic, a team lead, or a senior practitioner, treat it as a starting point rather than an authoritative reference: read it critically and independently verify anything you act on (code, commands, figures, and factual claims) against official documentation and primary sources before relying on it.

Calculating change over time#

The heart of analysis is calculation — turning organised data into the numbers that answer a question — and one of the most common calculations is trend analysis: how a value changes over time. Sales trend analysis is the classic case, and the spreadsheet formulas that compute it — growth rates, running totals, period-over-period comparisons — are foundational analytical tools, opening the calculation stage.

The core trend calculations#

Trends are built from a few standard computations, each a formula pattern:

Period-over-period change — the difference between consecutive periods: = this_period - last_period. The absolute change.
Growth rate (percentage change) — the change relative to the base:
```
= (this_period - last_period) / last_period
```
This expresses growth as a percentage (12% up, 5% down), which is comparable across different scales in a way absolute change is not.
Running (cumulative) total — the sum accumulated up to each period, built with a mix of absolute and relative references so it extends down the column:
```
= SUM($B$2:B2)     fill down: running total through each row
```
The $B$2 anchors the start (absolute) while B2 moves (relative), so each row sums from the beginning through itself.
Comparison to a baseline — each period against a fixed reference (a target, the same month last year), using an absolute reference to the baseline cell.

Reading the trend#

The formulas produce numbers; reading them is the analysis. A growth rate turns raw sales into a story — accelerating, steady, declining. A running total shows progress toward a goal. A year-over-year comparison strips out seasonality that a month-over-month view would confuse. The analyst’s job is not just computing these but interpreting what they reveal about the direction and health of what is measured.

The absolute/relative reference connection#

Trend formulas lean heavily on the relative-versus-absolute reference distinction from Section 2: the running total’s SUM($B$2:B2) and the baseline comparison’s anchored reference both depend on getting the $ right so the formula behaves correctly when filled down. This is the earlier concept put to real analytical work — a reminder that the foundational skills compound into the analytical ones.

The caveat#

Trend calculations can mislead in familiar ways. A percentage change from a small base is volatile and can look dramatic while representing little (100% growth from 2 to 4 sales); a short time span can show a “trend” that is really noise (the sufficiency lessons); and seasonality can masquerade as trend if not accounted for (December sales are not a growth trend). The numbers compute regardless — the judgement is whether the trend is real: enough data, an appropriate base, and seasonality considered. Compute the trend, then ask whether it is signal or artefact. The next lessons add conditional aggregation to the calculation toolkit.

Hint