Formula omits adjacent cells ошибка

This tutorial demonstrates how to get rid of the green (trace error) triangle in Excel.

If the data contained in a cell has an error as defined by Excel, and background error checking is switched on, then a small green triangle is displayed in the top-left corner of a cell. If you click in the cell, you get a list of suggested fixes. On some occasions, you may not want to change the data in your cell, but just to hide the green triangle and ignore the error.

Switch Off Background Error Checking

To stop the green triangle error from showing, you can switch off background error checking in Excel.

1. In the Ribbon, go to File > Options > Formulas > Error Checking.
2. Remove the tick from the Enable background error checking option, and then click OK.

Note: This removes background error checking for the active workbook and any workbooks opened in the future.

Error Checking Rules

Instead of turning background error checking completely off, you can switch off individual rules that are switched on by default in Excel. For example, in the data above, the numbers are formatted as text and therefore cannot be used in calculation formulas. The green triangle alerts to this and provides a list of possible solutions. You can switch off this specific error check.

1. In the Ribbon, select File > Options > Formulas > Error Checking.
2. In the Error checking rules section, remove the check mark from Numbers formatted as text or preceded by an apostrophe to stop Excel from checking for this specific error, and then click OK.

Ignore Error

It’s generally best to keep the background error checking switched on, as it is very useful in showing you if you have an error in the data or in a formula.

1. To remove the green triangles from cells where you are aware of the error – but do not want to see the triangle – select the cells, and then click on the little yellow exclamation mark icon.
2. Click Ignore Error to remove the green triangles, or if you want to solve the error, click Convert to Number, making the cell values suitable for use in calculations.

Use the Trace Error Drop Down

If your error has a different reason, for example a divide by zero formula error, then a different set of options is shown.

You can also ignore the error, or you can click Show Calculation Steps to resolve the error. If you click Edit in Formula Bar, Excel puts the formula into editing mode and place your mouse pointer in the formula bar so you can make changes to the formula.

Another reason for the error could be an inconsistent formula. This occurs when the formula in the cell is different to the formula that is in the cell above. If this is the case, a different set of options appears.

To solve this problem and remove the green triangle, you can click Copy Formula from Above or, if you do not want to change the formula, you can, once again, click Ignore Error.

Common Errors in Excel

A few more common Excel errors can cause the green triangle to show in a cell – all of which can be removed using the Ignore Error option, or can be solved using Excel’s error checking options.

Cell Reference Error

#REF – this error normally occurs when a formula is referring to a range that is cannot find – or if rows/columns have been removed.

Resting your mouse on the exclamation mark icon shows you the reason for error, while clicking on the drop down shows you a list of possible solutions.

Value Error

#VALUE – this error normally occurs when you are trying to include a cell that has text in it, in a calculation.

Adjacent Cells Error

Formula Omits Adjacent Cells – this error normally occurs when you have a function like the SUM Function and are not including all the possible cells in the calculation.