How to Calculate a Moving Average in Excel, 7-Day and 3-Month
Calculate a 7-day or 3-month moving average in Excel with AVERAGE, handle the first rows and missing days, then add it with the ToolPak or a chart trendline.
- Excel
- Formulas
- Charts

On this page
- How do you calculate a moving average in Excel?
- What do you put in the first six rows?
- How do you always show the average of the last 7 days?
- What if the export skips days?
- How do you calculate a 3-month moving average?
- How do you add a moving average with the Analysis ToolPak or a chart trendline?
- When does a moving average hide what you need to see?
- Where does Glow Reports fit?
- Common questions
A moving average replaces each day's figure with the average of the last few days, so the trend shows through the noise. Daily sales jump on Friday and fall on Sunday. A 7-day moving average smooths that weekly pattern out, and a 3-month moving average does the same for monthly figures. In Excel, the core formula is copied down. Below: that formula, what to do with the first rows, a summary cell that always reads the last 7 days and a version by date for exports that skip days. Then the 3-month version, the Analysis ToolPak, the chart trendline, and when a moving average hides what you need to see.
How do you calculate a moving average in Excel?
Average a fixed number of rows and let the range slide down. With daily sales in column B from row 2, type in C8 and copy it down. Each copy moves both ends of the range one row, so every row averages itself and the six days before it.
The examples use two weeks of daily sales, Monday 2 to Sunday 15 November 2026: dates in column A, sales in column B, the 7-day moving average in column C.
| Date | Sales | 7-day average |
|---|---|---|
| Mon 2 Nov | 980 | |
| Tue 3 Nov | 1,040 | |
| Wed 4 Nov | 1,010 | |
| Thu 5 Nov | 1,120 | |
| Fri 6 Nov | 1,460 | |
| Sat 7 Nov | 1,690 | |
| Sun 8 Nov | 610 | 1,130.00 |
| Mon 9 Nov | 1,050 | 1,140.00 |
| Tue 10 Nov | 1,100 | 1,148.57 |
| Wed 11 Nov | 1,080 | 1,158.57 |
| Thu 12 Nov | 1,170 | 1,165.71 |
| Fri 13 Nov | 1,530 | 1,175.71 |
| Sat 14 Nov | 1,780 | 1,188.57 |
| Sun 15 Nov | 660 | 1,195.71 |
=AVERAGE(B9:B15)AVERAGE "returns the average (arithmetic mean) of the arguments" Microsoft Support. The same page notes that empty cells are ignored while cells that hold zero are counted. That matters for a day with no sales: type 0, or the average skips the day instead of counting it.
Daily sales swing from 610 to 1,780 in this table. The moving average stays between 1,130.00 and 1,195.71 and climbs slowly, which is the story a report needs.
What do you put in the first six rows?
You have two honest choices: leave them empty, or average the days you have so far. A 7-day average needs seven days, so the simplest option is to start the formula in row 8, as above, and leave C2 to C7 blank.
If the report needs a figure from day one, let the window grow until it reaches seven days. Type this in C2 and copy it down:
=AVERAGE(INDEX($B$2:$B$15,MAX(1,ROWS($B$2:B2)-6)):B2)counts the rows from the top to the current one. Minus 6, it gives the first row of the window, and MAX stops it at row 1 of the data. In C5 the window covers four days, so the result is 1,037.50. From C8 on, it returns the same figures as the plain formula. Label those first rows in the report, because an average of four days is not a 7-day average.
How do you always show the average of the last 7 days?
Find the last filled row with COUNT and average the seven rows that end there. This is the summary cell for the top of a report, and it updates when you paste new days below the data.
The common answer on forums uses OFFSET. With a header in B1 and numbers from B2 down:
=AVERAGE(OFFSET(B1,COUNT(B:B)-6,0,7,1))OFFSET "returns a reference to a range that is a specified number of rows and columns from a cell or range of cells" Microsoft Support. Here it starts from B1, moves down 8 rows when 14 days are filled, and takes a block 7 rows tall and 1 column wide.
OFFSET is on Microsoft's list of volatile functions, which Excel recalculates every time anything changes, and "too much reliance on volatile functions can make recalculation times slow" Microsoft Learn. One cell is fine. A whole column of OFFSET formulas on a large export is not. INDEX gives the same result without the recalculation cost:
=AVERAGE(INDEX(B:B,COUNT(B:B)-5):INDEX(B:B,COUNT(B:B)+1))Both formulas assume the column holds numbers only below the header, with no gaps and no total row. A blank day breaks the count, which leads to the next problem.
What if the export skips days?
Average by date instead of by row. AVERAGEIFS "returns the average (arithmetic mean) of all cells that meet multiple criteria" Microsoft Support, so you can ask for every row whose date falls in the seven days up to the current one.
Exports from a till or a shop often have no row for a day without orders. Suppose the shop was closed on Sunday 8 November and the export has no row for it. The sheet now has 13 rows, and Saturday 14 November sits in row 13. A row-based reaches back to Saturday 7 November, so it covers eight calendar days and returns 1,342.86.
=AVERAGEIFS($B$2:$B$14,$A$2:$A$14,">"&A13-7,$A$2:$A$14,"<="&A13)This counts the six days that have a row. If the closed day should count as zero, divide the sum of the window by 7 instead. SUMIFS adds the values that meet every criterion Microsoft Support:
=SUMIFS($B$2:$B$14,$A$2:$A$14,">"&A13-7,$A$2:$A$14,"<="&A13)/7Three formulas, three answers for the same week: 1,342.86, 1,285.00 and 1,101.43. Choose before you build the chart. Average sales per open day and average sales per calendar day are both valid, but they are different numbers. If a window has no rows at all, AVERAGEIFS returns , as the same Microsoft page states. Both formulas read dates, so they also survive a sort.
How do you calculate a 3-month moving average?
Use the same formula on monthly totals with a window of three rows. With monthly revenue in B2:B13, type in C4 and copy it down. Each month then shows the average of itself and the two months before.
The figures below are the 2025 monthly revenue used in how to calculate a running total in Excel, with the average rounded to the nearest unit.
| Month | Revenue | 3-month average |
|---|---|---|
| Jan 2025 | 12,400 | |
| Feb 2025 | 11,800 | |
| Mar 2025 | 13,100 | 12,433 |
| Apr 2025 | 12,900 | 12,600 |
| May 2025 | 14,200 | 13,400 |
| Jun 2025 | 13,600 | 13,567 |
| Jul 2025 | 12,100 | 13,300 |
| Aug 2025 | 11,500 | 12,400 |
| Sep 2025 | 14,800 | 12,800 |
| Oct 2025 | 15,300 | 13,867 |
| Nov 2025 | 16,100 | 15,400 |
| Dec 2025 | 18,400 | 16,600 |
=AVERAGE(B11:B13)The summer dip shows in August, at 12,400, and the year-end rise shows from October. If your export is daily, total it by month first with a pivot table, as shown in how to create a pivot table by month, then average the monthly totals.
How do you add a moving average with the Analysis ToolPak or a chart trendline?
Both tools compute the same trailing average without a formula. The ToolPak writes the results into cells. The trendline draws them on the chart only.
The Analysis ToolPak is an add-in you load once. On Windows, go to FileOptionsAdd-ins, choose Excel Add-ins in the Manage box, select Go and check Analysis ToolPak. On a Mac, use ToolsExcel Add-ins. Microsoft notes that "the Data Analysis command is available on the Data tab" afterwards Microsoft Support. Then open DataData Analysis, pick Moving Average, set the Input Range to the sales column, the Interval to 7 and an Output Range. Microsoft's description of the tool says that "a moving average provides trend information that a simple average of all historical data would mask" Microsoft Support. The tool runs once. New rows need a new run, so formulas are easier to keep in a report you update every month.
For a chart, add a trendline to the sales series and set its type to moving average. Select the chart, open the Format Trendline pane and, "under Trendline Options, select Moving Average", then set the period Microsoft Support. With a period of 2, "the average of the first two data points is used as the first point in the moving average trendline" Microsoft Support. For daily data, set the period to 7.
In Google Sheets, and AVERAGEIFS work the same way (Google Docs Editors Help, Google Docs Editors Help). For a chart, double-click it and open CustomizeSeriesTrendline. The moving average type comes with a Periods setting Google Docs Editors Help. For which chart suits a trend, see types of charts for a report.
When does a moving average hide what you need to see?
When the window is wrong for the data, or when the reader needs the single day. A trailing average lags: a change on Monday takes a full week to show in full. A spike gets spread over seven days instead of standing out.
- The window should match the cycle. Seven days removes a weekly pattern, because every window holds one of each weekday. A 5-day window on shop sales holds two weekends in some windows and none in others, so it adds bumps of its own.
- Peaks disappear. Saturday 14 November sold 1,780, but its 7-day average reads 1,188.57. If the report is about a promotion day, show the daily bars with the average line on top.
- Compare like with like. The 7-day average went from 1,130.00 on 8 November to 1,195.71 on 15 November, a 5.8% rise, which is the same as the change in weekly totals, 7,910 to 8,370. To turn two figures into a growth rate, see how to calculate percentage change in Excel.
5.8% is the rise of the 7-day average from 8 to 15 November, equal to the change in weekly sales.
Where does Glow Reports fit?
The formulas above work in any sheet. A monthly report repeats them on a fresh export: decide how to treat missing days, rebuild the average, redraw the chart, update the text. Each step is a chance to average eight days instead of seven.
Glow Reports starts from the export itself. You upload the CSV or Excel file and ask, in plain language, for daily sales with a 7-day moving average, or monthly revenue with a 3-month average. The AI writes the SQL, builds the chart and drafts the text. The SQL sits under each figure, so you can check how the window is defined and how days without sales are handled, and edit the query if needed. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits. For the rest of a monthly report, see how to build a monthly sales report in Excel.
Common questions
What is the difference between a moving average and a rolling average?
None. Both names describe the same calculation: the average of the last N periods, recalculated as the window moves forward. "Simple moving average" is the same thing again, as opposed to weighted or exponential versions that give recent days more weight. For a weighted version, see how to calculate a weighted average in Excel.
How do I get one average per week instead of a rolling one?
Average fixed blocks of seven rows instead of a sliding window. In a Microsoft Tech Community thread, a user who wanted one average for each block of seven numbers, instead of a rolling one, got an answer built on INDEX that jumps seven rows per copy Microsoft Tech Community. A pivot table grouped by week, or AVERAGEIFS between two dates, gives the same result.
Should I put the average on the last day of the window or in the middle?
Excel's tools put it on the last day. Both the ToolPak and the chart trendline use the current point and the ones before it. A centred average, from three days before to three days after, lines up better with peaks but cannot be computed for the last three days of the data, which are often the ones the report is about.
How do I make the moving average update when I add new days?
Copy the formula down far enough, or turn the range into an Excel table so it fills down by itself. For a single summary cell, use the INDEX version with COUNT shown above, which always reads the last seven filled rows.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your sales export to Glow Reports and get daily sales with a 7-day moving average, with the SQL behind each figure.


