How To Get Running Totals In a Pivot Table

Pivot Tables are great in summarizing data from thousands of rows in just seconds. You can get Sum, Count, Average of numbers from any grouping in a Pivot table.

But the one thing that you need at times is a running total in a column. For example, in January, you sold 100 cans, and in February you sold 120 cans. So the total cans you sold till Feb is 100+120=220. This is a running total up to the month of February.

If you want Excel to do this, you can write a formula in the data cells. But to get this thing done in a Pivot Table, you need to use a different way. And it is already pre-built in  Excel Pivot Tables, starting from Microsoft Excel 1995.

All you need to do is to click on the Data Field in the Pivot Table Field List, and a popup comes up.

Choose the last option – Value Field Settings.

Move to the Second Tab – Show Values As.

Then select “Running Total in” in the drop-down. Select a base field. It must be a text field, like Product, Category, Region etc.

Voila, you get a running total in the data field inside the pivot table. And all you did was to select the correct option. No formulas to write… no calculations to make or check. Isn’t it wonderful?

Using % Running Total In

Similarly, if you choose % Running Total In as the calculation, the running total numbers would be displayed as a percentage of the total. Giving you a much nicer perspective.

If you’d like to learn more Excel Pivot Table Tricks, here are some additional articles:

• Display Values and Percentages in Pivot tables
• Analyze Data Using Pivot Tables – How To Tutorial (Video)

Questions About Pivot Tables in Excel?

If you have any questions regarding Pivot Tables in Excel, simply post a comment below and I’ll help you with it. All the best!

And Subscribe to our Excel Tips Newsletter, so we can send you more such Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

Vinai conducts 1-2 day seminars and Training Sessions on Microsoft Excel all over the world, helping companies maximize their productivity in using Microsoft Excel. If you would like to find out more, simply contact us.