Check error excel - Исправление ошибок и поиск оптимальных решений проблем

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More…Less

Formulas can sometimes result in error values in addition to returning unintended results. The following are some tools that you can use to find and investigate the causes of these errors and determine solutions.

Note: This topic contains techniques that can help you correct formula errors. It is not an exhaustive list of methods for correcting every possible formula error. For help on specific errors, you can search for questions like yours in the Excel Community Forum, or post one of your own.

Learn how to enter a simple formula

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula adds 3 to 1.

=3+1

A formula can also contain any or all of the following: functions, references, operators, and constants.

Parts of a formula

Functions: included with Excel, functions are engineered formulas that carry out specific calculations. For example, the PI() function returns the value of pi: 3.142…
References: refer to individual cells or ranges of cells. A2 returns the value in cell A2.
Constants: numbers or text values entered directly into a formula, such as 2.
Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies. Use + and – add and subtract values, and / to divide.

Note: Some functions require what are referred to as arguments. Arguments are the values that certain functions use to perform their calculations. When required, arguments are placed between the function’s parentheses (). The PI function does not require any arguments, which is why it’s blank. Some functions require one or more arguments, and can leave room for additional arguments. You need to use a comma to separate arguments, or a semi-colon (;) depending on your location settings.

The SUM function for example, requires only one argument, but can accommodate 255 total arguments.

=SUM(A1:A10) is an example of a single argument.

=SUM(A1:A10, C1:C10) is an example of multiple arguments.

The following table summarizes some of the most common errors that a user can make when entering a formula, and explains how to correct them.

Make sure that you	More information
Start every function with the equal sign (=)	If you omit the equal sign, what you type may be displayed as text or as a date. For example, if you type SUM(A1:A10), Excel displays the text string SUM(A1:A10) and does not perform the calculation. If you type 11/2, Excel displays the date 2-Nov (assuming the cell format is General) instead of dividing 11 by 2.
Match all open and closing parentheses	Make sure that all parentheses are part of a matching pair (opening and closing). When you use a function in a formula, it is important for each parenthesis to be in its correct position for the function to work correctly. For example, the formula *=IF(B5<0),»Not valid»,B51.05) will not work because there are two closing parentheses and only one open parenthesis, when there should only be one each. The formula should look like this: =IF(B5<0,»Not valid»,B51.05)*.
Use a colon to indicate a range	When you refer to a range of cells, use a colon (:) to separate the reference to the first cell in the range and the reference to the last cell in the range. For example, =SUM(A1:A5), not =SUM(A1 A5), which would return a #NULL! Error.
Enter all required arguments	Some functions have required arguments. Also, make sure that you have not entered too many arguments.
Enter the correct type of arguments	Some functions, such as SUM, require numerical arguments. Other functions, such as REPLACE, require a text value for at least one of their arguments. If you use the wrong type of data as an argument, Excel may return unexpected results or display an error.
Nest no more than 64 functions	You can enter, or nest, no more than 64 levels of functions within a function.
Enclose other sheet names in single quotation marks	If a formula refers to values or cells on other worksheets or workbooks, and the name of the other workbook or worksheet contains spaces or non-alphabetical characters, you must enclose its name within single quotation marks ( ‘ ), like =’Quarterly Data’!D3, or =‘123’!A1.
Place an exclamation point (!) after a worksheet name when you refer to it in a formula	For example, to return the value from cell D3 in a worksheet named Quarterly Data in the same workbook, use this formula: =’Quarterly Data’!D3.
Include the path to external workbooks	Make sure that each external reference contains a workbook name and the path to the workbook. A reference to a workbook includes the name of the workbook and must be enclosed in brackets ([Workbookname.xlsx]). The reference must also contain the name of the worksheet in the workbook. If the workbook that you want to refer to is not open in Excel, you can still include a reference to it in a formula. You provide the full path to the file, such as in the following example: =ROWS(‘C:My Documents[Q2 Operations.xlsx]Sales’!A1:A8). This formula returns the number of rows in the range that includes cells A1 through A8 in the other workbook (8). Note: If the full path contains space characters, as does the preceding example, you must enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point).
Enter numbers without formatting	Do not format numbers when you enter them in formulas. For example, if the value that you want to enter is $1,000, enter 1000 in the formula. If you enter a comma as part of a number, Excel treats it as a separator character. If you want numbers displayed so that they show thousands or millions separators, or currency symbols, format the cells after you enter the numbers. For example, if you want to add 3100 to the value in cell A3, and you enter the formula =SUM(3,100,A3), Excel adds the numbers 3 and 100 and then adds that total to the value from A3, instead of adding 3100 to A3 which would be =SUM(3100,A3). Or, if you enter the formula =ABS(-2,134), Excel displays an error because the ABS function accepts only one argument: =ABS(-2134).

You can implement certain rules to check for errors in formulas. These rules do not guarantee that your worksheet is error free, but they can go a long way toward finding common mistakes. You can turn any of these rules on or off individually.

Errors can be marked and corrected in two ways: one error at a time (like a spell checker), or immediately when they occur on the worksheet as you enter data.

You can resolve an error by using the options that Excel displays, or you can ignore the error by clicking Ignore Error. If you ignore an error in a particular cell, the error in that cell does not appear in further error checks. However, you can reset all previously ignored errors so that they appear again.

