PivotTable group skips ranges

Question

BACKGROUND: Excel pivot table tables can group data into numerical ranges, which can then be used as axis categories in a pivot chart.

EXAMPLE: Excel can group a series of numbers from 1 to 30 into three groups:

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

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

QUESTION: How can you include all groups, so when you create a pivot chart, it will be visually clear where the “holes” are in the data? (for example, in the above example, if you summarized the data in each group, how could you get a pivot table as follows:

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

shows the middle group, even if there are no data values ​​in this group?)

Answer 1

Field settings> Layout and print, select the Show items without data check box.