EXCEL tutorial nested IF function

Aller au Téléchargement

IF function

Hide All

This article describes the formula syntax and usage of the IF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, [value_if_true], [value_if_false])

The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

logical_test Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator. 

value_if_true Optional. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument. 

value_if_false Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE.

If logical_test evaluates to FALSE and the value of the value_if_false argument is omitted (that is, in the IF function, there is no comma following the value_if_true argument), the IF function returns the value 0 (zero).

Remarks

    Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) Alternatively, to test many conditions, consider using the ,VLOOKUP,HLOOKUP, or CHOOSEfunctions.

(See Example 4 for a sample of the LOOKUP function.) 

      If any of the arguments to IF are arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), every element of the array is evaluated when the IF statement is carried out. 

      Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIFor the COUNTIFSworksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIFor the SUMIFSworksheet functions. 

Examples

EXAMPLE 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

To switch between viewing the results and viewing the formulas that return the results, press

CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

To switch between viewing the results and viewing the formulas that return the results, press

CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 3

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

The preceding example demonstrates how you can nest IF statements. In each formula, the fourth IF statement is also the value_if_false argument to the third IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement, and the second IF statement is the value_if_false argument to the first IF statement. For example, if the first logical_test argument (Average>89) evaluates to TRUE, "A" is returned. If the first logical_test argument evaluates to FALSE, the second IF statement is evaluated, and so on. You can also use other functions as arguments.

The letter grades are assigned to numbers, using the following key.

From 70 to 79            C

Less than 60               F

EXAMPLE 4

In this example, the LOOKUP function is used instead of the IF function because there are thirteen conditions to test. You may find the LOOKUP function easier to read and maintain than the IF function.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

See Also

    Logical functions (reference)

SUMIFS function

Hide All

This article describes the formula syntax and usage of the SUMIFS function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:

=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")

 The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the

sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], )

The SUMIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

sum_range Required. One or more cells to sum, including numbers or names, ranges, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) that contain numbers. Blank and text values are ignored. 

criteria_range1 Required. The first range in which to evaluate the associated criteria. 

criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."              criteria_range2, criteria2, … Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Remarks

    Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two criteria_range arguments. If the first cell of criteria_range1 meets criteria1, and the first cell of criteria_range2 meets critera2, the first cell of sum_range is added to the sum, and so on, for the remaining cells in the specified ranges. 

      Cells in the sum_range argument that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0 (zero). 

      Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_range argument must contain the same number of rows and columns as the sum_range argument. 

      You can use the wildcard characters — the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Examples

EXAMPLE 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 2: ADDING AMOUNTS FROM BANK ACCOUNTS BASED ON INTEREST PAID

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 3: ADDING RAINFALL FOR SPECIFIC DAYS

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 4: ADDING RAINFALL FOR MORNING AND EVENING PERIODS OF SPECIFIC DAYS

This example expands on the data in Example 3, separating the rainfall, average temperatures, and average wind speed into two 12-hour periods for each day.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 5: ENTERING CRITERIA AS A REFERENCE OR BY USING WILDCARD CHARACTERS

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

See Also

    Math and trigonometry functions (reference)

INDEX function

Hide All

This article describes the formula syntax and usage of the INDEX function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) form and the reference form. 

Array form

DESCRIPTION

Returns the value of an element in a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), selected by the row and column number indexes.

Use the array form if the first argument to INDEX is an array constant.

SYNTAX

INDEX(array, row_num, [column_num])

The INDEX function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Array Required. A range of cells or an array constant. 

      If array contains only one row or column, the corresponding row_num or column_num argument is optional.

      If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

Row_num Required. Selects the row in array from which to return a value. If row_num is omitted, column_num is required. 

Column_num Optional. Selects the column in array from which to return a value. If column_num is omitted, row_num is required.

REMARK

      If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num. 

      If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press

CTRL+SHIFT+ENTER. 

      Row_num and column_num must point to a cell within array; otherwise, INDEX returns the #REF!

error value.

EXAMPLES

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A2:A3 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.

Top of Page

Reference form

DESCRIPTION

Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.

SYNTAX

INDEX(reference, row_num, [column_num], [area_num])

The INDEX function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Reference Required. A reference to one or more cell ranges. 

            If you are entering a nonadjacent range for the reference, enclose reference in parentheses.

      If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).

Row_num Required. The number of the row in reference from which to return a reference. 

Column_num Optional. The number of the column in reference from which to return a reference. 

Area_num Optional. Selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1. 

      For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4.

REMARK

      After reference and area_num have selected a particular range, row_num and column_num select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of row_num and column_num. 

      If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.

      Row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns the #REF! error value. If row_num and column_num are omitted, INDEX returns the area in reference specified by area_num. 

      The result of the INDEX function is a reference and is interpreted as such by other formulas.

Depending on the formula, the return value of INDEX may be used as a reference or as a value.

For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1. 

EXAMPLE

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Top of Page

See Also

    Lookup and reference functions (reference)

Excel> Validating data

Apply data validation to cells

Hide All

You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered.

This article describes how data validation works in Excel and outlines the different data validation techniques available to you. It does not cover cell protection, which is a feature that lets you "lock" or hide certain cells in a worksheet so that they can't be edited or overwritten.