For Excel on Windows, Click File > Options > Formulas, or
for Excel on Mac, click the Excel menu > Preferences > Error Checking.

In Excel 2007, click the Microsoft Office button > Excel Options > Formulas.
Under Error Checking, check Enable background error checking. Any error that is found, will be marked with a triangle in the top-left corner of the cell.
To change the color of the triangle that marks where an error occurs, in the Indicate errors using this color box, select the color that you want.
Under Excel checking rules, select or clear the check boxes of any of the following rules:
- Cells containing formulas that result in an error: A formula does not use the expected syntax, arguments, or data types. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each of these error values have different causes and are resolved in different ways.
  
  Note: If you enter an error value directly in a cell, it is stored as that error value but is not marked as an error. However, if a formula in another cell refers to that cell, the formula returns the error value from that cell.
- Inconsistent calculated column formula in tables: A calculated column can include individual formulas that are different from the master column formula, which creates an exception. Calculated column exceptions are created when you do any of the following:
  - Type data other than a formula in a calculated column cell.
  - Type a formula in a calculated column cell, and then use Ctrl +Z or click Undo on the Quick Access Toolbar.
  - Type a new formula in a calculated column that already contains one or more exceptions.
  - Copy data into the calculated column that does not match the calculated column formula. If the copied data contains a formula, this formula overwrites the data in the calculated column.
  - Move or delete a cell on another worksheet area that is referenced by one of the rows in a calculated column.
- Cells containing years represented as 2 digits: The cell contains a text date that can be misinterpreted as the wrong century when it is used in formulas. For example, the date in the formula =YEAR(«1/1/31») could be 1931 or 2031. Use this rule to check for ambiguous text dates.
- Numbers formatted as text or preceded by an apostrophe: The cell contains numbers stored as text. This typically occurs when data is imported from other sources. Numbers that are stored as text can cause unexpected sorting results, so it is best to convert them to numbers. ‘=SUM(A1:A10) is seen as text.
- Formulas inconsistent with other formulas in the region: The formula does not match the pattern of other formulas near it. In many cases, formulas that are adjacent to other formulas differ only in the references used. In the following example of four adjacent formulas, Excel displays an error next to the formula =SUM(A10:C10) in cell D4 because the adjacent formulas increment by one row, and that one increments by 8 rows — Excel expects the formula =SUM(A4:C4).
  
  If the references that are used in a formula are not consistent with those in the adjacent formulas, Excel displays an error.
- Formulas which omit cells in a region: A formula may not automatically include references to data that you insert between the original range of data and the cell that contains the formula. This rule compares the reference in a formula against the actual range of cells that is adjacent to the cell that contains the formula. If the adjacent cells contain additional values and are not blank, Excel displays an error next to the formula.
  
  For example, Excel inserts an error next to the formula =SUM(D2:D4) when this rule is applied, because cells D5, D6, and D7 are adjacent to the cells that are referenced in the formula and the cell that contains the formula (D8), and those cells contain data that should have been referenced in the formula.
- Unlocked cells containing formulas: The formula is not locked for protection. By default, all cells on a worksheet are locked so they can’t be changed when the worksheet is protected. This can help avoid inadvertent mistakes like accidentally deleting or altering formulas. This error indicates that the cell has been set to be unlocked, but the sheet has not been protected. Check to make sure that you do not want the cell locked or not.
- Formulas referring to empty cells: The formula contains a reference to an empty cell. This can cause unintended results, as shown in the following example.
  
  Suppose you want to calculate the average of the numbers in the following column of cells. If the third cell is blank, it is not included in the calculation and the result is 22.75. If the third cell contains 0, the result is 18.2.
- Data entered in a table is invalid: There is a validation error in a table. Check the validation setting for the cell by going to the Data tab > Data Tools group > Data Validation.

Select the worksheet you want to check for errors.
If the worksheet is manually calculated, press F9 to recalculate.

If the Error Checking dialog is not displayed, then click on the Formulas tab > Formula Auditing > Error Checking button.
If you have previously ignored any errors, you can check for those errors again by doing the following: click File > Options > Formulas. For Excel on Mac, click the Excel menu > Preferences > Error Checking.

In the Error Checking section, click Reset Ignored Errors > OK.

Note: Resetting ignored errors resets all errors in all sheets in the active workbook.

Tip: It might help if you move the Error Checking dialog box just below the formula bar.
Click one of the action buttons in the right side of the dialog box. The available actions differ for each type of error.
Click Next.

Note: If you click Ignore Error, the error is marked to be ignored for each consecutive check.

Next to the cell, click the Error Checking button that appears, and then click the option you want. The available commands differ for each type of error, and the first entry describes the error.

If you click Ignore Error, the error is marked to be ignored for each consecutive check.

If a formula cannot correctly evaluate a result, Excel displays an error value, such as #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each error type has different causes, and different solutions.

The following table contains links to articles that describe these errors in detail, and a brief description to get you started.

