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?
- Select the chart and click the plus icon beside it, then tick Trendline. Excel adds a linear one by default.
- Right-click the dotted line and choose Format Trendline to switch the type.
- For Moving Average set the Period, for example 3, so each point averages three months.
- 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?
| Month | Spend (₹) | 3-month average (₹) |
|---|---|---|
| Apr | 48,000 | - |
| May | 52,000 | - |
| Jun | 47,500 | 49,167 |
| Jul | 51,000 | 50,167 |
| Aug | 49,500 | 49,333 |
| Sep | 63,000 | 54,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
- Plot at least twelve months of data, from April to March if you follow the financial year.
- Mark one-off items in a note and consider showing the chart with and without them.
- Add a 3-month moving average as the main line to read.
- Keep a straight trendline only if the R-squared is reasonably high and you understand the months that drive it.
- 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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


