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.
Let me make a reservation right away that the material in this article is intended for novice Excel users. Experienced users have already energetically danced on this rake more than once, so my task is to protect young and inexperienced “dancers” from this.
You don't give table column headings
Many Excel tools, such as sorting, filtering, smart tables, and pivot tables, assume that your data contains column headings. Otherwise, you either won’t be able to use them at all, or they won’t work quite correctly. Always make sure your tables contain column headings.
Empty columns and rows inside your tables
This confuses Excel. When it encounters 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, don’t hide rows/columns you don’t need inside the table; it’s better to delete them.
There are several tables on one sheet
If these are not tiny tables containing reference books of values, then you shouldn’t do this.
It will be inconvenient for you to fully work with more than one table on a sheet. For example, if one table is located on the left and the second on the right, then filtering one table will affect the other. If the tables are located one below the other, then it is impossible to use the freezing of areas, and you will also have to constantly search for one of the tables and perform unnecessary manipulations in order to place the table cursor on it. Do you need it?
Data of the same type is artificially located in different columns
Very often, users who know Excel rather superficially prefer this table format:
It would seem that we have a harmless format for accumulating information on agent sales and their fines. This table layout is visually well perceived by humans, since it is compact. However, believe me, it is a complete nightmare to try to extract data from such tables and get subtotals(aggregate information).
The fact is that this format contains 2 dimensions: to , you must decide on the row by looping through the branch, group and agent. When you find the desired column, then you will have to look for the required column, since there are many of them. And this “two-dimensionality” greatly complicates working with such a table even 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 data by year or quarter, since the indicators are separated into different fields. You don't have one sales volume field that can be conveniently manipulated, but rather 12 separate fields. You'll 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 use standard summation formulas like SUMIF, SUMIFS, SUMPRODUCT, you will also find that they will not work effectively with this table layout.
Spreading information across different sheets of the book “for convenience”
Another common mistake is having some standard format tables and needing analytics based on this data, distribute it into separate sheets Excel workbooks. For example, they often create separate sheets for each month or year. As a result, the amount of data analysis work is effectively multiplied by the number of sheets created. You don't have to do that. Accumulate information on ONE sheet.
Information in the comments
Users often add important information, which they may need, in the cell comment. Keep in mind that what is in the comments you can only look at (if you find it). It's difficult to get it into the cell. I recommend that you create a separate column for comments.
Formatting mess
Definitely won't add anything good to your table. This looks off-putting to the people who use your spreadsheets. At best, they won’t attach any importance to this, at worst, they will think that you are unorganized and sloppy in your affairs. Aim for the following:
Merging cells
Use cell merging only when you absolutely cannot do without it. Merged cells make it very difficult to manipulate the ranges they belong to. There are problems when moving cells, when inserting cells, etc.
Combining text and numbers in one cell
A painful impression is made by a cell containing a number appended at the back with the text constant “RUB.” or "USD" entered manually. Especially if it's not printed form, but 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 will have a regular format. This will cause problems with formulas.
If your table will be presented through an LCD projector
Choose the most contrasting combinations of color and background. Looks good on the projector dark background and light letters. The most terrible impression is made by red on black and vice versa. This combination looks extremely low-contrast on a projector - avoid it.
Page mode of worksheet in Excel
This is the same mode in which Excel shows how the sheet will be divided into pages when printed. Page borders are highlighted in blue. I do not recommend constantly working in this mode, which many people do, since the printer driver is involved in the process of displaying data on the screen, and this depends on many reasons (for example, the printer is networked and this moment unavailable) is fraught with freezes in the visualization process and recalculation of formulas. Work as usual.
Even more useful information you can learn about Excel at
Making even small change in an Excel worksheet may cause errors in other cells. For example, you might accidentally enter a value in a cell that previously contained a formula. This simple mistake can have a significant impact on other formulas, and you may not be able to detect it until you make some changes to the worksheet.
Errors in formulas fall into several categories:
Syntax errors: Occurs when the formula syntax is incorrect. For example, a formula has incorrect parentheses, or a function has the wrong number of arguments.
Logical errors: In this case, the formula does not return an error, but has a logical flaw, which causes the calculation to be incorrect.
Invalid link errors: The formula logic is correct, but the formula uses an incorrect cell reference. As a simple example, the range of data to sum in the SUM formula may not contain all the items you want to sum.
Semantic errors: For example, the name of the function is misspelled, in which case Excel will return the error #NAME?
Errors in array formulas: When you enter an array formula, you must press Ctrl + Sift + Enter when you finish typing. If you don't do this, Excel won't realize it's an array formula and will return an error or incorrect result.
Errors in incomplete calculations: In this case, the formulas are not fully calculated. To make sure that all formulas are recalculated, type Ctrl + Alt + Shift + F9.
The easiest way is to find and correct syntax errors. More often than not, you know when a formula contains a syntax error. For example, Excel will not allow you to enter a formula with inconsistent parentheses. Other syntax error situations result in the following errors being displayed in the worksheet cell.
Error #DIV/0!
If you create a formula that divides by zero, Excel will return the error #DIV/0!
Since Excel treats an empty cell as zero, dividing by an empty cell will also return an error. This problem often occurs when creating a formula for data that has not yet been entered. The formula in cell D4 has been stretched across the entire range (=C4/B4).
This formula returns the ratio of the values of columns C to B. Since not all data for days was entered, the formula returned the error #DIV/0!
To avoid the error, you can use , to check whether the cells of column B are empty or not:
IF(B4=0;"";C4/B4)
This formula will return a blank value if cell B4 is empty or contains 0, otherwise you will see the counted value.
Another approach is to use the ISERROR function, which checks for an error. The following formula will return an empty string if the expression C4/B4 returns an error:
IFERROR(C4/B4;"")
Error #N/A
The #N/A error occurs when the cell referenced by the formula contains #N/A.
Typically, the #N/A error is returned as a result of running . In the case where no match was found.
To catch the error and display an empty cell, use the =ESND() function.
ESND(VLOOKUP(A1,B1:D30,3,0);"")
Please note that the ESND function is new feature in Excel 2013. For compatibility with previous versions use an analogue of this function:
IF(END(VLOOKUP(A1,B1:D30,3,0));"";VLOOKUP(A1,B1:D30,3,0))
Error #NAME?
Excel may return the error #NAME? in the following cases:
- Formula contains an undefined named range
- The formula contains text that Excel interprets as an undefined named range. For example, a misspelled function name will return the error #NAME?
- The formula contains text not enclosed in quotation marks
- The formula contains a reference to a range that does not have a colon between the cell addresses
- The formula uses a worksheet function that was defined by an add-in, but the add-in was not installed
Error #EMPTY!
Error #EMPTY! occurs when a formula tries to use the intersection of two ranges that do not actually intersect. The intersection operator in Excel is space. The following formula will return #EMPTY! because the ranges do not overlap.
Error #NUMBER!
Error #NUMBER! will be refunded in the following cases:
- A non-numeric value was entered into a formula's numeric argument (for example, $1,000 instead of 1000)
- An invalid argument was entered into the formula (for example, =ROOT(-12))
- A function that uses iteration cannot calculate the result. Examples of functions using iteration: VSD(), BET()
- The formula returns a value that is too large or too small. Excel supports values between -1E-307 and 1E-307.
Error #LINK!
- You deleted a column or row that was referenced by a formula cell. For example, the following formula will return an error if the first row or columns A or B were deleted:
- You deleted the worksheet that was referenced by a formula cell. For example, the following formula will return an error if Sheet1 was removed:
- You copied the formula to a location where the relative reference becomes invalid. For example, if you copy a formula from cell A2 to cell A1, the formula will return a #REF! error because it is trying to reference a cell that does not exist.
- You cut the cell and then paste it into the cell referenced by the formula. In this case, the error #LINK! will be returned.
Error #VALUE!
Error #VALUE! is the most common error and occurs in the following situations:
- The function argument has an incorrect data type or the formula is attempting to perform an operation using incorrect data. For example, when trying to add a numeric value to a text value, the formula will return an error
- Function argument is a range when it should be a single value
- Custom sheet functions are not calculated. To force a recalculation, press Ctrl + Alt + F9
- A custom worksheet function attempts to perform an operation that is not valid. For example, a custom function cannot change Excel environment or make changes in other cells
- You forgot to press Ctrl + Shift + Enter when entering an array formula
After entering or adjusting a formula, as well as when changing any value of a function, it happens that a formula error appears rather than the required value. In total, the spreadsheet editor recognizes seven main types of such incorrect calculations. We'll look at what errors look like in Excel and how to fix them below.
Below we will present a description of the formulas shown in the picture with detailed information for every mistake.
1. #CASE!– “division by 0”, most often occurs when trying to divide by zero. That is, the formula embedded in the cell, performing the function of division, comes across a cell with a zero value or “Empty”. To resolve the issue, check all cells involved in the calculation and correct any invalid values. The second action leading to #DIV/O! – this is the entry of incorrect values into some functions, such as =AVERAGE(), if during the calculation the value range contains 0. The same result will be caused by empty cells accessed by a formula that requires specific data for calculation.
2. #N/A- "no data". This is how Excel marks values that are not clear to the formula (function). By entering inappropriate numbers into a function, you are sure to trigger this error. When it appears, make sure that all input cells are filled out correctly, and especially in those where the same inscription is illuminated. Often found when used
3. #NAME? – “invalid name”, an indicator of an incorrect name of the formula or some part of it. The problem disappears if you check and correct all the titles and names accompanying the calculation algorithm.
4. #EMPTY!– “there is an empty value in the range”, a signal that somewhere in the calculation there are non-overlapping areas or a space is inserted between the specified ranges. Quite a rare mistake. An erroneous entry may look like this:
SUM(G10:G12 I8:J8)
Excel does not recognize such commands.
5. #NUMBER!– an error is caused by a formula containing a number that does not correspond to the specified range.
6. #LINK!– warns that cells associated with this formula have disappeared. Check to see if the cells specified in the formula have been deleted.
7. #VALUE!– the type of argument for the function operation is incorrectly selected.
8. Bonus, error ##### — the cell width is not sufficient to display the entire number
Additionally, Excel issues a warning that the formula is incorrect. The program will try to tell you exactly how to place punctuation (for example, parentheses). If the proposed option meets your requirements, click “Yes”. If a hint requires manual adjustment. Then select “No” and rearrange the brackets yourself.
Errors in Excel. Using the ERROR() function for Excel 2003
The function helps to eliminate errors in Excel well. It works by finding errors in cells; if it finds an error in the formula, it returns TRUE and vice versa. In combination with =IF(), it will allow you to replace the value if an error is found.
Working formula: =IF(ERROR(expression),error,expression).
IF(ISERROR (A1/A2);””;A1/A2)
Explanation: If an error is found while executing A1/A2, empty ("") will be returned. If everything went correctly (i.e. ERROR (A1/A2) = FALSE), then A1/A2 is calculated.
Errors in Excel.Using IFERROR() for Excel 2007 and higher
One of the reasons why I quickly switched to Excel 2007 was IFERROR() (the main reason is )
The iferror function contains the capabilities of both the ERROR() and IF() functions, but is available in newer ones Excel versions, which is very convenient
The tool is activated as follows: =IFERROR(value; value on error). Instead of “value” there is a calculated expression/link to the cell, and instead of “value in case of error” - what should be returned if an inaccuracy occurs, for example, if when calculating A1/A2 #CASE! then the formula will look like this:
IFERROR(A1/A2;””)
» download here
When creating complex formulas (and simply carelessness) in MS Excel error quite easy to do. Typically, MS Excel in such cases displays error messages or even suggests the “correct”, in its opinion, version of writing the formula, but even if there is help system, at first it is quite 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 meaning)
If there was a “top of MS Excel errors”, the first place in it would belong to the error #VALUE!. As you can guess from the name, it occurs when an incorrect value is substituted into a formula or function. If you are trying to carry out arithmetic operations with text, or substitute a range of cells into the function, when you need to specify only one cell, the result of the calculation will be the error #VALUE!.
As was said, trying to add a number and text leaves MS Excel at a dead end.
Error #LINK! (incorrect cell reference)
One of the most common mistakes during calculations. It means the simplest thing - the formula uses a reference to a cell that you either did not create or accidentally deleted. More often #LINK! occurs when you delete an “unnecessary” column, some cells of which, as it turns out, were involved in the calculations.
Error #DIV/0! (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 this basic rule has been violated and you are still trying to divide a certain number by zero. In this case, the “zero” itself is not necessary - any attempt to divide an existing number into an “empty” cell will also cause this error.
You can't divide by zero - empty cell is perceived by MS Excel as the same zero
Error #N/A (value not available)
Error #N/A occurs when a function is missing an argument, 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’m using the VLOOKUP function, I’ve set the division 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. Or rather, MS Excel did not understand what you meant - you are clearly pointing to some element, but the program cannot find it. In what cases does this usually happen?
- The function specifies a cell or range of cells with a non-existent (most often incorrectly entered) name.
Trying to sum a non-existent range called Column
- The text inside the function is enclosed in quotation marks. If this does not happen (that is, instead of = “Vasya” we enter = Vasya), MS Excel becomes completely perplexed.
Another simple mistake is that the text in functions and formulas is indicated in quotes
- There was an accidental typo in the function name.
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 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 #NUMBER! (wrong number)
Error #NUMBER! ms Excel reports in cases where the result of mathematical calculations in the formula generates some completely unrealistic result. A 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 #NUMBER error!
Calculate the root of a negative number? Not only Excel would not understand you
Hash marks in an Excel cell (#######)
In the past, a very common MS Excel “error” associated with the sudden filling of a cell with pound signs (#) could be caused by the fact that a number was entered into the cell that did not fit entirely in it (but only if the cell was 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 cell width to fit the number. If you see “lattices”, the easiest way to get rid of them is to manually increase the width of the cell.
Just increase the column width and the problem will disappear
If you know in what cases a particular 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 in the form of an icon next to the erroneous value exclamation mark in a yellow diamond. When you click on it, you will be offered 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 calculation steps...” - you can’t fool the program, it accurately displays the fragment of the formula where the 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 of actions nested within each other.
I enter a formula, but instead of the result I see the text #N/A or #####. What does it mean?
This means that there was an error in the source data, the formula itself, formatting, or in another case. The error message contains an indication of where exactly the error occurred. Below we will look at error messages and their most common causes:
Error #####
Most often, this error means that the column is not wide enough to display the contents of the cell. To solve the problem, just increase the column width.
Another reason for the error is that the cell contains a date or time with negative values.
Error #N/A
This error occurs if a function or formula contains a reference to a value (or a value itself) that does not exist. In other words, a required field is not filled in a function or formula, or there is a reference to an empty cell when the cell must contain a value. The most common reasons:
- The function is missing one or more required arguments.
- The VLOOKUP, GLOOKUP, LOOKUP, or MATCH functions specified an incorrect value for the lookup_value argument.
- The VLOOKUP, HLOOKUP, or MATCH functions are used to search an unsorted table.
- In the original table, #N/A or ND() was entered instead of missing data.
Error #DIV/0!
Error #DIV/0! Indicates that the calculation process involves division by zero. The reasons may be the following:
- The formula explicitly specifies division by zero (for example, =10/0).
- In a formula or function, the divisor is a reference to an empty cell or a cell that contains zero.
Error #EMPTY!
This error occurs when two intersecting ranges are specified in the conditions of a function, but they do not actually intersect.
Note: In some tasks, the user needs to perform actions only on those cells that are simultaneously present in two ranges. To do this, cell ranges in a function are separated not by a semicolon, but by a space (which is called the intersection operator).
- If the function must not specify overlapping ranges (for example, when summing), it is likely that a space was used as the range separator rather than a semicolon. Or a space was used when specifying a range, instead of a colon.
- If the function must specify overlapping ranges, it is likely that the ranges do not actually have common cells. You need to check the specified ranges in the function arguments.
Error #NUMBER!
This error occurs if incorrect numeric values are specified in a formula or function. For example:
- In a function that uses a numeric argument, the argument is text or a cell reference with a non-numeric value.
- A function that iterates (a calculation that repeats until a specified condition, such as IRR or BET, is met) cannot calculate a result (for example, the specified condition has not occurred).
- The result of the calculation is a number that is too large or too small to be used in Excel. We remind you that maximum number in Excel cannot exceed 10307 and be less than minus 10307.
A message like this tells us that the cell reference is invalid. Most often, this may indicate that the cells referenced by the formula have been deleted, or other values have been copied into those cells.
Error #VALUE!
This error occurs when the wrong type of argument or operand is used. For example, a user might get the following wrong:
- In a formula instead of a number or boolean value(TRUE or FALSE) text entered.
- After entering or editing an array formula, press ENTER instead of CTRL+SHIFT+ENTER.
- The reference, formula, or function is specified as an array.
- A range is specified as a function condition that requires a single value.
I understood what the error means. But my formula is very large/works with a large amount of data. How can I find where the mistake was made?
Excel 2007 can point out the error right away, or it can help you review calculations step by step to find the error. To do this, click on the icon that appears next to the cell with the error message. If the formula consists of only one function or the reason for the error is quite simple, the context menu will contain an item Source of error. After clicking on this item, Excel will show you a cell with incorrect data automatically:
If the formula contains several functions, then context menu The Show calculation steps option appears. In the window that opens, you can see which calculation is causing the error.
I have a #NAME error? and I can't find the reason for it.
- Check the spelling of the function name(s). To do this, you can call the Function Wizard (Function Library group of the Formulas tab), and select the desired function from the proposed list.
- Text that was not included in the formula was entered into the formula. double quotes. Let's say the result of the formula was supposed to be a text with a number - for example, Total 200 rubles. When you enter the text Total in a formula, if the text is not enclosed in double quotation marks, Excel interprets the data as a name, which causes an error.
Note: A name is a word, phrase, or other set of characters specified by the user that identifies a cell, range of cells, formula, or constant.
- The formula or function uses a name that has not been defined. Check that the given name exists. On the Formulas tab, in the Named Cells group, select Name Manager and see if the name is in the list. If this name is missing, you can add it by selecting Create Name.
- There was a mistake in spelling the name. To verify that you have entered the correct names in the formula bar, press F3, click the name you want, and then click OK.
- The formula or function uses a reference to another worksheet that is not enclosed in single quotation marks. If the name of another sheet or workbook contains non-letter characters or a space, then the name must be enclosed in apostrophes (").
I have a huge database of clients - buyers. Every day, a column is added to the database indicating the amount of revenue from each customer. If the buyer has not purchased anything, #N/A is placed in the cell. How can I sum a column if there is such data there?
The easiest way to solve this problem is to change the principle of filling the database and not fill in cells for customers who have not made purchases.
But if the main table cannot be changed (for various reasons), you can try to solve this problem using the BDSUMM function.
Let's say the source table looks like this:
By using the BDSUMM function, we can select for summing those cells that do not contain the #N/A value. The BDSUMM function has the following syntax:
BDSUMM(database, field, conditions) , where
- database_is the range of cells with which actions will be performed.
- field is the column from which the values will be summed.
- conditions are a set of conditions that must be true for a cell's value in order for that value to be summed.
That is, in the function we need to set the summation of the first column of our table, provided that the cell value of this column is not equal to #N/A. To do this, first create a condition - in any convenient location worksheet we will write the condition #N/A and title this cell the same way as the column of the database we need.
BDSUMM(C2:C18;1;E2:E3) where
- C2:C18 is our table,
- E2:E3 is our condition.
Please note that database and condition ranges must contain column headers! In this case, the title of the condition must match the title of the column on which the calculations are performed!
Our result is shown below the table:
If our database contains errors different types, it is enough to add them to the conditions (and, accordingly, expand the range of conditions in the formula) in order to get the desired result.
This method can also be used for the functions COUNT, BCOUNT, DMAX, DMIN, BDPRODUCT and the like.
Loading...
