We solve problems with formulas in Excel. What if the formula doesn’t work or shows an error

With Excel, you can do almost anything you want: calculate and even program the actions of a computer. This is because functions and formulas are embedded in the functional. But sometimes the user has to deal with errors when entering them. Especially often they occur when a person tries to enter the formula manually. If the user is experienced, this problem is not very common, since he already knows which syntax is correct, and is also quite attentive. But even professionals can make mistakes.

Often we do not understand why they occur. And professionals, too, may not know what the reasons are. He can understand what this or that error code means, but he cannot find a specific place in the formula. But for a professional, this is not a problem, because he knows how to find errors using the methods of Excel itself. Today we will take a closer look at the causes of the most common errors when entering formulas in Excel and analyze the methods for solving this problem. Thus, we will be much closer to the professionals. Well, shall we begin?

Changing the Format of Excel Cells

A fairly common reason why a person fails when writing formulas is that he has incorrectly chosen the format of the cells to which she refers. So, if the “text” format was set in it, then instead of the final value, we will simply see the formula in the form of a text line.

There are cases when the format is considered correct, but displayed incorrectly. The reason for this phenomenon is also an erroneously chosen format. This problem can also appear by itself, through glitches in the program. Oddly enough, the human factor also affects the operation of a computer that is not a person. But this is already a lyrical digression.

Of course, in this case, you should set the correct format. For this you need to follow these steps:

1. Select the cell and go to the Home tab on the Ribbon. After that, move the mouse cursor to the “number” group. There is also a field that shows the format currently assigned to the cell.
2. You need to click on the down arrow next to this field. After that, a list with different cell formats will appear. You need to choose the one that matches the one that you want to apply in a particular formula. After that, click on the selected option.

There is another tool, the settings of which are wider. To apply it, you must follow the following instructions:

1. Determine if we need to change the format of one cell or several. After that, select the one that suits us (or select the whole range), after which we right-click on it (them). Next, look in the list that appears for the item “Format Cells”. The task can be greatly simplified if the user presses Ctrl+1.

2. After that, a dialog box appears, in which the “Number” tab will be open by default. It is important to make sure that it is open. After that, you should turn your attention to the list on the left side of the window. There is a list of available cell formats. You need to choose the one that suits your particular situation and click OK.

To save the received changes, you must use the editing function for each cell with an error when entering a formula. After we find the element that we need, we should edit it using the F2 key or double-click on the corresponding cell. You can also make changes by left-clicking in the formula bar. After making all changes to the document, you need to confirm your actions by pressing the Enter key.

Attention. If there is a lot of information, the last step can be very time consuming. In this case, you can make things easier by using an autocomplete token. True, this method can be used only in one case. The main requirement is that the same formula must be applied in each cell.

What should be done in practice?

1. Select the cell that is located at the very top of the range.
2. We hover over its lower right part (that is, at the corner), make sure that a black plus sign has appeared, press the left button, hold it down and move the cursor to the place where this range should end.

After performing these operations, we have a ready-made column in front of our eyes, which was generated almost without our participation.

Disable “Show formulas” mode in Excel

There are situations when the user does not find the results, although he expected them after performing the operations of the function. Instead, only formulas are displayed. The reason for this may lie in the fact that the demonstration of formulas is enabled, and naturally, we need to disable it in order to see the result of the calculations. We perform the following actions:

1. We find the “Formulas” tab in the main menu. A little lower and to the right of it is the Formula Dependency tool group, which contains the Show Formulas button. You need to make one left mouse click, provided that this function is activated.

2. After that, in each cell, you can now see the totals of the calculations. Naturally, this can affect the column borders, but there’s no super scary problem with that. This issue is solved in just a few clicks.

Activate automatic formula recalculation in Excel

From time to time there are situations when the result of the formula does not change if one of the cells whose data was used for the calculation changes values. To fix this problem, you need to use the settings menu. Follow these steps:

1. Go to the “File” menu.
2. After that, we look for the “Settings” section and go to it.
3. Next we will see a window. The list on the right contains a large number of different sections, but we are interested in “Formulas”. After clicking, we will see that the right side of the window has changed. Go to the “Calculation Options” group, where we put a checkmark next to the “automatic” function. After the steps are completed, we need to click the OK button.

