Yes. In Excel for Microsoft 365 on Windows, put a numeric field in a PivotTable’s Rows or Columns area, right-click one of its numbers, choose Group, and set Starting at, Ending at, and By. Excel then groups the values into equal-width ranges; this is different from grouping dates by month or manually combining selected labels.
What grouping by value range does
Numeric grouping changes how a PivotTable displays the distinct numbers in a source field. Instead of listing every individual value, Excel creates adjacent intervals, often called bins. You can then summarize another field for each interval—for example, count orders by order-value band or total revenue by quantity band.
The field being grouped usually needs to appear in the Rows or Columns area, because that is where Excel displays its items. The same source field can also be used in Values when that makes sense. For instance, you could put Order Amount in Rows, group it into ranges, and put Order Amount in Values to see the sum or count for each range. The grouping determines the categories; the Values area determines the calculation.
A range is not a new calculation on the source data. It is a way to organize records in the PivotTable. The source values remain unchanged, and the intervals are part of the PivotTable’s layout rather than a replacement for the original numbers.
How to group numbers into ranges
- Make sure the source column contains numeric values, then create or select a PivotTable based on that data.
- Drag the numeric field you want to group into Rows or Columns. If you want summaries, add a suitable field to Values as well.
- In the PivotTable, right-click one of the displayed numbers in the field you want to group and choose Group.
- In the Grouping dialog box, enter the first boundary in Starting at, the final boundary in Ending at, and the interval width in By. For example, use 0, 100, and 10 for ten-unit intervals spanning 0 to 100.
- Select OK, then check the resulting labels and summaries. If the intervals do not match your intent, right-click a grouped label, choose Ungroup, and group the field again with corrected boundaries.
On a Mac, Control-click or right-click the PivotTable item to open its context menu; available commands can vary with the Excel version. If the context-menu command is difficult to find, select a PivotTable cell and look for grouping commands on the PivotTable Analyze tab. Excel for the web has fewer PivotTable editing capabilities than desktop Excel, so use the desktop app if Group is unavailable.
Choosing boundaries and interval size
Starting at and Ending at define the span you want to cover, while By sets the width of each interval. If a field contains test scores from 0 through 100, grouping from 0 to 100 by 10 is a reasonable way to create bands of roughly ten points. The labels Excel displays make the actual boundaries visible, so review them instead of assuming that an endpoint is included in a particular bin.
Choose boundaries based on the question the PivotTable should answer. A report about performance might use ten-point score bands; an inventory report might use groups of 25 units; a sales report might use wider bands at higher amounts. Equal-width intervals are useful for a quick distribution, but they are not automatically meaningful categories. A business may instead need named bands such as Small, Medium, and Large, with boundaries chosen to match its own definitions.
- Use a narrow interval when detail matters and the source has enough records to make each group useful.
- Use a wider interval when a long list of small groups would be hard to read or would contain very few records.
- Check the source’s scale before choosing By. Grouping a field recorded in cents or decimal measurements by 10 can produce a very different report from grouping whole-dollar or whole-unit values by 10.
- Decide how to treat values beyond the expected span. Include suitable outer limits or review unusual values separately so the report does not conceal outliers.
Excel’s grouping dialog is intended for regular numeric intervals. It is not a general rule builder for unequal bands such as 0–9, 10–49, and 50 or more. For custom bands, use a helper column or another explicit classification method.
Worked example: score bands
Suppose a source table has a Score column containing whole-number results and a Student column. Put Score in Rows, put Student in Values, and set the Values calculation to Count if Excel has not selected that calculation automatically. Group Score from 0 to 100 by 10. The resulting PivotTable reports how many students fall in each score interval, rather than showing a separate row for every score.
The exact displayed labels depend on Excel’s treatment and presentation of the boundaries. Read the labels in the finished PivotTable and test a known boundary value if the distinction matters to the report. A score of 10, for example, should appear in the interval intended to start at 10. This check is especially important when people will use the report to make decisions or compare it with a separate set of rules.
For a revenue report, put Revenue in Rows and group it into intervals, then place a transaction identifier in Values and choose Count to count transactions in each band. If you instead put Revenue in Values and leave its default summary as Sum, the result is total revenue per band. Those answer different questions, even though the row ranges are identical.
When to use a helper column instead
A helper column is usually the better option when ranges have unequal widths, need descriptive names, or must follow a rule that other reports can reuse. Add a category column to the source table, fill it down, and use that new field in the PivotTable’s Rows area. Unlike a PivotTable-only grouping, the category is then visible in the underlying records and can be used in charts, filters, or other analyses.
For example, assume whole-number scores are in B2:B20 and you want categories Below 0, 0–9, 10–19, and 20 or more. Put this formula in C2 and fill it down; for a score of 17, it returns 10–19:
=IF(B2<0,"Below 0",IF(B2<10,"0–9",IF(B2<20,"10–19","20 or more")))
This example assumes integer scores. If the source contains decimals, define the category labels and cutoff rules to match those decimals rather than using whole-number labels that could be misleading. For a longer list of irregular bands, a small lookup table of lower and upper limits can be easier to audit than a deeply nested formula.
Requirements and common reasons Group is unavailable
Numeric grouping works best when the field contains actual numbers throughout the source data. Text that looks like a number, blank cells, errors, or mixed entries can prevent grouping or make the source unsuitable. Check the original column, not just the PivotTable display: formatting a text value to look numeric does not necessarily convert it into a number.
- Mixed data types: Inspect the source column for text, blanks, and error values. Correct or handle those records consistently, then refresh the PivotTable and try again.
- Wrong field area: If the field appears only in Values, move it to Rows or Columns before attempting to group its items.
- Data Model or OLAP source: Grouping options can be restricted for PivotTables based on an OLAP source or the workbook Data Model. If the command is disabled, a source helper column or a suitable model-level category may be needed.
- Unexpected limits: Starting and ending boundaries must make sense for the values in the field. Recheck the source minimum and maximum, then ungroup and set the desired limits again.
After refreshing a PivotTable, review the grouped field if new records have been added. New source values can change the range of data the report needs to cover, so confirm that the chosen limits still include the intended records and that the displayed groups remain appropriate.
Grouping versus sorting, filtering, and date grouping
Grouping is not the same as sorting. Sorting changes the order of individual items; it does not combine them into ranges. Filtering hides selected items but also does not create interval categories. If you want a concise distribution, grouping is the relevant operation; if you only want large values at the top, sorting may be enough.
Date fields have their own grouping behavior. Excel can group dates into calendar units such as months, quarters, or years, depending on the source and the options available. That is different from numeric grouping by a fixed interval. A date field that has been stored as text may not behave like a true Excel date, just as number-like text can interfere with numeric grouping.
Manual grouping is different again: it combines selected items into a group you define. That can be useful for categorical labels, but it does not automatically create equal-width numeric ranges. Use numeric Group for regular intervals and a helper column for deliberate, named, or irregular categories.
Keeping a grouped report reliable
Write down the range boundaries and interval width somewhere near the report, especially if readers need to interpret the bands consistently. A label such as 10–19 is clearer than an unexplained group name, and a note such as Scores grouped in ten-point intervals makes the design easier to maintain. Avoid relying on color alone to communicate what a group means.
When you change the source data, refresh the PivotTable and review both the grouped labels and the summary calculation. A refresh updates the report from its source, but it does not guarantee that the original bin design still answers the question. If the source now contains negative values or a much higher maximum, revise the boundaries or choose a helper column with explicit categories.
If you share the workbook, check that the recipient can see the PivotTable fields and understands whether a displayed number is a count, sum, or another calculation. The range label describes the records being grouped; it does not tell the reader how the Values field was summarized.
Frequently Asked Questions
Can an Excel PivotTable group numbers into ranges automatically?
Yes. Put a numeric field in Rows or Columns, right-click one of its PivotTable items, choose Group, and specify Starting at, Ending at, and By. Excel creates regular intervals; it does not infer the most meaningful business categories for you.
Can I group a value field that is only in Values?
Grouping applies to the field’s displayed items, so place the field in Rows or Columns first. You can also use a field in Values to calculate a count, sum, or other summary alongside the grouped categories.
Why is the Group command greyed out?
Common causes include mixed numeric and text entries, blanks or errors, a field that is only in Values, or a PivotTable based on the Data Model or an OLAP source. Check the source column and the PivotTable source type; use a helper category field if grouping is not supported.
Can I create ranges of different sizes?
Not with the regular numeric Group dialog, which uses one interval width. Create a helper column with explicit rules or category labels for unequal ranges such as 0–9, 10–49, and 50 or more.
How do I undo numeric grouping?
Right-click a grouped item in the PivotTable and choose Ungroup. Excel returns the field to its individual items, after which you can group it again with different boundaries.
Related
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Line Chart With Multiple Series: A Step-by-Step GuideTo create a line chart with multiple series in Excel for Microsoft 365 on Windows, arrange each series…
- Excel Gantt Chart Template XLS: How to Find or Build OneYes—an Excel Gantt chart template can be opened or built in Excel, but “XLS” is the older Excel…
- Interactive Excel Dashboard: How to Build OneYes. An interactive Excel dashboard is a worksheet that lets people explore key figures by changing filters or…
- Excel Waterfall Chart Totals: How to Add and FormatTo show a total in an Excel waterfall chart, right-click the bar that represents the total and choose…
- Excel Gantt Chart Template: Build and Customize OneAn Excel Gantt chart template is a worksheet that lays out tasks against dates, then uses colored cells…
- Excel Waterfall Chart: Rotate Axis Labels in 5 StepsTo rotate labels in an Excel waterfall chart in Microsoft 365 for Windows, select the chart’s axis labels,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.