Excel Unmerge Cells and Fill Every Cell with Its Data

How-to basics · Updated November 19, 2026

To unmerge cells in Excel for Microsoft 365 on Windows, select the merged range and choose Home > Merge & Center > Unmerge Cells. Excel keeps the value in the former merged area’s upper-left cell and leaves the other cells blank; to copy that value into the blanks too, use Go To Special to select them and fill them with a reference to the value cell.

What Unmerging Does to Your Data

A merged cell is one visible cell made from two or more adjacent cells. Excel stores its content in the upper-left cell of the merged area. Unmerging restores the original grid, but it does not duplicate the content across the restored cells. For example, if A2:C2 is merged and contains North, unmerging leaves North in A2 and makes B2 and C2 blank.

That behavior is important when a merged heading is being turned into a data table. A person may expect the heading to appear in every row or column, but Excel does not infer that intention. Unmerge and fill are separate operations: first restore the cells, then decide whether the original label should be copied into the blanks, left only in its original cell, or handled another way.

Unmerging also changes the sheet’s layout. Text that previously appeared centered across several columns may now sit at the left edge of the first cell, depending on its alignment settings. The other cells become editable independently, which can make sorting, filtering, formulas, and data import easier.

Unmerge Cells in Excel for Windows

To unmerge one area, click a cell in it. To unmerge several areas at once, select a range that contains them, or select multiple nonadjacent areas with Ctrl-click. Then use the ribbon path:

  1. Select the merged cell or range.
  2. On the Home tab, open the Merge & Center drop-down.
  3. Choose Unmerge Cells.

The menu item is available even when the button displays a different merge option. If the selected range contains a mixture of merged and unmerged cells, Excel unmerges the merged areas within the selection. Check the result before filling blanks, particularly if the selection also includes unrelated empty cells.

On Excel for Mac, select the range and use the Home tab’s Merge & Center menu to choose Unmerge Cells; the ribbon layout can vary slightly by release. In Excel for the web, the command is also on the Home tab under Merge & Center. If the workbook is shared or being edited in a browser, check that the change has saved before closing it.

Fill Every Cell in a Vertical Merged Area

For a vertical merged area, the quickest built-in method is to fill each blank from the cell directly above it. This is useful when each original merged block has a value at its top and the cells below it should repeat that value.

  1. Unmerge the relevant cells.
  2. Select only the restored range you want to populate. Avoid including unrelated blank cells.
  3. Choose Home > Find & Select > Go To Special, select Blanks, and click OK. Excel selects the blank cells in the selected range.
  4. Type an equals sign, press the Up Arrow once, then press Ctrl+Enter. Each selected blank is filled with a relative reference to the cell above it.

For example, suppose A2 contains North and A3:A4 are the blanks created by unmerging a vertical merged area. With A3:A4 selected as blanks, the relative-reference method fills them from the cells above, so the resulting cells display North. A direct formula in A3, assuming North is in A2, is =A2; it returns the value from A2. The multi-cell method adjusts the reference for each selected blank, so A4 refers to A3 rather than repeating the literal formula =A2.

On Windows, Ctrl+G opens Go To, where you can choose Special to reach the same selection options. On a Mac, use the Go To command from the Edit menu or the keyboard shortcut available in your Excel release, then choose Special; Control+Return is commonly used to enter the same formula into selected cells. If shortcut behavior differs on your Mac, use the ribbon or menus rather than guessing at a key combination.

Fill Horizontal and Mixed Merged Areas

The fill-from-above technique assumes the blank cells sit below the source value. It is not suitable for a horizontal merge such as A2:C2, because the blanks are to the right of the value. After unmerging that area, select its blank cells and fill them from the cell immediately to the left. For a blank in B2 with North in A2, the corresponding reference is =A2; it displays North. If filling multiple blanks, make sure the relative references progress across the row as intended.

For an area merged both across columns and down rows, or for a selection containing blocks with different orientations, process each block according to where its original value sits. A single fill direction can produce wrong references when some blanks are below a value and others are to its right. Select and fill vertical groups and horizontal groups separately, or use a helper column and inspect the results before replacing anything.

Excel’s Fill Down and Fill Right commands can also copy a value or formula across a straightforward range. Select the source cell together with the cells to fill, then use Home > Fill > Down or Home > Fill > Right. On Windows, Ctrl+D fills down and Ctrl+R fills right. These commands are often easier for one simple block, while Go To Special is useful when the blanks are scattered among values after unmerging multiple areas.

Keep Formulas or Make the Results Permanent

