****SUBSCRIBE****** to my Excel at Excel newsletter 3 FREE Excel Tips every 15 days http://www.howtoexcelatexcel.com
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 an error appears when you try to carry out the most simple of tasks such as sorting a column of data.
Merged cells and the 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.
There is no direct way to do this but it is achievable with a bit of clicking around.
•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
That’s it Excel will reveal which cells are merged.