Automobile by Edison // PerfectXL Spreadsheet Validation

Do Not Merge Cells!

It is so 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 Can Be Risky

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

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. They might look good, but they are most definitely not worth the risk in most cases.

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 find them easily and decide whether or not you will unmerge them, as we suggest. Here you will also find the distinction between merged cells that are referred to, and those that are stand alone.

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.