For more information on cell protection, see Protect worksheet or workbook elements

In this article

Overview of data validation

WHAT IS DATA VALIDATION?

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.

For example, in a marketing workbook, you can set up a cell to allow only account numbers that are exactly three characters long. When users select the cell, you can show them a message such as this one:

If users ignore this message and type invalid data in the cell, such as a two-digit or five-digit number, you can show them an actual error message.

In a slightly more advanced scenario, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed $4,000 in cell E7, which exceeds the maximum limit specified for commissions and bonuses.

If the payroll budget were to increase or decrease, the allowed maximum in E7 would automatically increase or decrease with it.

Data validation options are located on the Data tab, in the Data Tools group.

You configure data validation in the Data Validation dialog box.

Top of Page

WHEN IS DATA VALIDATION USEFUL?

Data validation is invaluable when you want to share a workbook with others in your organization, and you want the data entered in the workbook to be accurate and consistent. 

Among other things, you can use data validation to do the following:

Restrict data to predefined items in a list For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet. 

Restrict numbers outside a specified range For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell. 

Restrict dates outside a certain time frame For example, you can specify a time frame between today's date and 3 days from today's date. 

Restrict times outside a certain time frame For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens. 

Limit the number of text characters For example, you can limit the allowed text in a cell to 10 or fewer characters. Similarly, you can set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1), plus 10 characters. 

Validate data based on formulas or values in other cells For example, you can use data validation to set a maximum limit for commissions and bonuses of $3,600, based on the overall projected payroll value. If users enter more than $3,600 in the cell, they see a validation message.

Top of Page

DATA VALIDATION MESSAGES

What users see when they enter invalid data into a cell depends on how you have configured the data validation. You can choose to show an input message when the user selects the cell. Input messages are generally used to offer users guidance about the type of data that you want entered in the cell. This type of message appears near the cell. You can move this message, if you want to, and it remains until you move to another cell or press ESC.

You can also choose to show an error alert that appears only after users enter invalid data.

You can choose from three types of error alerts:

Stop                   Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel.

Information       Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can

             click OK to accept the invalid value or Cancel to reject it. 

You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.

Input messages and error alerts appear only when data is typed directly into the cells. They do not appear under the following conditions:

      A user enters data in the cell by copying or filling. 

      A formula in the cell calculates a result that is not valid. 

      A macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) enters invalid data in the cell. 

Top of Page

TIPS FOR WORKING WITH DATA VALIDATION

In the following list, you will find tips and tricks for working with data validation in Excel.

      If you plan to protect (protect: To make settings for a worksheet or workbook that prevent users from viewing or gaining access to the specified worksheet or workbook elements.) the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells. 

      If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won't be able to change the validation settings unless you stop sharing. However, Excel will continue to validate the cells that you have designated while the workbook is being shared. 

      You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically. 

      To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear All. 

      To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings. 

      When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won't have access to the list. 

If data validation isn't working, make sure that:

Users are not copying or filling data Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. To prevent users from copying and filling data by dragging and dropping cells, clear the Enable fill handle and cell drag-and-drop check box in the Advanced category of the Excel Options dialog box (File tab, Options command), and then protect the worksheet.

For more information about how to protect a worksheet, see .

Manual recalculation is turned off If manual recalculation is turned on, uncalculated cells can prevent data from being validated correctly. To turn off manual recalculation, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.

Formulas are error free Make sure that formulas in validated cells do not cause errors, such as #REF! or #DIV/0!. Excel ignores the data validation until you correct the error.

Cells referenced in formulas are correct If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won't appear.

Top of Page

How to handle a data validation alert

When you try to enter or change data in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) cell, you see a data validation error alert. This alert signifies that the owner of the workbook applied data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.) to the cell to prevent users from entering invalid data, and implemented the error alert to let you know that the data you entered is invalid.

You can enter only valid data in cells that have data validation applied. If you are not clear about the validity of the data that you can enter, you should contact the owner of the workbook.

If you inherited the workbook, you can modify or remove the data validation unless the worksheet is protected with a password that you do not know. If possible, you can contact the previous owner to help you unprotect the worksheet. You can also copy the data to another worksheet, and then remove the data validation.

Top of Page

Add data validation to a cell or range

In the next sections, you will learn the different techniques for adding validation to cells in a worksheet.

RESTRICT DATA ENTRY TO VALUES IN A DROP-DOWN LIST

It is not possible to change the font or font size for items in a list.

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table might be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You might currently be entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering data, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select List. 

5.  Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default). 

For example:

§  To limit entry to a question, such as "Do you have children?", to two choices, type Yes, No. 

§  To limit a vendor's quality reputation to three ratings, type Low, Average, High.

You can also create the list entries by referring to a range of cells elsewhere in the workbook.

Note The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

6.  Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell. 

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

0.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

Note If you want to allow users to type entries that are not in the list, clear the Show error alert after invalid data is entered check box instead.

1.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

2.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title uses the default string "Microsoft Excel" and the message is (by default): "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Top of Page

RESTRICT DATA ENTRY TO A WHOLE NUMBER WITHIN LIMITS

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select Whole number. 

5.  In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between. 

6.  Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value. 

For example, to set a minimum limit of deductions to two times the number of children in cell F1, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell." 

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

RESTRICT DATA ENTRY TO A DECIMAL NUMBER WITHIN LIMITS

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select Decimal. 

5.  In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between. 

6.  Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value. 

For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.

Note To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style in the Number group on the Home tab.

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell." 

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

RESTRICT DATA ENTRY TO A DATE WITHIN A TIME FRAME

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select Date. 

5.  In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.

6.  Enter the start, end, or specific date to allow. You can also enter a formula that returns a date. 

For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Minimum box, and enter =TODAY()+3 in the Maximum box.

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

RESTRICT DATA ENTRY TO A TIME WITHIN A TIME FRAME

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select Time. 

5.  In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than. 

6.  Enter the start, end, or specific time to allow. You can also enter a formula that returns a time. 

For example, to set a time frame for serving breakfast between the time when the restaurant opens (the value in cell H1) and five hours after that, select between in the Data box, enter =H1 in the Start time box, and then enter =H1+"5:00" in the End time box.

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

RESTRICT DATA ENTRY TO TEXT OF A SPECIFIED LENGTH

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select Text Length. 

5.  In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to. 

6.  Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value. 

For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell." 

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

CALCULATE WHAT IS ALLOWED BASED ON THE CONTENT OF ANOTHER CELL

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select the type of data that you want. 

5.  In the Data box, select the type of restriction that you want. 

6.  In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed. 

For example, to allow entries for an account only if the result won't go over the budget in cell E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.

7.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell. 

8.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

9.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

USE A FORMULA TO CALCULATE WHAT IS ALLOWED

1.  Select one or more cells to validate. 

2.  On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

3.  In the Data Validation dialog box, click the Settings tab. 

4.  In the Allow box, select Custom. 

5.  In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example: 

All the data in the cell range A1:A20 contains unique      =COUNTIF($A$1:$A$20,A1)=1  values.

You must enter the formula in the data validation for cell A1, and then fill the cells A2 though A20 so that the data validation for each cell in the range has a similar formula, but the second argument to the COUNTIF will match the current cell. 

6.  To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell. 

7.  Optionally, display an input message when the cell is clicked. 

How to display an input message

1.  Click the Input Message tab. 

2.  Make sure the Show input message when cell is selected check box is selected. 

3.  Fill in the title and text for the message.

8.  Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

How to specify a response to invalid data

1.  Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 

2.  Select one of the following options for the Style box: 

§  To display an information message that does not prevent entry of invalid data, select Information.

§  To display a warning message that does not prevent entry of invalid data, select Warning. 

§  To prevent entry of invalid data, select Stop.

3.  Fill in the title and text for the message (up to 225 characters). 

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

9.  Test the data validation to make sure that it is working correctly. 

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect. 

Tip If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Top of Page

LOOKUP function

Hide All

This article describes the formula syntax and usage of the LOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

The LOOKUP function returns a value either from a one-row or one-column range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) or from an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). The LOOKUP function has two syntax forms: the vector form and the array form. 

Look in a one-row or one-column range (known as a vector)               Vector formUse the vector form when you for a value and return a value from the same position in a         have a large list of values to look

second one-row or one-column range                                                                                     up or when the values may

change over time.

NOTES

      You can also use the LOOKUP function as an alternative to the IF function for elaborate tests or tests that exceed the limit for nesting of functions. See the examples in the array form. 

      For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP,HLOOKUP, or MATCHfunctions.

Vector form

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or onecolumn range (known as a vector) for a value and returns a value from the same position in a second onerow or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to match. The other form of LOOKUP automatically looks in the first column or row.

SYNTAX

LOOKUP(lookup_value, lookup_vector,  [result_vector])

The LOOKUP function vector form syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

lookup_value Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. 

lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. 

 The values in lookup_vector must be placed in ascending order: , -2, -1, 0, 1, 2,

, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

result_vector Optional. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.

REMARKS

      If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

      If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value. 

EXAMPLE

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Top of Page

Array form

The array form of LOOKUP looks in the first row or column of an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to match are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row.

 In general, it's best to use the HLOOKUP or VLOOKUP function instead of the array form of

LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs.

SYNTAX

LOOKUP(lookup_value, array)

The LOOKUP function array form syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

lookup_value Required. A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value. 

      If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

      If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

array Required. A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. 

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.

      If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row.

      If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

      With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column.

 The values in array must be placed in ascending order: , -2, -1, 0, 1, 2, , A-Z,

FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

EXAMPLES

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Example 2

The following example uses an array of numbers to assign a letter grade to a test score.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

See Also

    Lookup and reference functions (reference)

Excel> Worksheets> Selecting and navigation

Find and select cells that meet specific conditions

Hide All

You can use the Go To command to quickly find and select all cells that contain specific types of data

(such as formulas) or only cells that meet specific criteria (such as the last cell on the worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) that contains data or formatting).

1. Do one of the following: 

§  To search the entire worksheet for specific cells, click any cell. 

§  To search for specific cells within a defined area, select the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), rows, or columns that you want. 

How to select ranges, rows, or columns

             A single cell                         Click the cell, or press the arrow keys to move to the cell.

A large range of cells           Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.

