Lifehacker readers are already familiar with Denis Batyanov who shared with us. Today Denis will talk about how to avoid the most common problems with Excel, which we often create for ourselves.

I'll make a reservation right away that the article material is intended for beginner Excel users. Experienced users have already danced incendiaryly on this rake more than once, so my task is to protect young and inexperienced "dancers" from this.

You do not give titles to table columns

Many Excel tools, such as sorting, filtering, smart tables, pivot tables, assume that your data contains column headers. Otherwise, you will either not be able to use them at all, or they will not work quite correctly. Always make sure your tables contain column headings.

Empty columns and rows inside your tables

This is confusing for Excel. Upon encountering an empty row or column inside your table, it starts to think that you have 2 tables, not one. You will have to constantly correct it. Also, do not hide the rows / columns you do not need inside the table, it is better to delete them.

Several tables are located on one sheet

If these are not tiny tables containing reference values, then you should not do this.

It will be inconvenient for you to fully work with more than one table per sheet. For example, if one table is on the left and the second on the right, then filtering on one table will affect the other. If the tables are located one below the other, then it is impossible to use the pinning of areas, and also one of the tables will have to constantly search and perform unnecessary manipulations in order to stand on it with a table cursor. Do you need it?

Data of the same type is artificially arranged in different columns

Very often, users who know Excel rather superficially prefer this table format:

It would seem that we have before us a harmless format for accumulating information on the sales of agents and their fines. Such a table layout is well perceived by a person visually, since it is compact. However, believe me, it is a real nightmare to try to extract data from such tables and get subtotals (aggregate information).

The fact is that this format contains 2 dimensions: so that you have to decide on a row, going through a branch, a group and an agent. When you find the right stock, then you will have to search for the required column, since there are a lot of them here. And this "two-dimensionality" greatly complicates the work with such a table for standard Excel tools - formulas and pivot tables.

If you build a pivot table, you will find that there is no way to easily get the data by year or quarter, since the indicators are split into different fields. You do not have one sales field that can be conveniently manipulated, but there are 12 separate fields. You will have to manually create separate calculated fields for quarters and years, although if it were all in one column, the pivot table would do it for you.

If you want to apply standard summation formulas like SUMIF, SUMIFS, SUMPRODUCT, you will also find that they will not work effectively with this table layout.

Distribution of information on different sheets of the book "for convenience"

Another common mistake is, having some kind of standard table format and needing analytics based on this data, distribute it to separate sheets of the Excel workbook. For example, they often create separate sheets for each month or year. As a result, the amount of data analysis work is actually multiplied by the number of sheets created. Don't do that. Collect information on ONE sheet.

Information in comments

Often users add important information they may need in the cell comment. Keep in mind that what is in the comments, you can only look (if you find it). Pulling this out into the cell is difficult. I recommend it is better to have a separate column for comments.

A mess with formatting

Definitely won't add any good to your table. This looks off-putting to people using your spreadsheets. At best, they will not attach importance to this, at worst - they will think that you are not organized and sloppy in business. Strive for the following:

Merging cells

Use cell concatenation only when you can't do without it. Merged cells make it very difficult to manipulate the ranges they fall into. Problems occur when moving cells, inserting cells, etc.

Combine text and numbers in one cell

A painful impression is produced by a cell containing a number, padded at the back with the text constant "RUB." or "USD" entered manually. Especially if it's not printed form, a regular table... Arithmetic operations with such cells are naturally impossible.

Numbers as text in a cell

Avoid storing numeric data in a cell in text format. Over time, some of the cells in such a column will have a text format, and some in a regular format. There will be problems with formulas because of this.

If your table will be presented through an LCD projector

Choose the most contrasting combinations of color and background. A dark background and light letters look good on the projector. The most terrible impression is made by red on black and vice versa. This combination looks extremely low-contrast on the projector - avoid it.

Page mode worksheet in Excel

This is the same mode in which Excel shows how the sheet will be paginated when printed. Page borders are highlighted in blue. I do not recommend constantly working in this mode, which many do, since the printer driver is involved in the process of displaying data on the screen, and this, depending on many reasons (for example, a network printer and this moment unavailable) is fraught with freezes in the visualization process and recalculation of formulas. Work as usual.

