Create running totals

Introduction

You’ve got some data where revenue or gifts come in each day, week, or month and you want to convert these to running totals. For example, let’s say you have the following record of year to date cashflow through July, by month, for one of your programs:

This view makes it easy to answer questions like, “How much revenue came in during the month of April?” But it’s a little more difficult to answer questions like, “How much total revenue has come in through the end of April?” This is where running totals come in. We’re going to walk through two ways to accomplish this. One using a static spreadsheet like the screenshot above, and one using pivot tables.

Method 1: Static data

If you’ve got a relatively simple use case like the example above, we can use an approach with a formula and the fill handle. A running total by month is simply the sum of the current month’s revenue with all prior month revenue. This means that for January, our running total is just the sum of all January revenue (since there are no prior months), which we can get with the cell reference =B2:

Now that we have January’s running total set, we can use a formula to fill the rest of the months. Each month, the running total should grow by the amount of revenue raised that month. This means for February, the running total is January’s running total plus February’s revenue, or =C2+B3. We can enter this formula in cell C3 and then simply fill that formula down:

And we’re done! We have created a running total alongside our monthly revenue figures.

Method 2: Dynamic data with a pivot table

Method 1 works great if you have a simple spreadsheet and you need to add a running total to it. But what if you have data in a pivot table and need to convert it to a running total? In this example, we have a pivot table that has summarized revenue by month, and we want to convert the monthly revenue figures into a running total. We accompish this using Excel’s “Show Values As” functionality using just a few clicks. Here’s our pivot table:

To convert this to a running total, simply right-click anywhere in the “Sum of Revenue” column and select “Show Values As.” This will pop out a second menu. Select “Running total in…” Excel will ask you which “base field” to use. In our example it’s “Fiscal Month” - choose whichever pivot field you have on your rows:

And that’s it, you’ve converted your monthly revenue into a running total!