In Excel for Microsoft 365 on Windows, find the worksheet-outline command at Data > Outline > Ungroup. If you mean grouped worksheet tabs or items in a PivotTable, the command is in a different place, so first identify what is grouped.
Where to find Ungroup for rows and columns
For rows or columns grouped with Excel’s outline feature, select the grouped rows or columns, then choose Data > Outline > Ungroup. The Outline group is usually toward the right side of the Data tab. Depending on the ribbon width and window size, some commands may be compressed into smaller buttons or menus, but Ungroup remains associated with the Data tab’s outline tools.
Excel may ask whether you want to ungroup rows or columns if it cannot tell which type of outline selection you intend. Choose the appropriate option. The command removes the outline grouping for the selection; it does not delete the selected rows, columns, or their contents.
A quick way to recognize an outline is to look beside the row numbers or above the column letters for bracket lines and level buttons marked 1, 2, or 3. A minus sign on a bracket collapses a group, while a plus sign expands it. Those controls hide and show detail; Ungroup changes or removes the grouping structure itself.
Ungrouping worksheet rows or columns
Use these steps when a worksheet has an outline around rows or columns and you want to remove a group without clearing all outline information:
- Expand the outline if needed so you can see the rows or columns you intend to change. You can use the plus or minus controls beside the row numbers or above the column letters.
- Select the rows or columns belonging to the group. For rows, click and drag across their row numbers; for columns, select their column letters. Selecting cells inside a group can work, but selecting the relevant entire rows or columns makes your intent clearer.
- Open the Data tab and choose Ungroup in the Outline group.
- If Excel asks which direction to ungroup, select Rows or Columns as appropriate and confirm.
For example, suppose rows 5 through 12 contain detail beneath a subtotal and have a bracket at the left. Select rows 5 through 12, then use Data > Outline > Ungroup. Their values and formulas remain in place, but that selected outline group is removed. If several outline levels are nested, you may need to repeat the command for the level you want to remove.
What Ungroup changes—and what it does not
An outline group is a way to organize worksheet rows or columns so detail can be collapsed and expanded. Ungroup removes the grouping relationship for the selected level. It does not erase data, remove formulas, or automatically delete subtotal rows. If the purpose of the outline was simply to hide detail temporarily, use the plus or minus control to expand or collapse it instead.
Grouping and hiding are related but not identical. A row can be hidden manually without belonging to an outline group. Ungroup may not make a manually hidden row visible because it is not removing the hide setting. To reveal hidden rows, select the surrounding rows, right-click their row headings, and choose Unhide. For columns, use the corresponding column headings and Unhide command.
Also distinguish Ungroup from deleting rows or columns. Ungroup leaves the worksheet layout and contents intact. If you actually want to remove data, use Excel’s delete commands deliberately; do not expect Ungroup to do that work.
Ungrouping items in a PivotTable
PivotTables have their own grouping feature. Dates may be grouped into months or quarters, and numeric values may be divided into ranges. To undo one of these groupings, click a grouped item inside the PivotTable, then look for PivotTable Analyze > Group > Ungroup. The PivotTable Analyze tab appears when a PivotTable cell is selected; in some Excel versions or ribbon layouts, the contextual tab name or button arrangement can differ slightly.
For example, if a date field displays Jan, Feb, and Mar instead of individual dates, select one of the grouped date labels and choose Ungroup from the PivotTable Analyze tab. Excel returns the field to its underlying individual items, provided the source data and PivotTable structure support that operation. This is not the same command as Data > Outline > Ungroup: the latter is for worksheet row and column outlines.
If Ungroup is unavailable for a PivotTable, check that you clicked inside the PivotTable and selected a grouped value rather than a blank area or a field heading. Some PivotTables based on particular data sources or models may not allow the same grouping operations as a standard PivotTable. Refreshing or changing the source will not necessarily make an unsupported grouping editable.
Ungrouping selected worksheet tabs
Excel can also group whole worksheet tabs. This happens when multiple sheet tabs are selected together, often so the same edit can be made on several sheets. In that case, there usually is not a ribbon button named Ungroup for worksheet tabs. Right-click one of the selected sheet tabs and choose Ungroup Sheets.
You can also click a sheet tab that is not part of the current selection, if one is available, to end the group. When tabs are grouped, Excel commonly shows [Group] in the workbook title area, and the selected tabs appear highlighted. Be careful: changes made to cells or formatting while sheets are grouped can be applied to every selected sheet. Ungrouping tabs stops that linked editing; it does not undo edits already made.
This feature is useful when several similarly structured monthly sheets need the same heading or layout change. It is risky when grouping happened accidentally. Before typing or pasting, check whether more than one sheet tab is selected. If an edit has already affected several sheets, use Undo promptly if appropriate, or restore from a saved copy or version history.
Ungroup versus Clear Outline
Excel’s Ungroup menu may include a separate Clear Outline command. Use Ungroup when you want to remove a particular selected grouping level while preserving other outline structure. Use Clear Outline when you want to remove the outline structure from the worksheet altogether. Clearing an outline does not delete worksheet data, but it removes the grouping controls and structure that let you collapse and expand those rows or columns.
For instance, a report might have detail rows nested inside department groups, with an additional level for regions. Ungrouping one selected group is the more targeted action if you want to keep the remaining hierarchy. Clear Outline is a broader reset for when you no longer need the worksheet’s outline. If you only need to see all the details, expand the outline rather than using either removal command.
Why Ungroup may be unavailable
If the button is dimmed or does not appear to change anything, check the type of selection and the type of grouping before assuming the workbook is broken. Common causes include:
- No outline group is selected. The active cells may not be part of a row or column outline. Select the grouped rows or columns, or use the PivotTable-specific route if the grouping is in a PivotTable.
- You are using the wrong Ungroup command. Data > Outline applies to worksheet outlines; PivotTable Analyze > Group applies to grouped PivotTable items; Ungroup Sheets applies to selected worksheet tabs.
- The outline is collapsed. Expand it first if the hidden rows or columns make it unclear which group you need to select.
- The sheet is protected. Protection can restrict structural changes. Check the Review tab for the sheet protection controls; you may need permission or the password to make the change.
- The selection crosses different groups or levels. Select one intended group at a time and try again. With nested groups, removing one level may leave another outline level visible.
If the outline controls themselves are not visible, look at Data > Outline for the Show Outline Symbols setting. The symbols can be hidden even though outline structure remains. Showing them can make it easier to identify the group before ungrouping. Do not choose Clear Outline merely to make the symbols visible; it removes the structure instead.
Excel on Mac and Excel for the web
In Excel for Mac, select the relevant outlined rows or columns and look on the Data tab for Ungroup, in the outline controls. Ribbon arrangement can vary with the Excel version and window width, so the command may be displayed differently from Windows. For grouped worksheet tabs, use the sheet-tab context menu and choose Ungroup Sheets. For PivotTables, select a grouped item and look for the contextual PivotTable Analyze grouping controls.
Excel for the web also presents commands through its ribbon, but available features and the visible ribbon layout can vary. Select the grouped rows or columns and inspect the Data tab’s outline commands. If a particular outline or PivotTable operation is unavailable in the browser, open the workbook in the desktop Excel app and use the corresponding command there. The key distinction remains the same across platforms: worksheet outlines, PivotTable groupings, and grouped sheet tabs are separate features.
On Windows, Alt+Shift+Left Arrow is the keyboard shortcut for ungrouping selected outline rows or columns in Excel. It is intended for worksheet outlines, not for ungrouping worksheet tabs or PivotTable items. If the shortcut does nothing, select the outline group and use the ribbon command to confirm that you are working with the right kind of grouping.
Frequently Asked Questions
Where is Ungroup in Excel for rows?
Select the grouped rows, then choose Data > Outline > Ungroup. If prompted, select Rows. The command removes grouping, not the rows or their contents.
How do I ungroup columns in Excel?
Select the grouped columns by their column letters, then choose Data > Outline > Ungroup. Choose Columns if Excel asks which direction to apply.
How do I ungroup sheets in Excel?
Right-click one of the selected sheet tabs and choose Ungroup Sheets, or click a sheet tab outside the selection. Check for multiple highlighted tabs before editing, because grouped sheets can receive the same changes.
Does Ungroup delete rows or data?
No. Ungroup removes an outline grouping relationship. It does not delete the selected rows, columns, cell values, or formulas. Use a separate delete command if you intend to remove worksheet content.
What is the difference between Ungroup and Clear Outline?
Ungroup removes a selected grouping level or group. Clear Outline removes the worksheet’s outline structure more broadly. Neither command is a substitute for expanding a collapsed group when you simply want to view its detail.
Where is Ungroup in a PivotTable?
Click a grouped item in the PivotTable and choose PivotTable Analyze > Group > Ungroup. The command is separate from the worksheet outline button on the Data tab.
Related
- Vendor Contact List Excel Template: Fields and Setup
- Vendor Comparison Matrix Template in Excel: Scoring Guide
- Vendor Comparison Excel Template: A Practical Setup Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Print an Excel Column on Every Page: Step-by-StepTo print the same Excel column on every printed page, open the worksheet and choose Page Layout >…
- How to Remove Formatting in Word Without Losing TextTo remove formatting in a Word document, select the text, then choose Home > Clear All Formatting. To…
- How to Delete a Header from One Page in WordTo delete a header from just one page in Word, isolate that page in its own section, turn…
- Google Sheets: How to Combine Data From Two ColumnsYes. In Google Sheets, combine two columns row by row with the ampersand operator: if the values are…
- Excel Text Box Linked to Cell: Setup, Uses, and FixesYes. In Excel for Microsoft 365 on Windows, select the text box by its outer border, click the…
- Google Sheets: How to Ignore Errors SafelyTo make Google Sheets show a chosen value instead of a formula error, wrap the calculation in IFERROR;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.