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

Download File

Download the sample file here: Podcast2001.xlsx

Title Photo: Gadini / pixabay