Problems with formulas in an Excel spreadsheet

Excel features allow you to perform calculations of almost any complexity thanks to formulas and functions. However, sometimes users may encounter the fact that the formula refuses to work or gives an error instead of the desired result. In this article, we will look at why this happens, and what actions to take to solve the problem.

Solution 1: Change cell format

Very often, Excel refuses to perform calculations due to the fact that the wrong cell format is selected.

For example, if a text format is specified, then instead of the result we will see just the formula itself in the form of plain text.

In some situations, when the wrong format is selected, the result can be calculated, but it will be displayed in a completely different way than we would like.

Obviously, the cell format needs to be changed, and this is done as follows:

1. To determine the current cell format (range of cells), select it and, being in the tab “Home”, pay attention to the group of tools “Number”. There is a special field here that shows the format currently in use.
2. You can choose another format from the list that will open after we click on the down arrow next to the current value.

Cell format can be changed using another tool that allows you to set more advanced settings.

1. Having selected a cell (or having selected a range of cells), right-click on it and in the list that opens, click on the command “Cell Format”. Or instead, after selection, press the combination Ctrl + 1.
2. In the window that opens, we will find ourselves in the tab “Number”. Here in the list on the left are all the available formats that we can choose from. On the left side, the settings of the selected option are displayed, which we can change at our discretion. Press when ready OK.
3. In order for the changes to be reflected in the table, we activate the editing mode one by one for all cells in which the formula did not work. Having selected the desired element, you can proceed to editing by pressing the key F2, by double-clicking on it, or by clicking inside the formula bar. After that, without changing anything, click Enter.

Note: If there is too much data, it will take a long time to manually complete the last step. In this case, you can do otherwise – use fill marker. But this one only works if the same formula is used in all cells.

1. We perform the last step only for the topmost cell. Then we move the mouse pointer to its lower right corner, as soon as a black plus sign appears, hold down the left mouse button and drag it to the end of the table.
2. We get a column with the results calculated using formulas.

Solution 2: turn off the “Show formulas” mode

When we see the formulas themselves instead of the results, this may be due to the fact that the formula display mode is activated, and it needs to be turned off.

1. Switch to tab “Formulas”. In the tool group “Formula dependency” click on the button “Show Formulas”if it is active.
2. As a result, cells with formulas will now display the results of calculations. True, because of this, the boundaries of the columns may change, but this is fixable.

Solution 3: Activate automatic recalculation of formulas

Sometimes a situation may arise when the formula has calculated some result, however, if we decide to change the value in one of the cells to which the formula refers, the recalculation will not be performed. This is fixed in the program options.

1. Go to the menu “file”.
2. Select a section from the list on the left “Parameters”.
3. In the window that appears, switch to the subsection “Formulas”. On the right side of the window in the group “Computation Options” check the box next to the option “automatically”if another option is selected. Click when ready OK.
4. Everything is ready, from now on all formula results will be recalculated automatically.

Solution 4: fixing errors in the formula

If errors are made in the formula, the program may perceive it as a simple text value, therefore, calculations on it will not be performed. For example, one of the most popular errors is a space placed before the sign “equals”. At the same time, remember that the sign “=” must always come before any formula.

Also, quite often errors are made in function syntaxes, since filling them out is not always easy, especially when multiple arguments are used. Therefore, we recommend using Function wizard to insert a function into a cell.

To make the formula work, all you need to do is carefully check it and correct any errors found. In our case, you just need to remove the space at the very beginning, which is not needed.

Sometimes it’s easier to delete a formula and write it again than to try to look for an error in an already written one. The same goes for functions and their arguments.

Common mistakes

In some cases, when the user made a mistake when entering a formula, the following values ​​may be displayed in the cell:

• #DIV/0! is the result of division by zero;
• #N/A – input of invalid values;
• #NUMBER! – incorrect numerical value;
• #VALUE! – the wrong kind of argument is used in the function;
• #EMPTY! – wrong range address;
• #LINK! – the cell referred to by the formula has been deleted;
• #NAME? – invalid name in the formula.

If we see one of the above errors, first of all we check if all the data in the cells participating in the formula is filled in correctly. Then we check the formula itself and the presence of errors in it, including those that contradict the laws of mathematics. For example, division by zero is not allowed (error #DEL/0!).

In cases where you have to deal with complex functions that refer to many cells, you can use the validation tools.

1. We mark the cell containing the error. In the tab “Formulas” in the tool group “Formula dependencies” press the button “Calculate formula”.
2. In the window that opens, step-by-step information on the calculation will be displayed. To do this, press the button “Compute” (each press advances to the next step).
3. Thus, you can track each step, find the error and fix it.

You can also use the useful a tool “Error Checking”, which is located in the same block.

A window will open in which the cause of the error will be described, as well as a number of actions regarding it, incl. formula bar fix.

Conclusion

Working with formulas and functions is one of the main features of Excel, and, of course, one of the main areas of use of the program. Therefore, it is very important to know what problems can arise when working with formulas, and how they can be corrected.