Topic	Description
Correct a #### error	Excel displays this error when a column is not wide enough to display all the characters in a cell, or a cell contains negative date or time values. For example, a formula that subtracts a date in the future from a date in the past, such as =06/15/2008-07/01/2008, results in a negative date value. Tip: Try to auto-fit the cell by double-clicking between the column headers. If ### is displayed because Excel can’t display all of the characters this will correct it.
Correct a #DIV/0! error	Excel displays this error when a number is divided either by zero (0) or by a cell that contains no value. Tip: Add an error handler like in the following example, which is =IF(C2,B2/C2,0)
Correct a #N/A error	Excel displays this error when a value is not available to a function or formula. If you’re using a function like VLOOKUP, does what you’re trying to lookup have a match in the lookup range? Most often it doesn’t. Try using IFERROR to suppress the #N/A. In this case you could use: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
Correct a #NAME? error	This error is displayed when Excel does not recognize text in a formula. For example, a range name or the name of a function may be spelled incorrectly. Note: If you’re using a function, make sure the function name is spelled correctly. In this case SUM is spelled incorrectly. Remove the “e” and Excel will correct it.
Correct a #NULL! error	Excel displays this error when you specify an intersection of two areas that do not intersect (cross). The intersection operator is a space character that separates references in a formula. Note: Make sure your ranges are correctly separated — the areas C2:C3 and E4:E6 do not intersect, so entering the formula =SUM(C2:C3 E4:E6) returns the #NULL! error. Putting a comma between the C and E ranges will correct it =SUM(C2:C3,E4:E6)
Correct a #NUM! error	Excel displays this error when a formula or function contains invalid numeric values. Are you using a function that iterates, such as IRR or RATE? If so, the #NUM! error is probably because the function can’t find a result. Refer to the help topic for resolution steps.
Correct a #REF! error	Excel displays this error when a cell reference is not valid. For example, you may have deleted cells that were referred to by other formulas, or you may have pasted cells that you moved on top of cells that were referred to by other formulas. Did you accidentally delete a row or column? We deleted column B in this formula, =SUM(A2,B2,C2), and look what happened. Either use Undo (Ctrl+Z) to undo the deletion, rebuild the formula, or use a continuous range reference like this: =SUM(A2:C2), which would have automatically updated when column B was deleted.
Correct a #VALUE! error	Excel can display this error if your formula includes cells that contain different data types. Are you using Math operators (+, -, *, /, ^) with different data types? If so, try using a function instead. In this case =SUM(F2:F5) would correct the problem.

When cells are not visible on a worksheet, you can watch those cells and their formulas in the Watch Window toolbar. The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets. By using the Watch Window, you don’t need to repeatedly scroll or go to different parts of your worksheet.

This toolbar can be moved or docked like any other toolbar. For example, you can dock it on the bottom of the window. The toolbar keeps track of the following cell properties: 1) Workbook, 2) Sheet, 3) Name (if the cell has a corresponding Named Range), 4) Cell address, 5) Value, and 6) Formula.

Note: You can have only one watch per cell.

Add cells to the Watch Window

Select the cells that you want to watch.

To select all cells on a worksheet with formulas, on the Home tab, in the Editing group, click Find & Select (or you can use Ctrl+G, or Control+G on the Mac)> Go To Special > Formulas.
On the Formulas tab, in the Formula Auditing group, click Watch Window.
Click Add Watch.
Confirm that you have selected all of the cells you want to watch and click Add.
To change the width of a Watch Window column, drag the boundary on the right side of the column heading.
To display the cell that an entry in Watch Window toolbar refers to, double-click the entry.

Note: Cells that have external references to other workbooks are displayed in the Watch Window toolbar only when the other workbooks are open.

Remove cells from the Watch Window

If the Watch Window toolbar is not displayed, on the Formulas tab, in the Formula Auditing group, click Watch Window.
Select the cells that you want to remove.

To select multiple cells, press CTRL and then click the cells.
Click Delete Watch.

Sometimes, understanding how a nested formula calculates the final result is difficult because there are several intermediate calculations and logical tests. However, by using the Evaluate Formula dialog box, you can see the different parts of a nested formula evaluated in the order the formula is calculated. For example, the formula =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)is easier to understand when you can see the following intermediate results:

In the Evaluate Formula dialog box	Description
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	The nested formula is initially displayed. The AVERAGE function and the SUM function are nested within the IF function. The cell range D2:D5 contains the values 55, 35, 45, and 25, and so the result of the AVERAGE(D2:D5) function is 40.
=IF(40>50,SUM(E2:E5),0)	The cell range D2:D5 contains the values 55, 35, 45, and 25, and so the result of the AVERAGE(D2:D5) function is 40.
=IF(False,SUM(E2:E5),0)	Because 40 is not greater than 50, the expression in the first argument of the IF function (the logical_test argument) is False. The IF function returns the value of the third argument (the value_if_false argument). The SUM function is not evaluated because it is the second argument to the IF function (value_if_true argument), and it is returned only when the expression is True.

Select the cell that you want to evaluate. Only one cell can be evaluated at a time.
Select the Formulas tab > Formula Auditing > Evaluate Formula.
Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.

The Step In button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.
Continue clicking Evaluate until each part of the formula has been evaluated.
To see the evaluation again, click Restart.
To end the evaluation, click Close.

Notes:

Some parts of formulas that use the IF and CHOOSE functions are not evaluated — in these cases, #N/A is displayed in the Evaluation box.
If a reference is blank, a zero value (0) is displayed in the Evaluation box.
The following functions are recalculated each time the worksheet changes, and can cause the Evaluate Formula dialog box to give results different from what appears in the cell: RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, RANDBETWEEN.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

