Why doesn't Excel automatically recalculate formulas? Formulas don't work in Excel. Excel Errors - Formula Error Due to Value Too Large

Main function Excel programs– working with formulas. This is what it is most often used for. Sometimes it happens that formulas in Excel do not work, in which case something needs to be done. This is exactly what will be discussed further.

Fixing the problem

There are quite a few reasons why the formula does not work in Excel. Perhaps your program or specific range settings are set incorrectly, or the formula is entered incorrectly.

Formulas don't work when dragging

The essence of the error is that several columns are created, for example, 2, and the third should display the result. There the formula is indicated and stretched down to cover the entire range, but in the end the value from the first cell is simply transferred to the next ones. In this case, there are several ways to fix everything:

Press the F9 button to start recalculating the sheet.
Change the cell format to General. To do this, select it and right-click. A menu will open where you should select “Cell Format” and change to “General”.
Open the parameters for calculating formulas and check that the marks are as on the screenshot.

The formula doesn't count

There are several reasons why the formula in Excel does not calculate:

Invalid cell format. To change it, you need to select the desired range of cells and select “Format Cells”.

The “Show formulas” mode is activated. To disable it, you need to go to the “Formulas” section in the top panel, click on “Formula Dependencies” and disable “Show Formulas”.

The formula or its syntax was entered incorrectly. In this case, the formula is displayed instead of the value. You need to check it carefully.

These are some of the main reasons why Excel does not calculate formulas.

The program does not calculate the formula entered into the cell

This often happens because the cell format is set to text. How to change it to another has already been described earlier.

The amount is not calculated

There are two options why the Excel application does not calculate the amount:

The cell format is test.
Amount calculation is deactivated. Needed using right button Click the mouse on the special panel and set the checkbox to “Amount”.

That's why the amount doesn't count.

Cell data is not summed up correctly

Some users have encountered problems where Excel incorrectly calculates the sum of selected cells. This often happens because some cells, or even most of them, are in text format. Because of this, not all data is summarized and the result is incorrect. You need to change the format to "General" to fix the problem.

Recalculation disabled

It happens that the user has calculated all the data, but when they are adjusted in the cells associated with the formula, the result does not change. This means that the parameters are set incorrectly:

Open program settings.
There you should go to the “Formulas” section. At the top there will be a block “Calculation parameters”, and there will be a parameter “Calculations in the book”, where the “Automatic” option should be selected. If not, set it properly and click “OK”.

Problems with formulas working in Microsoft Excel

Now, if any value is changed, the result will be recalculated.

From all this it is clear that there are many different reasons why Excel calculates formulas incorrectly or refuses to calculate them at all. If the user displays a formula where the result should be, it means the cell format is set to text or there is an error in the formula itself. View mode may be activated. Users also often forget to put “=” before the beginning of the formula. If the values in related cells change, but the result remains the same, you need to check whether auto-update is activated. In some cases, an error is displayed in the cells with totals, then you should check all the values.

Lifehacker readers are already familiar with Denis Batyanov who shared with us. Today Denis will talk about how to avoid the most common problems with Excel, which we often create for ourselves.

Let me make a reservation right away that the material in this article is intended for novice Excel users. Experienced users have already energetically danced on this rake more than once, so my task is to protect young and inexperienced “dancers” from this.

You don't give table column headings

Many Excel tools, such as sorting, filtering, smart tables, and pivot tables, assume that your data contains column headings. Otherwise, you either won’t be able to use them at all, or they won’t work quite correctly. Always make sure your tables contain column headings.

Empty columns and rows inside your tables

This confuses Excel. When it encounters an empty row or column inside your table, it starts to think that you have 2 tables, not one. You will have to constantly correct it. Also, don’t hide rows/columns you don’t need inside the table; it’s better to delete them.

There are several tables on one sheet

If these are not tiny tables containing reference books of values, then you shouldn’t do this.

It will be inconvenient for you to fully work with more than one table on a sheet. For example, if one table is located on the left and the second on the right, then filtering one table will affect the other. If the tables are located one below the other, then it is impossible to use the freezing of areas, and you will also have to constantly search for one of the tables and perform unnecessary manipulations in order to place the table cursor on it. Do you need it?

Data of the same type is artificially located in different columns

Very often, users who know Excel rather superficially prefer this table format:

It would seem that we have a harmless format for accumulating information on agent sales and their fines. This table layout is visually well perceived by humans, since it is compact. However, believe me, it is a complete nightmare to try to extract data from such tables and get subtotals(aggregate information).

