Merged cells, they cause havoc with a spreadsheet. The Effects can be far reaching and sometimes not appear at the time of development. If you do use merged cells you will run the risk of the following issues-

• Losing the ability to sort your data correctly
• Inability to run VBA if necessary- it does not handle merged cells very well
• You lose the functionality of a normal data table in Excel; that is the great functions such as Pivot Tables, SUMIF, COUNTIF etc the list goes on!.
• Autofill does not work – so if you have merged cells in your data list – forget the handy auto fill functionality
• Copying and pasting- the same as auto fill, forget it it will not happen if you have merged cells in the range you want to copy and paste

So, there are some pretty heavy reasons why we should not use merged cells, so what if your worksheet functionality is not working as expected- one of the first things I do is check for merged cells. Excel will also tell you if you have an issue with merged cells if this error appears when you try to carry out the most simple of tasks such as sorting a column of data which an error seen below-

Merged cells and the above error are not such an inconvenience if you column of data is not that large but if your data column contains hundreds if not thousands of entries then it is not so easy to deal with. So, here is an easy way to smoke out those evil merged cells.

• Select the range you want to find the merged cells in
• Hit CTRL+F to open the Find dialog box
• Hit Options then the Format button
• After Format button is clicked go to the Alignment tab

• Un-tick Wrap Text and Shrink to fit so the Merged Cells option is the only one selected
• Now hit Find All- a list of the merged cells will be displayed
• In my example D4,D8 and D9 are merged

That’s it Excel will reveal which cells are merged.