Helper Column in Pivot Table

Question

My original table is like this:

PACKAGE      Num Items     Box Size
Package1          4          small
Package2          4          small
Package3          10         small
Package4          22         large
Package5          50         large

The Box Size is a helper column. It's "Large" if the Num Items is more than 20, and "Small" if 20 or less.

My Pivot Table should look like this:

          Count of Box Size
Large            2
Small            3

Is there some way of creating the Box Size rows (Large/Small) without actually adding the helper column to the original table?

I tried creating a Calculated Field with the formula =If(NumItems>20,"Large","Small"), but it just gives me a lot of #VALUE errors.

I wouldn't mind if the Pivot table looked like this, since it includes the totals:

Box Size     Num Item    Count of Box Size
Small           4           2
                10          1
  Total                     3
Large           22          1
                50          1
  Total                     2

MarioS MarioS · Accepted Answer · 2018-05-22T19:26:15

Drag 'Box Size' in the "Rows" box within the pivot table.

Right click one of the values, and click "Group". Start at 20. Excel will automatically group everything below 20 into one group.

Helper Column in Pivot Table

1 Answers