PivotdrillGroup numbers into bands
Put the number field in Rows, right-click any value, choose Group, set Starting at, Ending at and By (the bucket size) and click OK. Excel creates labels such as 0-99 and 100-199 and sums the Values for each band.
Syntax
Right-click a number in Rows > Group > Starting at, Ending at, By > OK| Part | What it is |
|---|---|
Starting at | Lower edge of the first band; defaults to the minimum in the data, so round it |
Ending at | Upper edge; values above go into a > band |
By | Width of each band |
Examples
Rows: Order Value (Group: Starting at 0, Ending at 500, By 100). Values: Count of Order IDResult: Bands 0-99, 100-199, 200-299, 300-399, 400-499 with the number of orders in each.
Rows: Age (Group: Starting at 20, Ending at 70, By 10). Values: Average of SalaryResult: 20-29, 30-39, ... with the average salary per age band.
Rows: Salary (Group: By 10000). Values: Count of Employee IDResult: A salary distribution, 40000-49999, 50000-59999 and so on.
Common mistakes
- The first band is odd, like 37-136: Starting at defaulted to the data minimum, so type a round number.
- The bands are labeled 0-100, 100-200 and look overlapping: Excel bands are inclusive of the start and the end value shown belongs to that band; set By to 100 with Starting at 0 and labels read 0-99.
- Group is disabled: the field has text or blanks; clean the source.
- You cannot change one band's width: Excel bands are equal width; for custom bands add a helper column in the source with IFS or LOOKUP.
Practice Group numbers into bands
These drills check your answer on live data, free, no account needed.
- Smallest order by channelFor each Channel, show the smallest Sales value of a single order.
- Average salary by departmentShow the average Salary in each Department.
Questions
Can I make bands of unequal size?
Not with the Group dialog. Add a column to the source such as =IF(A2<100,"Small",IF(A2<500,"Medium","Large")) and pivot on that.
How do I rename a band?
Click the band label and type a new name. Excel keeps it after refresh.
Related
- Group dates by month, quarter and yearGroup a date field in a pivot table by months, quarters and years with right-click, Group, and get year by month breakdowns. Auto grouping, ungrouping, weeks.
- Sum, Count and Average in a pivot tableChange a pivot table value from Count to Sum or Average in Value Field Settings, show total, count and average side by side, and fix a column that counts.
- Calculated fieldInsert a calculated field with Fields, Items & Sets to compute margin, commission or price per unit from other fields. Formula rules, why averages go wrong.
Previous: Group dates by month, quarter and yearNext: Dates not grouping