Nonadjacent cells or   Select the first cell or range of cells, and then hold down CTRL while you select the other cell ranges   cells or ranges. 

You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.

You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.

Adjacent rows or         Drag across the row or column headings. Or select the first row or column; then hold columns            down SHIFT while you select the last row or column.

The first or last cell in a               Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or row or column     LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).

 To cancel a selection of cells, click any cell on the worksheet.

2.  On the Home tab, in the Editing group, click Find & Select, and then click Go To. 

Keyboard shortcut You can also press CTRL+G.

3.  Click Special. 

4.  In the Go To Special dialog box, click one of the following options. 

Comments            Cells that contain comments.

Formulas               Cells that contain formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).). 

The check boxes below Formulas define the type of formula that you want to select.

Current            The current region (current region: The block of filled-in cells that includes the currently selected cell region      or cells. The region extends in all directions to the first empty row or column.), such as an entire list.

Objects                  Graphical objects, including charts and buttons, on the worksheet and in text boxes.

If more than one column is selected, the comparison is performed for each column of that selection. The cell that is used in the comparison for each additional column is located in the same row as the active cell.

Dependents          Cells with formulas that refer to the active cell. 

Click Direct only to find only cells with formulas that refer directly to the active cell.

Click All levels to find all cells that directly or indirectly refer to the active cell.

Visible cells           Only cells that are visible in a range that crosses hidden rows or columns.

only

Data                 Only cells that have data validation rules applied.  validation

Click All to find all cells that have data validation applied.

Click Same to find cells that have the same data validation as the currently selected cell.

Excel> Conditional formatting

Add, change, find, or clear conditional formats

Hide All

Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.

What do you want to do?

Learn more about conditional formatting

Conditional formatting helps you visually answer specific questions about your data. You can apply conditional formatting to a cell range, a Microsoft Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable report.

THE BENEFITS OF CONDITIONAL FORMATTING

Whenever you analyze data, you often ask yourself questions, such as:

      Where are the exceptions in a summary of profits over the past five years? 

      What are the trends in a marketing opinion poll over the past two years? 

      Who has sold more than $50,000 dollars this month? 

      What is the overall age distribution of employees? 

      Which products have greater than 10% revenue increases from year to year? 

      Who are the highest performing and lowest performing students in the freshman class?

