Sunday, December 05, 2004

Merging Cells in Excel

Don't do it!

OK now I'll explain why.
First of all you can merge cells if you want to, but I'll explain the consequences below. Basically merged cells should only be used in cosmetic non database spreadsheets.

Microsoft have changed the formatting option relating to centering text across cells a number of times in its evolution of Excel. Not all of these changes are backward compatabile either.
Before the Merge Cells feature was introduced (in Office 2000 I think) Excel used to have a center across cells button in its tool bar which replicated the menu function Edit Cells Format Alignment, Horizontal, Center across Selection.
This feature simply centered the text in a cell across all the cells that had been selected before choosing the option.
When they upgraded to Office 2000 they used the same button on the toolbar (a little a) and replaced its function with the new Merge Cells function. People who were used to centering text across cells kept using the button, only now a more insidious change was being made to the spreadsheet.
Merge Cells A1 and B1 and B1 will no longer exist. Merge C3 to C8 and cells C4 through C8 no longer exist. This makes it very awkward to work with rows and columns (records and fields) of data. I have repaired countless spreadsheet applications that were wrecked by this inadvertent use of the "old" toolbar button with a new function.
Office 2003 works with it a little smarter than 2000 and 2002 (XP) in that you can select columns that contain merged cells without the selection expanding to the entire range of the merged cells. However the database structure and integrity is gone.

In a nutshell, if you want to manipulate data in a database type format, and you must center across columns than use the Menu and select this option in the Format Cells Alignment tab.
If you must use the merge cells function then don't expect your file to be user friendly for formulas like SUMPRODUCT, SUMIF, AVERAGE, COUNT, VLOOKUP etc. in fact any formula that you might want to use that relies on a column or row of data.
Use it only on cosmetic pages, help, instruction and menu sheets etc.


Posted by: Jethro | 1:19 AM |