The Go To Special method enters formulas that point to neighboring cells; it does not initially create independent copies of the original text. That distinction matters if you later change or delete the source cell. The filled cells may then display the source’s new value or become blank, because they depend on the reference.

If you want each cell to retain the current displayed result independently, convert the formulas to values after checking them. Select the filled range, copy it, then choose Home > Paste > Paste Values. This replaces the formulas with their current results. You can also leave the formulas in place if repeated labels should update whenever the source changes.

Before converting, confirm that the filled cells contain the expected labels and that no formula refers to an unintended blank. For example, a formula referencing a blank cell may display 0 in some circumstances, while a reference to a text cell displays that text. The goal is not simply to make every cell nonblank; it is to repeat the correct source value for each original merged block.

Choose a Safe Method for Large Selections

For a few merged areas, unmerge and fill each block manually. For a larger cleanup, make a copy of the worksheet first, then work on a defined range. A backup makes it easier to recover the original layout and content if the selection includes cells that were not part of the intended merged headings.

  • Use fill down when every merged block is vertical and the value is at the top.
  • Use fill right when every merged block is horizontal and the value is at the left.
  • Use Go To Special > Blanks when you have many blank cells in a well-defined range and can verify the direction of each reference.
  • Use a helper range when orientations are mixed or when the original values need review before changing the source data.

For a simple vertical list, a helper column can preserve the original column while you inspect a filled copy. Copy the completed results, paste them as values into the intended destination, and compare several rows—including the start and end of each merged block. There is no need to use a macro for ordinary unmerge-and-fill work, and a formula-based approach is easier to inspect and reverse before values are pasted over cells.

Common Problems and How to Avoid Them

Only one cell contains the value after unmerging. That is normal. Excel retains the content from the upper-left cell; it does not automatically repeat it. Use a fill method only if repeating the label is appropriate for the data.

The wrong text appears in some cells. A fill direction may not match the merged area’s orientation, or the selected range may have included unrelated blanks. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac), then repeat on a smaller, correctly oriented range.

Go To Special selects too much. It selects blanks within the range you selected before opening the dialog. If that range includes empty rows or unrelated columns, those blanks may be filled too. Limit the initial selection to the cells that belong to the merged blocks, and inspect the selection before entering a formula.

Unmerge Cells is unavailable or has no visible effect. Confirm that the selection includes a merged cell. If the worksheet is protected, editing may be restricted; check Review > Unprotect Sheet if you have permission and the password, if one is required. A protected workbook or restricted shared file may also limit what you can change.

The result looks different after unmerging. Alignment, borders, and formatting are separate from the stored value. Unmerging can leave formatting applied to individual cells, and text may no longer appear centered across the former merged area. Adjust alignment or borders after confirming the data is correct.

Consider Alternatives to Merging

Merged cells can make headings look centered, but they often complicate sorting, filtering, copying, and using a range as a structured data source. If the purpose is only to center a heading across columns without combining the cells, select the cells and use Format Cells > Alignment > Horizontal > Center Across Selection. This changes the presentation while keeping the cells separate; availability and wording can vary slightly between desktop and web versions.

For tabular data, another practical alternative is to put a category or group name in every row where it applies. Repeated values are easier to filter, sort, and use in PivotTables than a label stored in a merged cell with blanks beneath it. However, do not repeat headings automatically if the blank cells intentionally mean something different, such as not applicable or unknown. Choose the data structure based on what the blanks mean, not just how the sheet looks.

Frequently Asked Questions

Does Excel copy the merged value into every cell when I unmerge?
No. Excel keeps the value in the upper-left cell of each merged area and leaves the other restored cells blank. Fill the blanks separately if you need repeated values.

Can I unmerge many cells at once?
Yes. Select a range containing the merged areas, then choose Home > Merge & Center > Unmerge Cells. Review the selection first so you do not fill unrelated blanks afterward.

How do I fill blanks after unmerging without typing each value?
Select the relevant range, choose Home > Find & Select > Go To Special > Blanks, and enter a reference to the appropriate neighboring source cell. Use a reference from above for vertical blocks or from the left for horizontal blocks.

Will the copied values change if I edit the original cell?
If you used formulas to fill the blanks, yes: the cells depend on their references. Use Copy and Paste Values to replace those formulas with their current results.

Can I undo an unmerge?
Immediately after the change, use Ctrl+Z on Windows or Command+Z on Mac. If you have made later edits, undoing may also reverse those edits, so a saved copy is the safer option for larger cleanup work.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.