<img height="1" width="1" src="https://www.facebook.com/tr?id=222920331449599&amp;ev=PageView &amp;noscript=1">

Never make these 3 common Excel errors again

written by Priscila BernardesSep 20, 2016 9:30:00 AM

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. 

New Call-to-action

Picture of Priscila Bernardes

About Priscila Bernardes

Passionate about relationship building, Priscila leads Lancom’s customer experience and growth initiatives. With an Executive MBA and a decade of IT experience, Priscila loves challenging the status quo and finding innovative ways to service our clients, while sharing what she is learning with the community.