Troubleshooting issues in Excel can be easier than you might think, if you know how to interpret error messages. Formula errors in Excel almost always start with a hashtag, followed by the error code. We’ve prepared this guide to help you understand the way errors are handled in Excel and suggest some workarounds or solutions as to how you can get rid of them. Press the button below to download our sample workbook that contains all errors we’re going to cover. Let’s take a look at 8 most common formula errors together.
1. ##### Error
The most common cause is that the column is not wide enough to display the cell content, especially if the content is a large number or a long piece of text. This occurs more often when working with dates, as full date and time text usually won’t fit into the regular size cells. To resolve this, you can adjust the column width. Simply move your cursor to the right edge of the column header until it turns into a double-sided arrow, then click and drag to resize the column. Alternatively, you can double-click the right edge of the column header to auto-fit the column to the content's width.
Excel displays ##### error when a formula results in a negative date or time value, which Excel cannot display. Check the formula to ensure it's producing a valid, positive date or time value. If the negative result is unexpected, you may need to adjust the formula logic to prevent negative results.
Using a custom number format that's too long for the cell can also result in ##### error. Either increase the column width or adjust the custom number format to a shorter one.
If you're printing an Excel sheet and see ##### in a cell, it might be because the cell is merged and the spreadsheet doesn't have enough space to display the content. Consider adjusting the page layout, scaling options, or unmerging cells and using a different method to format your data for printing.
At high zoom levels, particularly in combination with wider cells, Excel might not display the content correctly, showing ##### error instead. Reducing the zoom level can sometimes resolve this display issue.
2. #VALUE! Error
The #VALUE! error in Excel usually means a problem with the way your formula is set up, specifically indicating that there's a type mismatch or an issue with the formula arguments. Most common case of #VALUE! error is when a formula expects a number, but one of the arguments is text that cannot be automatically converted to a number by Excel. Make sure all inputs expected as numbers are indeed numbers or convertible to numbers. You can use the VALUE function to convert text that represents a number to an actual numeric value. For example, VALUE("123") will return 123 as a number.
Using a non-mathematical operator in a mathematical operation will also cause #VALUE! errors. For example, concatenating text with & and then trying to add a number to it. Check the formula for any operators that might be used incorrectly and correct them. Ensure mathematical operations are only performed on numeric values or expressions.
If a formula refers to another cell that has #VALUE! or another error, it can propagate the error. Check referenced cells for errors and correct them. The error may be resolved once all referenced cells return valid results.
Another common cause of #VALUE! error is when performing operations on date or time values that are formatted as text, not as actual date or time serial numbers that Excel can recognize. Convert text representations of dates and times to actual date or time values using functions like DATEVALUE or TIMEVALUE.
3. #DIV/0! Error
Math can’t calculate division by 0, and neither can Excel. #DIV/0 error in Excel occurs when a formula attempts to divide a number by zero or an empty cell.
Begin by double checking your formula and make sure all parameters are entered correctly. If, for some reason, zero values and the subsequent errors are inevitable, you can use the IFERROR function to bypass this error.
Another common cause of the #DIV/0! error arises when the formula attempts to divide by a cell that is empty. Since Excel treats empty cells as zero when used in numerical operations, this inadvertently leads to a division by zero. Employing functions like IF to check for empty cells or IFERROR to provide a fallback value can effectively prevent this error, ensuring that division operations are only performed when valid data is present.
The error can also manifest when the denominator is derived from another formula that, under certain conditions, returns zero. This indirect route to a division by zero can be more challenging to detect, as it requires a thorough understanding of how the values in the denominator are computed. Implementing error-checking mechanisms such as IFERROR in the formula that generates the denominator can help manage these instances by providing alternative outcomes or default values in case of a zero result.
4. #REF! Error
The #REF! error in Excel is a telltale sign of a reference issue within formulas, indicating that a cell reference is no longer valid. One primary cause of this error is the deletion of cells, column, rows or an entire worksheet that a formula depends on.
It's important to check formulas before deleting cells they might reference, or to adjust the formulas to remove or update these references prior to deletion. Otherwise, the only viable workaround is to bring back the original reference.
Another scenario leading to the #REF! error occurs when cutting and pasting cells. Moving a cell that's referenced by a formula can break the link between them, especially if the move is to a different worksheet or workbook. Instead of cutting, copying and pasting, followed by clearing the original cell if necessary, can preserve the integrity of cell references and avoid the error.
Linking to data in another workbook can also cause the #REF! error if the external workbook is moved, renamed, or deleted. Excel cannot automatically update these external references, leading to broken links. Ensuring that all linked workbooks remain in their original locations and maintaining consistent filenames can help prevent such reference issues.
Addressing the #REF! error typically involves scrutinizing the affected formulas to identify and correct or remove the invalid references. This process may require tracing precedents to see which cells the formula is attempting to reference and making necessary adjustments to ensure all references are valid and intact.
5. #N/A! Error
You will see the #N/A! error when a formula can’t find the reference value. A common trigger for this error is when a lookup function, such as VLOOKUP, HLOOKUP, XLOOKUP, or MATCH, fails to find the specified value in the source data. This might occur if the lookup value does not exist in the range or if there's a mismatch in data types between the lookup value and the source data.
To find the root cause, double check both your formula, and the reference range you are working in. Ensure the lookup value exists in the source range and that both the lookup value and the source data share the same format, whether it be text, number, or date.
Another cause of the #N/A! error is an incorrect range specification in a lookup function, where the range does not include the lookup value. This often happens when the range is manually defined and does not extend to include all necessary data. Expanding the lookup range to encompass all relevant data or dynamically defining the range to automatically adjust to data changes can help eliminate this error.
The #N/A! error can also appear in array formulas that return an array of values, particularly when the formula is expecting a single value but receives multiple or no matches. In such cases, refining the criteria used in the array formula or employing aggregate functions like INDEX and MATCH together can provide more control over the returned value, ensuring that the formula retrieves a single, specific result.
In scenarios involving data consolidation or comparison, the #N/A! error may surface due to discrepancies in the data sets being analyzed, such as missing or extra entries. Employing data cleaning techniques, such as removing duplicates, ensuring consistency in data entry, and using functions like IFNA to provide alternative results for non-matching entries, can effectively address this issue.
6. #NAME? Error
The #NAME? error occurs when Excel can’t recognize a formula, a name, or a reference. This error typically occurs because of a typo or a missing named range. For example, entering IFEROR instead of IFERROR will display this message.
Carefully checking the spelling and syntax of function names can quickly resolve this issue.
Another scenario that leads to the #NAME? error occurs when referring to named ranges that do not exist. Excel allows users to define specific names for cell ranges to simplify formula creation, but if a formula refers to a named range that hasn't been defined or is misspelled, Excel will return the #NAME? error. Ensuring that all named ranges are correctly defined and accurately referenced in formulas is essential for avoiding this error.
External links in formulas that reference other workbooks or data sources can also be a source of the #NAME? error if the external source is unavailable or the reference is broken. This can occur when the external workbook is closed, moved, or renamed, breaking the link established in the formula. To fix this, verify that all external links are correct and that the referenced workbooks or data sources are accessible.
7. #NUM! Error
Excel will give the #NUM! error if it encounters an invalid number within a formula. One common cause of this error is when a formula or function, like SQRT, receives a negative number when it expects a positive one. For instance, attempting to calculate the square root of a negative number will result in a #NUM! error because square roots of negative numbers are not defined within the realm of real numbers. Ensuring that input values are within the valid range for the specific function being used can mitigate this issue.
The #NUM! error can also occur in scenarios involving large or small numbers that exceed Excel's calculation limits. Excel has a finite precision, and attempting to calculate with values beyond this precision can lead to errors. Excel supports numbers from -1*10^308 to 1*10^308. For example, extremely large numbers resulting from exponential calculations might cause Excel to return a #NUM! error. Reviewing the formula to avoid such extreme calculations or using alternative mathematical approaches can help prevent this error.
Another scenario leading to the #NUM! error involves functions that require iterative calculations, such as those related to financial analyses (IRR, RATE), reaching their maximum iteration limit without converging to a result. This typically happens when the function cannot find a numerical solution within the specified number of iterations or the calculation's parameters lead to an unstable or unsolvable situation. Adjusting the initial guess parameter, if available, or revising the underlying data to ensure it falls within a solvable range can often resolve the problem.
8. #NULL! Error
You will get the #NULL! error when two or more ranges are separated incorrectly or when trying to intersect ranges that actually can’t intersect each other. Typos, like missing a semi colon or an operator, can also causes this error. Here are some examples,
A common cause for the #NULL! error can arise from inadvertently using a space instead of a different operator like a comma (for union) or a colon (for range). In formulas, a space is interpreted as an attempt to find the intersection, so when it's used incorrectly, Excel cannot execute the formula as intended.
The #NULL! error in Excel can also be triggered by omitting a colon (:) between cell references in a formula, which is essential for defining a continuous range of cells. In Excel, a colon is used to specify a range that encompasses all cells between and including the references on either side of the colon. For instance, the notation B2:D2 denotes all cells from B2 through D2. If you mistakenly leave out the colon, as in B2 D2, and use this within a formula expecting a continuous range, Excel interprets the space as an attempt to find the intersection between two separate references (B2 and D2). Since B2 and D2 as standalone references do not form an intersecting range, Excel is unable to process the formula as intended and returns a #NULL! error.
A missing comma (,) between cell references or ranges within certain functions can inadvertently lead to a #NULL! error, particularly in functions that can accept multiple ranges or arguments. The comma serves as an argument separator in Excel, clearly delineating distinct parameters, ranges, or values that a function is meant to process. When a comma intended to separate arguments or ranges is omitted, Excel might interpret the adjacent references as an attempt to define an intersection. For instance, if you write SUM(B3 C3,D3) without a comma, Excel tries to find the intersection between B3 and C3. If these ranges do not intersect — which is often the case — Excel cannot execute the function as intended and returns a #NULL! error, indicating the absence of a valid intersection.
This error is also encountered in formulas that use a space character to denote the intersection operator between two ranges. For instance, if you write a formula like =SUM(B4:C4 B6:C6), intending to sum the intersection of these two ranges, Excel will return a #NULL! error because there is no overlap between the ranges B4:C4 and B6:C6. To correct this, you would need to ensure that the ranges you're trying to intersect actually share common cells or revise the formula to use a different method of referencing the desired cells.
First, check your workbook for typos, like missing spaces or missing characters. Also make sure that all reference ranges have intersecting values.
