Running Totals and Running Balance with Excel Pivot Tables – How To

Last time when I discussed how to do running totals and running balance using Excel many different ways were discussed but one was left for today which is doing running totals and running balance using Pivot Tables in Excel. The reason was by that time we didn’t discussed how to make pivot tables. But as we have discussed it now and learned how to make pivot tables in Excel now we can learn this technique.

Those who are new to running total and running balance concept they can read simple explanation of it on this link. I have explained it with accounting perspective.

Download Excel Tutorial Workbook
To understand better and to apply the techniques and tips learned in this how-to tutorial download this excel workbook and open this file in your Excel program

Running Totals with Pivot Tables – Three to Glee!

Open up the file you downloaded. In the Source-Total worksheet you have the data for which you like to calculate running total. PT-Total contains the pivot table already made for you. To learn how to make such a pivot table read a beginners’ guide on Pivot table.

Step 1: Click anywhere inside pivot table and field pane on the right will open. If task pane does not appear then go to Pivot Table Tools > Options > Show group > click Field list button.

Step 2: Click and drag the sales field and drop it under the already present “Sum of Sales”. New column in the pivot table will automatically gets added with the name “Sum of Sales2”. Click the header and replace the name with Running totals.

Step 3: Right click on any cell inside the newly added column (Running Totals) and from the context menu, move cursor over “Show value as” and from the submenu select “Running totals in”. A new dialogue box appear, click OK. You will get the running totals.

Following animation walks you through visually how to carry out these three steps:

rt pivot table

Didn’t I told you Three to Glee? 🙂 Smack easy. No formulas, nothing new to be done. With pivot table in place its all a game of few clicks.

Running Balance with Pivot Tables

The source data is available on worksheet named Source-Balance. Activate worksheet named PT-Balance. Pivot table is already made for you. In this we have three columns; Months, Debit and Credit. We need running balance column so that we can assess the cash left after every transaction.

Step 1: Click anywhere inside pivot table and go to Pivot Tables > Options > in the calculations group click Fields, Items and Sets drop down and select calculated field.

Step 2: A new window opens. In the name field type Difference and in the formula type:

=Debit-Credit

Click Add button and then click OK to close the dialogue box. A new column with the name “Sum of Difference” will be added.

This way we will have line by line difference of debit and credit columns. But this gives us a balance of one line but not the running balance. To get that we will add another column to the pivot table that basically do the running total of “Difference” column we just added.

Step 3: Drag the Difference field from the field list and drop it in the values box in the last. A new column will be added with a default name of “Difference2”. Rename it as “Running Balance” by editing header of the column.

Step 4: Right click on any cell in the newly added “Running Balance” column > Show values as > Running total in. A new dialogue box appears. Click OK.

Watch the following animation to better understand the above four steps:

rb pivot tables

TADA!!!! Now you have the running balance column. Difference column is now not needed so simply uncheck it from the fields list pane on the right.

More Tutorials on Excel Pivot Tables

So my friend you have just excelled two steps forward on Excel’s advanced feature called Pivot Tables. We learned how to have running totals in pivot tables and also learned how to use calculated fields in pivot tables. There is a lot lot more you can do with calculated fields. The example I shared was a simple one. In future we are going to need this little feature of pivot table very often.

Liked it? Pin it!

Running Totals and Balance in Excel using Pivot Tables

13 COMMENTS

  1. thank you for the tutorials

    How do you have a running balance when you have several columns/field in the pivot table.
    I mean for instance when i am having the following columns; Date,description, ref… Sum amt & running balances respectively in my pivot table. the running balance becomes distorted and it seems like other columns apart from the date which i have selected as a based are equally affected by the command applied.
    How do i deal with this one. Thank you

    • i have the same question did you find a solution?

  2. Hassan,
    I would like the running total in the pivotable to be divided by a number so that the result is a %. would you show me how to do that?

  3. Great. I just save hours of manipulation!

    Thank you from Canada

  4. This is awesome. Thanks so much for this piece.??

  5. Very Good

  6. Hi Hasaan,

    This tutorial has been great but I would like to have a running total running horizontally, not vertically. Can you give me some help with this?

    Thank you

    Sarah

    • show your example data?

  7. Jayne Whittaker Jayne Whittaker

    Hasaan, I teach Excel to students here in South Africa and your tips are amazing. I found your info on Pinterest and have sent many of my learners to there to pin the tips so they can explore excel for themselves. Thank you so much for the interesting info you send.

    • Hey Jayne,
      My wishes and regards for you and prayers for your students. Happy you liked the effort.

      • Dear Hasaan Fazal,
        Can you tell about a way to create running total, if multiple fields are added in rows sections of Pivot table? Thanks

  8. @Hassan,, I like VBA Code rather than others,, if you can please post samples of the HR related Data Analysis projects, ?

    • My VBA skills are still in infancy but I am learning it as and when I can spare some time. I also wish to get a grip of VBA sooner but its still a work in progress 🙂

      But tell me what kind of analysis you want to conduct on HR data may be we can do it without VBA.

Comments are closed.