MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table Why Count


August 09, 2017 - by Bill Jelen

Pivot Table Why Count

In a perfect world, numeric fields will Sum in a pivot table

I am on an eight-year mission to convince the Excel team to offer a setting somewhere in Excel 2018 to allow people to specify whether they prefer Compact Layout, Outline Layout, or Tabular Layout. I began this mission after the team added Repeat All Item Labels to Excel 2010. I had been asking for that feature since 2002. Now that's been added, my new passion is to allow everyone who prefers Tabular to Compact to have a checkbox somewhere to make Tabular be the default. If you also prefer Tabular Layout, get ready to wait until Excel 2018. Plus two years for your IT department to decide to upgrade. That puts the feature out to 2020 for most of us.

If you don't want to wait until the Excel team finally gets tired of my lobbying and adds the feature you can invest $30 today to buy an amazing add-in called Pivot Power Premium from Debra Dalgleish at Contextures.com. Here you see the Add-In's Set Preferences dialog, where you can specify the default layout plus dozens of other settings.

Pivot Power Premium from Debra Dalgleish
Pivot Power Premium from Debra Dalgleish

This add-in will force all new Values fields to Sum. You will never have to deal with Counts! Plus it offers a way to control the number format.

Control Number Format
Control Number Format

This add-in takes all the obscure pivot functionality that is buried deep in the Options dialog and exposes it in the Ribbon. Let’s stop talking about it. It is worth every penny of the $30 license fee. Just go buy it: http://mrx.cl/pppdebra.

Watch Video

  • In a perfect world, numeric fields will Sum in a pivot table
  • Why do they sometimes Count?
  • Reason 1: Empty or text cells
  • How to Fix reason 1: Go To Special Blanks. 0. Ctrl + Enter
  • Reason 2: You are selecting the whole column
  • You are selecting the whole column so you can add more data later
  • Ctrl + T to the rescue
  • Notice the end of table marker

Video Transcript

Learn Excel for MrExcel Podcast, Episode 2001 – Pivot Table Why Count

Hey I'll be podcasting this whole book. Go click that I on the top right hand corner to see to the playlist. Hey welcome back to the MrExcel netcast.  I'm Bill Jelen.

Alright so it happens every single time. I'm in front of a room full of accountants. I'm doing my Power Excel Seminar and I start to talk about Pivot Tables and I do insert Pivot Table, okay. Put any field down the left hand side and then I check mark Revenue and mine gets a SUM and goes to the right place and someone will invariably raise their hand and say, whoa, wait a second, that never works for me, I always get Account. Why am I getting account?

As they look, it comes down to one of two reasons. There's always one or two reasons. Reason number one: you have some empty sales. There's some data that you don't have. There's either text there or empty cells and we create a Pivot Table from this Product and Revenue. First off, the Revenue goes to the wrong place. If you would drag it to the right place you get a count, right? instead of being 6.7 million dollars, you're a 559 Dollar company, right?  So the solution, if that's your problem, and in most cases is not, is to select al the cells that should be numeric, like this and then Home, Find and Select, Go To Special, choose the blanks or if your problem is Text, choose the Text. Click OK, now all the blanks are selected 0 + CTRL ENTER to fill those in. Now insert Pivot Table okay. Check mark Product, check mark Revenue and everything will work perfectly.

Alright, but here's the reason why most people run into this problem, is they've been taught somewhere along the way,and I appreciate the person that taught this to you, to not select one Solid Data but to choose all of the Data. Alright and they're doing that because they know that you're going to be adding more data next month, or next year, or something like that, and they don't want you to go through the horribly, painful, awful, terrible process of clicking Change Data Source and specifying the extra rows. I know that's like one, two, three, six clicks of hell. Alright, so because of that, you're getting COUNTS instead of SUMS. Let's stop doing that and do this: first, let's take a Pivot Table, create the Pivot Table, just from a single cell. Insert Pivot Table, okay. Put whatever you need to put in the Pivot Table, Products and Revenue. I mean we're going to add the date field in here, across the top and you see very nicely Excel 2016 has summarized that down the years for me. I'm going to leave that there. Okay, so now, you have your Pivot Table, everything is great. You now want to add more data. At this point, we do CTRL T, which is format as a table. It applies some formatting. If you hate the formatting, you just go back to light and everything is good.

I'm going to leave the formatting there though and I want to show you at the very end of the data. At the end of the table, is this beautiful little angle icon, that is called the End Of Table Marker, End Of Table Marker. So now that Pivot Table knows that is all the data down to the End Of Table Marker, we get some more data. Oh, here's some more data for next year. Take that data, copy the data CONTROL C, CONTROL Page Up to go to the previous sheet and then watch the End Of Table Marker, when I paste, is going to move. It now becomes there, alright? So now, anything that was based on this table, now is pointed in more cells. Has the Pivot Table changed? No, not yet, but if we refresh, just simply do a refresh, don't have to go in and do the six clicks under to Change Data Source we get the extra data appearing. we went from 6.7 million up to 6.9 million.

Alright so, the title here is why Pivot Count and the reason is because of empty cells, which we can be fixed with Go To Special, Blank, Zero, CONTROL ENTER, but more likely it's because you're selecting the whole columns, when you originally created the Pivot Table. By using Tables, CTRL T, it solves the problem.

Hey I want to encourage you to go out and buy this book it has all of these tips. Will be a great cross reference this whole series of 60 podcastS in August and September.

Recap, in a perfect world numeric fields will SUM in a Pivot Table. Why do they sometimes count? It's either empty or text cells, which you do with Go To Special, Blank, Zero, CONTROL ENTER or you're selecting the whole column and you're doing that so you can have more data later. CTRL T to the rescue. That End Of Table Marker shows you where the end of data is.

I want to thank you for stopping by we'll see you next time for another netcast from MrExcel.

Download File

Download the sample file here: Podcast2001.xlsx

Title Photo: Gadini / pixabay


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.