When multiplying cells in Excel, it displays the value. What errors exist in Excel and how to fix them. #NAME? - What does it mean and how to fix it
When creating complex formulas (or simply being careless) in MS Excel, it is quite easy to make a mistake. Usually MS Excel in such cases displays error messages or even suggests the “correct” version of the formula in its opinion, however, even with a help system, at first it is quite difficult to understand what the “stupid program” wants from us. In this article we will look at all types of errors that occur in MS Excel formulas, and learn how to correct and understand them.
Error #VALUE! (error in meaning)
If there was a “top of MS Excel errors”, the first place in it would belong to the error #VALUE!. As you can guess from the name, it occurs when an incorrect value is substituted into a formula or function. If you try to perform arithmetic operations on text, or substitute a range of cells into a function when you only need to specify one cell, the calculation will result in a #VALUE! error.
As was said, trying to add a number and text leaves MS Excel at a dead end.
Error #LINK! (incorrect cell reference)
One of the most common errors in calculations. It means the simplest thing - the formula uses a reference to a cell that you either did not create or accidentally deleted. More often #LINK! occurs when you delete an “unnecessary” column, some cells of which, as it turns out, were involved in the calculations.
Error #DIV/0! (division by zero)
From school we remember a simple rule: you cannot divide by zero! Error #DIV/0!- this is a warning from MS Excel that this basic rule has been violated and you are still trying to divide a certain number by zero. In this case, the “zero” itself is not necessary - any attempt to divide an existing number into an “empty” cell will also cause this error.
You cannot divide by zero - an empty cell is perceived by MS Excel as the same zero
Error #N/A (value not available)
Error #N/A occurs when an argument is missing from a function, or one of the values used in the formula becomes unavailable. I saw #N/A - first of all, look for what is missing in your calculations.
I’m using the VLOOKUP function, I’ve set the division sign, but I forgot to indicate which cell it belongs to
Error #NAME? (invalid name)
Error #NAME- a sign that you and Excel did not understand each other. Or rather, MS Excel did not understand what you meant - you are clearly pointing to some element, but the program cannot find it. In what cases does this usually happen?
- The function specifies a cell or range of cells with a non-existent (most often incorrectly entered) name.
Trying to sum a non-existent range called Column
- The text inside the function is enclosed in quotation marks. If this does not happen (that is, instead of = “Vasya” we enter = Vasya), MS Excel becomes completely perplexed.
Another simple mistake is that the text in functions and formulas is indicated in quotes
- There was an accidental typo in the function name.
Error #EMPTY! (empty set)
Error #EMPTY Most often it occurs when one of the operators is missing in the formula, but it can also occur when we need to find the intersection of two ranges of cells, and this intersection simply does not exist.
Everything would be fine, but I forgot about the second “+” sign
Error #NUMBER! (wrong number)
Error #NUMBER! ms Excel reports in cases where the result of mathematical calculations in the formula generates some completely unrealistic result. A result in the form of an extremely large or small number, an attempt to calculate the root of a negative number - all this will lead to the #NUMBER error!
Calculate the root of a negative number? Not only Excel would not understand you
Hash marks in an Excel cell (#######)
In the past, a very common MS Excel “error” associated with the sudden filling of a cell with pound signs (#) could be caused by the fact that a number was entered into the cell that did not fit entirely in it (but only if the cell was in the “numeric” or “date” format). .
With the advent of MS Office 2013, the error practically disappeared, since the “wiser” Excel began, in most cases, to automatically increase the cell width to fit the number. If you see “lattices”, the easiest way to get rid of them is to manually increase the width of the cell.
Just increase the column width and the problem will disappear
If you know in what cases a particular error occurs in Excel, you will most likely immediately understand what caused it. However, the program simplifies your work even more and displays a special character next to the erroneous value in the form of an exclamation point icon in a yellow diamond. When you click on it, you will be offered a list of possible actions to correct the error.
Click the icon to get help fixing the error
In the first line of the context menu that appears, you will see the full name of the error, in the second you can call up detailed help on it, but the most interesting thing is hidden in the third item: “ Show calculation steps...«.
“Show calculation steps...” - you can’t fool the program, it accurately displays the fragment of the formula where the error was made
Click on it and in the window that appears you will see the very fragment of the formula where the mistake was made - this is especially convenient when you have to “unravel” a whole tangle of actions nested within each other.
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? 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 you have a huge number of formulas on your worksheet, every change you make 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 into 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 help's authors present the material in a very accessible way 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 reached, 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 select “Show calculation steps” in the context menu.
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!
After entering or adjusting a formula, as well as when changing any value of a function, it happens that a formula error appears rather than the required value. In total, the spreadsheet editor recognizes seven main types of such incorrect calculations. We'll look at what errors look like in Excel and how to fix them below.
Below we will present a description of the formulas shown in the picture with detailed information on each error.
1. #CASE!– “division by 0”, most often occurs when trying to divide by zero. That is, the formula embedded in the cell, performing the function of division, comes across a cell with a zero value or “Empty”. To resolve the issue, check all cells involved in the calculation and correct any invalid values. The second action leading to #DIV/O! – this is the entry of incorrect values into some functions, such as =AVERAGE(), if during the calculation the value range contains 0. The same result will be caused by empty cells accessed by a formula that requires specific data for calculation.
2. #N/A- "no data". This is how Excel marks values that are not clear to the formula (function). By entering inappropriate numbers into a function, you are sure to trigger this error. When it appears, make sure that all input cells are filled out correctly, and especially in those where the same inscription is illuminated. Often found when used
3. #NAME? – “invalid name”, an indicator of an incorrect name of the formula or some part of it. The problem disappears if you check and correct all the titles and names accompanying the calculation algorithm.
4. #EMPTY!– “there is an empty value in the range”, a signal that somewhere in the calculation there are non-overlapping areas or a space is inserted between the specified ranges. Quite a rare mistake. An erroneous entry may look like this:
SUM(G10:G12 I8:J8)
Excel does not recognize such commands.
5. #NUMBER!– an error is caused by a formula containing a number that does not correspond to the specified range.
6. #LINK!– warns that cells associated with this formula have disappeared. Check to see if the cells specified in the formula have been deleted.
7. #VALUE!– the type of argument for the function operation is incorrectly selected.
8. Bonus, error ##### — the cell width is not sufficient to display the entire number
Additionally, Excel issues a warning that the formula is incorrect. The program will try to tell you exactly how to place punctuation (for example, parentheses). If the proposed option meets your requirements, click “Yes”. If a hint requires manual adjustment. Then select “No” and rearrange the brackets yourself.
Errors in Excel. Using the ERROR() function for Excel 2003
The function helps to eliminate errors in Excel well. It works by finding errors in cells; if it finds an error in the formula, it returns TRUE and vice versa. In combination with =IF(), it will allow you to replace the value if an error is found.
Working formula: =IF(ERROR(expression),error,expression).
IF(ISERROR (A1/A2);””;A1/A2)
Explanation: If an error is found while executing A1/A2, empty ("") will be returned. If everything went correctly (i.e. ERROR (A1/A2) = FALSE), then A1/A2 is calculated.
Errors in Excel.Using IFERROR() for Excel 2007 and higher
One of the reasons why I quickly switched to Excel 2007 was IFERROR() (the main reason is )
The iferror function contains the capabilities of both functions - ERROR() and IF(), but is available in newer versions of Excel, which is very convenient
The tool is activated as follows: =IFERROR(value; value on error). Instead of “value” there is a calculated expression/link to the cell, and instead of “value in case of error” - what should be returned if an inaccuracy occurs, for example, if when calculating A1/A2 #CASE! then the formula will look like this:
IFERROR(A1/A2;””)
» download here
» 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 different resolution methods.
##### — What does it mean and how to fix it?
These symbols indicate that the column containing numbers is not wide enough for them, or that the date and time entered into the cells in that 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.
- On the formula bar in the name box, the correct spelling of the required formula will be displayed, according to which you can 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 the required arguments to a standard worksheet function are not supplied, then you must enter all required corresponding function arguments.
Option 4
If your formula uses a function that is not currently available, you must verify that the workbook that uses 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, which 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 different programs and is used to place a document created in one program into another program. For example, you can insert a Word document into an Excel workbook and vice versa.
Option 3
Option 4
If you use a macro that calls a macro function that, under 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 a formula calculation 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.
Date: December 24, 2015 Category:Errors in Excel are an indispensable companion for everyone who... When an expression in a cell cannot be evaluated, the program displays an error message in the cell. It begins with a "#" followed by the name of the error. There is no need to be afraid of this if you are familiar with Excel functions and know how to follow the simplest logic of mathematical operations - you will easily find and correct the error.
If a cell is completely filled with pound signs (#), this is not an error at all. There is not enough space in the cell to display the result. Increase the cell size or decrease the font size so that the result can be displayed.
Types of errors
If an error does occur, decryption will help in correcting it:
| Error | Description |
| #DIV/0! | An error occurs when trying to divide by zero |
| #NAME? | The program cannot recognize the entered name. For example, you misspelled the function name, or did not enclose the text string in quotes |
| #N/A | Data not available. For example, I didn’t find any value |
| #EMPTY! | You requested the intersection of ranges that do not intersect |
| #NUMBER! | The problem is with one of the numeric values used in the formula. For example, you are trying to take the square root of a negative number. In classical mathematics this operation makes no sense |
| #LINK! | The formula contains a link that does not exist. For example, you deleted the cell it refers to |
| #VALUE! | The formula contains invalid components. Often this error occurs when the syntax of formulas is violated. |
When there is an error in a formula in a cell, a marker appears next to it. By clicking on it, you can read help about this error. You can also see the stages of calculation. Select this item and the program will show a window where the location of the error will be underlined. This is the best way to determine where the error is occurring.
In addition, you can do without correcting such errors, but simply . But it must be appropriate. Errors should only be worked around if they cannot be corrected. Otherwise, the calculation results may be distorted.
Tracing an error through calculation steps Circular links in Excel
Another type of error is a circular reference. It occurs when you reference a cell whose value depends on the one in which you write the formula. For example, in a cage A1 the formula =A2+1 is written, and in A2 write =A1 , a cyclic reference will appear that will be recalculated endlessly. In this case, the program warns about the appearance of a cyclic reference and stops the calculation of “cyclic formulas”. A double-headed arrow appears on the left side of the cells. You will have to correct the error that occurred and repeat the calculation.
Sometimes complex “looping” occurs when a cyclic link is formed with several intermediate formulas.
To track down such errors, run Formulas – Formula Dependencies – Error Checking – Circular References. In the drop-down list, the program displays the addresses of the cells that create an endless loop. All that remains is to correct the formulas in these cells.
Tracking Circular Links In Excel, you can try to calculate the result of looping formulas. To do this, check the box File – Options – Formulas – Enable Iterative Calculations. In the same block, you can set the maximum number of iterations (calculations) to find the balance and the permissible error. In most cases this is not necessary, so I recommend not checking this box. However, when you know that the looping formulas are correct and their calculation will lead to a stable result - why not do it?
That's all about the types of errors in Excel. This short article has given you enough information to deal with the most common errors in Excel by analyzing the return value. But read the extended list of errors in! I’m ready to answer your questions - write in the comments.
In the next article I will tell you. Needless to say that Excel functions are “our everything”?
I think no. Therefore, go ahead and read, this will be the first step into the world of complex formulas with the right results!
