If Excel cannot correctly evaluate the formula or function of the worksheet; it will display the error value - for example, #NAME ?, #NUMBER !, #VALUE !, # N / A, #NOT! #REF! - in the cell where the formula is located. Let's analyze the types errors in Excel, them possible reasonsand how to fix them.

#NAME error?

#NAME error appears when a name that is used in a formula has been deleted or has not been previously defined.

Causes of occurrence errors #NAME?:

1. If the formula uses a name that has been deleted or not defined.

Errors in Excel - Using Name in Formula

Eliminating the error: define a name. How to do this is described in this.

1. Function name misspelling:

Errors in Excel - Error in writing the MATCH function

Eliminating the error: check the spelling of the function.

1. A colon (:) is missing in a cell range reference.

Errors in Excel - Error writing a range of cells

Eliminating the error: correct the formula. In the above example, this is \u003d SUM (A1: A3).

1. The formula uses text that is not enclosed in double quotes. Excel throws an error, since it perceives such text as a name.

Errors in Excel - Error combining text with a number

Eliminating the error: Enclose formula text in double quotes.

Errors in Excel - Correct Concatenation of Text

Error #NUM!

Error #NUM! in Excel is displayed if the formula contains an invalid number. For instance:

1. Use a negative number when a positive value is required.

Errors in Excel - Error in formula, negative argument value in function ROOT

Eliminating the error: check the correctness of the arguments entered in the function.

1. The formula returns a number that is too large or too small to represent in Excel.

Errors in Excel - Error in formula due to too large value

Eliminating the error: Adjust the formula so that the result is a number in the available Excel range.

Error #VALUE!

This excel error occurs when an invalid value argument is entered in a formula.

Reasons for #VALUE! Error:

1. The formula contains spaces, symbols, or text, but must contain a number. For instance:

Errors in Excel - Summing Numeric and Text Values

Eliminating the error: check if the types of the arguments in the formula are correct.

1. A range is entered in the function argument, and the function is expected to enter a single value.

Errors in Excel - The VLOOKUP function uses a range as an argument instead of a single value

Eliminating the error: Specify the correct arguments in the function.

1. When using an array formula, the Enter key is pressed and Excel displays an error, since it interprets it as a regular formula.

Eliminating the error: To finish entering the formula, use the key combination Ctrl + Shift + Enter.

Errors in Excel - Using Array Formula

Error #REF

Errors in Excel - Error in formula, due to deleted column A

Eliminating the error: change the formula.

# DIV / 0 error!

This errorExcel occurs when dividing by zero, that is, when a reference to a cell that contains a zero value or a reference to an empty cell is used as the divisor.

Errors in Excel - Error # DIV / 0!

Eliminating the error: correct the formula.

Error # N / A

# N / A error in Excel means the formula is using an unavailable value.

Reasons for # N / A error:

1. When using VLOOKUP, HLOOKUP, LOOKUP, SEARCH, an invalid lookup_value argument is used:

Errors in Excel - The value you are looking for is not in the array being searched

Eliminating the error: provide a correct argument to the desired value.

1. Errors in using the VLOOKUP or HLOOKUP functions.

Eliminating the error: see section dedicated

1. Errors in working with arrays: using inappropriate range sizes. For example, array arguments are smaller than the resulting array:

Errors in Excel - Errors in Array Formula

Eliminating the error: Adjust the formula reference range to match the rows and columns, or enter an array formula in the missing cells.

1. One or more required arguments are not specified in the function.

Errors in Excel - Errors in formula, no required argument

Eliminating the error: Enter all required function arguments.

Error # NULL!

Error # NULL! in Excel occurs when non-overlapping ranges are used in a formula.

Errors in Excel - Using non-overlapping ranges in the SUM formula

Eliminating the error: check the spelling of the formula.

Error ####

Reasons for the error

1. The column is not wide enough to display the contents of the cell.

Errors in Excel - Increase Column Width to Display Value in Cell

Eliminating the error: increase the width of the column / columns.

1. The cell contains a formula that returns a negative value when calculating a date or time. Date and time in Excel must be positive values.

Errors in Excel - Difference between dates and hours must not be negative

Eliminating the error: Check the spelling of the formula, the number of days or hours was a positive number.

