Everybody loves Excel because it is a powerful, versatile and capable tool which can deliver the goods for simple tasks while being just as adept at sorting out deeply complex data processing requirements. But there always has to be a but – and in this case, it’s those annoying errors which seem to creep in to almost everyone’s Excel work.
This week, we’ve picked out the most common ones we’ve made, with easy solutions to help you avoid them in future.
1. #REF!
Yes, you’ve seen that before, probably routinely. #REF! is Excel’s way of telling you that a formula is trying to reference an invalid cell. In other words, somewhere along the way, you’ve deleted or overwritten information in a cell that your formula depends on. No cell, no formula.
Have a look at this example: The "Outcome" column references the formula: =SUM(A2,B2,C2)
If the "Number 2" column is deleted, Excel can’t do the maths, so this error appears:
Here's how to avoid this error:
Before pasting over a set of cells, ensure there are no formulas that will be affected. Ahead of deleting cells, check if any formulas are referred in them. If formulas depend on them…don’t delete.
Remember the quick way of going back should you make a mistake: CTRL+Z for PC / Command + Z on a Mac .
2. #N/A
When the numbers for the formula can’t be accessed, Excel spits up #N/A. Again, this is because something which was there, no longer is. You may have deleted a number or row, or even an entire sheet. Excel pros can run into this error when a cell can't be found from a formula referenced in a VLOOKUP
Here's how to avoid this error:
Go over the formulae carefully and examine which sheets or rows may have been deleted or incorrectly referenced. When several formulae are linked together, make sure everything in each formula has an assigned value.
Power user but stuck on VLOOKUP? Check out this guide.
3. #####
No, that isn’t Excel getting sweary with you. ##### is just ever-polite Excel letting you know that the values in the column don’t have enough room to be displayed. It’s probably one of the most commonly encountered problems – and also the easiest to fix.
Here's how to avoid this error:
Just grab the right border of the column header with the mouse pointer and drag to increase the column width.
Double click the right of the header to automatically fit the widest cell in that column.