The fact is that this format contains 2 dimensions: in order to , you must decide on the line by going through the branch, group and agent. When you find the right column, you will then have to look for the required column, since there are many of them. And this “two-dimensionality” greatly complicates working with such a table even for standard Excel tools - formulas and pivot tables.

If you build a pivot table, you will find that there is no way to easily get data by year or quarter, since the indicators are separated into different fields. You don't have one sales volume field that can be conveniently manipulated, but rather 12 separate fields. You'll have to manually create separate calculated fields for quarters and years, although if it were all in one column, the pivot table would do it for you.

If you want to use standard summation formulas like SUMIF, SUMIFS, SUMPRODUCT, you will also find that they will not work effectively with this table layout.

Spreading information across different sheets of the book “for convenience”

Another common mistake is, having some standard table format and needing analytics based on this data, distributing it into separate sheets Excel workbooks. For example, they often create separate sheets for each month or year. As a result, the amount of data analysis work is effectively multiplied by the number of sheets created. You don't have to do that. Accumulate information on ONE sheet.

Information in the comments

Users often add important information, which they may need, in the cell comment. Keep in mind that what is in the comments you can only look at (if you find it). It's difficult to get it into the cell. I recommend that you create a separate column for comments.

Formatting mess

Definitely won't add anything good to your table. This looks off-putting to the people who use your spreadsheets. At best, they won’t attach any importance to this, at worst, they will think that you are unorganized and sloppy in your affairs. Aim for the following:

Merging cells

Use cell merging only when you absolutely cannot do without it. Merged cells make it very difficult to manipulate the ranges they belong to. There are problems when moving cells, when inserting cells, etc.

Combining text and numbers in one cell

A painful impression is made by a cell containing a number appended at the back with the text constant “RUB.” or "USD" entered manually. Especially if it is not a printed form, but a regular table. Arithmetic operations with such cells are naturally impossible.

Numbers as text in a cell

Avoid storing numeric data in a cell in text format. Over time, some of the cells in such a column will have a text format, and some will have a regular format. This will cause problems with formulas.

If your table will be presented through an LCD projector

Choose the most contrasting combinations of color and background. A dark background and light letters look good on the projector. The most terrible impression is made by red on black and vice versa. This combination looks extremely low-contrast on a projector - avoid it.

Page mode of worksheet in Excel

This is the same mode in which Excel shows how the sheet will be divided into pages when printed. Page borders are highlighted in blue. I do not recommend constantly working in this mode, which many people do, since the printer driver is involved in the process of displaying data on the screen, and this depends on many reasons (for example, the printer is networked and this moment unavailable) is fraught with freezes in the visualization process and recalculation of formulas. Work as usual.

Even more useful information you can learn about Excel at

How to calculate the sum of values in Excel

Let's say you have a column with values and you need to know their sum. The easiest way is that you can select the required cells and in the lower right corner of Excel, in the status bar, you will see the sum of the values, their number and even the arithmetic mean.