Much more useful information you can learn about Excel at

Date: 24 December 2015 Category:

Errors in Excel are an indispensable companion for everyone. When the expression in a cell cannot be calculated, the program displays an error message in the cell. It starts with a "#" character followed by the name of the error. You don't need to be intimidated if you are familiar with Excel functions and know how to follow the simplest logic of mathematical operations - you can easily find and fix the error.

If the cell is completely filled with hash signs (#), this is not an error at all. There is not enough room in the cell to display the result. Increase the size of the cell or decrease the font so that the result can be displayed.

Types of errors

If an error still occurs, decryption will help in fixing it:

Error	Description
# DIV / 0!	An error occurs when trying to divide by zero
#NAME?	The program cannot recognize the entered name. For example, you misspelled the name of the function, or did not enclose the text string in quotes.
# N / A	No data available. For example, I did not find any values
# EMPTY!	You requested the intersection of ranges that do not overlap
#NUMBER!	The problem is one of the numeric values ​​used in the formula. For example, you are trying to square root of negative number... In classical mathematics, this operation is meaningless.
#LINK!	The formula contains a link that does not exist. For example, you deleted the cell it refers to
#VALUE!	Formula contains invalid components. Often this error occurs when the formula syntax is violated.

When a formula with an error in a cell, a marker appears next to it. By clicking on it, you can read the help for this error. And you can also see the stages of the calculation. Select this item, and the program will show a window where the place of the error will be underlined. it The best way determine where the error occurs.

In addition, you can do without fixing such errors, but simply them. But it should be appropriate. Errors should be circumvented only if they cannot be corrected. Otherwise, the calculation results may be distorted.

Tracking error through calculation steps

Cyclic links in Excel

Another type of error is circular reference. It occurs when you reference a cell whose value depends on the one in which you are writing the formula. For example, in a cage A1 the formula = A2 + 1 is written, and in A2 write = A1, a circular reference will appear, which will be recalculated indefinitely. In this case, the program warns about the appearance of a cyclic reference, stops the calculation of "looped formulas". A double-headed arrow appears on the left side of the cells. We'll have to correct the error and repeat the calculation.

Sometimes a complex "looping" happens when a circular reference is formed with several intermediate formulas.

To track down such errors, run Formulas - Formula Dependencies - Error Checking - Circular References... In the drop-down list, the program displays the addresses of the cells that create an infinite loop. All that remains is to correct the formulas in these cells.

Tracking circular links

In Excel, you can try to calculate the result of looped formulas. To do this, check the box File - Options - Formulas - Enable Iterative Calculations... In the same block, you can set maximum amount iterations (miscalculations) to find the balance and the margin of error. In most cases, you don't need to do this, so I recommend not installing this checkbox. Nevertheless, when you know that the looped formulas are correct and their calculation will lead to a stable result - why not do it?

That's all about the error types in Excel. In this short article, you've got enough information to deal with the most common errors in Excel by parsing the return value. Read the extended list of errors in! I am ready to answer your questions - write in the comments.

In the next article I will explain. Needless to say, Excel's functions are “our everything”?
I think no. Therefore, go ahead and read, this will be the first step into the world of complex formulas with the right results!

When creating complex formulas (and just inattention) in MS Excel error quite easy to accomplish. Usually MS Excel in such cases displays error messages or even offers the “correct” variant of writing a formula, in its opinion, however, even with the help system, at first it is rather difficult to understand what the “stupid program” wants from us. In this article, we will look at all types of errors that occur in MS Excel formulas, and learn how to correct and understand them.

Error #VALUE! (error in value)

If there were "top MS Excel errors", the first place in it would belong to the error #VALUE!... As you might guess from the name, it occurs when an incorrect value is substituted into a formula or function. If you are trying to perform arithmetic operations on text, or if you substitute a range of cells into a function when you only need to specify one cell, the calculation will result in a #VALUE! Error.

As it was said - an attempt to add a number and a text confuses MS Excel

Error # LINK! (wrong cell reference)

One of the most common calculation errors. Indicates the simplest thing - the formula uses a reference to a cell that you either did not create or accidentally deleted. Often #LINK! occurs when you delete an "unnecessary" column, some of the cells of which, as it turns out, were involved in the calculations.

# DIV / 0 error! (division by zero)

From school, we remember a simple rule: you cannot divide by zero! Error # DIV / 0!- this is a warning from MS Excel that it is basic rule violated and you are still trying to divide a number by zero. In this case, the "zero" itself is not required - any attempt to divide an existing number by an "empty" cell will also cause this error.

You cannot divide by zero - empty cell perceived by MS Excel as the same zero

Error # N / A (value not available)

Error # N / A occurs when an argument is missing in the function, or one of the values ​​used in the formula becomes unavailable. I saw # N / A - first of all, look for what is missing in your calculations.

I apply the VLOOKUP function, I put the separation sign, but I forgot to indicate which cell it belongs to

Error #NAME? (invalid name)

Error #NAME- a sign that you and Excel did not understand each other. Rather, MS Excel did not understand what you meant - you explicitly point to some element, but the program cannot find it. When does this usually happen?

• The function specifies a cell or a range of cells with a non-existent (most often incorrectly entered) name.

Attempting to sum a non-existent range named Column

• The text inside the function is enclosed in quotes. If this does not happen (that is, instead of = "Vasya" we enter = Vasya), MS Excel is completely bewildered.

Another one the simplest mistake- text in functions and formulas is indicated in quotes

• There is a mistake in the name of the function.

Error # EMPTY! (empty set)

Error # EMPTY most often it occurs when one of the operators is missing in the formula, but it can also occur in the case when we need to find the intersection of two ranges of cells, and this intersection simply does not exist.

Everything would be fine, but I forgot about the second sign "+"

Error #NUM! (wrong number)

Error #NUMBER! ms Excel displays in cases where the result of mathematical calculations in the formula generates some completely unrealistic result. The result in the form of an extremely large or small number, an attempt to calculate the root of a negative number - all this will lead to the error #NUM!

Calculate the root of a negative number? Excel is not the only one who would not understand you

Pound signs in an Excel cell (#######)

In the past, a very common "error" in MS Excel associated with the sudden filling of a cell with pound signs (#) could be caused by the fact that a number was entered into a cell that does not fit in it entirely (but only if the cell is in the "numeric" or "date" format) ...

With the advent of MS Office 2013, the error practically disappeared, since the "wiser" Excel began, in most cases, to automatically increase the width of the cell to match the number. If you see "lattices", the easiest way to get rid of them is to manually increase the width of the cell.

It is enough to increase the column width and the problem disappears

If you know in what cases this or that error occurs in Excel, you will most likely immediately understand what caused it. However, the program simplifies your work even more and displays a special symbol next to the erroneous value in the form of an icon exclamation mark in a yellow diamond. When you click on it, you will be presented with a list of possible actions to correct the error.

Click the icon to get help fixing the error

In the first line of the context menu that appears, you will see the full name of the error, in the second you can call up detailed help on it, but the most interesting thing is hidden in the third item: “ Show Calculation Steps ...«.

"Show the stages of calculation ..." - the program cannot be fooled, it accurately displays a fragment of the formula where an error was made

Click on it and in the window that appears, you will see the very fragment of the formula where the mistake was made - this is especially convenient when you have to "unravel" a whole tangle from the bulk of nested actions.

»Will display special message about the error. In this case, each type of error is indicated by its own message, different reasons and accordingly requires different ways permissions.

##### - What does it mean and how to fix it?

These characters indicate that the column containing the numbers is not wide enough for them, or that the date and time entered in the cells in that column contain negative numbers.
In the first case, it is enough to simply increase the column width or change the numeric data format (for example, reduce the number of decimal places).
In the second case, you need:

• check the formula if calculating the number of days between two dates;
• if the formula does not contain errors, then you need to change the format of the cell and switch, for example, from the format "Date and Time" to "General" or "Numeric" format.

#VALUE! - What does it mean and how to fix it?

These messages are about using text instead of a number or a logical value (TRUE or FALSE). That is, Excel is such a playboy and cannot convert this text in a cell to the correct data type.
You need to make sure that the formula or function refers to those cells that contain valid values.
For example, if cell A2 contains a number, and cell A3 contains text, then cell A1 with the formula = A2 + A3 will display #VALUE! ...

# DIV / 0! - What does it mean and how to fix it?

These messages indicate that a number is being divided by 0 (zero) in a cell, or references to an empty cell are used.

• In the open sheet window, select the cell with this error and press the F2 key.
• When the cell itself displays the formula or function, and all cells linked to this formula or cell become selected, carefully check the values ​​in the selected cells and, if necessary, make adjustments to the formula or change the references to empty cells.
• Press Enter or the Enter Button on the Formula Bar.

If an empty cell is used as an operand, then it is automatically considered equal to zero.

#NAME? - What does it mean and how to fix it?

These symbols indicate that the formula is using a name that does not exist or an invalid operator.

Option 1

If a name is used that has not been defined, then the following must be done:

• In the open sheet window, go to the Formulas tab and in the Defined Names group click the Name Manager button.
• In the Name Manager window, check if the given name on the list.

If this name is absent, then you need to add it according to the instructions "".

Option 2

If there is an error in the spelling of the name, then it is necessary to check its spelling.

• In the open sheet window, press the F3 key.
• In the Insert Name box, select the name you want from the list and click OK.
• Make corrections (if necessary) in the formula that appears in the appropriate cell.
• Press Enter to pin.

Option 3

If the formula uses a misspelled function.
For example, SUM (A1: A10) instead of SUM (A1: A10).

• In the open sheet window, select the cell with the misspelled function.
• Expand the menu of the Error Source button next to this cell.
• In the list of commands, select Edit Formula Bar.
• The correct spelling of the required formulas will be displayed on the formula bar in the name box, according to which change the erroneous spelling.
• Secure the result by clicking on the Enter key.

Option 4

If you enter text in a formula that is not enclosed in double quotes, then you must check all text entries in the formula and enclose them in double quotes. Otherwise, Excel will try to recognize the given text as the name of a range of cells, although this was not intended.

Option 5

If a colon is missing in the reference to a range of cells, then in order to correct it, it is necessary to check the colon sign in all such references in the formula and correct it as necessary.
For example, SUM (A1 A10) instead of SUM (A1: A10).

Option 6

# N / A - What does it mean and how to fix it?

These symbols indicate that the desired value is not available for the function or formula.

Option 1

If missing data was entered into the formula, as well as # N / A or ND (), then # N / A must be replaced with new data.

The # N / A designation is entered in cells, data from which are not yet available.

Option 2

If in the HLOOKUP, LOOKUP, SEARCH, or VLOOKUP functions, an incorrect value for the "lookup_value" argument is specified (for example, a reference to a range of cells, which is not allowed), then you must respectively specify a reference only to the required cell.

Option 3

If required arguments are not given standard function worksheet, then you need to enter all the required corresponding function arguments.

Option 4

If the formula uses a function that is not available at the moment, then you need to check that the workbook using the sheet function is open, and that the function is working correctly.

Option 5

When you use VLOOKUP, HLOOKUP, or MATCH to view values ​​in an unsorted table, for which the table view information should be in ascending order by default.
The VLOOKUP and HLOOKUP functions contain an "interval_view" argument that allows you to search definite meaning and in an unsorted table. However, in order to find a specific value, the interval_lookup argument must be FALSE.
The MATCH function contains a match_type argument that allows you to sort the data for the search. If the corresponding value cannot be found, then it is recommended to set the "match_type" argument to 0.

Option 6

If the array formula uses an argument that does not match the range specified in the array formula, then you need to check the formula reference range for the number of rows and columns, or enter the array formula in fewer cells.

Option 7

If one or more required arguments of a standard or custom worksheet function are not specified, you must check and set all required function arguments.

#LINK! - What does it mean and how to fix it?

Option 1

If the cell referenced by the formula has been deleted or the value of the copied cells is placed in this cell, then you need to change the formula taking into account the new references.

Option 2

If you are using an OLE feature associated with a program that is not running, then you must run the required program.

The OLE (Object Linking and Embedding) interface is supported by many different programs and is used to place a document created in one program into another program. For example, you can insert Word document v Excel workbook and vice versa.

Option 3

Option 4

If a macro is used that calls a macro function that, under certain variants, returns the value #REF! ... You need to check the function argument to make sure it refers to valid cells or ranges of cells.

#NUMBER! - What does it mean and how to fix it?

This is a message about using incorrect numeric values ​​in a formula or function.

Option 1

If an unacceptable value has been inserted into a function using a numeric argument, then you need to check all the function arguments and, if necessary, correct the spelling of all numbers and the format of the corresponding cells.

Option 2

If it is impossible to find the result in a function with iteration (selection of parameters), for example, "IRR" or "BET", then you need to try a different initial approximation or change the number of iterations.

Option 3

If, as a result of calculating the formula, a number is obtained that is too large or, conversely, too small to be displayed in Excel, then you need to change the formula and ensure that the result is in the range from 1 * 10307 to 1 * 10307.

# EMPTY! - What does it mean and how to fix it?

These messages indicate that there are no shared cells when a re
section of two regions.

Option 1

If an erroneous range operator is used, then corrections must be made, namely:

• to refer to a continuous range of cells, a colon (:) is used as the separator between the start and end cell of the range. For example, SUM (C1: C20).
• the semicolon (;) concatenation operator is used to indicate a reference to two disjoint ranges. For example, SUM (C1: C20; D1: D20).

Option 2

If the specified ranges do not have common cells, then you need to change the links, achieving the desired intersection.

Lists and Ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

How to show 0 instead of an error in a cell with a formula

There are situations when many formulas are created on sheets in a workbook that perform various tasks. At the same time, the formulas were created once upon a time, perhaps even on you. And formulas return errors. For example # DIV / 0! (# DIV / 0!). This error occurs if division by zero occurs inside a formula: = A1 / B1, where B1 contains zero or empty. But there may be other errors (# N / A, #VALUE! Etc.). You can change the formula by adding an error check:

= IF (ISERR (A1 / B1), 0, A1 / B1)
arguments:
= IF (EOSH (1 argument), 2 argument, 1 argument)
These formulas will work in any version of Excel. True, the EOS function will not handle the # N / A (# N / A) error. To also process # N / A, you must use the ISERROR function:
= IF (ISERROR (A1 / B1), 0, A1 / B1)
= IF (ISERROR (A1 / B1), 0, A1 / B1)
However, further in the text I will use the EOS (because it is shorter) and besides, it is not always necessary to "not see" the # N / A error.
But for Excel versions 2007 and higher, a slightly more optimized function can be applied IFERROR:
= IFERROR (A1 / B1,0)
= IFERROR (A1 / B1, 0)
arguments:
= IFERROR (1 argument, 2 argument)

1 argument: expression to evaluate
Argument 2: the value or expression to be returned to the cell in case of an error in the first argument.

Why is IFERROR better and I call it more optimized? Let's take a closer look at the first formula:
= IF (EOSH (A1 / B1); 0; A1 / B1)
If we calculate step by step, we will see that first the expression A1 / B1 is calculated (i.e. division). And if its result is an error, then EOSH will return TRUE, which will be passed to the IF. And then the IF function will return the value from the second argument 0.
But if the result is not erroneous and ISERR returns FALSE, then the function will recalculate the previously calculated expression: A1 / B1
With the above formula, this does not play a special role. But if you use a formula like VLOOKUP with a scan of several thousand lines, then the calculation two times can significantly increase the time to recalculate the formulas.
The IFERROR function evaluates the expression once, remembers its result, and if it is erroneous, it returns what is written as the second argument. If there is no error, then it returns the stored result of evaluating the expression from the first argument. Those. the calculation in fact occurs once, which will practically not affect the speed of the general recalculation of formulas.
Therefore, if you have Excel 2007 and above and the file will not be used for more earlier versions- then it makes sense to use IFERROR.

Why correct formulas with errors at all? It is usually done for a more aesthetic display of data in reports, especially if the reports are then sent to the management.

So, there are such formulas on the sheet, the errors of which need to be processed. If there are one or two similar formulas for correction (yes, even 10-15), then there are almost no problems to replace manually. But if there are several dozen or even hundreds of such formulas, the problem becomes almost universal in scale :-). However, the process can be simplified by writing relatively simple code Visual basic for Application.
For all versions of Excel:

Sub IfIsErrNull () Const sToReturnVal As String = "0", vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid (s, 2) ss = " = "&" IF (ISERR ("& s &"), "& sToReturnVal &", "& s &") "If Left (s, 9)<>"IF (ISERR (" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err .Number Then MsgBox "Formulas processed"

Sub IfIsErrNull () Const sToReturnVal As String = "0" "if it is necessary to return empty instead of zero" Const sToReturnVal As String = "" "" "" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "The selected range contains no data", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid (s, 2) ss =" = "&" IF (ISERR ("& s &"), "& sToReturnVal &", "& s &") "If Left (s, 9)<>"IF (ISERR (" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err .Number Then MsgBox "Unable to transform formula in cell:" & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

For versions 2007 and higher

Sub IfErrorNull () Const sToReturnVal As String = "0" "if it is necessary to return empty instead of zero "Const sToReturnVal As String =" "" "" " Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "The selected range contains no data", vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid (s, 2) ss = "=" & "IFERROR (" & s & ", "& sToReturnVal &") "If Left (s, 8)<>"IFERROR (" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Unable to convert formula in cell:"& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Else MsgBox "Formulas processed", vbInformation, "www.site" End If End Sub

Sub IfErrorNull () Const sToReturnVal As String = "0" "if it is necessary to return empty instead of zero" Const sToReturnVal As String = "" "" "" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "The selected range contains no data", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid (s, 2) ss =" = "&" IFERROR ("& s &", "& sToReturnVal &") "If Left (s, 8)<>"IFERROR (" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Unable to transform formula in cell:" & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

How it works
If you are not familiar with macros, then first it is better to read how to create and call them: What is a macro and where to find it? since it may happen that you do everything right, but you forget to enable macros and nothing will work.

Copy the above code, go to the VBA editor ( Alt+F11), create a standard module ( Insert -Module) and just paste this code into it. Go to the desired Excel workbook and select all the cells, the formulas in which need to be converted so that in case of an error they return zero. Press Alt+F8, choose the code IfIsErrNull(or IfErrorNull, depending on which one you copied) and press Execute.
An error handling function will be added to all formulas in the selected cells. The given codes also take into account:
-if the IFERROR or IF (EOSH) function has already been applied in the formula, then such a formula is not processed;
-the code will correctly process the array functions as well;
- you can select non-adjacent cells (via Ctrl).
What is the disadvantage: complex and long array formulas can cause a code error, due to the peculiarity of these formulas and their processing from VBA. In this case, the code will write about the impossibility to continue working and will highlight the problem cell. Therefore, I strongly recommend making replacements on copies of files.
If the error value needs to be replaced with empty, not zero, then you need the line

"Const sToReturnVal As String =" "" "" "

Remove apostrophe ( " )

You can also given code called by pressing a button (How to create a button to call a macro on a sheet) or placed in an add-in (How to create your own add-in?) so that you can call from any file.

And a little addition: try to apply the code thoughtfully. Returning an error doesn't always get in the way. For example, when using VLOOKUP, it is sometimes useful to see which values ​​were not found.
I also want to note that it should be applied to really working formulas. Because if the formula returns #NAME! (# NAME!), Then this means that some argument is incorrectly written in the formula and this is an error in writing the formula, and not an error in the calculation result. It is better to analyze such formulas and find the error in order to avoid logical errors of calculations on the sheet.

Did the article help? Share the link with your friends! Video lessons

("Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))