If Excel cannot recognize the formula you are trying to create, you might receive an error message like this:

Unfortunately, this means Excel doesn't understand what you are trying to do, so you might be better off getting out and starting over.

First of all press the button OK or key ESCto close the error message.

You will be returned to the cell with the wrong formula, for which the edit mode will be turned on and the problem area will be highlighted. If you do not know what to do with this and want to start over, exit the edit mode by pressing the key again. ESC or button Cancellation on the formula bar.

If you'd like to go ahead, the following checklist contains troubleshooting steps to help you figure out what might be wrong.

Do you see the hash (#) error?

Excel displays various hash (#) errors such as #VALUE !, #REF !, #NUM, # N / A, # DIV / 0 !, #NAME? and #NULL !, to indicate what is not working correctly in the formula. For example, #VALUE! the error was caused by incorrect formatting or unsupported data types in the arguments. Also, you will see #REF! error if the formula refers to cells that have been deleted or replaced with other data. Troubleshooting recommendations will be different for each error.

Note: # # # # is not a formula-related error. This means that the column is not wide enough to display the contents of the cell. Just drag a column to expand it, or go to the tab home\u003e format\u003e AutoFit Column Width.

Check out the following sections for the errors you see:

Each time you open a spreadsheet with formulas that reference values \u200b\u200bin other spreadsheets, you will be prompted to update the links or leave them as they are.

Excel displays the dialog above to ensure that the formulas in the current sheet always point to the most updated values \u200b\u200bin case the reference value has changed. You can update links or skip this step. Even if you decide not to update the links, you can do it manually at any time directly in the spreadsheet.

You can also disable the display of this dialog when opening a file. To do this, go to the section File\u003e Options\u003e Advanced\u003e General and uncheck Ask to update automatic links.

Formula returns syntactic construct, not value

If the formula does not produce a value, follow the instructions below.

The formula is not calculated

If the formula is not evaluated, check if included in Excel function automatic calculation. Formulas are not evaluated if manual calculation is enabled. To check if automatic calculation, follow the steps below.

For more information on calculations, see Change formula recalculation, iteration, or precision.

The formula contains one or more circular references

A circular reference occurs when a formula refers to the cell it is in. The fix is \u200b\u200bto move the formula to a different cell, or change the syntax of the formula to avoid circular references. However, in some scenarios, you may need circular reference, as they result in functions iterating over and over until a certain numeric condition is met. In this case, you will need to enable or disable the circular link.

For more information on circular references, see Remove or Resolve Circular References

Does the function start with an equal sign (\u003d)?

If a record does not start with an equal sign, it is not considered a formula and is not calculated (this is a common mistake).

If you enter SUM (A1: A10)Excel will display a text string SUM (A1: A10) instead of the result of the formula. Also, when you type 11/2 excel displays a date such as Nov 2 or 02/11/2009 instead of dividing 11 by 2.

To avoid these unexpected results, always start your formula with an equal sign. For example, enter = SUM (A1: A10) and =11/2 .

Do open and close parentheses match?

If a formula uses a function, it is important for each open parenthesis to have a closing parenthesis for it to work correctly, so make sure that each parenthesis is matched. For example, the formula \u003d IF (B5<0);"Недопустимо";B5*1,05) won't work because it has two closing braces and only one opening parenthesis. The correct version of this formula is as follows: \u003d IF (B5<0;"Недопустимо";B5*1,05) .

Does the syntax include all required arguments?

Functions in Excel have arguments - values \u200b\u200bthat must be specified for the function to work. Only a small number of functions (such as PI or TODAY) work without arguments. Check the formula syntax that appears when you start typing the function to ensure that all required arguments are specified.

If you need to quickly see the syntax of a specific function, see the Excel Function List (by category).

Are there unformatted numbers in formulas?

Do not enter numbers formatted with dollar signs ($) and decimal separators (,) in formulas, because dollar signs are absolute references and commas are argument separators. Instead $1,000 you must enter 1000 in the formula.

If you use formatted numbers in arguments, you get unexpected results, but you might also see the #NUM! ... For example, if you enter the formula \u003d ABS (-2 134) to find the absolute value -2134, Excel will show #NUM! an error because the ABS function only takes one argument and it sees the numbers -2 and 134 as separate arguments.

Note: Formula result can be formatted using decimal separators and currency symbols after entering a formula with unformatted numbers (constants). As a rule, it is not recommended to use constants directly in formulas: they can be difficult to find in case you need to update values, and besides, when entering them, there are often typos. It is much more convenient to put constants in separate cells in which they will be available and easily referenced.

The formula may not return the expected results if the data type of the cell is not suitable for the calculation. For example, if you enter a simple formula \u003d 2 + 3 in a cell that is in text format, Excel will not be able to calculate the entered data. The cell will display the line =2+3 ... To fix this error, change the cell data type from Text on Generalas described below.

Select a cell.

In the tab home click the arrow next to the list Number format (or press CTRL + 1) and select General.

Press the key F2to enter edit mode and then press Enterto confirm the formula.

If you enter a date in a cell that has numerical data type, it can be displayed as a numeric value rather than a date. To display this number as a date, in the collection Number format select format date.

Are you trying to do multiplication without using the * character?

A cross is often used as a multiplication operator in a formula ( x), but Excel must use an asterisk (*) for this. If you use "x" in a formula, an error message appears and prompts you to correct the formula by replacing x on "*".

Are quotes missing around text in formulas?

If the formula contains text, you must enclose it in quotation marks.

For example, the formula \u003d "Today" & TEXT (TODAY (); "dddd, dd.MM") concatenates the text string "Today" with the results of the TEXT and TODAY functions and returns a result similar to the following: Today is Monday, 30.05.

In the formula, the Today field has a space before the closing quotation mark to provide white space between the words today and Monday, May 30th. If the quotes are missing around the text in the formula, #NAME? error .

Does the formula include more than 64 functions?

Are sheet names enclosed in apostrophes?

For example, to return the value from cell D3 on the quarterly data sheet in the workbook, enter: \u003d "quarterly data"! D3... Does #NAME appear in the formula without quotes around the sheet name? error .

You can also click values \u200b\u200bor cells in another sheet to link to them in the formula. Excel will automatically quote sheet names.

If the formula refers to an external workbook, is the workbook path correct?

For example, to reference cells A1 through A8 in the Sales sheet in workbook Q2 open in Excel, enter: \u003d Sales! A1: A8... If there are no square brackets in the formula, #REF! ...

If the workbook is not open in Excel, enter the full path to the file.

for instance \u003d ROWS ("C: \\ My Documents \\ [Q2 Transactions.xlsx] Sales"! A1: A8).

Note: If the full path contains spaces, you must enclose it in apostrophes (at the beginning of the path and after the book name, before the exclamation mark).

Advice: To get the path to another workbook, the easiest way is to open it by typing an equal sign (\u003d) in the original workbook, and then using the keys ALT + TAB go to the second book and select any cell on the desired sheet. After that, the source book can be closed. The formula will automatically update to show the full path to the sheet name with the correct syntax. You can copy and paste this path if necessary.

Are you trying to divide numeric values \u200b\u200bby zero?

Dividing a cell by another cell that has zero (0) or no value results in # DIV or 0! Error.

To fix this error, you can simply check if the denominator exists.

IF (B1; A1 / B1; 0)

The meaning of this formula is as follows: IF B1 exists, return the result of dividing A1 by B1, otherwise return 0.

Does the formula refer to deleted data?

Before deleting data in cells, ranges, specific names, sheets, and workbooks, always check to see if you have any formulas that refer to them. You will be able to replace formulas with their results before deleting the referenced data.

If you are unable to replace formulas with their results, read the error information and possible solutions:

If the formula refers to cells that have been deleted or replaced with other data, it returns a #REF! Highlight the cell with #REF! If the position you specified is before the first or after the last item in the field, the formula returns the #REF! Error. On the formula bar, click the #REF! and remove it. Then enter the range for the formula again.

If a specific name is not present and the formula that refers to that name returns #NAME? error Define a new name that refers to the range you want, or change the formula to refer directly to the range of cells (for example, a2: D8).

If there is no sheet and the formula referencing it returns #REF! Error: Unable to fix this problem, unfortunately the sheet that was deleted cannot be restored.

If a workbook is missing, this does not affect the formula that references it until you update the formula.

For example, if you use the formula \u003d [Book1.xlsx] Sheet1 "! A1, and there is no such book anymore, the values \u200b\u200breferencing it will be available. But if you change and save the formula that refers to this workbook, a dialog box appears Update Values with a prompt to enter a file name. Click the button Cancellation and ensure data integrity by replacing the formula that references the missing workbook with its results.

Graphs and charts (5)
Working with VB project (12)
Conditional Formatting (5)
Lists and Ranges (5)
Macros (VBA procedures) (64)
Miscellaneous (41)
Excel bugs and glitches (4)

Excel calculates incorrectly. Why?

Often, when calculating the difference between two cells in Excel, you can see that it is not zero, although the numbers are the same. For example, cells A1 and B1 contain the same number 10.7, and in C1 we subtract another from one:

And the strangest thing is that in the end we don't get 0! Why?

The reason is obvious - the format of the cells
First, the most obvious answer: if you are comparing the values \u200b\u200bof two cells, then you need to make sure that the numbers there are really equal and not rounded by the format of the cells. For example, if you take the same numbers from the example above, then if you select them - right mouse button - Format cells -tab Number -select the Numeric format and set the number of decimal places to 7:

Now everything becomes obvious - the numbers are different and were simply rounded by the format of the cells. And naturally they cannot be equal. In this case, it will be optimal to understand why the numbers are exactly the same, and only then make a decision. And if you are sure that the numbers should really be rounded to tenths, then you can apply the ROUND function in the formula:
\u003d ROUND (B1,1) -CIRCULAR (A1,1) \u003d 0
\u003d ROUND (B1,1) -ROUND (A1,1) \u003d 0
There is also a more radical method:

• Excel 2007: Office button -Excel options -Advanced -
• Excel 2010: File -Options -Advanced -Set precision as displayed
• Excel 2013 and higher: File -Options -Advanced -Set precision as displayed

This will write all the numbers on all sheets of the book exactly as they are displayed in the cell format. This action is best performed on a copy of the book, because it brings all the numerical data in all sheets of the book to the form as they are displayed on the screen. Those. if the number itself contains 5 decimal places, and only 1 is specified in the format of the cells, then after applying this option, the number will be rounded to 1 decimal place. However, you cannot undo this operation, unless you close the workbook without saving.

The reason is software
But often in Excel you can observe a more interesting "phenomenon": the difference between two fractional numbers obtained by the formula is not equal to exactly the same number written directly into the cell. For example, write the following formula in a cell:
=10,8-10,7=0,1
in appearance the result should be the answer TRUE ... But in fact there will be FALSE ... And this example is not the only one - such behavior of Excel is far from uncommon in calculations. It can also be found in a less explicit form - when calculations are based on the value of other cells, which, in turn, are also calculated by formulas, etc. But the reason is the same in all cases.

Why are seemingly identical numbers not equal?
Let's first figure out why Excel considers the above expression to be false. After all, if you subtract the number 10.7 from 10.8 - in any case, you get 0.1. So, somewhere along the way, something went wrong. Let's write in a separate cell the left side of the expression: \u003d 10.8-10.7. 0.1 appears in the cell. And now we select this cell - the right mouse button - Format cells -tab Number -select the Numeric format and set the number of decimal places to 15:


and now you can see that in fact the cell is not exactly 0.1, but 0.100000000000001. Those. in the 15th significant digit we have a "tail" in the form of an extra unit.
And now let's figure out where this "tail" came from, because logically and mathematically it shouldn't be there. I will try to tell you very briefly and without unnecessary cleverness - you can find a lot of them on this topic on the Internet if you wish.
The thing is that in those distant times (this is about the 1970s), when the PC was still something like an exotic, there was no single standard for working with floating point numbers (fractional, if simple). Why bother with this standard? Then, that computer programs see numbers in their own way, and fractional ones in general with the status "everything is complicated." And at the same time, the same fractional number can be represented in different ways and operations with it can be processed too. Therefore, in those days, the same program, when working with numbers, could produce different results on different PCs. Taking into account all the possible pitfalls of each PC is not an easy task, so at one point the development of a single standard for working with floating point numbers began. Omitting various details, nuances and interestingness of the story itself, I will only say that in the end all this resulted in iEEE754 standard... And according to its specification, in the decimal representation of any number, errors are allowed in the 15th significant digit. This leads to inevitable errors in calculations. Most often this can be observed precisely in subtraction operations, since it is the subtraction of numbers that are close to each other that leads to the loss of significant digits.
More information about the specification itself can also be found in the Microsoft article: Results of floating point arithmetic operations in Excel may be inaccurate
This is exactly the fault of this behavior in Excel. Although in fairness it should be noted that not only Excel, but all programs based on this standard. Of course, a logical question arises: why did they adopt such a buggy standard? I would say there was a trade-off between performance and functionality. There may have been other reasons, though.

Much more important is something else: how to deal with it?
In fact, nothing, tk. this is a software "bug". And in this case, there is no other way out how to use all sorts of patches like ROUND and similar functions. In this case, ROUNDL should be applied here not as shown in the very beginning, but a little differently:
\u003d ROUND (10.8 - 10.7; 1) \u003d 0.1
\u003d ROUND (10.8-10.7.1) \u003d 0.1
those. in ROUNDL we have to put the "buggy" expression itself, and not each of its arguments separately. If you put each argument, then it will not give an effect, because the problem is not in the number itself, but in how the program sees it. And in this case, 10.8 and 10.7 have already been rounded to one digit, and it is clear that rounding each number separately will have no effect at all. You can, however, get out in another way. Multiply each number by some value (say, by 1000, to remove the decimal places 100%) and then subtract and compare:
=((10,8*1000)-(10,7*1000))/1000=0,1

I would like to believe that at least someday the described feature of the IEEE754 standard will be defeated by Microsoft, or at least made a patch that will perform simple calculations no worse than a 50-ruble calculator :) Did the article help? Share the link with your friends! Video lessons

("Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity \u003d 60); "," titlecss ":" display: block; position: relative; font: bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Today I ran into a situation when in excel formulas did not work when pulling, i.e. there were two columns and it was necessary to calculate the difference between the first two columns in the third column. As a result, a formula is made and stretched down to the end, but the result from the first cell is copied to the rest of the cells.

Option 1

It is likely that you have disabled automatic sheet recalculation, for this, just press the key F9

2 Option for all Office versions

Check that the cell format is not text (because of this, calculations and, accordingly, formulas may not work)

PS to fix this, select the cells and select format General (see the figure Format Cells)

3 Option for Office 2010

Check the settings for calculating formulas.

To do this, click on the FILE-\u003e PARAMETERS-\u003e FORMULAS menu and in the "Calculation parameters" menu, see where you have a checkmark (see the picture should be like this)

4 Option for Office 2007/2010

For Office 2007, the path is Formula-\u003e Calculation Options-\u003eand put a daw on Automatically, except for data tables.(see picture below)

It happens that a table is copied to Excel, but the numbers in it are not counted, formulas don't work inExcel... One of the reasons may be that the cell values \u200b\u200bare in text format. Remember, Excel has two formats - cell format and value format. For more information, see the Convert Date to Excel Text article.
So consider, why inExcel formula not working.
You need to add the top numbers in the cells. In cells, values \u200b\u200bare written in text format with non-printable characters inserted between lines. We need to convert the text format of the values \u200b\u200bto numeric and highlight the first number in the cell. We have such a copied table from 1C.
First step.
Convert the value of the cells to a number format.
Select cells. On the "Home" tab in the "Editing" section, click on the "Find" button.
In the dialog box that appears, in the "Find" line, put a comma ". We do not put anything in the "Replace" line.
Click the "Replace All" button. It will turn out like this.
Second phase.
In the same "Find and Replace" window (we did not remove it and the cells remained selected), in the "Find" line we put a period. And in the line "Replace" we put a comma. Click "Select All".

It turned out like this.
Remove the Find and Replace window.
Stage three.
How to highlight the first number in a cellExcel.
In cell B1 we write the following formula. \u003d LEFT (A1, FIND (CHAR (10), A1) -1)
With this formula, we have selected the first number from the cell. Copy the formula column by column. It turned out like this.
In cell B4, we wrote the addition formula. \u003d B1 + B2 + B3
AutoSum doesn't work, but handwritten formulas do.
In Excel, you can link a cell to another sheet, range, cell, site, etc. When you click on this link, Excel follows it. But, how to highlight the cell itself, not the hyperlink? Read about this article "