Contextures

Contextures News 20180501

Excel Running Totals

May 1, 2018

Create running totals, make better charts, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Show All the Items

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:

  • Right-click an item in the field that you want to change
  • Click Field Settings, and go to the Layout & Print tab
  • Add a check mark to "Show items with no data", then click OK

show pivot items with no data

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.

Pivot Power Premium add-in

Running Total by City

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.

  • What formula would you use for the running total?
  • How would you calculate a running total per city, in column E?

My formulas are below the screen shot.

insert worksheet

To calculate the overall running total in column D, I used this formula in cell D2, and copied it down:

=SUM(C$2:C2)

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:

=SUMIF(B$2:B2,B2,C$2:C2)

See a Running total video, and another example on my Sum Functions page.

Excel Articles

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 Reader View feature in Firefox, 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.

Power BI Webinar

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

In the Garden

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.

weekly photo

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: https://www.contextures.com/newsletter/excelnews2018/20180501ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: July 6, 2018 1:06 PM