Good day, friends!
In this article we will talk about what types of errors in formulasExcel that we encounter when working with electronic Excel tables. I am more than sure that everyone has seen mistakes, but less know how to get rid of them correctly. Still, this knowledge is important, as it will insure you against typical mistakes or will help you quickly and without panic get rid of or correct received errors in formulasExcel.
We can talk a lot about errors in Excel, but let's look at the most common ones, I'll tell you why and how they happen, as well as how to fix them errors in formulasExcel, For correct display data.
Well, here’s what it actually is like errors in formulasExcel:
- Error #####. This is one of the most common and simplest errors in Excel formulas. It only means one thing: the column width is not wide enough to fully display your data. The solution to this problem is very simple, move the mouse cursor to the border of the column, and while holding down the left button, increase the cell until the data begins to be displayed or double click on the column border will allow for the widest cell in the column.
- Error #NAME?. This error (#NAME?) occurs in Excel formulas only when the editor cannot recognize the text in the formula (for example, an error in the name of the function due to a typo =SUM(A1:A4). To correct this errors in formulasExcel, you need to read it carefully and correct the error (A1:A4).
- Error #VALUE!. This error (#VALUE!) You may have this problem when the formula contains an argument whose type is not suitable for your calculations. For example, your mathematical formula contains the text value =A1+B1+C1, where C1 is text. The solution to the problem is simple, use a formula that ignores cells that contain text or just remove given value from cell C1.
- Error #BUSINESS/0. As you can see from the error that appeared in the formula, you simply multiplied your argument by the number 0, and this cannot be done based on mathematical rules. To correct this error, you must change the number so that it does not equal 0 or change the formula, for example, logical, which will avoid the error. =IF(A2=0;””;A1/A2)
- Error #LINK!. This is one of the most common and confusing mistakes in Excel functions. When you see this error, it means that the formula is referencing a cell that no longer exists. This is especially problematic when you work with large amounts of data and a large number of . When you edit your tables like this errors in formulasExcel They shouldn’t scare you, they are very easy to fix, you just need to have everything return to its place, or, if necessary, manually rewrite the formula, eliminating the erroneous argument from it.
I hope this article is about what they are errors in formulasExcel and correcting them, became useful to you, and you learned something new and interesting for yourself.
See you in new articles!
“Why is the world so arranged that people who know how to live for pleasure never have money, and those who have money have no idea what it means to “waste their life”?
D.B. Show
Making even a small change in an Excel worksheet can create errors in other cells. For example, you might accidentally enter a value in a cell that previously contained a formula. This simple mistake can have a significant impact on other formulas, and you won't be able to detect it until you make some changes to the worksheet.
Errors in formulas fall into several categories:
Syntax errors: Occurs when the formula syntax is incorrect. For example, a formula has incorrect parentheses, or a function has the wrong number of arguments.
Logical errors: In this case, the formula does not return an error, but has a logical flaw, which causes the calculation to be incorrect.
Invalid link errors: The formula logic is correct, but the formula uses an incorrect cell reference. As a simple example, the range of data to sum in the SUM formula may not contain all the items you want to sum.
Semantic errors: For example, the name of the function is misspelled, in which case Excel will return the error #NAME?
Errors in array formulas: When you enter an array formula, you must press Ctrl + Sift + Enter when you finish typing. If you don't do this, Excel won't realize it's an array formula and will return an error or incorrect result.
Errors in incomplete calculations: In this case, the formulas are not fully calculated. To make sure that all formulas are recalculated, type Ctrl + Alt + Shift + F9.
The easiest way is to find and correct syntax errors. More often than not, you know when a formula contains a syntax error. For example, Excel will not allow you to enter a formula with inconsistent parentheses. Other situations of syntax errors lead to the conclusion following errors in a sheet cell.
Error #DIV/0!
If you create a formula that divides by zero, Excel will return the error #DIV/0!
Since Excel treats an empty cell as zero, dividing by an empty cell will also return an error. This problem often occurs when creating a formula for data that has not yet been entered. The formula in cell D4 has been stretched across the entire range (=C4/B4).
This formula returns the ratio of the values of columns C to B. Since not all data for days was entered, the formula returned the error #DIV/0!
To avoid the error, you can use , to check whether the cells of column B are empty or not:
IF(B4=0;"";C4/B4)
This formula will return a blank value if cell B4 is empty or contains 0, otherwise you will see the counted value.
Another approach is to use the ISERROR function, which checks for an error. The following formula will return an empty string if the expression C4/B4 returns an error:
IFERROR(C4/B4;"")
Error #N/A
The #N/A error occurs when the cell referenced by the formula contains #N/A.
Typically, the #N/A error is returned as a result of running . In the case where no match was found.
To catch the error and display an empty cell, use the =ESND() function.
ESND(VLOOKUP(A1,B1:D30,3,0);"")
Please note that the ESND function is new feature in Excel 2013. For compatibility with previous versions use an analogue of this function:
IF(END(VLOOKUP(A1,B1:D30,3,0));"";VLOOKUP(A1,B1:D30,3,0))
Error #NAME?
Excel may return the error #NAME? in the following cases:
- Formula contains an undefined named range
- The formula contains text that Excel interprets as an undefined named range. For example, a misspelled function name will return the error #NAME?
- The formula contains text not enclosed in quotation marks
- The formula contains a reference to a range that does not have a colon between the cell addresses
- The formula uses a worksheet function that was defined by an add-in, but the add-in was not installed
Error #EMPTY!
Error #EMPTY! occurs when a formula tries to use the intersection of two ranges that do not actually intersect. The intersection operator in Excel is space. The following formula will return #EMPTY! because the ranges do not overlap.
Error #NUMBER!
Error #NUMBER! will be refunded in the following cases:
- A non-numeric value was entered into a formula's numeric argument (for example, $1,000 instead of 1000)
- An invalid argument was entered into the formula (for example, =ROOT(-12))
- A function that uses iteration cannot calculate the result. Examples of functions using iteration: VSD(), BET()
- The formula returns a value that is too large or too small. Excel supports values between -1E-307 and 1E-307.
Error #LINK!
- You deleted a column or row that was referenced by a formula cell. For example, the following formula will return an error if the first row or columns A or B were deleted:
- You deleted the worksheet that was referenced by a formula cell. For example, the following formula will return an error if Sheet1 was removed:
- You copied the formula to a location where the relative reference becomes invalid. For example, if you copy a formula from cell A2 to cell A1, the formula will return a #REF! error because it is trying to reference a cell that does not exist.
- You cut the cell and then paste it into the cell referenced by the formula. In this case, the error #LINK! will be returned.
Error #VALUE!
Error #VALUE! is the most common error and occurs in the following situations:
- The function argument has an incorrect data type or the formula is attempting to perform an operation using incorrect data. For example, when trying to add a numeric value to a text value, the formula will return an error
- Function argument is a range when it should be a single value
- Custom sheet functions are not calculated. To force a recalculation, press Ctrl + Alt + F9
- A custom worksheet function attempts to perform an operation that is not valid. For example, a custom function cannot change the Excel environment or make changes to other cells
- You forgot to press Ctrl + Shift + Enter when entering an array formula
» will display a special error message. Moreover, each type of error is indicated by its own message, is caused by different reasons and, accordingly, requires in various ways permissions.
##### — What does it mean and how to fix it?
These symbols indicate that the column containing numbers is not wide enough for them, or the date and time entered into the cells of this column, contain negative numbers.
In the first case, it is enough to simply increase the column width or change the numeric format of the data (for example, reduce the number of decimal places).
In the second case, you need to:
- check the formula if the number of days between two dates is calculated;
- if the formula does not contain errors, then you need to change the cell format and switch, for example, from the “Date and Time” format to the “General” or “Number” format.
#VALUE! — What does it mean and how to fix it?
These messages are about using text instead of a number or a Boolean value (TRUE or FALSE). That is, Excel is such a playboy and cannot convert the given text in a cell into the correct data type.
You must ensure that the formula or function references cells that contain valid values.
For example, if cell A2 contains a number and cell A3 contains text, then cell A1 with the formula =A2+A3 will display #VALUE! .
#DIV/0! — What does it mean and how to fix it?
These messages indicate that a cell is dividing a number by 0 (zero) or references to an empty cell are used.
- In the open worksheet window, select the cell with this error and press F2.
- When the formula or function itself is displayed in a cell, and all cells linked by links to this formula or cell are selected, carefully check the values in the selected cells and, if necessary, make adjustments to the formula or change links to empty cells.
- Press Enter or the Enter button on the formula bar.
If an empty cell is used as an operand, it is automatically considered equal to zero.
#NAME? — What does it mean and how to fix it?
These symbols indicate that the formula is using a non-existent name or an incorrect operator.
1 option
If a name is used that has not been defined, then the following must be done:
- In the open worksheet window, go to the Formulas tab and in the Defined Names group, click the Name Manager button.
- In the Name Manager window, see if the name is in the list.
If this name is missing, then you need to add it according to the instructions “”.
Option 2
If there is an error in the spelling of a name, you need to check its spelling.
- In the open worksheet window, press F3.
- In the “Insert Name” window, select the desired name from the name list and click the “OK” button.
- Make corrections (if necessary) to the formula that appears in the appropriate cell.
- To pin, press Enter.
Option 3
If a formula uses a misspelled function.
For example, SUM(A1:A10) instead of SUM(A1:A10) .
- In the open worksheet window, select the cell with the misspelled function.
- Expand the “Error Source” button menu next to this cell.
- From the list of commands, select Edit in Formula Bar.
- The correct spelling will be displayed on the formula bar in the name box. required formulas, according to which change the erroneous spelling.
- Save the result by clicking the Enter key.
Option 4
If you enter text into a formula that is not enclosed in double quotes, you must check all text entries in the formula and enclose them in double quotes. Otherwise, Excel will try to recognize the given text as the name of a range of cells, even though it is not intended to do so.
Option 5
If a colon is missing in a reference to a range of cells, then to correct it, you need to check the colon sign in the formula in all such references and correct it as necessary.
For example, SUM(A1 A10) instead of SUM(A1:A10) .
Option 6
#N/A - What does it mean and how to fix it?
These symbols indicate that the desired value is not available for the function or formula.
1 option
If missing data, as well as #N/A or ND() were entered into the formula, then #N/A must be replaced with new data.
The designation #N/A is entered into cells for which data is not yet available.
Option 2
If the LOOKUP, LOOKUP, MATCH, or VLOOKUP functions specify an incorrect value for the “lookup_value” argument (for example, a reference to a range of cells, which is not allowed), then you must accordingly specify a reference only to the desired cell.
Option 3
If required arguments are not given standard function sheet, then you need to enter all the necessary corresponding function arguments.
Option 4
If a formula uses a formula that is not available in at the moment function, you need to verify that the workbook using the worksheet function is open and that the function is working correctly.
Option 5
If you use the VLOOKUP, GLOOKUP, or MATCH functions to view values in an unsorted table, the default table view information should be in ascending order.
The VLOOKUP and GLOOKUP functions contain an "interval_lookup" argument that allows you to search for a specific value in an unsorted table. However, to find a specific value, the "interval_lookup" argument must be FALSE.
The MATCH function contains a match_type argument that allows you to sort the data for the search. If the corresponding value cannot be found, then it is recommended to set the “matching_type” argument to 0.
Option 6
If an array formula uses an argument that does not match the range specified in the array formula, you must check the formula's reference range to ensure it matches the number of rows and columns, or enter the array formula into fewer cells.
Option 7
If one or more required arguments to a standard or created worksheet function are not specified, you must check and set all required function arguments.
#LINK! — What does it mean and how to fix it?
1 option
If a cell referenced by a formula has been deleted, or the value of copied cells has been placed in that cell, the formula must be modified to take into account the new references.
Option 2
If you are using an OLE function that is associated with a program that is not running, you must start the required program.
The OLE (Object Linking and Embedding) interface is supported by many various programs and is used to place a document created in one program into another program. For example, you can insert Word document to an Excel workbook and vice versa.
Option 3
Option 4
If you use a macro that calls a macro function that, when certain options returns the value #LINK! . You must check the function argument to ensure that it refers to valid cells or ranges of cells.
#NUMBER! — What does it mean and how to fix it?
This message is about using incorrect numeric values in a formula or function.
1 option
If an unacceptable value has been inserted into a function that uses a numeric argument, you must check all of the function's arguments and, if necessary, correct the spelling of all numbers and the format of the corresponding cells.
Option 2
If it is impossible to find a result in a function with iteration (selection of parameters), for example “VSD” or “BET”, then you need to try a different initial approximation or change the number of iterations.
Option 3
If the result of the formula is a number that is too large or, conversely, too small to be displayed in Excel, then you need to change the formula and ensure that the result is in the range from 1*10307 to 1*10307.
#EMPTY! — What does it mean and how to fix it?
These messages indicate that there are no shared cells when redirect is specified.
cross section of two regions.
1 option
If an incorrect range operator is used, corrections must be made, namely:
- To indicate a reference to a contiguous range of cells, use a colon (:) as a separator between the starting and ending cells of the range. For example, SUM(C1:C20) .
- To indicate a reference to two disjoint ranges, the union operator is used - a semicolon (;). For example, SUM(C1:C20;D1:D20) .
Option 2
If the specified ranges do not have common cells, then you need to change the links to achieve the desired intersection.
Questions about errors in Excel are the most common, I receive them every day, thematic forums and answering services are filled with them. It's very easy to make mistakes in an Excel formula, especially when you're working quickly and with a lot of data. The result is incorrect calculations, dissatisfied managers, losses... How can you find the error that has crept into your calculations? Let's figure it out. There is no single tool or algorithm for finding errors, so we will move from simple to complex.
If a cell contains a formula instead of a result
If you wrote a formula in a cell, pressed Enter, and instead of the result, the formula itself is displayed in it, then the text format of the value for this cell is selected. How to fix it? First, study and select the one you need, but not text, calculations are not performed in this format.
Now on the ribbon, find Home - Number, and in the drop-down list select the appropriate data format. Do this for all cells where the formula has become plain text.
If the formulas on the worksheet are not recalculated
If you change the source data, but the formulas on the sheet do not want to be recalculated, you have disabled automatic recalculation of formulas.
To fix this, click on the ribbon: Formulas – Calculations – Calculation Options – Automatic. Now everything will be recalculated as usual.
Please remember that automatic recalculation may have been disabled on purpose. If on your sheet huge amount formulas - each change forces them to be recalculated. As a result, working with a document develops into a chronic state of waiting after each change. In this case, you need to switch the recalculation to manual mode: Formulas – Calculations – Calculation Options – Manual. Now make all changes to the source data, the program will wait. When all changes have been made, press F9, all formulas will update the values. Or turn on automatic recalculation again.
If the cell is filled with a hash sign
Another classic situation when, as a result of calculations, you get not a result, but a cell filled with pound signs:
In fact, this is not an error, the program tells you that the result does not fit into the cell, it just needs to be adjusted to the required size.
The calculation result is in the wrong format
Sometimes it happens that after performing calculations, the result does not appear in the form you expect. For example, we added two numbers and got a date as a result. Why is this happening? It is likely that the data format in the cell was previously set to "date". Just change it to the one you need, and everything will be your way.
When external links are not available
If an Excel formula returns the wrong result
If you have written a formula, and it returns an obviously incorrect result, let’s look at the logic of the formula. You could have made a simple mistake in the parentheses and the order of the operators. Study and check if everything is correct in yours. If correct, move on to the next step.
If you use functions, make sure you know. Each of them has its own syntax. Check whether you have set the parameters for the formula correctly by reading the help on it. Press F1 and in the Excel Help window, in the search, write your function, for example, “”. The program will display a list of available materials for this function. As a rule, they are enough to get a complete picture of how your function works. The authors of the reference present the material very clearly and provide examples of use.
Users often incorrectly specify cell references in formulas, which is why they get erroneous results. The first thing you need to do to test external formulas is to enable the display of formulas in cells. To do this, run on the ribbon Formulas – Formula Dependencies – Show Formulas. Now the cells will display formulas rather than calculation results. You can run your eyes over the sheet and check if the links are correct. To show the results again, run the same command again.
To make the process easier, you can turn on link arrows. You can easily determine which cells a formula refers to by clicking Formulas – Formula Dependencies – Influencing Cells. On the sheet, blue arrows will indicate which data you are referring to.
Similarly, you can see cells whose formulas refer to a given cell. To do this we do: Formulas – Formula Dependencies – Dependent Cells.
Please note that in complex tables, drawing arrows will take a lot of time and machine resources. To remove arrows, click Formulas – Formula Dependencies – Remove Arrows.
Typically, carefully checking formulas with the tools listed above will solve problems of erroneous results. We are looking for a problem until we win!
If a circular reference occurs in Excel
Sometimes after entering a formula, the program warns that a cyclic reference has been entered. The calculation stops. This means that the formula refers to a cell, which, in turn, refers to the cell in which you enter the formula. It turns out that there is a closed cycle of calculations, the program will have to calculate the result indefinitely. But this will not happen, you will be warned and given the opportunity to fix the problem.
Often, cells refer to each other indirectly, i.e. not directly, but through intermediate formulas.
To find such “incorrect” formulas, look on the ribbon: Formulas – Formula Dependencies – Error Checking – Cyclic Errors. This menu opens a list of cells with looped formulas. Click on any one to place the cursor in it and check the formula.
Naturally, circular references are eliminated by checking and correcting the calculation logic. However, in some cases, a circular reference will not be an error. That is, this system of formulas still needs to be calculated to a state close to equilibrium, when practically no changes occur. Some engineering problems require this. Luckily, Excel allows this. This approach is called “iterative calculations”. To turn them on, click File – Options – Formulas, and check the “Iterative calculations” checkbox. Install there:
- Limit iteration number– the maximum number of iterations (cycles) that will be carried out until a complete stop
- Relative error– the minimum change in target values during one iteration at which the recalculation will be stopped.
That is, the cyclic formula will be calculated until the relative error is achieved, but not more than the specified maximum number of iterations.
Built-in Excel errors
Sometimes during calculations there are errors starting with the “#” sign. For example, “#N/A”, “#NUMBER!”, etc. these errors are me, read this post and try to understand the reason for your error. When this happens, you can easily fix everything.
If you cannot find an error in a rather complex formula, click on the exclamation mark next to the cell and context menu select Show Calculation Steps.
A window will appear on the screen displaying at which point in the calculation the error occurs; it will be underlined in the formula. This is a sure way to understand what exactly is wrong.
These are, perhaps, all the main ways to find and correct errors in Excel. We looked at the most common problems and how to fix them. But I’ll tell you what to do when errors need to be foreseen immediately in the calculations in a post about . Do not spare 5 minutes of time for this article; reading it carefully will save a lot of time in the future.
And I look forward to your questions and comments on this post!
When calculations are incorrect, formulas display several types of errors instead of values. Let's look at them at practical examples in the process of working formulas that gave erroneous calculation results.
Excel formula errors displayed in cells
This lesson will describe the meaning of formula errors that cells may contain. Knowing the meaning of each code (for example: #VALUE!, #DIV/0!, #NUMBER!, #N/A!, #NAME!, #EMPTY!, #LINK!), you can easily figure out how to find an error in the formula and eliminate her.
How to remove #DIV/0 in Excel
As can be seen when dividing by cell with empty value The program perceives it as division by 0. As a result, it produces the value: #DIV/0! You can verify this with the help of a hint.
In other arithmetic calculations (multiplication, summation, subtraction), an empty cell is also a zero value.
The result of an erroneous calculation is #NUMBER!
Incorrect number: #NUMBER! – this is an error of inability to perform a calculation in a formula.
Some practical examples:
Error: #NUMBER! occurs when a numerical value is too large or too small. Also this error may occur when trying to get the root of a negative number. For example, =ROOT(-25).
In cell A1 - too large number(10^1000). Excel can't handle such large numbers.
Cell A2 has the same problem with large numbers. It would seem that 1000 is a small number, but returning its factorial results in a numeric value that is too large for Excel to handle.
In cell A3, the square root cannot be a negative number, and the program displayed this result with the same error.
How to remove ND in Excel
Value not available: #N/A! – means that the value is not available for the formula:
The written formula in B1: =MATCH(„Maxim”, A1:A4) searches for the text content “Maxim” in the range of cells A1:A4. Content found in second cell A2. Therefore, the function returns the result 2. The second formula searches for the text content “Andrey”, then the range A1:A4 does not contain such values. Therefore, the function returns the error #N/A (no data).
Error #NAME! in Excel
Belong to the category of errors in writing functions. Invalid name: #NAME! - means that Excel did not recognize the text written in the formula (the name of the function =SUM() is unknown to it, it is written with an error). This is the result of a syntax error when writing the function name. For example:
Error #EMPTY! in Excel
Empty set: #EMPTY! are errors of the set intersection operator. In Excel there is such a thing as the intersection of sets. It is used to quickly retrieve data from large tables by querying the intersection point of a vertical and horizontal range of cells. If the ranges do not intersect, the program displays an erroneous value - #EMPTY! The set intersection operator is the single space. It separates the vertical and horizontal ranges specified in the function arguments.
In this case, the intersection of the ranges is cell C3 and the function displays its value.
The given arguments in the function: =SUM(B4:D4 B2:B3) – do not form an intersection. Therefore, the function returns an error value - #EMPTY!
#LINK! – Excel cell reference error
IN in this example The error occurred when the formula was copied incorrectly. We have 3 ranges of cells: A1:A3, B1:B4, C1:C2.
Under the first range, in cell A4, enter the summing formula: =SUM(A1:A3). And then we copy the same formula under the second range, in cell B5. The formula, as before, sums only 3 cells B2:B4, bypassing the value of the first B1.
When the same formula was copied under the third range, the function returned the error #REF! in cell C3. Since there can only be 2 cells above cell C3 and not 3 (as required by the original formula).
Note. In this case, it is most convenient to press the combination of hot keys under each range before starting input. ALT keys+=. Then insert the summation function and automatically determine the number of summing cells.
How to fix VALUE in Excel
#VALUE! – error in meaning. If we try to add a number and a word in Excel, the result is the #VALUE! An interesting fact is that if we tried to add two cells in which the value of the first is a number and the second is text using the =SUM() function, then no error will occur, and the text will take the value 0 during calculation. For example:
Grids in an Excel cell
A series of hash marks instead of the cell value ###### - this value is not an error. This is simply information that the column width is too narrow to accommodate the correctly displayed cell contents. You just need to expand the column. For example, double-click with the left mouse button on the border of the column headers of this cell.
So hash marks (######) instead of cell values can be seen with a negative date. For example, we are trying to subtract a new date from an old date. And as a result of the calculation, the cell format is set to “Date” (and not “General”).
An incorrect cell format may also display a series of hash symbols (######) instead of values.