Do Not Merge Cells!

It is so tempting! Merge cells in Excel so they form a header above two or more columns. Yes, it looks nice but resist the temptation because it can be dangerous.

Excel Guideline 06: Do Not Merge Cells :: PerfectXL

Confusing the Database

Firstly, consider this from within a spreadsheet’s structure. Sheets in a spreadsheet resemble tables in a database, but with much more freedom than with a traditional database, such as SQL. The columns are variables, the rows are observations. By merging cells,  two or more variables suddenly become one. You can imagine that this is very difficult for a database. Does the entered value belong to one or the other variable? And if something is customized in one of the columns in its entirety, should the merged cell be taken into account or not?

Try It Out!

Excel tries to go as far as possible. In principle, Excel always takes the upper left corner of merged cells as the true row and column value. However, to keep possibilities open for the user, Excel will add another value during merging when the top left corner is empty. Try it out. Leave A1 blank, insert a number into B1 and merge the cells. The number from B1 is moved into the merged cell. Then undo the merge. Where does the value come true? We also had to test it for a while. The value appears in A1 to be saved. Not illogical, but also a bit crazy.

Excel Guideline 06: Do Not Merge Cells - Example :: PerfectXL

Merging Cells Can Be Risky

Merging cells involves risks when referenced. The reference may be incorrect if the left top corner is not taken into account or becomes invalid when moving data. In addition, they are difficult as they often make it impossible to insert rows or columns.

Restrict Merges To A Few Cells

We know that many Excel users merge cells often because it’s an easy way to decorate the layout. If you really can’t stop doing it, restrict the merge to a few cells and only in Output Tabs, where formatting is very important.

How PerfectXL Can Help You

Our PerfectXL Validation Tool can easily generate a list for you with all merged cells in a spreadsheet so you can decided to unmerge them when needed. You can choose to only display merged cells referred to in other cells or all merged cells.

Never miss a Guideline!