How to Ungroup Data in a Pivot Table: Step-by-Step Fix for Split Fields

Here’s how to ungroup data in a pivot table when fields get tangled—it’s easier than you’d think, and I’ve saved countless spreadsheets with this trick. ✨ reports that would’ve been scrapped otherwise.

Most people miss the right-click menu option, but once you know where to look, it takes under 30 seconds to restore your data structure.

Here’s the thing—ungrouping isn’t just about reversing a mistake. It’s about reclaiming control when Excel’s automatic grouping hides the details you actually need. I’ve seen analysts spend hours rebuilding data because they didn’t know this trick.

The solution lives in the field settings menu, not the ribbon, and it works the same way in Excel 2016 through 365.

You’ll end up with clean, usable data again—no more squinting at merged categories or wrestling with nested hierarchies. The best part? This method preserves all your filters and calculations while putting you back in the driver’s seat. Once you’ve done it once, you’ll wonder how you ever lived without it.

Works for grouped dates, text fields, or numbers—anything Excel’s auto-grouping decided to collapse. Let’s get your pivot table back to normal without losing a single row of data.

What you need

Step-by-Step instructions for splitting grouped fields in Excel pivot tables

Here's the exact method I use to separate combined data fields—no coding, just clicks.

Open your Excel workbook and navigate to the pivot table containing the grouped data. Look for the field that was accidentally combined—it’ll appear as a single field with multiple levels (like "State-City" instead of separate "State" and "City" fields). This is your starting point.

If you're unsure which field is grouped, hover over the field header in the pivot table. The tooltip will show the full field name with any hidden grouping indicators. For example, you might see "[State-City]" instead of just "[State]".

Right-click anywhere on the grouped field header in the pivot table. From the context menu, select "Field Settings". This opens the Field Settings dialog box where you can modify how the data is displayed and processed.

In the dialog box, look for the "Layout & Print" tab. Here, you'll find options to control how the field is organized. However, the key to ungrouping lies in the "Drill Down" or "Show Values As" sections—these often reveal hidden groupings.

In the pivot table, click the small dropdown arrow in the upper-right corner of the field header. From the dropdown menu, select "Ungroup". If this option isn’t available, your data may have been grouped using a different method, like custom calculations or Power Query.

If "Ungroup" isn’t an option, you’ll need to edit the source data. Go to the original data range, split the combined field into separate columns (using Excel’s Text to Columns tool), and refresh the pivot table. The pivot table will now recognize the newly separated fields.

After ungrouping, click anywhere in the pivot table and press Alt + F5 to refresh it. The field should now appear as two separate columns in the pivot table fields pane, reflecting the original split data structure.

Double-check by dragging the newly ungrouped fields into the pivot table’s rows or columns area. Verify that the data displays correctly—no longer combined, and with proper hierarchy. For example, "State" and "City" should now appear as distinct fields instead of a single "State-City" field.

Save your workbook immediately after refreshing the pivot table. This ensures your changes aren’t lost if Excel crashes or you close the file unexpectedly. Next, test the pivot table by filtering or sorting the ungrouped fields to confirm they function as intended.

Here’s the thing—if you encounter errors after ungrouping, it might mean the original data had dependencies (like formulas or relationships). In that case, revisit the source data and ensure all references are updated before refreshing the pivot table again.

Tips & tricks for ungrouping data in pivot tables

These simple tricks will help you navigate the ungrouping process like a pro—saving you time and frustration along the way.

Visual Cue for Identifying Grouped Fields: In Step 1, when you hover over a field header, look for the square brackets around the name like "[State-City]". This is your clear signal that the field is grouped. I've seen people miss this because they assume all field names appear the same. Pro tip: If you're unsure, check the Field Settings dialog box—grouped fields often show up with a small icon indicating they contain multiple levels.

Alternative Ungrouping Method: If the "Ungroup" option isn't available in Step 3, don't panic! This often happens when the data was grouped through Power Query or custom calculations. In this case, you'll need to edit the source data directly. Use Excel's Text to Columns tool (Data tab → Text to Columns) to split the combined field into separate columns. Choose "Delimited" and select the character that separates your data (like a hyphen or comma), then click Finish. This works beautifully for fields like "State-City" where you know the separator.

Refresh Shortcut: After ungrouping in Step 4, remember that pressing Alt + F5 refreshes your pivot table instantly. This is faster than right-clicking and selecting "Refresh" from the menu. I've saved myself countless minutes using this shortcut, especially when testing multiple ungrouping scenarios. Just make sure you've made all your changes before refreshing—once you hit that shortcut, Excel won't ask for confirmation.

Dependency Check: In Step 5, when testing your updated pivot table, pay special attention to any formulas or relationships that might depend on the original grouped field. If you encounter errors, it's often because the pivot table is still referencing the old field structure. Go back to your source data and verify all references are updated. I learned this the hard way after spending an hour troubleshooting why my pivot table kept showing errors—turns out it was still looking for the old "[State-City]" field instead of the new separate fields.

Frequently asked questions

Still have questions about ungrouping data in your Pivot Table? You’re not alone—even seasoned Excel users run into hiccups! Here are answers to the most common queries, straight from the trenches:

Wrapping up and next steps

Mastering how to ungroup data in a Pivot Table unlocks deeper insights from your datasets—no more frustratingly tangled fields! Whether you’re splitting dates, separating text, or fixing misaligned categories, these steps put control back in your hands. 🎯

Now that you’ve got the know-how, why not take it further? Experiment with dynamic Pivot Table formulas or explore advanced Excel functions like TEXTSPLIT or GETPIVOTDATA to supercharge your analysis. Your data’s next breakthrough is just a click away!