Excel does not calculate formulas, shows 0. Problems with formulas working in Microsoft Excel

Date: November 15, 2018 Category:

Hello friends. Has it ever happened to you that, after entering a formula, the formula itself is displayed in the cell instead of the calculation result? This is a little discouraging, because you did everything right, but you got something unclear. How can I force the program to calculate the formula in this case?

First you need to understand the reasons for this behavior, and then correct the situation. And the reasons may be as follows.

Formula display enabled

Excel has a calculation checking mode. When it is turned on, you see the formulas on the worksheet. I talked about how and why to use formula display in . Check to see if it is activated, then disable it. There is a button on the ribbon Formulas – Formula Dependencies – Show Formulas. If it is enabled, click on it to disable it.

Often, the display of formulas is turned on accidentally by pressing the Ctrl+` key combination.

The formula is perceived by the program as text

If Excel thinks that a cell contains a text string, it will not calculate the value, but will simply display the contents. There may be several reasons for this:

I have listed all the common cases where the formula is displayed instead of the result. I think your case will be on the list above; fixing the problem will not be difficult. If it didn’t work out, write in the comments.

Promises to be very interesting. We'll look at some simple techniques for writing formulas quickly. If you bring them to automaticity, your work will be done easier and faster. Subscribe to updates so you don't miss out!

When calculations are incorrect, formulas display several types of errors instead of values. Let's look at them using 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 from negative number. For example, =ROOT(-25).

Cell A1 contains too large a number (10^1000). Excel cannot work with these 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 – square root cannot be from a negative number, but the program displayed this result 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 (it does not know the name of the function =SUM(), 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 for quick receipt data from large tables by request of 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 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 cell value ###### – given 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, do double click 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 from an old date new 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.

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 fractional 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 “Regional and Language 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 "Number" format if it is 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.

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 due to the fact that text format cells. 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. You need to use the right mouse button to click on a 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 in linked cell values 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.

You can customize the display of zero values in a cell, or you can use a set of formatting standards in the table that require you to hide zero values. You can show and hide null values in different ways.

Hide and show all null values in a worksheet

Select File > Options > Additionally.

In a group

Show zeros in cells that contain zero values.

Hiding zero values in selected cells

Follow these steps to hide zero values in selected cells using a number format. If a value in one of these cells changes to a non-zero value, it will appear in the cell and be formatted the same as the general number format.

Showing Hidden Values.

Highlight cells with hidden zero values.

You can press the keys CTRL+1 or on the tab Home click Format > Format Cells.

To apply the default number format, select Number > General and press the button OK.

Hiding null values returned by a formula

On the tab Home Conditional Formatting and choose Cell Highlight Rules > Equals.

In the left field, enter 0 .

In the right field, select Custom Format.

In the field Cell Format open the tab Font.

On the list Color select white and press the button OK.

Display zeros as spaces or dashes

To solve this problem, use the IF function.

If a cell contains null values, use a formula such as this to return an empty cell:

IF(A2-A3=0;"";A2-A3)

Here's how to read the formula. If the result of the calculation (A2-A3) is "0", nothing is displayed, including "0" (this is indicated by double quotes ""). Otherwise, the result of the calculation A2-A3 is displayed. If you need to leave the cells empty, but display something other than "0", insert a hyphen "-" or other symbol between the double quotes.

Select a report pivot table.

On the tab Analysis in the group Pivot table Options and select Options.

Go to the tab Layout and format

In the field Format check the box For errors display

Check the box Display for empty cells

The need to display zero values (0) on sheets does not always arise. Whether formatting standards or your own preferences require you to show or hide null values, there are several ways to implement all these requirements.

Select File > Options > Additionally.

In a group Show options for next sheet select a sheet, and then do one of the following:

To display zero (0) values in cells, select the check box Show zeros in cells that contain zero values.

To display zero values as empty cells, clear the check box Show zeros in cells that contain zero values.

You can press the keys CTRL+1 or on the tab Home click Format > Format Cells.

In the field Type enter 0;-0;;@

Notes:

Hidden values only appear in the formula bar or in a cell if you edit its contents. These values are not printed.

To show hidden values again, select the cells, and then press CTRL+1 or on the tab Home in the group Cells hover over element Format and select Cell Format. To apply the default number format, in the list Category select General. To display the date and time again, select the appropriate date and time format from the Number.

Select the cell that contains the zero (0) value.

On the tab Home in the group Styles click the arrow next to the element Conditional Formatting, hover over element Cell selection rules and select an option Equals.

In the left field, enter 0 .

In the right field, select Custom Format.

In the dialog box Cell Format open the tab Font.

In the field Color choose white color.

To perform this task, use the function IF.

Example

Copying an example

IF function.

Hiding null values in a PivotTable report

On the tab Options in the group PivotTable Options click the arrow next to the command Options and select Options.

Go to the tab Layout and format, and then do the following:

Change the way errors are displayed. In the field Format check the box For errors display. Enter in the field the value that you want to display instead of errors. To display errors as blank cells, remove all text from the field.

Change the way empty cells are displayed. Check the box Display for empty cells. Enter in the field the value that you want to display in empty cells. To keep them blank, remove all text from the field. To display zero values, clear this check box.

Show or hide all null values in a worksheet

Hiding zero values in selected cells using number format

These steps allow you to hide zero values in selected cells. If the value in one of the cells becomes non-zero, its format will be the same as the general number format.

Select the cells that contain zero (0) values that you want to hide.

You can press the keys CTRL+1 or on the tab Home in the group Cells click Format > Format Cells.

In the field Type enter 0;-0;;@

Notes:

Hiding null values returned by a formula using conditional formatting

Select the cell that contains the zero (0) value.

On the tab Home in the group Styles click the arrow next to the button Conditional Formatting and choose Cell Highlight Rules > Equals.

In the left field, enter 0 .

In the right field, select Custom Format.

In the dialog box Cell Format open the tab Font.

In the field Color choose white color.

Use a formula to show zeros as spaces or dashes

To accomplish this task, use the IF function.

Example

To make this example easier to understand, copy it to blank sheet.

Copying an example

Highlight the example provided in this article.

Important: Do not highlight row or column headings.

Highlighting an example in help

Click CTRL keys+C.

In Excel, create a blank workbook or sheet.

Select cell A1 on the sheet and press CTRL+V.

Important: For the example to work correctly, it must be inserted into cell A1.

To switch between viewing the results and viewing the formulas that return those results, press CTRL+` (accent mark) or on the tab Formulas In the Formula Dependencies group, click Show formulas.

By copying the example onto a blank sheet, you can customize it to suit your needs.

For more information about using this function, see the article IF Function.

Hiding null values in a PivotTable report

Click the PivotTable report.

On the tab Options in the group PivotTable Options click the arrow next to the command Options and select Options.

Go to the tab Layout and format, and then do the following: