A pivot table is a master tool for data analysis, it’s that flexible and powerful.
One of the things which I like about a pivot is we can easily add running total into it, which can further help us in an analysis.
Today, in this post, I’d like to share a simple step process to add a running total in a pivot table.
What’s a Running Total?
It’s a cumulative total which includes the past total as well.
For example, if we have month wise data from Jan to Dec, a running total will show you YTD total every month.
We can use a running total to measure complete growth against the target.
Steps to Add a Running Total in Pivot Table
Adding a running/cumulative total requires simply few clicks and here are the steps to do it.
- Right click on it and click “Value Field Setting”.
- Now, you have “Value Field Setting” window.
- Go to “Show Values As” tab.
- From “Show Values As” drop-down list, select “Running Total In”.
Now we have a running total in the pivot table with a cumulative total in every month.
Note: While adding running total make sure that pivot table sorted in a right way. If we want to add it from Jan to Dec then you the values must have sorted from Jan to Dec.
Different Ways to Add a Running Total in a Pivot Table
Here are some examples of adding a running total in different ways.
1. Start Running Total from Zero
Someone asked that how can we start total from a zero. Well, for this we just need to make a small amendment in our source data.
- First of all, in the source data we need to enter a dummy entry with zero value.
- Here our data is starting from Jan’15, and that is why I have used Dec’15 for the dummy entry.
- Now, go to the pivot table and refresh it. And once we do that it give a running total starting from zero.
2. Percentage Running Total
We can also use the percentage of running total instead values and here are the steps.
- First of all, right click on total column and open "Value Field Setting".
- After that, go to “Show Values As” tab and select select “% Running Total In” from the drop down.
3. Running Total with Grouped Dates
When we add a running total where we have grouped dates there could be a problem, let me show you.
In the below pivot table, we have grouped dates and create two-row labels from it “Quarters” and “Months”.
And, we have also applied running total in the data field. Now if you look, after a change in the quarter, the running total is starting over again.
But we can fix it using a helper column and here are the steps.
- First of all, go to your source data and enter a new column after the date column.
- After that, name that column “Q-M” and enter the below formula:
- Now, refresh your pivot table and remove both the “Quarters” and “Months” column fields and add column "Q-M".
4. Running Total in a Pivot Chart
With a running total, we can also create a pivot chart to show that cumulative values in a graphical way.
In the above example, we have a pivot chart along with the pivot table to show the trend of values increasing month by month.
Steps for this are exactly same, all you need to do is insert a pivot chart with a pivot table and add a running total column in it.
By adding running total to a pivot table we can track amount/values cumulatively, which is quite useful while tracking sales, budgets, interest values, etc.
One final thing which I wanted to share with you is that running total changes when sort the pivot table.
I hope this tip will help you use pivot tables with full power and now tell me one thing.
Have you ever tried to apply a running total before?
Share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.
Must Read Pivot Table Tutorials
- Group Dates in a Pivot Table: Instead of convert dates into months, quarters and, year, we can group them directly into the pivot tables [...]
- Pivot Table Timeline: A timeline with a pivot table helps to filter data in a better way with full control [...]
- Pivot Table using Multiple Files: If you have a multiple files and you can combine data from all of these files using pivot table [...]
- Ranks in a Pivot Table: There is an simple to apply option in pivot table which we can use to add a rank column within a pivot table [...]
- Conditional Formatting to a Pivot Table: To make you report more effective and easily understandable, you can use CF for that [...]