The popular spread­sheet program Excel included in Microsoft Office allows you to enter numbers and text in spread­sheets to make sense of your data. Excel offers a wide range of functions which have a variety of uses, es­pe­cial­ly for work­sheets con­tain­ing multiple values. With over 400 different options, you can edit or evaluate your data au­to­mat­i­cal­ly. Excel AND has always been one of the most popular functions. Using this function, you can define up to 255 con­di­tions and check whether the values in your spread­sheet meet these con­di­tions in just a few clicks. In the following, we will provide you with step-by-step in­struc­tions on how to use the Excel AND function.

Excel AND function – TRUE or FALSE?

Excel AND is one of Excel’s logical functions which allows you to check whether or not certain con­di­tions have been met. This function results in two possible return values: if all the con­di­tions you specified have been met, Excel will output the return value “TRUE”, but if even one condition has not been met, the return value will be “FALSE”.

Excel AND: a step-by-step ex­pla­na­tion of how to use this function

So, you have an Excel spread­sheet con­tain­ing a many values, and you wish to determine which values meet specific re­quire­ments. Per­form­ing a manual search would take a lot of time. Not to mention, the human eye can easily overlook values and is thus a possible source of error. That is why Excel provides the AND function which is great for au­to­mat­i­cal­ly checking whether certain con­di­tions have been met. In the following, you will find step-by-step in­struc­tions on how to use the Excel AND function.

Step 1: Defining con­di­tions

In this example, we will check which employees meet all the re­quire­ments for receiving a bonus payment. There are two con­di­tions: the employee must have obtained at least three new customers and two or more contract ex­ten­sions. So, for example, if an employee has obtained five new customers (i.e. more than required) but only one contract extension, they will not receive a bonus. The Excel AND function works well for this concrete example because it allows the program to optimally test whether all the con­di­tions have been met.

Image: Excel example: creating a spreadsheet
To use the Excel AND function to determine which employees will receive a bonus, the cor­re­spond­ing values to be analyzed are first entered in the spread­sheet.

Step 2: Using the Excel AND function

After entering all the values for this Excel AND example, you apply the function to the spread­sheet. For clarity’s sake, it is rec­om­mend­ed to use a separate column for the function and its return value. There are two ways to enter the function:

Enter the function in the cell

You can enter the function in the cell where you want the return value to be displayed. Functions always begin with an equals sign and the name of the function. This is followed by the in­di­vid­u­al­ly defined pa­ra­me­ters for the function which are placed inside paren­the­ses.

The following is the general syntax for the Excel AND function:

=AND([logical1];[logical2];etc.)
Note

If you use lowercase letters when writing “AND”, Excel will au­to­mat­i­cal­ly correct the input. Therefore, you do not need to worry about using the correct cap­i­tal­iza­tion.

The logical values are your con­di­tions which are separated with a comma or semicolon depending Excel version you are using. When using Excel AND, you can enter anywhere from two to 255 con­di­tions in the program. However, you should avoid using an excessive number of con­di­tions. The more con­di­tions a function contains, the more complex and nested it becomes. This makes it more difficult to create, check, and manage a function.

When defining and entering the logical values (i.e. the con­di­tions), use the equals sign (=), greater-than sign (>) and less-than sign (<). In our example, logical value 1 is the condition stating that an employee must have obtained at least three new customers. The value in column B must therefore be greater than or equal to three. You define this condition by entering “B2>=3”. Logical value 2 is the condition stating that the employee must have also obtained two contract ex­ten­sions. This is defined by entering “C2>=2”. In our example, the entire Excel function is as follows:

=AND(B2>=3;C2>=2)
Image: Excel AND function: entering the function in the cell
Op­tion­al­ly, you can enter the Excel AND function directly in the cell.

Press the Enter key on your keyboard to confirm your entry. Excel will then display the resulting return value in the cell, not the function. In our example, Employee 1 only meets one of the two con­di­tions. Therefore, the displayed return value is “FALSE”.

Image: Excel AND: example of the function’s return value
Cell D2 only contains the resulting return value of the Excel AND function, not the un­der­ly­ing function.

Open the function using the menu

Al­ter­na­tive­ly, Excel offers the option to use a separate dialog box for the Excel AND function. To open this dialog box, go to “Formulas” and then select “Insert Function” in the menu or left-click on the cor­re­spond­ing icon located next to the formula bar.

Image: Excel “Insert Function” dialog box
There are multiple ways to open the dialog box for the Excel AND function via the menu.

Excel will then open the “Insert Function” dialog box. When it opens, the category “Most Recently Used” will au­to­mat­i­cal­ly be selected. If you have recently used the Excel AND function, it will appear in the list below. If this is the case, select the “AND” function. This will highlight it in blue. Confirm your selection by clicking “OK”.

Image: “Insert Function” dialog box: Excel AND function
Excel will display a list of the most recently used functions which usually includes Excel AND.

If the desired function is not included in the list, open the drop-down menu next to “Or select a category” and select “All”. Excel will then display a list of all the available functions. Use the arrows to scroll until you find the “AND” option. Left-click on the function. Once it is high­light­ed blue, confirm your selection by clicking “OK”.

Image: Selecting the Excel AND function
Select the Excel AND function from a list of over 400 functions.

Excel will then open the “Function Arguments” dialog box. In this dialog box, you enter each condition on a separate line. As you do this, the program will already display whether the con­di­tions have been met. Once you have entered all the con­di­tions, confirm them by left-clicking on “OK”. Excel then applies the formula and displays the resulting return value in the cell.

Image: Excel AND: “Function Arguments” dialog box
To define the con­di­tions for the Excel AND function, enter the desired logical values.

Step 3: Checking all the spread­sheet values

You do not need to manually insert the Excel AND function in each row. If the defined con­di­tions apply to all values (all employees in our example), Excel can apply the function to all lines in just a few clicks.

To do this, first select the cell con­tain­ing the AND function by left-clicking on it. Then, left-click on the corner of the green rectangle around the cell. Keep holding the mouse button and drag the function across all the cells where it is to be applied. In our example, this includes cells D2 (Employee 1) to D11 (Employee 10).

Image: Excel AND function: extending it to multiple cells
Click on the green rectangle and drag it over the other cells to apply the Excel AND function to them.

Excel will au­to­mat­i­cal­ly carry the AND function over to the other cells and adjust the formula ac­cord­ing­ly. The program will then check each employee to see whether they have met the con­di­tions.

Image: Excel AND: example of how it is used
Excel displays whether the con­di­tions have been met for all employees.

Comparing expected and actual values using the IF function

Using the IF function, you can check whether a requested value matches an expected one. The general syntax for this formula is as follows:

=IF(logical_test;[value_if_true];[value_if_false])

The IF function is very similar to the AND function. The following are the two main dif­fer­ences:

  • The IF function only checks if one of the con­di­tions has been met.
  • You can in­di­vid­u­al­ly define the displayed return values for the eval­u­a­tion when using the IF function.

When using the Excel AND function, the possible resulting return values are “TRUE” and “FALSE”. In contrast, when using the IF function, you use the pa­ra­me­ters “value_if_true” and “value_if_false” to define the resulting return values that Excel displays in the cell after the eval­u­a­tion.

Tip

The special SUMIF function can be used to add only specific values together. You can read about how to use this function in our article “The SUMIF function explained”.

In our example spread­sheet, the IF function is used as follows: If an employee obtains at least five new customers, they will receive a bonus of 100 euros. If the employee does not achieve this target number, their bonus will be 0 euros. This condition is defined by the following IF function:

=IF(B2>=5;100;0)
Image: IF function in an Excel spreadsheet
You can also enter other functions like the IF function directly in the cell.

Combining the Excel AND function with the IF function

Excel allows you to combine multiple functions in the same formula. Combining the AND and IF functions provides two ad­van­tages: you can define multiple con­di­tions and in­di­vid­u­al­ly define what the displayed return values will be.

When you combine the two functions, the general syntax will look like the following:

=IF(AND([logical1];[logical2];[logical3];etc.);[value_if_true];[value_if_false])

For our example, the formula would look like this in practice:

=IF(AND(B2>=3;C2>=2);100;0)
Image: Combination of the Excel AND and IF functions
Excel allows for formulas that combine multiple functions together.

After con­firm­ing the combined formula in our example, we get the following results.

Image: Excel spreadsheet example with results
Excel spread­sheet example with results
Go to Main Menu