How to Use Error Checking

1. With the file that contains the errors open in Excel, in the Ribbon, select Formula > Formula Auditing > Error Checking.

2. In the Error Checking dialog box, click Show Calculation Steps.

Click on the small green triangle in the left-hand side of the cell that contains the error, and select Show Calculation Steps.

3. Click Evaluate to evaluate the error.

4. Keep clicking evaluate until you get the message: “The next evaluation will result in an error.”

The error is shown in the formula in the dialog box.

5. Click Close, and then (1) click Next to move to the next error. Then (2) click Trace to trace the error.

Excel will highlight the error with tracing arrows allowing you to see where the error is originating from. In this case, the #VALUE in the error is coming from the #VALUE in the previous error but the formula in the formula bar looks fine.

6. Click (1) Previous to move to the previous error. The cell pointer will in this case (2) move to E15. The formula in E15 is shown (3) in the formula bar. Here, you can see that the formula is multiplying a value (e.g., in D15) with text (e.g., in A3).

7. Click Edit in Formula bar.

8. Amend the formula as appropriate, and then click Resume.

If all the errors in the worksheet are fixed, Error Checking will stop.

This example showed #VALUE errors, but Error Checking will find all # errors. There is a different option for circular references.

Error Checking in Google Sheets

Error checking is automatic in Google Sheets. As soon as you have a cell that contains an error, it will show up on the screen with details about the error.

Источник

Если ваши таблицы содержат много формул, вам, вероятно, будет полезно воспользоваться преимуществами автоматической проверки ошибок. Вы можете включить эту функцию в разделе Формулы диалогового окна Параметры Excel (рис. 195.1).

Чтобы открыть это окно, выберите команду Файл ► Параметры. Включить или выключить проверку ошибок можно с помощью флажка Включить фоновый поиск ошибок. Кроме того, у вас есть возможность указать, какие типы ошибок следует проверять, устанавливая флажки в области Правила контроля ошибок.

Рис. 195.1. Excel может проверять ваши формулы на наличие возможных ошибок

Когда проверка ошибок включена, Excel постоянно оценивает таблицы, в том числе и содержащиеся в них формулы. Если возможная ошибка определена, то Excel добавит маленький треугольник в верхнем левом углу ячейки. Когда ячейка активизируется, появится смарт-тег. Щелчок на нем предоставит вам несколько команд на выбор. На рис. 195.2 показаны команды, которые появляются при выборе смарт-тега в ячейке, содержащей ошибку #ДЕЛ/0!. Команды различаются в зависимости от типа ошибки.

Во многих случаях вы просто игнорируете ошибку, выбрав команду Пропустить ошибку, предотвращающую ячейку от последующих проверок на ошибки. Однако все ранее проигнорированные ошибки можно сбросить, и тогда они появляются снова (нажмите кнопку Сброс пропущенных ошибок в разделе Формулы диалогового окна Параметры Excel).

Рис. 195.2. Щелчок на смарт-теге ошибки дает вам список команд

Даже если вы не используете функцию автоматической проверки ошибок, вы можете выбрать команду Формулы ► Зависимости формул ► Проверка наличия ошибок, чтобы открыть диалоговое окно, которое последовательно показывает каждую возможную ошибку ячейки, что во многом схоже с использованием функции проверки орфографии. На рис. 195.3 вы можете видеть диалоговое окно Контроль ошибок. Обратите внимание, что это немодальное диалоговое окно, и у вас остается доступ к листу, когда оно открыто.

Рис. 195.3. Использование окна Контроль ошибок для переключения между возможными ошибками, выявленными Excel

Помните, что возможность проверки ошибок несовершенна. На самом деле она даже не близка к совершенству. Другими словами, вы не можете считать, что ваш лист не содержит ошибок только потому, что Excel не выявил их! Не забывайте также, что эта возможность проверки ошибок не находит распространенный тип ошибок — перезапись ячейки с формулой другим значением.

Источник

Turn Off or Customize Error Checking Options (and Markers) in Excel

by Avantix Learning Team | Updated May 26, 2022

Applies to: Microsoft^® Excel^® 2010, 2013, 2016, 2019, 2021 and 365 (Windows)

In Microsoft Excel, errors are flagged by default with a small green marker or triangle in the upper left corner of the cell. However, these indicators display when there may be an error but is, in fact, not an error. Excel checks the cells against the error checking settings or rules in the Excel Options dialog box. You can turn error checking off completely or set the rules you prefer in the Options dialog box. The default settings can cause some irritating behaviour (like flagging cells with formulas referring to empty cells).

Recommended article: 8 Tips for Entering Excel Formulas

Do you want to learn more about Excel? Check out our virtual classroom or in-person classroom Excel courses >

Changing error checking options or rules

To turn off or change error checking options or rules:

Click the File tab in the Ribbon.
Select Options. A dialog box appears.
Click Formulas in the left pane.
In the right pane, select the desired options under Error Checking or Error checking rules and click OK.

Below is the Excel Options dialog box:

Changing the error marker color

To change the error marker color, in the Excel Options dialog box (Formulas category):

Beside Indicate errors using this color, select an alternate color from the color menu.
Click OK.

