In short
- A 7-day rolling average adds up the last 7 days and divides by 7, then moves forward one day at a time.
- In Excel or Google Sheets the formula is =AVERAGE(E2:E8), filled down the column.
- Save the file as a workbook, not a CSV, or you lose the formulas.
- Put the same line in your CRM or Looker Studio dashboard so it updates on its own.
What a rolling average is
A rolling average is also called a moving average. You take the average of a fixed window of days, then move the window forward one day and do it again.
Each point on the line is the average of the days before it. One very good or very bad day moves the line a little, not a lot. That makes the real trend easier to see.
The same method works for other windows. Use 30 days for a monthly view or 90 days for a quarterly one.
How the 7-day average is calculated
Add up the last 7 days and divide the total by 7. Day 1 + day 2 + ... + day 7, then divide by 7. The next day, drop day 1, add day 8, and divide by 7 again.
A 7-day window covers every day of the week once. Weekday and weekend patterns cancel out, so a quiet Sunday does not look like a drop.
The first 6 days of your data have no 7-day average yet, because there are not 7 days before them. Start the column on day 7.
Steps to build a 7-day rolling average in a spreadsheet
Export the data
Export your daily numbers (leads, calls, sales, sessions) to Excel or Google Sheets, one row per day. Sort from oldest to newest.
Add a column
Add a new column next to the daily numbers and name it "7-Day Rolling Average".
Enter the formula on day 7
If your numbers are in column E and the first day is row 2, go to row 8 and enter =AVERAGE(E2:E8). Use a colon for the range, not a minus sign.
Fill it down
Double-click the small square in the bottom-right corner of the cell, or drag it down. Each row now averages its own last 7 days.
Chart it
Select the date column and the average column and insert a line chart. Add the daily numbers as a second line if you want to see both.
Keep adding data
Add each new day at the bottom and fill the formula down one more row. The average keeps rolling.
How to read it
Say you ran a short campaign at the start of a month. The daily chart shows a spike, then a sharp drop. The rolling average shows a gentle bump, and tells you whether the overall level ended up higher than before.
- Use the daily numbers to spot one-off events: a launch, a campaign spike, a broken form.
- Use the rolling average to judge the trend: are you getting more leads than last month, or fewer?
- When the rolling line turns up or down, check what changed in that window: campaigns, budget, website or staffing.
- Cross-check the best periods against your CRM and GA4, so you know which campaigns brought leads that turned into sales.
Talk through your project
A free call about your integration, your CRM or whatever you are stuck on. Honest advice, even if you never hire us. No obligation.
Why use a 7-day rolling average
- It shows long-term trends that daily swings hide.
- It helps you find your strongest periods for leads, calls or sales.
- It makes it easier to see what moved a trend, such as a campaign or a change on the website.
- It gives you a steadier number for planning budget, staffing and marketing.
Stop doing it by hand
A spreadsheet works, but someone has to export and update it every day. The better option is to put the rolling average into the dashboard your team already uses.
Looker Studio (formerly Google Data Studio), Tableau and Salesforce reports can all show trends from live data. When the dashboard reads straight from your CRM, call tracking and GA4, the line updates on its own and everyone looks at the same numbers.
This is the kind of reporting we build: leads, calls, qualified leads and sales by day, with the source on every lead. If you want your trend lines without the daily export, book a free call.
Want help with this?
Book a free call about your CRM, integrations or reporting, or map your systems first with our free tech stack diagram builder.