Excel is a wonderful toolbox to compute, analyse, compare and crunch data but it can also lead to some unexpected errors. In this post, we will review the 6 most common errors that can happen in Excel and how to resolve them.
Understanding the # VALUE! error
What # Value! error means
This error occurs when a formula or function contains an invalid data type or argument.
This can happen for a few different reasons:
Incorrect data type: If you're trying to perform a calculation with a text value, for example, you may get a # VALUE! error because Excel can't perform calculations with text.
Invalid argument: Some functions require specific arguments, and if you provide an argument that doesn't meet the criteria, you may get a VALUE! error. For example, the VLOOKUP function requires that the lookup value be in the leftmost column of the lookup range.
A cell contains an error: If a cell that is referenced in a formula contains an error, such as DIV/0!, N/A, or REF!, the formula may return a # VALUE! error as well.
How to fix the # Value! error
To resolve a # VALUE! error, you should check the formula to ensure that it's using the correct data types and arguments, and that it's referencing cells that don't contain errors. You can also use the Evaluate Formula tool in Excel to step through the formula and identify any errors.
In some cases, you may need to modify the formula or use a different function to get the desired result.
Understanding the # REF! error
What # REF! error means in Excel
This error can happen when a formula or function contains a reference to a cell or range that no longer exists or has been deleted. Here are some examples of # REF! errors:
Deleting cells or rows: If you delete cells or rows that are referenced in a formula, you may get a # REF! error.
Moving cells or rows: If you move cells or rows that are referenced in a formula, you may get a # REF! error if the reference is no longer valid.
Renaming worksheets: If you rename a worksheet that is referenced in a formula, you may get a # REF! error.
How to fix the # REF! error in Excel
To resolve a # REF! error, you should first check the formula to ensure that it's referencing the correct cells and ranges. If you've deleted or moved cells or rows, you may need to update the formula to reflect the new cell references.
If you're unsure where the # REF! error is coming from, you can use the Trace Error tool in Excel to identify the source of the error. You can also use the Go To Special tool to find all cells in the worksheet that contain errors, including # REF! errors.
It’s important to note that # REF! errors can sometimes be caused by circular references, which occur when a formula refers to the cell that it's located in. Circular references can cause other issues in your spreadsheet as well, so it's important to avoid them when possible.
Understanding the # DIV/0! error
What # DIV/0! error means in Excel
This error occurs when you try to divide a number by zero or by a blank cell. This can happen when occurs when you try to divide a number by zero or by a blank cell. This can happen in a few different ways:
Dividing by zero: If you have a formula that divides a number by zero, Excel will return a # DIV/0! error. For example, if you enter "=5/0" into a cell, Excel will return a # DIV/0! error.
Dividing by a blank cell: If you have a formula that divides a number by a blank cell, Excel will also return a # DIV/0! error. For example, if you have a formula that divides a total by the number of items sold, and there haven't been any items sold yet, the divisor cell may be blank and cause a # DIV/0! error.
How to fix the # DIV/0! error in Excel
To resolve a # DIV/0! error, you should first check the formula to ensure that the divisor is not zero or blank. If possible, you should also try to avoid dividing by variables that may be zero or blank in the future.
For example, if you're dividing a total by the number of items sold, you may want to add an IF statement to the formula to check if there are any items sold before performing the division.
You can also use the IFERROR function in Excel to replace # DIV/0! errors with a specific message or value. For example, you could enter "=IFERROR(5/0, "Error: Division by zero")" into a cell, and Excel will return the message "Error: Division by zero" instead of the # DIV/0! error.
It's important to note that while the IFERROR function can help to hide # DIV/0! errors in your spreadsheet, it doesn't actually fix the underlying issue in the formula. You should always try to resolve the cause of the error instead of simply hiding it.
Understanding the # N/A error
What # N/A error means in Excel
The # N/A error is another common error in Excel, and it occurs when a formula or function is unable to find a value that it's looking for. This can happen for these reasons:
Lookup value not found: If you're using a lookup function like VLOOKUP or HLOOKUP, and the lookup value is not found in the lookup range, Excel will return a # N/A error.
Data not available: If you're using a function that requires data that is not available, such as a financial function that requires historical stock prices, Excel will return a # N/A error.
Formula returns no value: If a formula doesn't return a value for any other reason, such as an error in the formula, Excel will also return a # N/A error.
How to fix the # N/A error in Excel
To resolve a # N/A error, you should first check the formula to ensure that it's referencing the correct cells and ranges. If you're using a lookup function, you should also ensure that the lookup value is available in the lookup range.
If you're unsure where the # N/A error is coming from, you can use the Trace Error tool in Excel to identify the source of the error. You can also use the Go To Special tool to find all cells in the worksheet that contain errors, including # N/A errors.
It's important to note that # N/A errors can sometimes be caused by case-sensitive lookups. For example, if you're using a VLOOKUP function and the lookup value is not in the lookup range due to differences in capitalization, Excel will return a # N/A error. To resolve this, you can either ensure that the lookup value is in the correct case, or use a case-insensitive lookup function like INDEX/MATCH.
Here is a post that will refresh your memory on how combining the functions INDEX and MATCH.
Understanding the # NUM! error
What # NUM! error means in Excel
The # NUM! error is another common error in Excel, and it occurs when a formula or function returns a value that is not a valid numeric value. This can happen in the following cases:
Invalid arguments: If a function is given invalid arguments, such as a negative number where a positive number is expected, Excel will return a # NUM! error.
Overflow or underflow: If a formula or function returns a value that is too large or too small to be displayed, Excel will return a # NUM! error. This can happen with functions like EXP, LOG, and POWER.
Circular references: If a formula references itself or creates a circular reference with another cell, Excel will return a # NUM! error and good luck with that!
How to fix the # NUM! error in Excel
To resolve a # NUM! error, you should first check the formula to ensure that it's referencing the correct cells and ranges, and that the function is being used correctly. If you're using a function that is returning a very large or very small value, you may need to adjust the arguments to the function to avoid the overflow or underflow error.
If you're getting a # NUM! error due to a circular reference, you can try to break the circular reference by changing the formula or adjusting the cell references. Alternatively, you can enable iterative calculations in Excel to allow circular references.
It's important to note that # NUM! errors can also be caused by issues with precision in Excel. Excel uses a limited number of digits to represent numbers, and some calculations may exceed this limit, resulting in a # NUM! error. To avoid this, you can use the ROUND function to round values to a specific number of digits.
Understanding the # SPILL! error
What # SPILL! error means in Excel
The # SPILL! error, also known as the spill range error, is a new type of error in Excel related to the use of dynamic arrays or spill ranges that are automatically populated by a formula that returns multiple values.
The spill error occurs when a formula spills over into a cell that is already occupied by another formula or data. This can cause unexpected results or errors in your spreadsheet.
If you are not aware of the dynamic arrays or spill ranges, no worries! Here comes a small recap on this new feature in Excel.
Dynamic arrays allow formulas to automatically spill into adjacent cells, creating a dynamic range of values. With dynamic arrays, you can write a single formula that can return multiple values, or even create entire tables of data with just one formula.
For example, if you have a list of numbers in column A, and you want to sort the values in the list, you could use the following formula in cell C1: =SORT(A2:A10,1,-1)
When you enter this formula, Excel will automatically spill the top 3 values into cells C1:C3.
Dynamic arrays are a powerful tool for working with large datasets and performing complex calculations in Excel, as they allow you to write more concise and efficient formulas that can generate results for multiple cells or entire tables of data with just one formula.
How to fix the # SPILL! error in Excel
To resolve the spill error, you can either adjust the size of the spill range, modify the formula to return a smaller set of results, or use the spill error handling options available in Excel to manage the overflow of data.
It is important to note that the spill error is a relatively new type of error that may not be supported in older versions of Excel. If you encounter a spill error, you may need to upgrade to a newer version of Excel or modify your formulas to work with your current version of Excel.
If you are an heavy user of Excel, you will most certainly like the Power-user add-in. Get dozens of new advanced features that will make you the boss of Excel!