It’s frustrating to feel like you’ve done everything right, only to get a bad result. When getting started with Excel, this often looks like error messages causing frustration, or sometimes takes the form of someone pointing out an easy opportunity to improve something that you didn’t realize was flawed.
This post looks at some beginner mistakes that are both easy to make, and easy to avoid.
It’s almost impossible to run into this problem when you write a single formula that will be used in only one cell. But the minute you start copying and pasting formulas across multiple cells, this issue will pop up.
The most common occurrence of this issue happens with the second parameter of a VLOOKUP formula, although it is also an issue when creating dashboard style reports.
An error like the one below is often caused by relative references:
The third param in a vlookup is a common spot for this error to occur:
Replacing the relative reference with an absolute reference (or named range) should solve the problem (note the placement of the dollar signs below):
Copy vs. Cut – Implications for Formulas
Another easy way to create problems with the cell references that you used in formulas has to do with the cells that you’re referencing instead of how you’re referencing them.
Excel will help you out when you cut and paste (or insert) cells from one location to another. It will update your formulas so that they “follow” the cells that you referenced. This prevents you from having to update formulas anytime you reorganize a worksheet.
This Excel feature works when you cut a cell from its original location, but not when you copy them, and certainly not when you delete them. The formula bar offers an easy way to verify whether a cell is being referenced by other cells. Check out the Trace Dependents button under the Formula Auditing tab of the Formulas Ribbon, or find more details here.
Using the example above still, we’re able to create #REF! errors by adding a new column to our report:
Some errors are inevitable. This is especially true in reporting, where we will want to have formulas in place to calculate metrics that might not exist because of #DIV/0! issues. Luckily Excel offers a simple solution to this potential issue. The IFERROR formula allows you to clean up those error messages that would otherwise give you report an unsightly appearance.
The IFERROR formula is a fairly simple two-parameter formula. Essentially, the first parameter of your formula is “plan A” and is typically a calculation or formula that could potentially return an error message (like a metric that could potentially resolve as DIV/0!). Excel will display the result of this calculation or formula as long as it doesn’t resolve as an error. In the event that it does resolve as an error, Excel will display the result of your second parameter (which could be anything).
Typical implementation for the second parameter can be things like a custom value to display instead of an error (like a zero, or a null text string), or in some instances an alternative formula. Keep in mind that Excel will still display an error in the event that the first and second parameters of the formula resolve as errors. It will simply protect you from having the error incurred by the first parameter from being displayed.
The same table with and without =IFERROR() in the CPA column:
Behind the scenes:
Pivot Table Mistakes
For an inexperienced user, Pivot Tables can create problems just as efficiently as they can solve them. The most common mistakes involve using the right data for your table and fields. As explained in the link below, if you take a few steps to ensure that your table is using the right data, and avoid summating (or averaging) calculated metrics, then you should be just fine.
You can find detailed instructions on avoiding these mistakes here.