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 Risk Eliminator 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.
- Avoid >70 types of risks
- Improvement suggestions
- Quality reports
Read more about risks in Excel files
On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.
Here you will find information like system requirements, release notes, and answers to most questions about our software.
The VLOOKUP and the combination of INDEX and MATCH are well known, and to our great frustration, VLOOKUP is used a lot more often. The combination of INDEX and MATCH is less error prone and a lot more efficient.
Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.
Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.
Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.
(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.
Even Thomas Jefferson, 19th century president of the United States, remarked: “The most valuable of all talents is that of never using two words when one will do.”
The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.
Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.
You might need to run a calculation for inflation, the result of which is the needed in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation.
It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.
Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.
Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.
To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.
Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.
Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.