Spreadsheets

Add a trendline and moving average to an expense chart

Add a linear trendline or a 3-month moving average to an Excel expense chart, read the R-squared value sensibly and know when lumpy spending makes it misleading.

Editorial illustration for Add a trendline and moving average to an expense chart

Click your expense chart, choose the plus icon (Chart Elements), tick Trendline, and pick Linear for a straight best-fit line or Moving Average for a smoothed line. In More Options you can display the R-squared value and equation. A trendline summarises direction; it does not predict next month, especially when spending comes in lumps.

How do I add each type of trendline?

  1. Select the chart and click the plus icon beside it, then tick Trendline. Excel adds a linear one by default.
  2. Right-click the dotted line and choose Format Trendline to switch the type.
  3. For Moving Average set the Period, for example 3, so each point averages three months.
  4. Tick Display R-squared value on chart if you want it, and give the line a colour that differs from the data line.

What does a linear trendline say about my spending?

Take six hypothetical months of spend: ₹48,000, ₹52,000, ₹47,500, ₹51,000, ₹49,500 and ₹63,000. Number the months 1 to 6 in column C and the spend in column B, then use the functions Excel uses behind the trendline: =SLOPE($B$2:$B$7,$C$2:$C$7) and =RSQ($B$2:$B$7,$C$2:$C$7). The slope comes to about ₹2,029 a month, which suggests spending rising by roughly two thousand rupees each month.

Now remove September and recalculate on the first five months. The slope falls to ₹200 a month. One lumpy month moved the answer by a factor of ten, which is the central weakness of straight-line trends on small data sets.

What does the R-squared number mean?

R-squared ranges from 0 to 1 for a straight-line fit. For the six months above it is about 0.44, meaning the straight line accounts for around 44% of the ups and downs in the data and the rest is scatter around it. A high value says the points hug the line; it does not say why spending is changing, and it does not make the next month predictable. With only six points, treat even a high value with caution.

How does a moving average behave?

MonthSpend (₹)3-month average (₹)
Apr48,000-
May52,000-
Jun47,50049,167
Jul51,00050,167
Aug49,50049,333
Sep63,00054,500

June's average is (48,000 + 52,000 + 47,500) / 3 = ₹49,167. You can build the same column by hand with =AVERAGE(B2:B4) in the June row and fill down. The first two months have no average because three months are not yet available. The September spike lifts the average to ₹54,500, but gently, which is why moving averages suit seasonal or lumpy data better than a straight line.

When does a trendline mislead?

  • Annual premiums, school fees and festival shopping create spikes that look like a change of habit.
  • With fewer than about twelve points, a straight line can be steered by one month.
  • Extending the line forward (Forecast Forward in Trendline Options) assumes the pattern simply continues. It rarely does for household spending.
  • A moving average lags: it reacts a few months late to a real change, such as a new rent.

A routine that works

  1. Plot at least twelve months of data, from April to March if you follow the financial year.
  2. Mark one-off items in a note and consider showing the chart with and without them.
  3. Add a 3-month moving average as the main line to read.
  4. Keep a straight trendline only if the R-squared is reasonably high and you understand the months that drive it.
  5. Write what the line assumes beside the chart, for example: straight-line fit, six months, R-squared 0.44. A reader then knows how much weight to give it.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports