[ad_1]
Excel Pivot Table Tip – Why Does My Pivot Table COUNT Not SUM?

So picture the scene, you have your data source ready, you create your Pivot Table, add your data to the data section and you do not quite get the results you expected. You get Excel giving you a COUNT result in your data area- why is that?.

It is a question I get asked a lot. It’s all about the base data you are using to populate your Pivot Table. There are two ways to deal with this, at the source- which is your data, or get Excel within the Pivot to amend the data.

So firstly, why is this happening?.

It is happening because you may have hundreds of thousands of numeric cells and just 1 yes, 1 blank cell or a rogue cell that contains text. Excel will take this as the whole column as being text and therefore will default to counting rather than summing your data.

The blank cell/s that result in the Pivot Table tell us that there were no records for the particular combination of labels. One point to note that if there was a zero in the default view then it may mean that there is a net value, I.e a customer may have had sales but then these were refunded hence the net value is zero.

I have come across this rarely in my analysis work, and therefore the majority if not all of the time I am happy to replace blanks with zeros.

So, to find any rogue blank or text cells in your base or source data

• Select the numeric columns in your original data
• Hit F5 and hit Special in GOTO Dialog box
• Select the Blanks option and hit OK. Only the Blank cells will be selected, if you have some text in these columns also then go ahead and also select Constants and Text
• Hit OK
• Type 0 and hit CTL+Enter
• All the text or blanks will now contain zeros

If you already have a Pivot Table built or want to replace your blanks then it is easy to change the setting for your Pivot Table to show zeros in the place of blank cells-

• Select any cell within your Pivot
• Option tab and select the Pivot Table Options group to display the Pivot Table Options dialog box
• Layout and Format TAB within the Format Section
• In the For Empty Cells Show type 0
• Hit OK to save the changes

Your Pivot is now a continuous block of non blank cells reflected in the data now SUMMING instead of defaulting to COUNT.

[ad_2]

Source by BJ Johnston