Conditional formatting helps to answer these questions by making it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on conditions (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition.

You can sort and filter by format, including cell color and font color, whether you have manually or conditionally formatted the cells.

When you create a conditional format, you can reference only other cells on the same worksheet or, in certain cases, cells on worksheets in the same currently open workbook. You cannot use conditional formatting on external references to another workbook.

CONDITIONAL FORMATTING FOR A PIVOTTABLE REPORT

Conditional formatting in a PivotTable report is different from conditional formatting in a cell range or an Excel table in several ways:

      If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed. 

      The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows. 

In the data hierarchy, children do not inherit conditional formatting from the parent, and the

parent does not inherit conditional formatting from the children.

      There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to option button, the

New Formatting Rule dialog box, or the Edit Formatting Rule dialog box. The three methods of scoping (Home tab, Styles group, Conditional Formatting command) give you greater flexibility depending on your needs:

Scoping by selection Use this method if you want to select:

      A contiguous set of fields in the Values area, such as all of the product totals for one region. 

      A discontiguous set of fields in the Values area, such as product totals for different regions across levels in the data hierarchy.

Scoping by value field Use this method if you want to:

      Avoid making many discontiguous selections. 

      Conditionally format a set of fields in the Values area for all levels in the hierarchy of data. 

      Include subtotals and grand totals.

Scoping by corresponding field Use this method if you want to:

      Avoid making many discontiguous selections. 

      Conditionally format a set of fields in the Values area for one level in the hierarchy of data. 

      Exclude subtotals.

When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. However, when you scope by corresponding field, instead of by using all visible values, you can optionally apply the conditional format for each combination of:

      A column and its parent row field. 

      A row and its parent column field.

CONDITIONAL FORMATTING ACROSS WORKSHEETS

You can use conditional formatting on cells that are referenced in another worksheet in the same workbook. This capability is not available between workbooks.

Top of Page

Format all cells by using a two-color scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and red color scale, you can specify that higher value cells have a more green color and lower value cells have a more red color.

 If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value other than an error value.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

3.  Select a two-color scale. 

 Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values.

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the

Formatting Options button that appears next to a PivotTable field that has conditional formatting applied.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed. 

4.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Selected cells. 

§  All cells for a Value label, click All cells showing <Value label> values. 

§  All cells for a Value label, excluding subtotals and the grand total, click All cells showing <Value label> values for <Row Label>.

5.  Under Select a Rule Type, click Format all cells based on their values (default). 

6.  Under Edit the Rule Description, in the Format Style list box, select 2-Color Scale. 

7.  To select a type in the Type box for Minimum and Maximum, do one of the following: 

§  Format lowest and highest values Select Lowest Value and Highest Value. 

In this case, you do not enter a Minimum and MaximumValue.

§  Format a number, date, or time value Select Number, and then enter a Minimum and MaximumValue. 

§  Format a percentage Select Percent, and then enter a Minimum and MaximumValue. 

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

§  Format a percentile Select Percentile and then enter a Minimum and MaximumValue. 

Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20^thpercentile) in one color grade proportion and low values (such as the bottom 20^th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

§  Format a formula result Select Formula, and then enter values for Minimum and Maximum.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.

8.   

§  Make sure that the Minimum value is less than the Maximum value. 

§  You can choose a different type for the Minimum and Maximum. For example, you can choose a number for Minimum a percentage for Maximum.

9. To choose a Minimum and Maximum color scale, click Color for each, and then select a color. 

If you want to choose additional colors or create a custom color, click More Colors.

The color scale that you select is displayed in the Preview box.

Top of Page

Format all cells by using a three-color scale

Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

 If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value that you specify (such as 0, or "N/A") instead of an error value.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

3.  Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values. 

 Hover over the color scale icons to see which icon is a three-color scale.

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the

Formatting Options button that appears next to a PivotTable field that has conditional formatting applied..

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed. 

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

5.  Under Select a Rule Type, click Format all cells based on their values.

6.  Under Edit the Rule Description, in the Format Style list box, select 3-Color Scale. 

7.  Select a type for Minimum, Midpoint, and Maximum. Do one of the following: 

§  Format lowest and highest values Select a Midpoint.

In this case, you do not enter a Lowest and HighestValue.

§  Format a number, date, or time value Select Number, and then enter a value for Minimum, Midpoint, and Maximum. 

§  Format a percentage Select Percent, and then enter a value for Minimum, Midpoint, and Maximum. 

Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.

§  Format a percentile Select Percentile and then enter a value for Minimum, Midpoint, and Maximum. 

Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20^th percentile) in one color grade proportion and low values (such as the bottom 20^th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

§  Format a formula result Select Formula, and then enter a value for Minimum, Midpoint, and Maximum. 

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.

8.   

§  You can set minimum, midpoint, and maximum values for the range of cells. Make sure that the value in Minimum is less than the value in Midpoint, which in turn is less than the value in Maximum. 

§  You can choose a different type for Minimum, Midpoint, and Maximum. For example, you can choose a Minimum number, Midpoint percentile, and Maximum percent. 

§  In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements.

9. To choose a Minimum, Midpoint, and Maximum color scale, click Color for each, and then select a color. 

If you want to choose additional colors or create a custom color, click More Colors.

The color scale that you select is displayed in the Preview box.

Top of Page

Format all cells by using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

 If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value (such as 0 or "N/A") instead of an error value.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Data Bars, and then select a data bar icon. 

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the

Apply formatting rule to option button.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

5.  Under Select a Rule Type, click Format all cells based on their values.

6.  Under Edit the Rule Description, in the Format Style list box, select Data Bar. 

7.  Select a Minimum and MaximumType. Do one of the following: 

§  Format lowest and highest values Select Lowest Value and Highest Value. 

In this case, you do not enter a value for Minimum and Maximum.

§  Format a number, date, or time value Select Number, and then enter a Minimum and MaximumValue. 

§  Format a percentage Select Percent, and then enter a value for Minimum and Maximum. 

Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.

§  Format a percentile Select Percentile and then enter a value for Minimum and Maximum. 

Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20^th percentile) in one data bar proportion and low values (such as the bottom 20^th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

§  Format a formula result Select Formula, and then enter a value for Minimum and Maximum. 

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.

8.   

§  Make sure that the Minimum value is less than the Maximum value. 

§  You can choose a different type for Minimum and Maximum. For example, you can choose a Minimum number and a Maximum percent.

9.  To choose a Minimum and Maximum color scale, click Bar Color.

If you want to choose additional colors or create a custom color, click More Colors.

The bar color that you select is displayed in the Preview box.

10.  To show only the data bar and not the value in the cell, select Show Bar Only. 

11.  To apply a solid border to data bars, select Solid Border in the Border list box and choose a color for the border. 

12.  To choose between a solid bar and a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box. 

13.  To format negative bars, click Negative Value and Axis and then, in the Negative Value and Axis Settings dialog box, choose options for the negative bar fill and border colors. You can choose position settings and a color for the axis. When you are finished selecting options, click OK. 

14.  You can change the direction of bars by choosing a setting in the Bar Direction list box. This is set to Context by default, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to present your data.

Top of Page

Format all cells by using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it. To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions. You can also create your own combination of icon sets; for example, a green "symbol" check mark, a yellow "traffic light", and a red "flag."

Issue: Conditional formatting doesn't appear for any cell in the range

If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value (such as 0 or "N/A") instead an error value.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select an icon set. 

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

5.  Under Select a Rule Type, click Format all cells based on their values.

6.  Under Edit the Rule Description, in the Format Style list box, select Icon Set. 

1.  Select an icon set. The default is 3 Traffic Lights (Unrimmed). The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set. 

2.  If you want, you can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom. 

3.  Do one of the following: 

§  Format a number, date, or time value Select Number.

§  Format a percentage Select Percent. 

Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional. 

§  Format a percentile Select Percentile. 

Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20^th percentile) using a particular icon and low values (such as the bottom 20^th percentile) using another icon, because they represent extreme values that might skew the visualization of your data.

§  Format a formula result Select Formula, and then enter a formula in each Value box. 

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.

4.  To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order. 

5.  To show only the icon and not the value in the cell, select Show Icon Only.

