Pivot Table Issues

How to Create Custom Range Groups in an Excel PivotTable

John Wilson Oct 15, 2026 868 views

Question details

The user needs to group PivotTable data into uneven, customized intervals instead of using standard fixed intervals.

How to Create Custom Range Groups in an Excel PivotTable

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing varied percentage ranges, such as EBITDA Delta %, where standard fixed grouping intervals do not meet business reporting requirements.
Observed behavior: Excel's built-in PivotTable grouping feature only allows for fixed numerical intervals, preventing users from natively defining uneven custom bins like '< -20%' and '> 10%' directly within the PivotTable interface.

Before you start

Before modifying your PivotTable, ensure your original source data is organized in a proper tabular format without any blank columns, as you will need to insert a new helper column directly adjacent to your data.

Solution 1Recommended

Create Custom Groups Using a Helper Column

Add an extra column to your source data to categorize numbers into custom text labels, then use this new column as a field in your PivotTable.

Because the standard grouping feature in a PivotTable only allows fixed intervals (such as grouping every 10 units), you must handle custom ranges in your source data. By adding a helper column, you can use formulas to categorize each row into specific text labels.

Once the helper column is populated, it acts as a brand new dimension that your PivotTable can read and summarize effortlessly.

Insert a Helper Column

Go to your source dataset, right-click the column letter next to your target data (e.g., 'EBITDA Delta %'), and select 'Insert'. Name the new header 'Custom Range'.

Assign Custom Range Labels

In the first cell of the new column, enter an IF or IFS formula to assign categories, such as: =IFS(B2<-0.2, "< -20%", B2<=0, "-20% to 0%", B2>0, "> 0%"). Alternatively, you can type these labels manually.

Apply to All Rows

Double-click the fill handle in the bottom-right corner of the cell to copy the formula or labels down through the entire column.

Refresh the PivotTable

Navigate to the sheet containing your existing PivotTable. Click anywhere inside the PivotTable, go to the 'PivotTable Analyze' tab on the ribbon, and click 'Refresh' to update the field list.

Update PivotTable Fields

In the PivotTable Fields pane, remove the original numeric field from the 'Rows' area. Drag and drop your newly created 'Custom Range' field into the 'Rows' area to display your custom grouped data.

Create Custom Groups Using a Helper Column

Dynamic Updates: Using a formula (like IFS or VLOOKUP) rather than typing manually ensures that your custom range labels will update automatically if the original numerical values change.

Powerful Spreadsheet Data Analysis

Create Custom PivotTable Groups Seamlessly in WPS Spreadsheet

You can effortlessly organize, group, and analyze complex datasets using custom range groupings in WPS Spreadsheet. With full support for advanced logical formulas and a highly compatible PivotTable interface, customizing data bins has never been easier.

1. Open Your Dataset: Launch WPS Spreadsheet and open your workbook containing the data you want to analyze.
2. Build the Helper Column: Add a new column next to your target values. Use an IFS formula to define your specific custom bins (e.g., below -20%, 0% to 10%, etc.).
3. Insert a PivotTable: Select your entire data range, navigate to the 'Insert' tab, and click 'PivotTable'. Choose where to place the new table.
4. Apply Custom Ranges: Drag your newly created helper column from the field list into the 'Rows' box to view your dynamically grouped data.

Free and lightweight spreadsheet solutionFully compatible with Microsoft Excel (.xlsx) file formatsAdvanced formulas like IFS and VLOOKUP to automate custom groupingsIntuitive PivotTable interface for instant data summarization

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use the standard PivotTable Group feature for custom ranges?

The built-in Group feature in Excel PivotTables only supports fixed numerical intervals (e.g., grouping by 10s, 50s, or 100s). For uneven ranges like '< -20%' and '> 10%', you must categorize the data using a helper column in your source dataset before creating or updating the PivotTable.

Will my custom groups update if the source data changes?

Yes, if you use a formula (such as nested IFs or VLOOKUP) in your helper column, the text labels will recalculate automatically when the source data changes. You simply need to right-click the PivotTable and select 'Refresh' to see the updated groups.

Can I use VLOOKUP instead of IF formulas for custom grouping?

Absolutely. You can create a separate lookup table outlining your custom thresholds and use the VLOOKUP function with an approximate match (TRUE) in your helper column. This method is much cleaner than writing long, nested IF statements, especially for a large number of custom intervals.

Excel & Data Problems