Excel is powerful, but it’s also unforgiving. A single oversight can spiral into a major problem, especially when money or sensitive data is involved. And you know what they say, prevention is better than cure.
5
Not Structuring Your Workbook Properly
I have seen messy workbooks in my lifetime, with problems like inconsistent formatting and scattered formulas. The former can make the sheet hard to look at, navigate, and analyze. The latter can lead to hidden errors, which can produce wrong calculations, hindering decision-making in the process.
Those are just specific examples, but on a broad scale, a poorly structured workbook can, for instance, make collaboration harder. It can turn an Excel sheet into a frustrating puzzle of trying to find the key data and formulas, as others try to understand with vague labels and messy layouts.
It can also be time-consuming to fix, which can lead to wasted hours, duplicate work, and even conflicts (both inside and outside the sheet).
To avoid creating a poorly structured workbook, establish a consistent layout—download or create your Excel templates if you have to. To improve navigation, use clear labels and color-code sections. Also, put all important calculations in one specific place to make them easier to find and fix (should they need tweaking).
4
Not Using the Correct Functions
Some formulas work better than others in certain scenarios. A good example of this is the rigidity of the VLOOKUP function in Excel—it requires the lookup value to be in the first column of the table array and the lookup column to be on the right. If the table array is rearranged or you need to find a value on the right side of the lookup value, you can run into problems.
In these scenarios, XLOOKUP can be better than VLOOKUP since it’s more flexible and dynamic. Take a look at the data in the screenshot below, for example:
If I want to find the stock levels for the video games, I would enter the formula below somewhere, like cell E1:
=VLOOKUP(“Video Games”, A1:C4, 3, FALSE)
Here, the lookup column in the formula is 3, which translates to column C in the Excel sheet. If someone deletes column B, VLOOKUP will display an error because it can’t find the value.
To fix this, I would need to recalculate the columns and update the value of the lookup column to 2. That’s because the data in column C moved to B after the deletion, and VLOOKUP couldn’t tell because the lookup column is hard-coded.
Now imagine if these deletions happened in multiple places or large Excel sheets. Fixing this would be frustrating and time-consuming—not to mention what would happen if you miss something.
XLOOKUP can get around this problem. Here’s what the formula would look like instead:
=XLOOKUP(“Gadget”, A2:A4, C2:C4)
If I delete column B, XLOOKUP will automatically change the C2:C4 part of the formula to B2:B4—no errors or frustrations.
3
Using Hard-Coded Values in Formulas
With the VLOOKUP function, we saw a problem with hard-coded values—when values change, they don’t get updated automatically. For instance, below is the syntax for calculating the discount on a product:
original_price * (discount_rate / 100)
If you hard-code discount_rate, it means that if the rate changes, you have to go everywhere you entered it in the sheet and manually change it. This is a tedious and error-prone task, especially in large sheets.
If you enter the discount rate in a cell and reference it in the formula instead, all you have to do is change the value in that cell. Then, it will be automatically updated in every formula that references it.
2
Using the Wrong Cell Reference
Many Excel users don’t understand cell references in Excel. You have a relative reference (e.g., =A1), which changes the row and column based on the cell it’s copied to. There is an absolute reference (e.g., =$A$1), which doesn’t change, no matter where it’s copied. You also have a mixed reference (e.g., =$A1) where either the column or row doesn’t change when copied.
It’s important to know cell references because using the wrong ones can cause incorrect calculations and data inconsistencies. You can only imagine how time-consuming this can be to fix in a large Excel sheet.
Let’s look at a simple example using the data in the screenshot below:
Suppose you want to add the value in cell C1 with the ones in column A (A1:A3). You would normally write the following formula in cell B1 and then copy it to B2 and B3:
=A1+C1
Upon looking at the results, it becomes immediately clear—the result in the copied cells is wrong. Since this was a relative reference, it changed the reference from C1 to C2 and C3 when the formula was copied to cells B2 and B3.
To avoid this and get the right results, using an absolute reference in the formula would help. Here is what that would look like:
=A1+$C$1
Now, if you copy the formula to the cells below, you will get the right results since the reference to C1 doesn’t change.
1
Ignoring Data Validation
Data validation ensures that users enter the correct data in a cell. For instance, if you need them to enter numeric values in certain cells, and they enter text instead, it can cause errors and mess up analyses. Another example is if you need them to enter numbers within a certain range, and they don’t, leading to incorrect calculations.
With data validation, you can, for instance, show the user an error when they enter text in a cell that requires numerical values or when they enter a number that is out of range. You can even use dropdown lists in Excel to restrict entries to predefined options. Overall, data validation is a great way to ensure consistent and accurate data input.
Avoiding these common mistakes can ensure that you have a clean and accurate Excel sheet. Following these practices can help avoid costly mistakes in the long run, especially when your workbooks need to scale or be shared with colleagues. And they’re quite easy to keep in mind.