NOTES

§  You may need to adjust the column width to accommodate the icon.

§  There are three sizes of icons. The size of the icon that is displayed depends on the font size that is used in that cell.

Top of Page

Format only cells that contain text, number, or date or time values

To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to "SouthEast."

You cannot conditionally format fields in the Values area of a PivotTable report by text or date, only by number.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules. 

3.  Select the command that you want, such as Between, Equal To Text that Contains, or A Date Occurring. 

4.  Enter the values that you want to use, and then select a format.

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the

Apply formatting rule to option button.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or on other worksheets, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

5.  Under Select a Rule Type, click Format only cells that contain. 

6.  Under Edit the Rule Description, in the Format only cells with list box, do one of the following: 

§  Format by number, date, or time Select Cell Value, select a comparison operator, and then enter a number, date, or time. 

For example, select Between and then enter 100 and 200, or select Equal to and then enter 1/1/2009.

You can also enter a formula that returns a number, date, or time value. If you enter a formula, start it with an equal sign (=). Invalid formulas result in no formatting being applied.

It's a good idea to test the formula to make sure that it doesn't return an error value.

§  Format by text Select Specific Text, choose a comparison operator, and then enter text. 

For example, select Contains and then enter Silver, or select Starting with and then enter Tri.

Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is 255 characters.

You can also enter a formula that returns text. If you enter a formula, start it with an equal sign (=). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.

§  Format by date Select Dates Occurring, and then select a date comparison. 

For example, select Yesterday or Next week.

§  Format cells with blanks or no blanks Select Blanks or No Blanks. 

A blank value is a cell that contains no data and is different from a cell that contains

one or more spaces (spaces are considered as text).

§  Format cells with error or no error values Select Errors or No Errors. 

Error values include: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, and #NULL!.

7.  To specify a format, click Format. 

The Format Cells dialog box is displayed.

8.  Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK. 

You can choose more than one format. The formats that you select are displayed in the Preview box.

Top of Page

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department personnel analysis.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.

3.  Select the command that you want, such as Top 10 items or Bottom 10 %. 

4.  Enter the values that you want to use, and then select a format.

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the

Apply formatting rule to option button.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Apply Rule To, to optionally change the scope fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

5.  Under Select a Rule Type, click Format only top or bottom ranked values.

6.  Under Edit the Rule Description, in the Format values that rank in the list box, select Top or Bottom. 

7.  Do one of the following: 

§  To specify a top or bottom number, enter a number and then clear the % of the selected range check box. Valid values are 1 to 1000. 

§  To specify a top or bottom percentage, enter a number and then select the % of the selected range check box. Valid values are 1 to 100.

8.  Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field. 

By default, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

§  A column and its parent row field, by selecting each Column group. 

§  A row and its parent column field, by selecting each Row group.

9.  To specify a format, click Format. 

The Format Cells dialog box is displayed.

10.  Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK. 

You can choose more than one format. The formats that you select are displayed in the Preview box.

Top of Page

Format only values that are above or below average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.

3.  Select the command that you want, such as Above Average or Below Average.

4.  Enter the values that you want to use, and then select a format.

 You can change the method of scoping for fields in the Values area of a PivotTable report by using the

Apply formatting rule to option button.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

5.  Under Select a Rule Type, click Format only values that are above or below average. 

6.  Under Edit the Rule Description, in the Format values that are list box, do one of the following: 

§  To format cells that are above or below the average for all of the cells in the range, select Above or Below. 

§  To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation.

7.  Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field. 

By default, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

§  A column and its parent row field, by selecting each Column group. 

§  A row and its parent column field, by selecting each Row group.

8.  Click Format to display the Format Cells dialog box. 

9.  Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK. 

You can choose more than one format. The formats that you select are displayed in the Preview box.

Top of Page

Format only unique or duplicate values

You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Quick formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules. 

3.  Select Duplicate Values. 

4.  Enter the values that you want to use, and then select a format.

Advanced formatting

1.  Select one or more cells in a range, table, or PivotTable report. 

2.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

3.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

4.  Under Select a Rule Type, click Format only unique or duplicate values.

5.  Under Edit the Rule Description, in the Format all list box, select unique or duplicate. 

6.  Click Format to display the Format Cells dialog box. 

7.  Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK. 

You can choose more than one format. The formats that you select are displayed in the Preview box.

Top of Page

Use a formula to determine which cells to format

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range.

1.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

2.  Do one of the following: 

§  To add a conditional format, click New Rule. 

The New Formatting Rule dialog box is displayed.

§  To change a conditional format, do the following: 

1.  Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.  Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by selecting Expand Dialog . 

3.  Select the rule, and then click Edit rule. 

The Edit Formatting Rule dialog box is displayed.

3.  Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

§  Selection, click Just these cells. 

§  Corresponding field, click All <value field> cells with the same fields. 

§  Value field, click All <value field> cells.

4.  Under Select a Rule Type, click Use a formula to determine which cells to format. 

1. Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula. 

You must start the formula with an equal sign (=) and the formula must return a logical value of TRUE (1) or FALSE (0).

Example 1: Use one conditional format with multiple criteria and cell references outside of the range of cells

