PivotTable group skips ranges

Question

BACKGROUND: Excel pivot tables can group data into numerical ranges which you can then use as axis categories in a pivot chart.

EXAMPLE: Excel might group a series of numbers from 1-30 into three groups:

• 1-10
• 11-20
• 21-30

But, if the data were only: 5, 6, 22, 29, then Excel would list 1-10 on the first row, and 21-30 on the second row of the pivot table. It would skip the 11-20 range because there are no data values in that range.

QUESTION: How can you include all groups, so when you create a pivot chart, it will be visually clear where the "holes" in the data are? (e.g., in the example above, if you were summing the data in each group, how could you get the pivot table to look like this:

• 1-10: 11
• 11-20: 0
• 21-30: 51

showing the middle group, even though there are no data values in that group?)

Answer 1

Field Settings > Layout & Print, check Show items with no data.