Excel like a finance pro.
Pivot tables · Layout
Blank and error display
Controls what shows in empty cells and error cells of the pivot.
When to use it
Empty combinations show as blank cells and errors from calculated fields show as error values; both can be replaced with a chosen text or 0.
The shape of it
- How
PivotTable Options, Layout and Format, "For empty cells show" and "For error values show".
Worked examples
Zeros for blanks
PivotTable Analyze, Options, Layout and Format, For empty cells show 0 → Zeros instead of blanks
Cleaner cross-tabs.
Dash for errors
For error values show "-" → A dash instead of #DIV/0!
Hide calculation errors.
Reset
Uncheck For empty cells show → Back to blank
Reset.
Worth knowing
- Zeros in place of blanks make the pivot easier to reference.
- Text placeholders break downstream math; use 0 when formulas read the pivot.
- Errors usually mean a calculated field dividing by zero.
Where it goes wrong
- Text in place of blanks breaks charts.
- Hiding errors hides real problems.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.