In this formula, one conditional format with multiple criteria applied to the range A1:A5 formats the cells green if the average value for all of the cells in the range is greater than the value in cell F1 and any cell in the range has a minimum value greater than or equal to the value in G1. Cells F1 and G1 are outside of the range of cells for which the conditional format is applied. The AND function combines multiple criteria, and the AVERAGE and MIN functions calculate the values.

            =AND(AVERAGE($A$1:$A$5)>$F$1, MIN($A$1:$A$5)>=$G$1)        Green cell color

Example 2: Shade every other row by using the MOD and ROW functions

This formula shades every other row in the range of cells a blue cell color. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number. Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted.

            =MOD(ROW(),2)=1        Blue cell color

2.  Click Format to display the Format Cells dialog box. 

3.  Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK. 

You can choose more than one format. The formats that you select are displayed in the Preview box.

5. You can enter cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.). If you want Excel to adjust the references for each cell in the selected range, use relative cell references (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell

referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.).

Top of Page

Find cells that have conditional formats

If your worksheet has one or more cells with a conditional format, you can quickly locate them so that you can copy, change, or delete the conditional formats. You can use the Go To Special command to either find only cells with a specific conditional format or find all cells with conditional formats.

FIND ALL CELLS THAT HAVE A CONDITIONAL FORMAT

1.  Click any cell without a conditional format. 

2.  On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Conditional Formatting. 

FIND ONLY CELLS WITH THE SAME CONDITIONAL FORMAT

1.  Click the cell that has the conditional format that you want to find. 

2.  On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special. 

3.  Click Conditional formats. 

4.  Click Same under Data validation. 

Top of Page

Clear conditional formats

Worksheet

1.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Clear Rules.

2.  Click Entire Sheet.

A range of cells, table, or PivotTable

3.  Select the range of cells, table, or PivotTable for which you want to clear conditional formats. 

4.  On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Clear Rules.

5.  Depending on what you have selected, click Selected Cells, This Table, or This PivotTable.

Top of Page

VLOOKUP function

Hide All

This article describes the formula syntax and usage of the VLOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

You can use the VLOOKUP function to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration.

If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value ("Axel Delgado").

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

lookup_value Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value. 

table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are

the values searched by lookup_value. These values can be text, numbers, or logical values.

Uppercase and lowercase text are equivalent. 

col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. 

If the col_index_num argument is:

            Less than 1, VLOOKUP returns the #VALUE! error value. 

      Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: 

      If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. 

 If range_lookup is either TRUE or is omitted, the values in the first column of

table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

For more information, see Sort data in a range or table.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

      If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Remarks

When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value. 

For more information, see CLEAN functionand TRIM function.

      When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value. 

      If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters — the question mark (?) and asterisk (*) — in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Example

EXAMPLE 1

This example searches the Density column of an atmospheric properties table to find corresponding values in the Viscosity and Temperature columns. (The values are for air at 0 degrees Celsius at sea level, or 1 atmosphere.)

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 2

This example searches the Item-ID column of a baby products table and matches values in the Cost and Markup columns to calculate prices and test conditions.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 3

This example searches the ID column of an employee table and matches values in other columns to calculate ages and test for error conditions.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

See Also

    Lookup and reference functions (reference)

IS functions

Hide All

This article describes the formula syntax and usage of the IS functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE.

You can use an IS function to get information about a value before performing a calculation or other action with it. For example, you can use the ISERROR function in conjunction with the IF function to perform a different action if an error occurs:

=IF(ISERROR(A1), "An error occurred.", A1 * 2)

This formula checks to see if an error condition exists in A1. If so, the IF function returns the message "An error occurred." If no error exists, the IF function performs the calculation A1*2.

Syntax

ISBLANK(value) ISERR(value)

ISERROR(value)

ISLOGICAL(value) ISNA(value)

ISNONTEXT(value)

ISNUMBER(value)

ISREF(value)

ISTEXT(value)

The IS function syntax has the following argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

value Required. The value that you want tested. The value argument can be a blank (empty cell), error, logical value, text, number, or reference value, or a name referring to any of these.

       ISTEXT                Value refers to text.

Remarks

      The value arguments of the IS functions are not converted. Any numeric values that are enclosed in double quotation marks are treated as text. For example, in most other functions where a number is required, the text value "19" is converted to the number 19. However, in the formula ISNUMBER("19"), "19" is not converted from a text value to a number value, and the ISNUMBER function returns FALSE. 

      The IS functions are useful in formulas for testing the outcome of a calculation. When combined with the IF function, these functions provide a method for locating errors in formulas (see the following examples). 

Example

EXAMPLE 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

See Also

    Information functions (reference)

Excel> Worksheets> Copying and moving data

Copy specific cell contents or attributes in a worksheet

Hide All

You can copy and paste specific cell contents or attributes (such as formulas, formats, or comments) from the Clipboard in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) by using the Paste Special command.

Note For more information about how to copy an entire worksheet (or sheet) to another location in the same workbook or to a different workbook, see Move or copy a worksheet. For more information about copying cells and their content, see Move or copy cells and cell contents.

1.  On a worksheet, select the cells that contain the data or attributes that you want to copy. 

2.  On the Home tab, in the Clipboard group, click Copy . 

Keyboard shortcut You can also press CTRL+C.

