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.

Raw data for Pivot Table
Raw data for Pivot Table

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.

Show Values As - Running Total In
Show Values As – Running Total In

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?

Running Totals Calculated
Running Totals Calculated

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.

Running total Percentage in Excel Pivot
Percentage of Running total

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


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

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.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Leave a Comment