Grouping numeric values in Excel PivotTables
Grouping numeric values is useful when you use a numeric field in a row or column field. Excel enables you to specify numeric ranges into which the field items are grouped. For example, suppose you have an Excel PivotTable of invoice data that shows the extended price (the row field) and the salesperson (the column field). It would be useful to group the extended prices into ranges and then count the number of invoices each salesperson processed in each range.Follow these steps to group numeric values in an Excel PivotTable field:
- Select any item in the numeric field you want to group.
- Choose Analyze → Group → Group Field.
The Grouping dialog box appears.
The Grouping dialog box. - Use the Starting At text box to enter the starting numeric value.
Alternatively, select the Starting At check box to have Excel extract the minimum value of the numeric items and place that value in the text box.
- Use the Ending At text box to enter the ending numeric value.
Alternatively, select the Ending At check box to have Excel extract the maximum value of the numeric items and place that value in the text box.
- In the By text box, enter the size you want to use for each grouping.
- Click OK.
Excel groups the numeric values.
Grouping date and time value in Excel PivotTables
If your Excel PivotTable includes a field with date or time data, you can use Excel’s grouping feature to consolidate that data into more manageable or useful groups. Follow these steps:- Select any item in the date or time field you want to group.
- Choose Analyze → Group → Group Field.
The Grouping dialog box appears.
- In the Starting At text box, enter the starting date or time.
Alternatively, select the Starting At check box to have Excel extract the earliest date or time and place that value in the text box.
- Use the Ending At text box to enter the ending date or time.
Alternatively, select the Ending At check box to have Excel extract the latest date or time and place that value in the text box.
- From the By list, select the grouping you want, such as Months for dates or Hours for times.
If you select Days, you can also use the Number of Days spin buttons to set the days you want to use for the grouping interval.
To use multiple groupings, select each type of grouping you want to use.
- Click OK.
Excel groups the date or time values.
Grouping text values in Excel PivotTables
One common problem that arises when you work with Excel PivotTables is that you often need to consolidate items, but you have no corresponding field in the data. For example, the data may have a Country field, but what if you need to consolidate the PivotTable results by continent? Your source data isn't likely to include a Continent field. Similarly, your source data may include employee names, but you may need to consolidate the employees according to the people they report to. What happens if your source data does not include, say, a Supervisor field?The solution in both cases is to use the Grouping feature to create custom groups. For the country data, you can create custom groups named North America, South America, Europe, and so on. For the employees, you can create a custom group for each supervisor.
Here are the steps to follow to create such a custom grouping for text values in Excel PivotTables:
- Select the items that you want to include in the group.
- Choose Analyze → Group → Group Selection.
Excel creates a new group named Groupn (where n means that this is the nth group you have created; the first group is Group1, the second is Group2, and so on) and restructures the PivotTable.
- Select the cell that contains the group label, type a new name for the group, and then press Enter.
Excel renames the group.
- Repeat Steps 1 to 3 for the other items in the field until you have created all your groups.
Find out how to build Excel PivotTables for sales forecasting.