Avoid #REF! in Excel by knowing your Dependents and Precedents
Have you ever opened a file in Excel and saw the dreaded #REF! where a formula result should be. I usually see people fixing this formula by removing the section with the error, or guessing what range should be entered instead of the #REF!. This may work a lot of the time, but it might be easier to avoid #REF! in the first place.
What is #REF!?
Excel helpfully tells us that the #REF! relates to an “Invalid Cell Reference”.
From personal experience and helping others, I see this happening most frequently when cells have been deleted, but a formula was referencing part of the deleted range.
In the first image above, the #REF! was caused because the formula was adding cells B4, E5, B11 and also B15.
But somebody deleted all rows below row 11, which includes the cell B15. This caused my error because the formula is trying to include a deleted cell in its addition.
How to avoid #REF!
Before I delete any rows, columns, cells or sheets; I quickly check if there are any formulas that may be impacted by the deletion.
To do this, I check for dependents.
I will also briefly talk about precedents too in this post.
Dependents & Precedents
Depending on your version of Excel, you may see a “Formula Auditing” section of the Formula ribbon/menu.
We are going to deal with the 3 options on the left of this section.
Trace Precedents
In my example, my main total (with yellow fill) is the sum of all 4 sub-totals.
If I click on the cell with this formula (E10) and then click “Trace Precedents” then arrows from this cell go to all the cells that are referenced in this formula.
These are precedents.
Trace Dependents
Where tracing precedents shows you what cells precede (or are included in the formula of) the current cell, tracing dependents shows you if the current cell is referenced, or included in a formula in a different cell.
Below I have selected cell B7 and clicked “Trace Dependents” and an arrow goes from this cell to Sub-Total 3
Remove Arrows
As you trace dependents and precedents, it is also important to remember to click “Remove Arrows” or you end up with a build up of arrows on your sheet.
Precedents & Dependents On Another Sheet
So far we have just looked at precedents and dependents on the same sheet.
What happens if the precedents or dependents are on a different sheet?
You would still trace the dependent/precedent as before, but this time if the cell relates to another sheet you will see a black dashed arrow.
Clicking on this arrow will bring up a “Go To” dialog box.
You can see the sheets and cells being traced and you can click on any of them and “OK” to go to them.
Avoid #REF!
If I see that a cell/range/row/sheet that I am about to delete has any dependents, then I can review and amend the formula if needed before getting the #REF! and not knowing what caused it.
Do I review every cell that I am about to delete…. no is the short answer to that.
If I am about to delete a sheet or column or row, the number of cells to check for dependents can be huge!
Instead I usually check dependents for a sub-total or similar field that may be referenced in a summary somewhere else in the file for example. I do not check every one!!
This isn’t a fool-proof way to prevent 100% of #REF! errors, but it is a good start. I have not got any since I started adding a quick (and I keep it quick) check before deleting cells/ranges/etc.
If you don’t get these errors then you probably don’t see the point of this post, but thank you for reading this far anyway 🙂
If you do get these errors and find them as annoying as I do, then I hope this post can help you to avoid or at least reduce their occurrence in future.