If you need to use the received amount somewhere or simply display it in a cell, we will use the SUM function. It can work like manual mode, and in automatic. Select the required cells and click the “AutoSum” button, it is located on the “Home” tab. The formula for calculating the amount will appear in the next cell below the ones you highlighted. To insert a sum in an arbitrary place, select a blue free cell and type “=SUM(” without quotes in it, then select the desired range with the mouse and press Enter on the keyboard.

Why the amount may not be calculated in Excel

There are two most likely reasons.

1. Incorrect fraction separator. For example, in Windows there is a comma, but in the table you have a dot. Try replacing the separator sign for a pair of values and adding them up. To see what character is in the system, go to the “Control Panel”, select “Language and Regional Standards” -> “Advanced Settings”:

2. Your cells are in text format and, in addition to numbers, they contain extra characters, for example, spaces. Select them and press the right mouse button. Select "Format Cells" from the menu and set the format to "Number" if it's not the same. Check that there is nothing in the cells except numbers.

If you still have questions or need clarification, ask a question, our experts will promptly answer your request.

If Excel can't recognize the formula you're trying to create, you may receive an error message like this:

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

First of all click the button OK or key ESC to close the error message.

You will be returned to the cell with the incorrect formula, for which editing mode will be turned on and the problem area will be highlighted. If you don't know what to do with this and want to start over, exit edit mode by pressing the key again ESC or button Cancel in the formula bar.

If you want to move forward, the following checklist contains troubleshooting instructions to help you find 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 in the formula is not working correctly. For example, #VALUE! the error is caused by incorrect formatting or unsupported data types in the arguments. Plus you'll see #REF! error if the formula refers to cells that have been deleted or replaced with other data. Troubleshooting recommendations will vary 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. Simply drag a column to expand it, or go to the tab home > format > Automatic column width.

Review the following sections to address the errors you are seeing:

Every time you open a spreadsheet with formulas that reference values in other spreadsheets, you will be prompted to update the references or leave them as is.

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

You can also disable this dialog box to appear when opening a file. To do this, go to the section File > Options > Advanced > General and uncheck Request automatic link updates.

The formula returns syntactic construction, not the value

If the formula doesn't return a value, use the instructions below.

Formula doesn't evaluate

If the formula does not evaluate, check to see if it is included in Excel function automatic calculation. Formulas are not calculated if manual calculation is enabled. To check if it is enabled automatic calculation, follow the steps below.

additional information For information about calculations, see Change formula conversion, iteration, or precision.

The formula contains one or more circular references

A circular reference occurs when a formula references the cell in which it resides. The fix is to move the formula to a different cell or change the formula syntax to avoid circular references. However, in some scenarios you may need a circular reference because they result in iterating functions, repeating until a certain numeric condition is met. In this case, you will need to enable or disable circular reference.

For more information about circular references, see Remove or resolve a circular reference

Does the function begin with an equal sign (=)?

If an entry does not begin with an equal sign, it is not considered a formula and will not be calculated (this is a common mistake).

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

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

Do opening and closing parentheses match?

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

Does the syntax include all required arguments?

Functions in Excel have arguments - values that must be specified for the function to work. Only a small number of functions work without arguments (for example, PI or TODAY). Check the formula syntax that appears when you start typing the function to make sure that all required arguments are provided.

If you need to quickly review the syntax of a specific function, see the list of Excel functions (by category).

Are there unformatted numbers in the formulas?

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

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

Note: The result of a formula can be formatted using decimal separators and currency symbols after entering a formula with unformatted numbers (constants). As a general rule, it is not recommended to use constants directly in formulas: they can be difficult to find if you need to update values, and in addition, typos are often made when entering them. It is much more convenient to place constants in separate cells, where they will be accessible and easily referenced.

A formula may not return the expected results if the cell's data type is not suitable for the calculation. For example, if you enter the simple formula =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 string =2+3 . To fix this error, change the cell's data type from Text on General, as described below.

Select a cell.

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

Press the key F2 to switch to edit mode, and then press ENTER to 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 as 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 * symbol?

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

Are quotation marks missing around text in formulas?

If the formula contains text, it must be enclosed in quotation marks.

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

In the formula, the "today" field includes a space before the closing quotation mark to provide white space between the words "today" and "Monday, May 30." If there are no quotation marks around text in a formula, #NAME? may appear. 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 a workbook, enter: = "quarterly data"! D3. Without quotes around the sheet name, does the formula display #NAME? error .

You can also click values or cells on another sheet to add a reference to them in the formula. Excel will automatically enclose the sheet names in quotation marks.

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

For example, to reference cells a1 through A8 on the Sales sheet in the Q2 activity book open in Excel, enter: = 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 example =LOW("C:\My Documents\[Transactions for the second quarter.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 point).

Advice: To get the path to another workbook, the easiest way is to open it, enter the equal sign (=) in the original workbook, and then use the keys ALT+TAB go to the second workbook and select any cell on the desired sheet. After this, the source book can be closed. The formula will automatically update to show the full path to the sheet name with the correct syntax. This path can be copied and pasted if necessary.

Are you trying to divide numeric values by zero?

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

To resolve this error, you can simply check to see if the denominator exists.

IF(B1;A1/B1;0)

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

Does the formula refer to deleted data?

Before you delete data in cells, ranges, specific names, sheets, and workbooks, always check to see if you have formulas that reference them. You can replace formulas with their results before deleting the referenced data.

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

If a formula references cells that have been deleted or replaced with other data and returns a #REF! Select the cell with #REF! If the position you specify is before the first or after the last element in the field, the formula returns a #REF! error. In the formula bar, click the #REF! and delete it. Then enter the range for the formula again.

If a specific name is missing and a formula that references that name returns #NAME? error Define a new name that refers to the desired range, or change the formula so that it refers directly to the range of cells (for example, a2:D8).

If a sheet is missing and a formula referencing it returns #REF! Error: We cannot resolve this issue, unfortunately a sheet that has been deleted cannot be recovered.

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

For example, if you use the formula =[Book1.xlsx]Sheet1"!A1, and such a book no longer exists, the values referencing it will be available. But if you change and save a formula that references this workbook, a dialog box appears Update values prompting you to enter a file name. Click the button Cancel and ensure data integrity by replacing the formula that references the missing workbook with its results.