Exceljet

Quick, clean, and to the point

Pivot table count blanks

A pivot table is an easy way to count blank values in a data set. In the example shown, the source data is a list of 50 employees, and some employees are not assigned to a department. The Pivot Table is configured to group out data by department, and automatically creates a category called "(blank)" for employees without a department value. 

Fields

The pivot table shown is based on three fields: First, Last, and Department. The Department field is configured as a Row field, and Last is configured as a Value field, renamed "Count".

Pivot table count blanks field list

The Last field is renamed "Count" and configured to summarize by count:

Value field settings for Last name

In the example shown, the pivot table uses the Last field to generate a count. Any text field in the data that is guaranteed to have data can be used to calculate count.

Steps

  1. Create a pivot table
  2. Add Department field to the rows area
  3. Add Last field Values area

Notes

  1. Any non-blank field in the data can be used in the Values area to get a count.
  2. When a text field is added as a Value field, Excel will display a count automatically.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.