Now you can rest, because the problem is solved. All calculations are carried out automatically. And if any of the values ​​suddenly changes, the program automatically recalculates it.

Correction of errors in formulas

There are situations when a person makes a mistake when entering a formula. In this case, the program will simply define it as a simple value in text format. That is, there will be no calculations either. So, the most common mistake is the situation when a space is placed before entering the equal sign. The important thing to remember here is that there must not be any spaces before the = sign, and it must always appear before the formula.

In addition, errors often appear in the syntax of functions. The reason for this is simple – not all of them are easy for ordinary people to fill out. This is especially true in situations where several arguments are applied at once. Therefore, manually entering formulas is recommended only if the user is experienced, in which case the syntax should be carefully rechecked. In order to make entering functions easier, the Function Wizard has been created, which is called by pressing the fx key to the left of the formula input line.

To ensure that the formula works, you must carefully check it before entering it. In particular, you need to make sure that there are no signs anywhere that are not visible to the naked eye. In our case, this character was a space.

From time to time it is better to delete a formula where there are some non-printable characters or its syntax is written frankly wrong than to look for an error and fix it. The same applies not only to the name of the formula, but also to its functions. The fact that an error was made when entering the formula, Excel will report a certain code. Each code has a specific meaning.. Let’s list the most common ones.

If at least one of these errors appears, then you need to check whether the information in the arguments of this function is filled in correctly. After that, the formula itself is checked and whether there are errors in it. It is especially necessary to focus on those of them that do not correspond to the laws of mathematics. For example, a very common problem is division by zero. Often it occurs by accident. Just as a result of calculating a certain value by a function that is part of the formula and serves as an argument for a function of a higher order, the number zero appears. As a result, the formula generates a #DIV/0 error.

There are also more difficult situations when it is impossible to say exactly where the error lies. If you have to deal with complex functions, then in this case you need to use special verification tools. Follow this instruction:

1. Select the cell that contains the wrong formula. Next, go to the “Formulas” tab. There you need to find the tool “Formula dependencies” – “Calculate formula”.
2. A dialog box will appear before the user’s eyes. It will describe in detail how to carry out the report correctly. To solve the problem, you must click the “Calculate” button. After that, the function will be sequentially checked at each step. Therefore, the button must be pressed several times. Thus, you can track at what step the problem arose and quickly solve it.

There is another way to determine the cause of problems when working with formulas. This tool is called “Error Checking”. It can be found in the same block as the previous one.

After we click on the corresponding button, a window will appear. It will contain information about what caused the error, as well as recommendations for correcting the current situation.

To fix the problem, you must click on the “Change in the formula bar” button. After that, the formula will work correctly.

Fixing a syntax error

I must say that all the features of correcting errors in the syntax were described above, because this is a special case of errors in formulas. However, let’s recap what has been said since syntax error is a very common problem for new Excel users. There are two specialized tools for checking syntax: formula checking and calculation. You can also use the manual verification method. When is the best time to use?

1. In general, if the formula is small, you can check it yourself. It won’t take much time. You can also easily check the formulas yourself if their number is small. The fact is that with an increase in the number of formulas, the number of errors during verification also increases. The brain is cluttered with a lot of information, albeit easy to process.
2. If there are a large number of formulas or the formulas themselves are quite large, it is better to use a specialized tool. How exactly – it’s up to you. Each of them is suitable, just one provides for the execution of actions more in manual mode, and the other – automatically.

It is highly recommended that you practice using these tools at your leisure so that you do not get lost in a real work situation. So, reports are often made in Excel that have their own deadlines. And if an error appears close to the deadline that cannot be quickly fixed, the user may experience a big panic and fill up the entire report. Therefore, all skills related to Excel must be brought to automatism before using them in practice.

Conclusions

Every person who uses spreadsheets has to deal with errors. This is an essential part of the workflow. Therefore, the ability to handle errors is an essential skill. The most common errors that users may encounter when entering formulas have been listed above. In addition, methods for solving emerging problems were described and situations were given in which it is necessary to use each of them.