The #NAME? error occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a matter of correcting spelling or a syntax problem. The examples below...Read more
The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where...Read more
Normally, SUMIFS is used with data in a vertical arrangement, but it can also be used in cases where data is arranged horizontally. The trick is to make sure the sum_range and criteria_range are the same dimensions. In the example shown, the formula in cell I5, copied down the...Read more
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0!...Read more
The #N/A error appears when something can't be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name misspelled, a color option that doesn't exist, etc.
However...Read more
Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers. To calculate standard deviation in Excel, you can use one of two primary functions, depending on the data set. If the data...Read more
The NUMBERVALUE function converts a text value representing a number into a valid numeric using custom decimal and group separators. You can use NUMBERVALUE to translate numbers from a locale-specific text format into a locale-independent number.
To perform a numeric conversion, the ...Read more
This formula relies on a technique called "nested IFs" to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here.
The formula in this example is purposely more...Read more
To make a dynamic chart that automatically skips empty values, you can use dynamic named ranges created with formulas. When a new value is added, the chart automatically expands to include the value. If a value is deleted, the chart automatically removes the label.
In the chart shown, data is plotted in one series. Values come from a named range called "values", defined with the formula provided below:
Most people never receive proper Excel training and spend years frustrated by the simplest tasks, especially tasks that involve formulas. To use Excel with confidence, you must have a good understanding of formulas and functions. This article introduces the basic concepts you need to know...Read more