Turning off error checking

To turn off error checking completely in the the Excel Options dialog box (Formulas category):

Deselect (uncheck) the Enable background error checking.
Click OK.

Selecting error checking rules

You can select or deselect (check or uncheck) the following rules in the Error checking rules area in the Excel Options dialog (Formulas category):

Cells containing formulas that result in an error – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells that return error values.
Inconsistent calculated column formula in tables – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells in columns in tables that are different from other formulas in the column.
Cells containing years represented as 2 digits – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells that contain dates entered as text with only the last two digits of the year.
Numbers formatted as text or preceded by an apostrophe – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells that contain numbers entered as text (or preceded by an apostrophe).
Formulas inconsistent with other formulas in region – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells in which the formula is inconsistent from the others in the same area of the worksheet.
Formulas which omit cells in a region – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells in which a formula omits cells from the range referenced in the formula.
Unlocked cells containing formulas – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells containing a formula which is unlocked when the worksheet is protected.
Formulas referring to empty cells – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells with formulas referring to blank cells.
Data entered in a table is invalid – when checked, Excel displays an error indicator and adds the alert options Smart Tag to all cells with formulas in which data validation has been applied and that contain values considered to be invalid.

Resetting ignored errors

If a user has clicked the Smart Tag in a cell and selected Ignore Error for specific cells, this can be reset:

In the Excel Options dialog box (Formulas category), click Reset Ignored Errors. This does not reset rules you have unchecked.
Click OK.

Error checking markers can be very irritating and it’s simple to control Excel’s error checking behavior.

This article was first published on August 3, 2016 and has been updated for clarity and content.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Use Flash Fill in Excel (4 Ways)

How to Convert Text to Numbers in Excel (5 Ways)

How to Lock and Protect Excel Worksheets and Workbooks

10 Great Excel Navigation Shortcuts to Move Around in Workbooks

How to Replace Blank Cells in Excel with a Value from a Cell Above

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA) Macros

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you’d like to arrange custom instructor-led virtual classroom or onsite training on a date that’s convenient for you.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Источник

Чтобы сэкономить время на визуальный анализ больших таблиц с целью выявления ошибок, рационально применить формулы для определения их местонахождения. Например, будет весьма полезной информация о локализации первой возникшей ошибки относительно строк и столбцов листа.

Поиск ошибок в Excel формулой

Чтобы определить местонахождение ошибки в таблице с большим количеством строк и столбцов рекомендуем воспользоваться специальной формулой. Для примера покажем формулу, которая умеет легко работать с большими диапазонами ячеек, в пределах A1:Z100.

Для определения локализации первой ошибки на листе относительно строк следует использовать следующую формулу:

Данная формула должна выполняться в массиве, поэтом после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки, как на рисунке.

Таблица с большим объемом данных содержит ошибки, первая из которых находится в диапазоне третей строки листа 3:3.

Как получить адрес ячейки с ошибкой

Опираясь на результат вычисления этой формулы можно составить другую формулу, которая уже не просто определить строку или столбец, а укажет непосредственный адрес ошибки на листе Excel. Для решения данной задачи ниже (в ячейку AB3) введите другую формулу:

Данная формула так же должна выполняться в массиве, поэтом после ее ввода снова для подтверждения жмем комбинацию клавиш CTRL+SHIFT+Enter.

Результат вычисления локального адреса ячейки, которая содержит первую ошибку в таблице:

Принцип действия формулы для поиска ошибок:

В первом аргументе функции АДРЕС указываем номер строки, который должен быть возвращен в адресе ячейки содержащей результат действия целой формулы. Номер строки определен предыдущей формулой и является числом 3. Поэтому мы только ссылаемся на ячейку AB2 с первой формулой. Далее с помощью функции ДВССЫЛ определяется ссылка на диапазон, который должен быть найден в соответствии с местом нахождения ошибок. Нет необходимости выполнять поиск по целой таблице нагружая таким образом процессор компьютера излишне отнимая вычислительные ресурсы программы Excel. Нас интересует только третья строка.

С помощью функции ЕОШИБКА проверяется каждая ячейка в диапазоне A3:Z3 на наличие ошибок. На основании полученных результатов в памяти программы создается массив логических значений ИСТИНА и ЛОЖЬ. Следующая функция СТОЛБЕЦ возвращает в память программы второй массив из номеров столбцов с количеством элементов соответствующему количеству столбцов в диапазоне A3:Z3.

Скачать пример поиска ошибок в формулах Excel

Благодаря функции ЕСЛИ в первом массиве логическое значение ИСТИНА заменяется на соответственное числовое значение из второго массива. После чего функция МИН выбирает наименьшее числовое значение первого массива, которое соответствует номеру столбца содержащего первую ошибку. Так как били вычислены номер строки и столбца завершается вычисление формулы функцией АДРЕС. Она уже возвращает текстовым значением готовый адрес ячейки на основе номера столбца и строки указанных в ее аргументах.

Источник

Checking for errors in your Excel files is always a pain. It’s generally a very manual process that takes a lot of time, with a certain level of ambiguity regarding your return on investment – you could spend hours checking for issues in your Excel file, find nothing wrong, and your only tangible benefit is additional peace of mind. Additionally, when you’re building a huge model housing databases or working against a tough deadline, it’s tempting to just skip the error checking process altogether and not allocate time towards quality control.

