Excel Running Totals
May 1, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
By default, a Pivot Table only shows the items that have data. In the screen shot below, there weren't any snack sales in the East in June, so that item isn't listed.
You might want to see all the items for each region though, even if they didn't have sales in the selected month. That keeps the pivot table layout consistent -- things don't disappear and then reappear, when you apply filters. That consistency is also important if you have a pivot chart, and you don't want the series colours to keep changing.
To make all the items appear, even if there's no data, follow these steps:
There's a video on my website that shows the steps, and there are more Field Settings on that page too.
NOTE: If you have a copy of my Pivot Power Premium (PPP) add-in, it has commands to show or hide the items, for the selected pivot field.
Instead of having a grand total on your worksheet, you can add a column with a running total.
In this screen shot, quantities are in column C, and there is a running total in column D. In each row, it shows the total quantity, up to and including that row.
My formulas are below the screen shot.
To calculate the overall running total in column D, I used this formula in cell D2, and copied it down:
The starting row has a dollar sign, which makes it an absolute reference. The ending row is a relative reference, so it will automatically change, as the formula is copied down . For example, in cell D11, the formula is =SUM(C$2:C11)
To get the running total by City, use SUMIF, instead of SUM. Lock the starting row, in this formula too:
See a Running total video, and another example on my Sum Functions page.
Here are a couple of Excel articles that you might find useful or interesting.
Charts - Ann K. Emery has great tips for creating better charts, in 3 simple steps. The font on her site is really small, so I use the Chrome Reader View extension to make it easier to read. (Level - Int)
Power BI - Join Mynda Treacy for her brand new free webinar - Power Query & Power Pivot Excel Dashboards. She'll build 2 interactive dashboards, for a quick overview of what you can achieve with the Power BI tools. The live webinars are only available from May 1-10, 2018.
The weather is getting warmer, and the snow tires are finally coming off my car today -- I hope it's not too early! There are signs of life in our garden too, like this little stalk of rhubarb that is poking up among all the dead leaves. We should have blossoms and spring flowers soon too, if winter stays away.
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180501ctx.html
I'll also post any article updates or corrections there.
dsdalg @ gmail.com
Last updated: May 4, 2018 3:25 PM