Categories
Don’t merge cells in Excel
It’s very tempting to merge cells in Excel, so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!
Confusing the database
Firstly, consider merged cells from the perspective of a spreadsheet’s structure. Sheets in a spreadsheet can be thought of like tables in a database, but with much more freedom than with a traditional database, such as an SQL. The columns are variables, the rows are observations. By merging cells, two or more variables suddenly become one. You can imagine that this puts a lot of strain on a database. Which variable does the entered value relate to? if something is customized in column A, but not column B, should the merged cell between columns A and B be included or not?
Try it out
Excel does its best do to deal with these situations. 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 other values from the range during merging when the top left cell is empty. Try it out. Leave A1 blank, insert a value into B1 and merge the cells. The number from B1 is moved into the merged cell. Now undo the merge, where does the value end up? We also had to test it for a while. The value appears in A1 to be saved, it’s not illogical, but you can see how this might result in some confusion.
Merging cells is risky business
Merging cells involves risks when referenced. The reference may be incorrect if the top left cell is not referenced, also the merged cell often becomes invalid when data is moved. In addition, they are difficult for the layout as they often make it impossible to insert additional rows or columns.
Restrict merges to a few cells if you must
We know that many Excel users merge cells often because it’s an easy way to improve the layout. If you really can’t stop doing it, restrict the merging to, at most, a few cells and even then, only do so in Output Tabs where formatting is very important. Merged cells might look good, but they are most definitely not worth the risk in most cases.