With the Merged cells manager, Power-user helps you spot and fix merged cells in your Excel spreadsheets.
TABLE OF CONTENTS
- The issue with merged cells in Excel
- How to use the Merged cells manager
- Alternative to consider instead of merging cells: center across selection
The issue with merged cells in Excel
Merged cells can be a real nightmare for Excel users. When cells are merged:
- They twist the results of your formulas.
- They prevent you from using powerful tools such as Tables and PivotTables.
- They make it very difficult to select just a column or row, because the selection will expand to columns and rows that are part of the merged cells.
- They are actually very difficult to find, because there is no easy solution to detect where all the merged cells are.
For the above reasons, any advanced Excel user recommands to just stop merging cells in Excel, and look at an alternative solution to stop merging cells while getting the same result visually speaking.
How to use the Merged cells manager
The Merged cells manager of Power-user is a solution to comes in action. This tool will find all the merged cells in your current sheet, and let you unmerge them flexibly. To use it:
- From the Power-user ribbon in Excel, click Merged cells manager.
- The below window will open, listing all the merged cells in your Excel sheet:
- Click on any of the ranges on the left to link to the specific cells, so you can easily check if they can be unmerged.
- Click Unmerge to simply unmerge the cells of that specific range. As a standard unmerge, only the cell on the top will keep the content that was previously in the merged range.
- Click Unmerge and fill down to unmerge the cells while duplicating the content to each of the cells that were previously merged.
- Click Ignore to forget about this merged range and not be reminded about it, for instance if you know you are going to want to keep the cells merged.
- You can do this either cell by cell, or click Unmerge all or Unmerge and fill down using the buttons at the bottom of this window, and this will apply your choice to all the merged cells from the list.
If you have hundreds of merged cells and need to unmerge and fill them all (for instance, so you can use formulas or PivotTables), this is something that would take you hours to do manually and that Power-user can accomplish in seconds.
Alternative to consider instead of merging cells: center across selection
Due to the issues listed above, we recommend avoiding to use merged cells. Instead, you can use Center across selection which looks very similar. Instead of actually merging the cells, it will only format the content of the cells as if they were merged, while keeping them as separate cells that are not impacting your formulas, Tables, PivotTables, etc.
You can find this Center across selection button from the Power-user ribbon in Excel.
Was this article helpful?
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
We appreciate your effort and will try to fix the article