Merged cells have their place in MS Office documents, especially in Word when using a table. They give great flexibility in layout options. There is a big difference between using them in Word and then Excel.
Excel cells will probably contain formulas or calculations or data which may link then into other worksheets in the same work book, or cells in another work book. Excel interprets the contents of the merged cells as being contained in the top left cell of your range of cells only, they therefore can cause havoc with a spreadsheet.
Also Read: How to Unprotect an Excel Sheet Without Password
The Effects can be far reaching and sometimes not appear at the time of development. If you do use merge 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 will not happen if you have merged cells in the range you want to copy and paste
Check: Excel Tips & Tricks for a Successful and Efficient Data Analysis
So, there are some really serious reasons why we should not merge cells.
So if your worksheet functionality is not working as expected- especially if you have inherited a work book, one of the first things I do is check for merging. Excel will also tell you if you have an issue if an error appears which warns ‘This operation the merged cells to be identically sized‘ when you try to carry out the most simple of tasks such as sorting a column of data.
If your column of data is not that large it is easy enough to deal with but if your data column contains hundreds if not thousands of entries then it is not easy to resolve. So, here is an easy way to smoke out those evil cells.
There is no direct way to do this in Excel but it is achievable with a bit of clicking around.
- Select the range you want to find the 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-
That’s it, all offending cells will be identified.
Recommended for you: