Making even a small change in the workflow Excel sheet can lead to 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 effect on other formulas, and you won't be able to find 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 mismatched parentheses, or a function has an incorrect number of arguments.
Logic errors: In this case, the formula does not return an error, but has a logical flaw, which causes an incorrect calculation result.
Bad links errors: The formula logic is correct, but the formula uses the wrong cell reference. As a simple example, the range of data to sum in a SUM formula may not contain all of the items that you want to sum.
Semantic errors: For example, the function name is misspelled, in which case Excel will return the #NAME?
Errors in array formulas: When you enter an array formula, you must press Ctrl + Sift + Enter when you're done. If you don't do this, Excel won't know it's an array formula and will return an error or incorrect result.
Incomplete calculation errors: In this case, the formulas are not fully calculated. To make sure all formulas are recalculated, type Ctrl + Alt + Shift + F9.
The easiest way 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 situations syntax errors lead to the output of the following errors in the sheet cell.
Error #DIV/0!
If you create a formula that divides by zero, Excel will return the #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 extended to the full range (=C4/B4).
This formula returns the ratio of the values of columns C to B. Since not all data by day was entered, the formula returned the error #DIV/0!
To avoid the error, you can use , to check if the cells in column B are empty or not:
IF(B4=0;"";C4/B4)
This formula will return empty value if cell B4 is empty or contains 0, otherwise you will see the calculated value.
Another approach is to use the IFERROR function, which checks for an error. The following formula will return an empty string if the C4/B4 expression returns an error:
ISERROR(C4/B4;"")
Error #N/A
The #N/A error occurs when the cell referenced by the formula contains #N/A.
Usually, the #N/A error is returned as a result of running . In case no match was found.
To catch the error and display an empty cell, use the =ECND() function.
USND(VLOOKUP(A1;B1:D30;3;0);"")
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 a #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 #NAME?
- Formula contains unquoted text
- The formula contains a reference to a range that does not have a colon between cell addresses
- The formula uses a worksheet function that was defined by an add-in, but the add-in was not installed
Error #BLANK!
Error #BLANK! occurs when a formula tries to use the intersection of two ranges that do not actually intersect. The intersection operator in Excel is the space. The following formula will return #NULL! because the ranges do not overlap.
Error #NUMBER!
Error #NUMBER! will be returned in the following cases:
- A non-numeric value was entered in a numeric formula argument (for example, $1,000 instead of 1000)
- An invalid argument was entered in a formula (for example, =ROOT(-12))
- A function that uses iteration cannot calculate the result. Examples of functions using iteration: IRR(), RATE()
- The formula returns a value that is too large or too small. Excel supports values between -1E-307 and 1E-307.
Error #LINK!
- You have deleted a column or row referenced by a formula cell. For example, the following formula will return an error if the first row or columns A or B have been deleted:
- You have deleted the worksheet referenced by the formula cell. For example, the following formula will return an error if Sheet1 was removed:
- You copied the formula to the location where 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 refer to a cell that does not exist.
- You cut a cell and then paste it into the cell that the formula refers to. In this case, the #REF! error will be returned.
Error #VALUE!
Error #VALUE! is the most common error and occurs in the following situations:
- The function argument is of the wrong data type, or the formula is trying to perform an operation using the wrong 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 recalculation, press Ctrl + Alt + F9
- The user-defined worksheet function is trying to perform an operation that is not valid. For example, custom function can't change Excel environment or make changes in other cells
- You forgot to press Ctrl + Shift + Enter when entering an array formula
Functions for working with databases are used to work with databases (lists) and tables with the corresponding data structure. With these functions, you can perform worksheet data analysis. Statistical functions allow you to solve many different problems, both complex professional and simple, for example, determining the arithmetic mean.
Text functions are used when working with text, allow you to search, replace or combine certain sequences of characters, as well as count the number of characters, and much more.
Ten latest features that are used by the user, Excel automatically groups them into the 10 Most Recently Used category.
Diagnosing Errors in Excel Formulas
If Excel cannot process a formula in a cell and display the result, then it generates an error message and displays it in that cell (instead of the formula itself or its result). The error message always begins with a "#" sign.
Error messages in Excel can belong to one of 8 types:
######
#VALUE!
#DIV/0!
#NAME?
#NUMBER!
#EMPTY!
As a rule, the error ###### occurs when the result obtained does not fit in the cell. In this case, you need to increase the column width.
Error #VALUE ! occurs when an invalid argument type is used, such as when the user tries to add a text value and a numeric value.
The #DIV/0 error occurs when an attempt is made to divide by zero in a formula.
Error message like #NAME? appears when Excel cannot find the name used in the formula. For example, this situation will arise if:
o There was a typo when typing the name;
o the text was erroneously not enclosed in double quotes;
Error message like #NUMBER! occurs when an invalid argument for a function is specified in a formula.
Error message like #BLANK! appears when an invalid cell or range reference is used, such as the intersection of two regions that do not have cells in common.
Entering and processing data in Excel
A significant part of the work in Excel falls on data entry, editing and processing.
A worksheet in Excel 2007 has 13,384 columns and 1,048,576 rows.
You can enter a number, text, date/time, or formula in a worksheet cell. Input always takes place in the active cell. The location of the input characters is indicated by a flashing cursor.
All entered text and numbers are displayed both in the formula bar and in the cell itself. When entering formulas (by default), the result of calculations is displayed in the cell, and the entered formula or function itself is visible in the formula bar.
You can optionally correct errors before the input is committed, or change the contents of the cell after the input is committed.
Formatting and Protecting Worksheets
Entered into cells excel information can be displayed on the screen different ways. To change the form of display and access to information, formatting and protection tools are used.
Incorrect data format can cause significant problems, especially if the user does not have enough experience. For example, if the user enters the number 0.9, but a comma is used as a decimal separator in the computer settings, then the entered data will be perceived as text. An experienced user will immediately notice this, since usually, by default, text is left-aligned, and numbers are right-aligned in a cell.
Formatting in Excel includes the following questions:
change the font, size, style and color of characters;
alignment and reorientation of text and numbers in cells;
formatting numbers, dates and times;
formatting rows and columns;
creating and using custom formats;
conditional formatting;
protection of cells, sheets and workbooks;
using styles when formatting;
using autoformats.
Working with spreadsheets
Spreadsheets in Excel are located on the worksheets of workbooks, the last of which are the electronic equivalent of a folder-folder that "stacks" documents. The number of worksheets in the book can be adjusted by the user. You can additionally “file” charts, pivot tables, various reports, etc. into workbooks.
An e-book worksheet consists of cells, each of which has its own address: a combination of a column name and a row. Columns are identified by letters Latin alphabet(А, B, C, D…), astros
– Arabic numerals (1,2,3…). The cell in which the cursor is located is considered active, that is, intended for data entry. For example, the address F10 indicates that the information entered when active cell F10 will hit exactly that cell, and the contents of that cell will be reflected in the formula bar.
Many Excel commands allow you to work with blocks of cells. A block of cells is a rectangle defined by the coordinates of opposite corners, usually the top left and bottom right cells. Cell names in blocks are separated by a colon (:) . For example, block A1:B4 includes cells A1, A2, A3, A4, B1, B2, B3 and B4. Selecting a block of cells is done by dragging the mouse cursor over the desired range. In this case, the cell, starting from which the block is selected, remains white color, and the rest of the cells are dimmed. But a block of cells may not necessarily include adjacent cells. To select such cells in the block, use the key
Cells and blocks can be given names for ease of use.
Worksheets can also be renamed to better reflect the content of the problem being solved.
Lifehacker readers are already familiar with Denis Batyanov who shared with us. Today Denis will talk about how to avoid the most common Excel problems that we often create ourselves.
Immediately make a reservation that the material of the article is intended for novice users of Excel. Power Users already incendiary danced on this rake more than once, so my task is to protect young and inexperienced "dancers" from this.
You don't give headers to table columns
Many Excel tools, such as sorting, filtering, smart tables, pivot tables, assume that your data contains column headings. 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 confuses Excel. When it encounters an empty row or column inside your table, it starts to think that you have 2 tables, not just 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.
Multiple tables on one sheet
If these are not tiny tables containing reference books of values, then this is not worth doing.
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 other is 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 fixing of areas, and also one of the tables will have to be constantly searched and performed unnecessary manipulations in order to place the table cursor on it. Do you need it?
Data of the same type is artificially arranged in different columns
Very often, users who know Excel superficially enough prefer this table format:
It would seem that we have a harmless format for the accumulation of information on the sales of agents and their fines. Such a layout of the table is well perceived by a person visually, since it is compact. However, believe me, it's a real nightmare to try to extract data from such tables and get subtotals (aggregate information).
The fact is that given format contains 2 dimensions: for , you must decide on a row by iterating over branch, group, and agent. When you find the right row, then you will have to look for the desired column, since there are a lot of them. And this "two-dimensionality" greatly complicates the work with such a table and for standard Excel tools- formulas and pivot tables.
If you build a pivot table, you will find that it is not possible to easily get data by year or quarter, since the indicators are spread across different fields. You don't have one sales volume field that can be manipulated conveniently, but 12 separate fields. You will have to manually create separate calculated fields for quarters and years, although if it were all in one column, pivot table would do it for you.
If you would like to apply standard formulas summations like SUMIF, SUMIFS, SUMPRODUCT, you will also find that they will not be able to work effectively with this table layout.
Separation of information on different sheets of the book "for convenience"
Another common mistake is having some standard format tables and needing analytics based on this data, post it on separate sheets excel books. For example, they often create individual sheets for each month or year. As a result, the amount of data analysis work is actually 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 might need, in a cell comment. Keep in mind that what is in the comments, you can only see (if you find it). It's hard to get it into a cell. I recommend it is better to allocate a separate column for comments.
Formatting mess
Definitely won't add anything good to your table. This looks repulsive to the people who use your spreadsheets. AT best case they will not attach any 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 merging only when you can't do without it. Merged cells make it very difficult to manipulate the ranges they fall within. 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, supplemented at the back by 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 text format, and part in the usual. Because of this, there will be problems with the 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 worst impression is made by red on black and vice versa. This combination looks extremely low-contrast on the projector - avoid it.
Page mode sheet in Excel
This is the same mode in which Excel shows how the sheet will be paginated when printed. Page borders stand out blue color. 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 process of visualization and recalculation of formulas. Work normally.
Much more useful information Learn more about Excel at
After entering or correcting a formula, as well as when changing any value of a function, it happens that a formula error appears, and not the required value. In total, the spreadsheet editor recognizes seven main types of such incorrect calculations. What errors look like in Excel and how to fix them, we will analyze below.
Below we will present a description of the formulas shown in the picture with detailed information for each error.
1. #CASE!- “divide by 0”, most often occurs when trying to divide by zero. That is, the formula embedded in the cell, performing the division function, stumbles upon a cell where the value is “Empty” with a zero value or. 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 input of incorrect values \u200b\u200bin some functions, such as \u003d AVERAGE () if the calculation in the range of values \u200b\u200bis 0. The same result will provoke blank cells that are accessed by a formula that requires specific data to calculate.
2. #N/A- "there is no data". This is how Excel flags values that are incomprehensible to the formula (function). Entering the wrong numbers into the function will definitely trigger this error. When it appears, make sure that all the input cells are filled in correctly, and especially in those where the same inscription is lit. Often seen when using
3. #NAME? - "invalid name", an indicator of the incorrect name of the formula or some part of it. The problem disappears if you check and correct all the names and names that accompany the calculation algorithm.
4. #EMPTY!– “there is an empty value in the range”, a signal that non-intersecting areas are written somewhere in the calculation or a gap is placed between the specified ranges. Pretty rare mistake. The error entry might 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 boundaries of the indicated range.
6. #LINK!- warns that the cells associated with this formula have disappeared. Check if the cells indicated in the formula were most likely deleted.
7. #VALUE!– Incorrectly selected argument type for the function to work.
8. Bonus, mistake ##### - cell width is not enough to display the whole number
In addition, Excel issues a warning about an invalid formula. The program will try to tell you exactly how to arrange punctuation (for example, brackets). If the proposed option meets your requirements, click "Yes". If the tooltip requires manual adjustment. Then select "No" and rearrange the brackets yourself.
Mistakes in Excel. Using the ISERROR() function for Excel 2003
Well helps to eliminate errors in Excel function. It works by finding errors in the cells, if it finds an error in the formula it returns TRUE and vice versa. In combination with =IF(), it will allow the value to be replaced 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, an empty ("") will be returned. If everything went correctly (i.e. ISERROR (A1/A2) = FALSE), then A1/A2 is calculated.
Mistakes in Excel.Using IFERROR() for Excel 2007 and above
One of the reasons I quickly switched to Excel 2007 was IFERROR() (the most main reason- This )
The iferror function contains the capabilities of both the ISERROR() 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”, a calculation expression / cell reference is put, and instead of “value on error”, what should be returned if an inaccuracy occurs, for example, if during the calculation of A1 / A2, #CASE! then the formula will look like this:
ISERROR(A1/A2;””)
» download here
When you enter or edit a formula, or when one of the input values of a function changes, Excel may show one of the errors instead of the formula value. There are seven types of errors in the program. Let's look at their description and solutions.
- #CASE! — given error almost always means that the formula in the cell is trying to divide some value by zero. Most often this happens due to the fact that another cell that refers to this one has a null value or there is no value. You need to check all related cells for such values. Also, this error can occur when you enter wrong values to some functions, such as MOD() when the second argument is 0. Also, a divide-by-zero error can occur if you leave empty cells to enter data, and some formula requires some data. This will throw an error #DIV/0! which can be confusing end user. For these cases, you can use the IF() function to test, like =IF(A1=0;0;B1/A1) . In this example, the function will return 0 instead of an error if cell A1 contains a null or empty value.
- #N/A- This error stands for unavailable, which means that the value is not available to the function or formula. You may see this error if you enter an invalid value into a function. To fix, first check the input cells for errors, especially if they also have this error.
- #NAME?- this error occurs when you incorrectly specify the name in the formula or incorrectly set the name of the formula itself. To correct, check again all the names and titles in the formula.
- #EMPTY!- This error is related to the ranges in the formula. Most often, it occurs when two non-intersecting ranges are specified in the formula, for example =SUM(С4:С6,А1:С1) .
- #NUMBER!- an error occurs when the formula contains incorrect numerical values out of range.
- #LINK!- the error occurs when the cells referenced by this formula have been deleted.
- #VALUE!- in this case we are talking about using the wrong type of argument for a function.
If you accidentally misplaced parentheses when entering a formula, Excel will display a warning message on the screen - see fig. 1. In this message, you will see Excel's suggestion on how to arrange them. If you confirm this arrangement, click Yes. But often requires your own intervention. For this click Not and fix the brackets yourself.
Handling errors with the ISERROR() function
You can catch any errors and handle them using the ISERROR() function. This function returns true or false depending on whether an error occurs while evaluating its argument. General formula for interception looks like this: =IF(ISERROR(expression),error, expression) .
The if function will return an error (for example, a message) if an error occurs during the calculation. For example, consider the following formula: =IF(ISERROR(A1/A2),""; A1/A2) . If an error occurs (divide by 0), the formula returns an empty string. If no error occurs, the A1/A2 expression itself is returned.
There is another, more handy feature ISERROR() , which combines two previous features IF() and ISERROR() : IFERROR(value;value on error) where: meaning- expression for calculation, value on error— return result in case of an error. For our example, it would look like this: =IFERROR(A1/A2,"") .