One way to address the efficiency aspect of error checking is a greatly underutilized feature in Excel called the Inspect Workbook feature (also known as the Document Inspector).

Key Benefit

Inspect Workbook allows you to quickly check for a long list of issues that could potentially cause problems in your workbook. The feature is built into Excel versions 2007 and later and covers a wide range of items. Below is the full list of items that Inspect Workbook will check for:

Comments and Annotations
Document Properties and Personal Information
Data Model
Content Apps
Task Pane Apps
Pivotables, PivotChart, Cube Formulas, Slicers, and Timelines
Embedded Documents
Macros, Forms, and ActiveX Controls
Links to Other Files
Real Time Data Functions
Excel Surveys
Defined Scenarios
Active Filters
Customer Worksheet Properties
Hidden Names
Custom XML Data
Headers and Footers
Hidden Rows and Columns
Hidden Worksheets
Invisible Content

In terms of efficiency, the Inspect Workbook feature is extremely helpful because it can review each of these items in a single pass. You can imagine how long it would take to check your workbook for all of these items manually.

From a usage perspective, the best time to leverage this feature is when you inherit a document from someone else. Obviously if you’re just working in file by yourself, you’d probably know if you added macros or hid tabs in your workbook. Therefore, Inspect Workbook is probably not be that useful if you’re just working alone.

The Inspection Process

To initiate the Document Inspector, all you have to do is go to Excel’s File menu.

In that view, you should see the option to inspect your workbook in the middle of the screen.

From there, just click on the button for Check for Issues and then select Inspect Document.

Excel will remind you that it’s a good idea to save your file and give you the option to do so. Click Yes to save and proceed, or No if you don’t want to save your file at that moment.

Excel will bring up a menu asking you to confirm all the items you want to check for. You can generally just leave these all checked, as adding items doesn’t really make the inspection process take any longer.

Click the Inspect button once you’ve confirmed your inspection list.

Finally, Excel will return with a summary of all the items it found, and the option to remove some of these from your workbook.

Key Limitations

While Inspect Workbook is great for the identification of errors, it’s actually not that helpful in terms of actual error resolution. The best example of this is how it deals with hidden worksheets, shown below.

After running the Inspect Workbook tool, notice that Excel has identified that the worksheet I’m using has two hidden worksheets. From this interface, my only option is to delete these hidden worksheets without even looking at them. From an audit perspective, this would be a terrible idea. Those hidden tabs could be holding values and calculations that are critical to the rest of the file.

Ideally, what I’d want to do in this situation is unhide all tabs, inspect the ones that were hidden, and then decide if I actually want to delete them. Excel’s Document Inspector lacks that interface, so for now, you still have to go through that process manually.

Additionally, Inspect Workbook does not identify formula errors in your file. If you have reference errors or divide by zero errors showing up in your formulas, the tool does not have an option to call them out. Excel’s error checking feature will call these out in individual cells, but it’s not really linked to the Inspect Workbook feature.

Summary

The best time to use Inspect Workbook is when you’re given a huge file that you know nothing about, are expected to inherit and own the file going forward, and need to look for mousetraps that could cause mistakes and errors down the road. Depending on how and where you use Excel, you might use this feature often to manage handoffs. Other people who just work in Excel by themselves may not use it at all. At the end of the day, it’s important to be aware that this feature exists, so if you ever do need to audit a massive Excel file, you can save yourself a significant amount of time. Just note that, for the actual error resolution, you’ll still have to do most of that manually.

Источник

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Внесение даже небольшого изменения на рабочем листе Excel может привести к образованию ошибок в других ячейках. К примеру, вы можете случайно ввести значение в ячейку, которая раньше содержала формулу. Эта простая ошибка может оказать значительное влияние на другие формулы, и вы не сможете обнаружить ее, пока не сделаете какие-нибудь изменения на листе.

Ошибки в формулах делятся на несколько категорий:

Синтаксические ошибки: Возникают при неправильном синтаксисе формулы. Например, формула имеет несоответствующие скобки, или функция имеет не корректное количество аргументов.

Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.

Ошибки неправильных ссылок: Логика формул верна, но формула использует некорректную ссылку на ячейку. Простой пример, диапазон данных для суммирования в формуле СУММ может содержать не все элементы, которые вы хотите суммировать.

Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?

Циклические ошибки: Циклические ссылки возникают, когда формула ссылается на саму себя, прямо или косвенно. Циклические ссылки иногда могут быть полезны, но зачастую они указываю на наличие проблемы.

Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.

Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.

Проще всего найти и скорректировать синтаксические ошибки. Чаще всего, вы знаете, когда формула содержит синтаксическую ошибку. К примеру, Excel не даст ввести формулу с несогласованными скобками. Иные ситуации синтаксических ошибок приводят к выводу следующих ошибок в ячейке листа.

Ошибка #ДЕЛ/0!

Если вы создали формулу, в которой производится деление на ноль, Excel вернет ошибку #ДЕЛ/0!

Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).

Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!

Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:

Эта формула вернет пустое значение, если ячейка B4 будет пустой или содержать 0, в противном случае вы увидите посчитанное значение.

Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:

Ошибка #Н/Д

Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.

