How to Find all Merged Cells in an Excel Worksheet


Many folks use merged cells in their worksheets and they can definitely be helpful, but many times merged cells can cause you untold heartburn when selecting ranges, sorting and other functions we use in Excel every day.

Even if you do not use merged cells because you once experienced the problems mentioned above, you certainly may find yourself working on a worksheet that was created by someone else which contains merged cells.

If you want to be certain there are no merged cells in your worksheet follow the steps below to get rid of them.

  1. Click CTRL + F to open the Find and Replace dialog box.
  2. Be certain the Find what field is clear and the Match case and Match entire cell contents have not been selected.
  3. Click on Format.
  4. Select the Alignment tab.
  5. Check the Merge cells option.
  6. Click OK to close the dialog.
  7. Click Find All. Note that all merged cells will be listed at the bottom of the dialog.
  8. With the Find dialog box still open click CTRL + A to select all cells listed at the bottom and you will notice that all of those cells will also be selected in your worksheet as well.

excel find and replace

Now that you have identified the merged cells, you can:

  1. Close the Find dialog and apply a color background to the selected cells so that they stand out for you and you can locate them easily.
  2. Keep the Find dialog open and do your editing in the worksheet and when you need to select a group of merged cells, you can click the cell reference in the Find dialog. If you have un-merged some cells, you can click the Find All button once again to update your list.

Be certain once you have used this process, that you clear the formatting from the Find what criteria by clicking Format | clear | Ok in the Find dialog to prevent a problem when you next use the Find dialog.


Posted in:
About the Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol's Corner Office or follow Carol on FaceBook and Twitter.

There are no comments

Your email address will not be published. Required fields are marked *