Fields
The pivot table shown is based on three fields: Region, Color, and Sales:
Region has been configured as a Row field, Color as a Column field, and Sales is a Value field. Data has been filtered by Region to exclude East:
To force the display of items with no data, “Show items with no data” has been enabled on the Layout & Print tab of the Color field settings, as seen below:
To force the pivot table to display zero when items have no data, a zero is entered in general pivot table options:
Finally, the Accounting number format has been applied to the Sales field to display empty cells with a dash (-). Note: the same problem can occur with dates are grouped as months, and no data appears in a given month. You can use the same approach, with a few extra steps, described here.
Steps
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