Обычно, ошибка #Н/Д возвращается в результате работы формул подстановки (ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС). В случае, когда совпадение не было найдено.

Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().

Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:

Ошибка #ИМЯ?

Excel может вернуть ошибку #ИМЯ? в следующих случаях:

Формула содержит неопределенный именованный диапазон
Формула содержит текст, который Excel интерпретирует как неопределенный именованный диапазон. Например, неправильно написанное имя функции вернет ошибку #ИМЯ?
Формула содержит текст не заключенный в кавычки
Формула содержит ссылку на диапазон, у которого отсутствует двоеточие между адресами ячеек
Формула использует функцию рабочего листа, которая была определена надстройкой, но надстройка не была установлена

Ошибка #ПУСТО!

Ошибка #ПУСТО! возникает в случае, когда формула пытается использовать пересечение двух диапазонов, которые фактически не пресекаются. Оператором пересечения в Excel является пробел. Следующая формула вернет #ПУСТО!, так как диапазоны не пересекаются.

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! будет возвращена в следующих случаях:

В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.

Ошибка #ССЫЛКА!

Ошибка #ССЫЛКА! возникает в случаях, когда формула использует недействительную ссылку на ячейку. Ошибка возникает в следующих ситуациях:

Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:

Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:

Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.

Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!

Ошибка #ЗНАЧ!

Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:

Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
Аргумент функции является диапазоном, когда он должен быть одним значением
Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива

Вам также могут быть интересны следующие статьи

5 комментариев

Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.

Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru

Людмила, есть вариант, что мастер диаграмм перестает быть доступен, если книга находится в общем доступе. Если это ваш случай, сделайте монопольный доступ в книге, вставляйте диаграммы и снова делайте книгу общей.

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:

Причем заметьте, что итоги в нашей таблице тоже уже не считаются — одна ошибка начинает порождать другие, передаваясь по цепочке от одной зависимой формулы к другой. Так что из-за одной ошибочной ячейки, в конце концов, может перестать работать весь расчет.

Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.

Синтаксис функции следующий:

=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )

Так, в нашем примере можно было бы все исправить так:

Все красиво и ошибок больше нет.

Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:

Такой вариант ощутимо медленне работает и сложнее для понимания, так что лучше использовать новую функцию ЕСЛИОШИБКА, если это возможно.

Какие существуют обозначения ошибок и способы их исправления?

Если формула содержит ошибку, то «Excel 2007» отобразит специальное сообщение об ошибке. При этом каждый тип ошибки обозначается своим сообщением, вызывается разными причинами и соответственно требует различных способов разрешения.

##### — Что обозначает и как исправить?

Эти символы сообщают, что столбец, содержащий числа, недостаточно широк для них, или же дата и время, введенные в ячейки данного столбца, содержат отрицательные числа.
В первом случае достаточно просто увеличить ширину столбца или изменить числовой формат данных (например, уменьшить число знаков после запятой).
Во втором же случае надо:

проверить формулу, если вычисляется число дней между двумя датами;
если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.

#ЗНАЧ! — Что обозначает и как исправить?

Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ! .

#ДЕЛ/0! — Что обозначает и как исправить?

Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.

В окне открытого листа выделите ячейку с данной ошибкой и нажмите клавишу F2.
Когда в ячейке отобразится сама формула или функция, а также станут выделенными все ячейки, связанные ссылками с данной формулой или ячейкой, внимательно проверьте значения в выделенных ячейках и при необходимости внесите коррективы в формулу или измените ссылки на пустые ячейки.
Нажмите Enter или кнопку «Ввод» на строке формул.

#ИМЯ? — Что обозначает и как исправить?

Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.

1 вариант

Если используется имя, которое не было определено, то необходимо сделать следующее:

В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.

Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».

2 вариант

Если существует ошибка в написании имени, то необходимо проверить его орфографию.

В окне открытого листа нажмите клавишу F3.
В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
Для закрепления нажмите клавишу Enter.

3 вариант

Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10) .

В окне открытого листа выделите ячейку с ошибкой в написании функции.
Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
В списке команд выберите пункт «Изменить в строке формул».
На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
Закрепите результат щелчком по клавише Enter.

4 вариант

Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

5 вариант

Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

6 вариант

Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).

#Н/Д — Что обозначает и как исправить?

Эти символы сообщают, что нужное значение не доступно для функции или формулы.

1 вариант

Если в формулу были введены недостающие данные, а также #Н/Д или НД() , то #Н/Д необходимо заменить новыми данными.

2 вариант

Если в функциях ГПР , ПРОСМОТР , ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.

3 вариант

Если не заданы необходимые аргументы стандартной функции листа, то нужно ввести все необходимые соответствующие аргументы функции.

4 вариант

Если в формуле используется недоступная в данный момент функция, то необходимо проверить, что книга, использующая функцию листа, открыта, а также то, что функция правильно работает.

5 вариант

Если для просмотра значений в несортированной таблице используются функции ВПР , ГПР или ПОИСКПОЗ , для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ .
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.

6 вариант

Если в формуле массива используется аргумент, не соответствующий диапазону, указанному в формуле массива, то необходимо проверить диапазон ссылок формулы на соответствие числу строк и столбцов или ввести формулу массива в меньшее число ячеек.

7 вариант

Если не заданы один или несколько необходимых аргументов стандартной или созданной функции листа, необходимо проверить и задать все необходимые аргументы функций.

