PivotTables: Replace Blanks with Zeros

I love PivotTables because they do all the “data housework” for me, allowing me to analyze data rather than gather, manage, and format it.

One undesirable feature of the PivotTable is how it treats blank values. In our example below we have the sales for a few stores by day. The PivotTable leaves days without sales as blank. This should make you wonder: do these stores really not have sales those days, or is there an issue with the data? For example, the store may not have reported its sales. (Analysts never take blanks for granted!)

After some digging, you know for certain these days’ sales were 0 (awful store, huh?). Rather than repeat your line to the boss, you can change the PivotTable to show 0 anywhere there are blank cells. This makes for more consistent formatting.