Forum Discussion
Best way to get rolling 12-month totals
I am using Excel for Mac version 16.102. I have data in a Table which is basically date, category & value. There are many other columns I use for filtering. I pivot this data to get totals by month...
How about adding a Helper Column for Rolling Totals:
To automate rolling 12-month totals and make them graphable:
- Create a Helper Column in Your Source Table
Add a column called Rolling 12M Total. Use a formula like this (assuming your table is named Data and your date column is Date):
=SUMIFS(Data[Value], Data[Date], ">=" & EDATE([@Date], -11), Data[Date], "<=" & [@Date])This formula sums the values from the current row’s date back 11 months, giving you a 12-month rolling total.
- Add a Month Column
Create a column like MonthLabel with:
=TEXT([@Date], "mmm yy")This helps you group and visualize data by month.
- Use a Pivot Table or Chart
Now you can:
- Create a pivot table using MonthLabel as rows and Rolling 12M Total as values.
- Or build a line chart directly from the table to show movement over time.
