Merged cells manager

Modified on Sat, 14 Nov at 10:10 AM

The Power-user Merged cells manager is a feature of the Power-user add-in for Excel that finds all the merged cells of the current sheet and lets you unmerge them, one by one or all at once, with or without filling the content down. What takes hours by hand on hundreds of merged cells takes seconds.

In short

  • Where: Merged cells manager in the Power-user tab of Excel
  • What you get: the list of all the merged ranges of the sheet, each one a click away
  • Unmerge options: Unmerge, Unmerge and fill down, or Ignore, per range or for all ranges
  • Better than merging: Center across selection, also in the Power-user tab

How to find and unmerge merged cells in Excel

  1. On the Power-user tab of Excel, click Merged cells manager. A window lists all the merged ranges of the sheet.
  2. Click a range in the list to go to those cells and check whether they can be unmerged.
  3. Choose what to do with the range, or use the buttons at the bottom of the window to apply it to all the ranges of the list.

Merged cells manager window listing the merged ranges of the Excel sheet

Merged cells manager window listing the merged ranges of the Excel sheet

ButtonWhat it does
UnmergeUnmerges the cells of the range. As with a standard unmerge, only the top cell keeps the content.
Unmerge and fill downUnmerges the cells and copies the content into each cell that was merged.
IgnoreRemoves the range from the list so you are not reminded about it, for example if you want to keep it merged.
Unmerge all / Unmerge and fill down (bottom of the window)Apply the same choice to all the merged ranges of the list at once.
Tip: to use formulas or PivotTables on a table with hundreds of merged cells, click Unmerge and fill down at the bottom of the window: every row gets its value back in seconds.

Why merged cells cause problems in Excel

  • They distort the results of your formulas.
  • They prevent you from using Tables and PivotTables.
  • They make it hard to select a single column or row: the selection expands to the merged columns and rows.
  • They are hard to find: Excel offers no easy way to list all the merged cells of a sheet.

That is why advanced Excel users recommend to stop merging cells and use Center across selection instead.

Use Center across selection instead of merging cells

Center across selection looks like merged cells, but the cells stay separate: only the display of the content is centered across them. Your formulas, Tables and PivotTables keep working. You find the Center across selection button in the Power-user tab of Excel.

See also: other Power-user tools to clean your Excel files, such as Delete empty cells, Clean formulas and UnPivot table.

Frequently asked questions about merged cells in Excel

How do I find all merged cells in Excel?

Excel has no simple way to list them. With the Power-user add-in, click Merged cells manager in the Power-user tab of Excel: a window lists all the merged ranges of the current sheet, and clicking one takes you to it.

How do I unmerge cells and fill down the content in Excel?

In the Power-user Merged cells manager, click Unmerge and fill down: the cells are unmerged and the content is copied into each cell that was merged. Use the button at the bottom of the window to do it for all the merged ranges at once.

How do I unmerge all cells at once in Excel?

Open the Power-user Merged cells manager and click Unmerge all at the bottom of the window. All the merged ranges of the sheet are unmerged.

Why are merged cells bad in Excel?

Merged cells distort formula results, block Tables and PivotTables, make it hard to select a single column or row, and are hard to find. Center across selection gives the same look without these issues.

What is the alternative to merging cells in Excel?

Use Center across selection: the content is displayed centered across several cells, but the cells stay separate. Power-user has a Center across selection button in its Excel tab.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article