#ССЫЛКА! — Что обозначает и как исправить?

Эти символы сообщают о неверной ссылке на ячейку.

1 вариант

Если ячейка, на которую ссылается формула, была удалена или же в данную ячейку помещено значение скопированных ячеек, то необходимо изменить формулу с учетом новых ссылок.

2 вариант

Если используется функция OLE, связанная с незапущенной программой, то необходимо запустить требуемую программу.

3 вариант

Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.

4 вариант

Если используется макрос, вызывающий макрофункцию, которая при определенных вариантах выдает значение #ССЫЛКА! . Необходимо проверить аргумент функции и удостовериться, что он ссылается на допустимые ячейки или диапазоны ячеек.

#ЧИСЛО! — Что обозначает и как исправить?

Это сообщение об использовании неправильных числовых значений в формуле или функции.

1 вариант

Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.

2 вариант

Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.

3 вариант

Если в результате вычисления формулы получается число, которое слишком велико или, наоборот, слишком мало, чтобы оно могло быть отображено в Excel, то необходимо изменить формулу и добиться, чтобы результат находился в диапазоне от 1*10307 до 1*10307.

#ПУСТО! — Что обозначает и как исправить?

Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.

1 вариант

Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:

для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например, СУММ(С1:С20) .
для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например, СУММ(С1:С20;D1:D20) .

2 вариант

Если указанные диапазоны не имеют общих ячеек, то необходимо изменить ссылки, добиваясь нужного пересечения.

Источник

Transcript

In this video, we’re going to look through the error-checking options for formulas in Excel.

When you use Excel frequently, you’re likely to see a number of potential errors flagged on the worksheet as you work.

Exactly which errors are flagged depends on which error checking rules are enabled.

To confirm, navigate to: options > formulas > error checking rules

Note that these are global settings. If you disable a rule here, it will stay disabled for all worksheets until reenabled.

When Excel flags an error on a worksheet, it will appear as a small triangle in the upper left of a cell.

It’s important to understand that this symbol doesn’t necessarily mean that there is a problem. Instead, this symbol means that there could be a problem.

When you click into one of these cells, you’ll see a small caution symbol, which is called a «Smart Tag». This icon provides a menu of options and information.

The first item in the menu tells you what kind of error is being flagged.

The second item offers help on the error.

The next item opens the formula evaluator, which you can use to evaluate the formula step-by-step.

The next option is to ignore this error. If you select this option you are telling Excel to ignore this error in this worksheet only. This preference will be remembered when you save and re-open the worksheet.

«Edit in formula bar» simply brings you into edit mode in the formula bar, as if you clicked into the formula bar manually.

Use the last menu item to navigate directly to error-checking options. If you’ve told Excel to ignore the error and want Excel to pay attention to it again, use the «reset ignored errors» button.

You can ask Excel to check for errors explicitly on the Formulas tab of the Ribbon.

This opens an error-checking window that allows you to navigate through the flagged errors. Excel will select the first error it finds and stops. The window displays information about the error, and options for resolving or ignoring the error, similar to what we saw with the Smart Tag.

When you click the next button below, Excel will select the next cell it finds with an error and update the error information if the error is different.

As before, if you choose to ignore a certain error, Excel won’t stop on that error the next time you check for errors. If you want Excel to flag the error again, use the «Reset ignored errors» button as before.

Источник

Learn how to enter a simple formula

Need more help?

See Also

Need more help?

Table of contents

Find and Handle Errors in Excel

Example #1 – Error Handling through Error Handler

Example #2 – Formulas Error Handling

Things to Remember

Recommended Articles

Background Error Checking

How to Use Error Checking

Error Checking in Google Sheets

Turn Off or Customize Error Checking Options (and Markers) in Excel

Changing error checking options or rules

Changing the error marker color

Turning off error checking

Selecting error checking rules

Resetting ignored errors

Subscribe to get more articles like this one

More resources

Related courses

Поиск ошибок в Excel формулой

Как получить адрес ячейки с ошибкой

Key Benefit

The Inspection Process

Key Limitations

Summary

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Ошибка #ДЕЛ/0!

Ошибка #Н/Д

Ошибка #ИМЯ?

Ошибка #ПУСТО!

Ошибка #ЧИСЛО!

Ошибка #ССЫЛКА!

Ошибка #ЗНАЧ!

Вам также могут быть интересны следующие статьи

5 комментариев

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Какие существуют обозначения ошибок и способы их исправления?

##### — Что обозначает и как исправить?

#ЗНАЧ! — Что обозначает и как исправить?

#ДЕЛ/0! — Что обозначает и как исправить?

#ИМЯ? — Что обозначает и как исправить?

1 вариант

2 вариант

3 вариант

4 вариант

5 вариант

6 вариант

#Н/Д — Что обозначает и как исправить?

1 вариант

2 вариант

3 вариант

4 вариант

5 вариант

6 вариант

7 вариант

#ССЫЛКА! — Что обозначает и как исправить?

1 вариант

2 вариант

3 вариант

4 вариант

#ЧИСЛО! — Что обозначает и как исправить?

1 вариант

2 вариант

3 вариант

#ПУСТО! — Что обозначает и как исправить?

1 вариант

2 вариант

Transcript