Ungroup Rows or Columns in Excel: A Clear Answer

To ungroup rows or columns in Excel, select the grouped rows or columns, then go to the Data tab and click the Ungroup button (or press Shift+Alt+Left Arrow). This removes the grouping outline, but does not delete any data. If you want to remove the outline entirely, click the Ungroup arrow and choose Clear Outline.

What Grouping Does in Excel

Grouping in Excel creates an outline that lets you collapse or expand a set of rows or columns with a single click. It is a way to hide and show data without deleting it. When you group rows 2 through 10, for example, you get a minus sign (–) button at the left edge of the row numbers. Clicking it collapses those rows into a single bar, and the minus sign changes to a plus sign (+), which you click to expand them again.

The grouping feature is part of Excel’s outline tools, which also include the Subtotal command. The outline can have up to eight levels, but most people use just one or two. Grouping is often used for financial statements, budgets, or any report where you want to show summary lines and hide the detail lines.

How to Ungroup Rows or Columns

There are several ways to ungroup, depending on what you want to remove.

• Ungroup a specific group: Select the rows or columns that are grouped (for example, rows 2 to 10), then go to Data > Ungroup. Or press Shift+Alt+Left Arrow. This removes the group from just that selection.
• Ungroup all groups on the sheet: On the Data tab, click the arrow under Ungroup and choose Clear Outline. This removes every grouping on the current worksheet, but leaves your data intact.
• Using the right-click menu: Select the grouped rows or columns, right-click, and choose Ungroup from the context menu. This works the same as the Data tab button.
• Keyboard shortcut: Shift+Alt+Left Arrow ungroups the selected rows or columns. (The same shortcut works in Google Sheets.)

Note that ungrouping does not unhide rows or columns that are currently collapsed. If you have collapsed a group and then ungroup it, the rows may remain hidden. To show them again, select the rows around the hidden ones, right-click, and choose Unhide.

Ungroup vs. Unhide: What’s the Difference?

A common confusion is between ungrouping and unhiding. Grouping is a structural outline; hiding is a formatting state. When you collapse a group, the rows are effectively hidden, but they are still part of a group. If you ungroup while collapsed, the outline disappears but the rows stay hidden because hiding is a separate property.

To fully restore everything, you may need to both ungroup and unhide. For example, if you have rows 2-10 grouped and collapsed, then you ungroup them, rows 2-10 will still be hidden. You must select rows 1 and 11, right-click, and choose Unhide to see them again. This is a frequent source of frustration, so always check if rows are hidden after ungrouping.

How to Ungroup a Pivot Table

Pivot tables have their own grouping feature for dates and numbers. To ungroup items in a pivot table, right-click a cell in the grouped field, select Ungroup from the context menu. This removes the grouping (e.g., months or quarters) and returns the field to its original values. Note that this is different from ungrouping rows/columns in a worksheet – pivot table grouping is stored within the pivot table’s field settings.

Common Problems and Fixes

• Ungroup is grayed out: This usually means you haven’t selected any grouped rows or columns. Click inside the group (or select multiple rows/columns) and try again. If you are trying to clear all outlines, make sure the active cell is on the worksheet, not in a dialog.
• Rows remain hidden after ungroup: As mentioned, ungrouping doesn’t unhide. Use the Unhide command manually.
• Ungroup removes data?: No, ungrouping never deletes data. It only removes the outline structure. If data disappears, it’s because the rows/columns are hidden, not deleted.
• Clear Outline removes all groups but also removes subtotals: If you used the Subtotal command, Clear Outline will also remove the subtotal rows. To keep subtotals, you must ungroup each group individually instead of clearing the outline.
• Grouping is not available on a protected sheet: If the sheet is protected, you may need to unprotect it first (Review > Unprotect Sheet) before you can ungroup.

How to Choose Between Ungroup and Clear Outline

Decide based on how much you want to remove:

• If you want to remove only one group (e.g., just the quarterly group), select that group and click Ungroup.
• If you want to remove all grouping from the sheet, use Clear Outline. This is faster but also removes any subtotal structure.
• If you want to keep the ability to collapse/expand but change the group range, ungroup the old range and then regroup with a new range.

For a quick way to see all groups, go to Data > Outline > Show Detail (the plus sign) or Hide Detail (the minus sign). Use these to expand everything before ungrouping.

Grouping in Google Sheets vs. Excel

Google Sheets has a similar grouping feature, but with slight differences. In Google Sheets, you group rows by selecting them, right-clicking, and choosing Group rows 2-10. To ungroup, select the grouped rows, right-click, and choose Ungroup rows 2-10. There is also a “Remove all groups” option in the View menu. The keyboard shortcut is the same (Shift+Alt+Left Arrow). One difference: Google Sheets does not have a “Clear Outline” button; you must use the right-click menu or the View menu.

Frequently Asked Questions

Does ungrouping delete my data?

No, ungrouping only removes the outline structure. Your data remains in the cells, but if the rows/columns were collapsed, they may stay hidden. Use Unhide to show them.

Why is the Ungroup button grayed out?

The Ungroup button is grayed out when you haven’t selected any grouped rows or columns, or when the worksheet is protected. Select a cell within the group or unprotect the sheet first.

What’s the difference between Ungroup and Clear Outline?

Ungroup removes the group from the selected rows/columns only. Clear Outline removes all grouping from the entire worksheet at once. Clear Outline also removes any subtotal rows created by the Subtotal command.

How do I ungroup in Excel for Mac?

On a Mac, the shortcut is Command+Shift+Left Arrow. You can also go to the Data tab and click Ungroup, or right-click and choose Ungroup.

Can I ungroup without using the mouse?

Yes, use the keyboard shortcut Shift+Alt+Left Arrow (Windows) or Command+Shift+Left Arrow (Mac). This works when you have selected the grouped rows/columns.

Why do my rows stay hidden after ungrouping?

Because ungrouping doesn’t unhide rows that were collapsed. Select the rows around the hidden area, right-click, and choose Unhide. This is a separate step.

Related

• Pokemon Names for Dogs: 100+ Real Ideas
• Must Use Samsung Routines? What You Need to Know
• Cool Self Hosted Projects to Spin Up This Weekend