To unmerge cells in an Excel workbook, select the merged cell or range, then choose Home > Merge & Center > Unmerge Cells. Excel keeps the value in the upper-left cell of each merged area and leaves the other cells empty; unmerging does not restore information that was removed when cells were first merged. Save a copy of the workbook before making broad changes, especially if you are working across several sheets.
What You’ll Need
- Excel for Microsoft 365 on Windows, or a compatible version of Excel with the workbook open.
- Permission to edit the sheet. If it is protected, you may need the sheet password or help from the workbook owner.
- A backup copy if the workbook is important or you plan to unmerge many cells or sheets at once.
Step-by-Step
- Open the workbook and make a copy. Open the Excel file, then use File > Save As to save a working copy with a different name or in a separate folder. This gives you a way back if the layout changes unexpectedly. Keep the original file unchanged until you have checked the result.
- Choose the sheet to edit. Click the worksheet tab that contains the merged cells. If you know the cells you want to fix, locate them visually. Merged cells commonly appear in report headings, forms, calendars, and tables where a label spans several columns or rows. You can also click a suspected merged area; the Name Box to the left of the formula bar shows the selected cell or range, and the formula bar shows the stored value.
- Select the merged cell or complete range. Click a merged cell to select it. To unmerge several nearby areas, drag across a range that includes each entire merged area. A merged area might span columns, rows, or both. Avoid selecting only part of one: Excel may refuse the change or report that you cannot change part of a merged cell. If you want to unmerge the whole worksheet, click the Select All button at the top-left corner of the grid, where the row and column headings meet.
- Open the merge commands. On the ribbon, select Home. In the Alignment group, open the dropdown beside Merge & Center. If the ribbon is narrow, the command may appear as a compact button or within the Alignment options, so expand the ribbon or widen the Excel window if needed.
- Choose Unmerge Cells. Click Unmerge Cells in the dropdown. Excel removes the merged-cell formatting from the selected area. The cells become separate grid cells again, but their borders, fills, alignment, and other formatting may remain. Unmerging changes the cell structure; it does not automatically make the worksheet look exactly as it did before the merge.
- Check the values and layout. Look at each unmerged area. The original value is retained in its upper-left cell; the other cells in the former merged area are blank. Check headings, borders, text wrapping, column widths, and row heights. If a heading now appears only in the first cell, you can leave it there, copy it where appropriate, or use another layout method such as Center Across Selection.
- Repeat on other worksheets if needed. Click the next worksheet tab and repeat the selection and unmerge steps. Excel does not normally provide one simple command to unmerge every cell in every worksheet at once, so inspect each sheet and work only on the areas that need changing. If a sheet has no merged cells, its appearance will not change when you try the command on a selection.
- Save and verify the copy. Press Ctrl+S on Windows, or Command+S on Mac, and review the workbook at normal zoom. If other people use the file, check important print areas and formulas as well as the screen layout. Keep the original copy until you are satisfied that the workbook behaves as expected.
To unmerge matching positions on several sheets: You can group worksheets first, but use this only when the sheets have the same layout and you intend to change the same cell addresses on every selected sheet. On Windows, click the first sheet tab, then hold Shift and click the last tab in a consecutive group; for nonconsecutive tabs, hold Ctrl while clicking each tab. On Mac, use Shift or Command with the sheet tabs. Select the target cells, choose Home > Merge & Center > Unmerge Cells, then right-click a selected tab and choose Ungroup Sheets. Grouped edits affect every selected sheet, so ungroup immediately afterward and inspect the sheets. Do not use this shortcut for sheets with different layouts.
If you cannot find a merged area, use Home > Find & Select > Find, then select Options and open the Format settings. In the Find Format window, use the Alignment tab and look for the Merge cells option. After setting that search format, choose Find All to list matching cells. The exact dialog appearance can vary by Excel build; clear the search format afterward if you want future searches to find text or values regardless of formatting.
Common Mistakes
- Expecting all content to return. Before merging, Excel keeps only the content from the upper-left cell and warns that other values will be discarded. Unmerge restores the separate cells, not those discarded values. Check an earlier version, backup, or source file if you need them.
- Selecting only part of a merged area. A selection that cuts through a merged cell can trigger an error. Select the whole merged area or select the entire worksheet before choosing Unmerge Cells.
- Assuming unmerge also resets formatting. The cells may keep fills, borders, alignment, or number formats. Review and adjust these separately if the old formatting looks confusing.
- Changing grouped sheets unintentionally. When worksheet tabs are grouped, edits apply to all selected sheets at the same cell addresses. Confirm which tabs are selected and ungroup them after the operation.
- Saving over the only copy. Large-scale layout changes can be tedious to reverse, and some actions may be difficult to reconstruct later. Work on a separate copy first.
Tips
- Use Center Across Selection for a heading. If you want text centered over several columns without merging the cells, select the cells, open the Format Cells dialog with Ctrl+1 on Windows or Command+1 on Mac, choose the Alignment tab, and set Horizontal to Center Across Selection. This preserves separate cells for sorting and filtering. The option is not the same as Merge & Center.
- Plan for blank cells in tables. After unmerging a table, only the top-left cell of each old merged block contains its former value. If every row needs a label for sorting, filtering, or analysis, fill the blank cells deliberately rather than assuming Excel has duplicated the label.
- Use Undo for an immediate correction. If you unmerge the wrong selection and have not made other changes since, press Ctrl+Z on Windows or Command+Z on Mac. Undo may not be available after closing the workbook, and it cannot restore values that were discarded during the original merge if those values were already lost.
- Check accessibility and navigation. Merged cells can make moving through a grid, selecting ranges, and using screen readers less predictable. For data tables, keeping cells separate usually makes the structure easier to navigate and sort.
Troubleshooting
The Unmerge Cells command is unavailable or has no effect. Confirm that your selection includes a merged cell. If you selected only part of a merged area, select the whole worksheet with the Select All button and try again, or select the complete merged range. If the sheet is protected, go to Review > Unprotect Sheet. Excel may ask for a password; if you do not have it, ask the workbook owner rather than trying to bypass protection.
Excel says you cannot change part of a merged cell. Cancel the action, then select the full merged area. Clicking the merged cell itself is usually simplest. For a larger selection, include every row and column covered by the merged cells. Selecting all cells on that sheet is another option when you intend to remove all merges there.
The unmerged cells are blank except for the first one. That is expected. Excel stores a merged area’s content in its upper-left cell. If the other entries were deleted when the cells were merged, unmerging cannot bring them back. Check File > Info for version history, a backup, or an earlier copy of the workbook. If the workbook is stored in OneDrive or SharePoint, available version history may let you open or restore an earlier version; review carefully before replacing the current file.
The sheet still looks wrong after unmerging. Adjust column widths and row heights, and inspect alignment, wrapping, borders, and fills. If text was centered across a heading, Center Across Selection may give a similar appearance without merging. If a value seems hidden, check whether the column is narrow, the font color blends into the fill, or the cell uses a number format that changes how the value displays.
The menu is missing or looks different. Make sure you are in the desktop Excel application and on the Home tab. In Excel for the web, select the cells and look for the merge options on the Home ribbon; the available controls can differ from desktop Excel. On Mac, the ribbon command is also under Home and Merge & Center, though placement may vary with the window size. If commands are collapsed, expand the ribbon or use the ribbon search box to search for Unmerge Cells.
Frequently Asked Questions
Does unmerging Excel cells restore values that were removed?
No. Unmerge Cells separates the cells and retains the merged area’s upper-left value. Other contents discarded when the merge was made are not recreated. Look for a backup or earlier workbook version if you need them.
Can I unmerge every cell in a worksheet at once?
Yes. Click the Select All button at the grid’s top-left corner, then choose Home > Merge & Center > Unmerge Cells. This affects the active worksheet only. Review the result because the values stay in the upper-left cells and the surrounding cells become blank.
Can I unmerge cells across several worksheets at once?
Yes, by grouping the worksheet tabs and applying Unmerge Cells to the selected cell addresses. This is safe only when those sheets share the same layout and you intend the same change on each. Ungroup the tabs afterward. For sheets with different structures, handle them separately.
Will unmerging change formulas or delete formatting?
Unmerging is primarily a cell-layout change. It does not automatically clear all formatting, and formulas elsewhere may still refer to cells in the area. Review formulas and formatting after the change, especially if the merged cells were part of a report or data-entry form.
Can I unmerge a workbook in Excel for the web or on Mac?
Excel for the web and Excel for Mac provide merge controls, though the ribbon layout may differ from Windows. Select the merged cells and look under Home for the merge dropdown and its Unmerge Cells command. If the command is hard to locate, expand the ribbon or use the app’s search feature.
Does unmerge mean splitting one Excel file into multiple files?
No. In Excel, Unmerge Cells refers to separating cells that were combined on a worksheet. If you mean splitting one workbook into separate files or extracting worksheets, that is a different task and requires copying or saving sheets as separate workbooks.
Related
- How to Share an Excel Workbook in Windows 11 Safely
- How to Share Excel in Google Drive: A Clear Guide
- How to Share Excel in Microsoft 365
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Group Rows: Keep the Top Row Visible When CollapsedYes—Excel can keep a top row visible when you group rows, but the row must be outside the…
- Excel Frozen Not Responding? Steps to Recover SafelyIf Excel says Not Responding, stop clicking and give it a little time: it may be calculating, opening…
- How to Change View Settings in Outlook for a Better InboxTo change how your inbox looks, open Outlook’s view controls and adjust the message list, reading pane, sorting,…
- Excel Table to Markdown: Ways to Convert and FormatExcel has no built-in command that exports a range or table directly as Markdown. To convert one, copy…
- Loan Amortization Schedule in Google Sheets: How to BuildTo make a loan amortization schedule in Google Sheets, enter the loan amount, annual interest rate, and term,…
- Excel Keyboard Shortcut to Delete a Column SafelyIn Excel for Microsoft 365 on Windows, select any cell in the column and press Ctrl+Space, then press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.