3.  Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.). 

 To move or copy a selection to a different worksheet or workbook, click another worksheet

tab or switch to another workbook, and then select the upper-left cell of the paste area.

4.  On the Home tab, in the Clipboard group, click Paste , and then click Paste Special. 

Keyboard shortcut You can also press CTRL+ALT+V.

5.  In the Paste Special dialog box, under Paste, do one of the following: 

§    To paste static data, click the attribute of the copied data that you want. 

Values and number formats         Paste only values and number formatting options from the selected cells.

§    To paste linked data, click All or All except borders.

6.  Do any of the following as needed: 

§    To mathematically combine the contents of the copy area (copy area: The cells that you copy when you want to paste data into another location. After you copy cells, a moving border appears around them to indicate that they've been copied.) with the contents of the paste area, under Operation, specify the mathematical operation that you want to apply to the copied data. 

Divide                        Divide the values in the paste area by the values in the copy area.

§    Mathematical operations can be applied only to values. To use an option other than

None, you must select All, Values, All except border, or Values and number formats under Paste.

§    To avoid replacing values in your paste area when blank cells occur in the copy area, select Skip blanks. 

§    To change columns of copied data to rows, or vice versa, select Transpose.

Note Depending on the type of data that you copy and the Paste options that you select, specific options might be unavailable.

7.  If you want to link the pasted data to the original data, click Paste Link.

When you paste a link to the data that you copied, Excel enters an absolute reference to the copied cell or range of cells in the new location.

NotePaste Link is available only when you select All or All except borders under Paste in the Paste Special dialog box.

 You can also insert copied formulas, values, or linked data in the paste area by clicking Paste (on the

Home tab, in the Clipboard group), and then clicking Formulas, Paste Values, or Paste Link.

COUNTIF function

Hide All

This article describes the formula syntax and usage of the COUNTIF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person. For example:

=COUNTIF(B2:B25,"Nancy")

To count cells based on multiple criteria, see COUNTIFSfunction.

Syntax

COUNTIF(range, criteria)

The COUNTIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

range Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. 

criteria Required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32". 

NOTES

      You can use the wildcard characters — the question mark (?) and the asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. 

      Criteria are case insensitive; for example, the string "apples" and the string "APPLES" will match the same cells.

Example

EXAMPLE 1: COMMON COUNTIF FORMULAS

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE 2: COUNTIF FORMULAS USING WILDCARD CHARACTERS AND HANDLING BLANK VALUES 

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

To view a number as a percentage, select the cell and then, on the Home tab, in the Number group, click Percentage Style .

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

See Also

    Statistical functions (reference)

AVERAGEIF function

Show All

This article describes the formula syntax and usage of the AVERAGEIFfunction in Microsoft Excel.

Description

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

Syntax

AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers. 

Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4. 

Average_range Optional. The actual set of cells to average. If omitted, range is used.

Remarks

      If a cell in average_range is an empty cell, AVERAGEIF ignores it. 

      If range is a blank or text value, AVERAGEIF returns the #DIV0! error value. 

      If a cell in criteria is empty, AVERAGEIF treats it as a 0 value. 

      If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value. 

      You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. 

      Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example: 

The AVERAGEIF function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5. 

Median which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median.

For example, the median of 2, 3, 3, 5, 7, and 10 is 4. 

Mode which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Example

EXAMPLE: AVERAGING PROPERTY VALUES AND COMMISSIONS

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

EXAMPLE: AVERAGING PROFITS FROM REGIONAL OFFICES

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

    Statistical functions (reference)

AVERAGEIFS function

Hide All

This article describes the formula syntax and usage of the AVERAGEIFSfunction (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

Returns the average (arithmetic mean) of all cells that meet multiple criteria.

Syntax

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], )

The AVERAGEIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Average_range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers. 

Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria. 

Criteria1, criteria2, Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.

For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Remarks

      Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero). 

      Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. 

      Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. 

      If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value. 

      If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value. 

      You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

The AVERAGEIFS function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5. 

Median which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median.

For example, the median of 2, 3, 3, 5, 7, and 10 is 4. 

Mode which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Examples

EXAMPLE: AVERAGING STUDENT GRADES

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

EXAMPLE: AVERAGING REAL ESTATE PRICES

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

See Also

    Statistical functions (reference)

SUMIF function

Hide All

This article describes the formula syntax and usage of the SUMIF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

You use the SUMIF function to sum the values in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula:

=SUMIF(B2:B25,">5")

In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

To sum cells based on multiple criteria, see SUMIFS function.

Syntax

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. 

criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, 32, "32", "apples", or TODAY(). 

 Any text criteria or any criteria that includes logical or mathematical symbols must be

enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.

sum_range Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

NOTES

      The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using theupper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument. For example: 

      You can use the wildcard characters — the question mark (?) and asterisk (*) — as the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. 

Example

EXAMPLE 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1.  Select the example in this article. 

 Do not select the row or column headers.

Selecting an example from Help

2.  Press CTRL+C. 

3.  In Excel, create a blank workbook or worksheet. 

4.  In the worksheet, select cell A1, and press CTRL+V. 

 For the example to work properly, you must paste it into cell A1 of the worksheet.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

See Also

    Math and trigonometry functions (reference)