7 Excel Error Messages You're Sick of Seeing (And How to Fix Them)

Here's the truth: Excel struggles are real.

As marketers, it's likely that you know these tiny conflicts all too well. From accidental deletes to misplaced numbers, one click can throw your spreadsheet all out of whack. And putting it back together once you've done the damage can be seriously frustrating -- not to mention, time-consuming.  

But you're not alone: Even the most advanced Excel users experience these errors from time to time. So for that reason, we've put together some tips designed to save you a few minutes (or hours) when solving frustrating Excel errors. Check 'em out below. 

Best Practices That'll Help You Reduce Excel Errors

Before we go over the errors and tips, we wanted to share a few helpful tricks we've learned from our own Excel experts. These precautionary lessons are designed to help you avoid errors all together, making life a whole lot easier:

• Begin every formula with an equal sign.
• Use the * symbol to multiply numbers, not an X.
• Match all opening and closing parentheses so that they are in pairs.
• Use quotation marks around text in formulas.

7 Excel Error Messages You're Sick of Seeing (And How to Fix Them)

We've all fallen victim to the little green arrow in upper-left hand corner of a cell before. You know, that pesky little thing that Excel uses to indicate something has gone wrong?

In many cases, clicking on that arrow will pull up enough information for you to remedy the problem at hand. Take a look:

But in other cases, you still can't seem to figure out what's gone wrong. For those instances, we've provided the following list of common errors, explanations, and tips for overcoming them. Dig in for the context you need to right your wrongs and get back on the Excel saddle.

1) #VALUE!

Excel displays the #VALUE! error when it finds spaces, characters, or text in a formula where it is expecting a number.

Excel requires formulas to contain numbers only and won't respond to formulas associated with numbers, so it will show you an error is if you've included anything else.

How to Resolve This Error:

An easy solution to this error is to double check your formula to make sure that you only used numbers. If you're still seeing an error, check for blank cells, missing formulas linking to cells or any special characters you may be using.

In the example above, the "Sum" column is referring to empty cells. Excel can't calculate the sum of empty columns, so it gives us an error.

2) #####

When you see ##### displayed in your cell, it can look a little scary. The good news is that this simply means the column isn't wide enough to display the value you've inputted. And that's any easy fix!

How to Resolve This Error:

Click on the right border of the column header and increase the column width.

Pro Tip: You can double-click the right border of the header to automatically fit the widest cell in that column. 

3) #DIV/0!

When you see #DIV/0!, you are asking Excel to divide a formula by zero or an empty cell. In the same way that this task wouldn't work if you were doing division by hand, or on a calculator, it won't work in Excel either.

How to Resolve This Error:

This error is pretty easy to resolve. Simply change the value of the cell to a value that is not equal to 0 or add in a value if your cell was blank. Here's an example:

↓

In some cases, you might find that you're simply waiting for input in a particular cell. Rather than including a "0" as a placeholder, and subsequently turning up a #DIV/0! error, you can add a custom display message. Learn more about your alternative options here. 

4) #REF!

This one can sometimes be a little tricky to figure out, however Excel usually displays #REF! when a formula references a cell that is not valid.

What does that really mean?

That means that you may have accidentally deleted or pasted over a cell that was used in your formula. For example, let's say that the "Outcome" column references the formula: =SUM(A2,B2,C2).  

If we were to accidentally delete the "Number 2" column, we'd see this error:

How to Resolve This Error:

Before you paste over a set of cells make sure that there are no formulas that will be affected. Also, when deleting cells it’s important to double check what formulas are being referred in those cells.

Pro Tip: If you accidentally delete a few cells, you can click the Undo button on the Quick Access Toolbar (or press CTRL+Z for PC / Command + Z for Mac ) to restore them.

5) #NULL!

#NULL! errors occur when you specify an intersection of two areas that don't actually intersect, or when an incorrect range operator is used.

To give you some additional context, here's how Excel reference operators work:

• Range operator (semi colon): Defines a references to a range of cells.
• Union operator (comma): Combines two references into a single reference.
• Intersection operator (space): Returns a reference to the intersection of two ranges.

 ↓

How to Resolve This Error:

First things first, check to make sure that you are using the correct syntax in your formula.

• You should be using a colon to separate the first cell from the last cell when you refer to a continuous range of cells in a formula.
• On the other hand, you should be using a comma should when you refer to two cells that don't intersect. 

6) #N/A

When you see #N/A, this typically means that the numbers you are referring to in your formula cannot be found.

You may have accidentally deleted a number or row that's being used in your formula, or are referring to a sheet that was deleted or not saved.

For advanced users, one of the most common causes of the #N/A error is when a cell can't be found from a formula referenced in a VLOOKUP. (Check out this post for more on VLOOKUPs.)

How to Resolve This Error:

Triple check all your formulas and be sure to look closely at which sheets or rows may have been deleted or incorrectly referenced. If you have a few formulas linked together, check to see that everything in every formula has a value.

For advanced users using VLOOKUP functions, reference this guide.

7) #NUM!

If your formula contains numeric values that aren't valid, you'll see an #NUM! error appear in Excel. Often times this happens when you enter a numeric value that's different than the other arguments used in your formula.

For example, when you're entering an Excel formula, make sure you don't include values like $1,000 in currency format. Instead, enter 1000 and then format the cell with currency and commas after the formula is calculated.

How to Resolve This Error:

Check to see if you have entered any formatted currency, dates, or special symbols. Then, make sure to remove those characters from the formula, only keeping the numbers themselves. 

Here's how you can format numbers after removing the commas and currency from your formula:

(To adjust the currency, select the small triangle to the right of the icon to select an option from the dropdown menu.)

What pesky errors did we miss? Let us know in the comments section below.

via HubSpot Marketing Blog http://ift.tt/2aYiLAP