Sometimes we need to use Excel **Conditional Formatting** feature on **Multiple Columns** for quick calculation. This feature can scan the dataset easily and make the worksheet attractive. In this article, we are going to learn about the **Conditional Formatting** feature on multiple columns with some beautiful examples and explanations.

## Download Practice Workbook

Download the following workbook and exercise.

## 10 Easy Ways to Apply Conditional Formatting on Multiple Columns in Excel

Assuming, we have a dataset (**B4:F9**) of employees with their project names and working hours of each day. Now, we will use this dataset to show you how to apply conditional formatting on multiple columns in Excel.

### 1. Use AND Function with Conditional Formatting on Multiple Columns

We are going to use Excel **AND function** with **Conditional Formatting** to highlight which cells contain more than **5 **hours.

**STEPS:**

- Firstly, select the range
**D5:F9**of working hours each day. - Next, go to the
**Home**tab. - Then, select the
**Conditional Formatting**drop-down >> select the**New Rule**.

- Now, a
**New Formatting Rule**window will pop up. - After that, go to the
**Use a formula to determine which cells to format**option. - In the formula box, type the formula.

`=AND($D5>5,$E5>5,$F5>5)`

- Then, select
**Format**option.

- From the
**Format Cells**window, go to the**Fill**tab. - After that, select a background color. We can see the color preview from the
**Sample**option. - Lastly, click on
**OK**.

- Again, click on
**OK**.

- Finally, we can see the result.

🔎 **How Does the Formula Work?**

Excel **AND function** will return **TRUE** if cells **D5**, **E5**, **F5** are greater than **5**; otherwise **FALSE**. The **Conditional Formatting **will apply the formula to the whole dataset.

**Read more:** **Conditional Formatting with Formula for Multiple Conditions in Excel**

### 2. Apply Conditional Formatting on Multiple Columns with OR Function in Excel

Now, we are going to use Excel **OR function** with **Conditional Formatting **to find out which cells contain more than **7 **hours and less than **4** hours.

**STEPS:**

- In the beginning, open the
**New Formatting Rule**window going through the same steps shown in**Method 1**. - After that, go to the
**Use a formula to determine which cells to format**option. - Then, in the formula box, type the formula.

`=OR(D5>7,D5<4)`

- Next, go to the
**Format**option and select the cell background color as we did in the first method. - Lastly, click on
**OK**.

- In the end, we can see the output.

🔎 **How Does the Formula Work?**

Excel **OR function** will return **TRUE** if cells **D5 **is greater than **7 **or less than **4**; otherwise **FALSE**. The **Conditional Formatting **will apply the formula to the whole dataset.

**Read more:** **How to Do Conditional Formatting for Multiple Conditions**

### 3. Employ COUNTIF Function in Conditional Formatting for More Than Two Columns

Next, we are going to use the Excel **COUNTIF function** with **Conditional Formatting** to see which rows contain values more than **4**.

**STEPS:**

- To start with, open the
**New Formatting Rule**window following the same steps shown in**Method 1**. - After that, go to the
**Use a formula to determine which cells to format**option. - Now, in the formula box, type the formula:

`=COUNTIF($D5:$F5,">4")>2`

- Then, go to the
**Format**option and select the cell background color as we did in the first method. - Lastly, click on
**OK**.

- Finally, We can see the highlighted rows.

🔎 **How Does the Formula Work?**

Excel **COUNTIF function** will count the cell numbers if it is greater than **4** in a range of **$D5:$F5**. Then it will return **TRUE** for the exact match; otherwise **FALSE**. The **Conditional Formatting **will help to apply the formula to the whole dataset.

**Read more:** **How to Apply Conditional Formatting to Multiple Rows**

### 4. Find Duplicate Rows Based on Multiple Columns Using COUNTIFS Function

Here we have a dataset (**B4:D9**) of employees with their project names and total working hours. The **Conditional Formatting** feature with the Excel **COUNTIFS function** can help us to find duplicate rows based on multiple columns. The **COUNTIFS function** will count the number of cells from a range based on multiple criteria.

**STEPS:**

- Firstly, select the dataset.
- Next, go to the
**Home**tab >**Conditional Formatting**drop-down >**New Rule**.

- Now, we see a
**New Formatting Rule**window pop up. Go to the**Use a formula to determine which cells to format**option. - Then, in the formula box, type the formula.

`=COUNTIFS($B$5:$B$9,$B5,$C$5:$C$9,$C5,$D$5:$D$9,$D5)>1`

- Now, go to the
**Format**option. - After that, select the cell background color as we did in the first method.
- Lastly, click on
**OK**.

- Thus, we can see the duplicate rows are highlighted.

**Read more:** **Conditional Formatting Entire Column Based on Another Column**

### 5. Use Conditional Formatting to Find Duplicates from Multiple Columns in Excel

Excel has some built-in features to make the calculation easier. **Conditional Formatting** is one of them. This feature helps to find duplicates from the multiple columns in Excel. Now, we will find the duplicate working hours of each day from the given dataset.

**STEPS:**

- In the beginning, select the range
**D5:F9**. - After that, go to the
**Home**tab >**Conditional Formatting**drop-down. - Then, select the
**Highlight Cells Rules**option >> click on**Duplicate Values**.

- Now, we can see a
**Duplicate Values**message box. From the drop-down, select the color which will indicate the duplicate values in the end. - Next, click on
**OK**.

- At last, all the duplicate values appear in light red filled with dark red text.

**Similar Readings:**

**How to Compare Two Columns in Excel For Finding Differences****Pivot Table Conditional Formatting Based on Another Column****Apply Conditional Formatting to Each Row Individually: 3 Tips****Conditional Formatting on Multiple Rows Independently in Excel**

### 6. Combine OR, ISNUMBER, and SEARCH Functions in Conditional Formatting

Here we have a dataset (**B4:D9**) of employees with their project names and total working hours. We are going to find the value of **Cell F5** from the range **B5:D9**, using Excel **OR**, **ISNUMBER** & **SEARCH** functions with **Conditional Formatting**.

**STEPS:**

- Firstly, select the dataset.
- Now, go to the
**Home tab**>**Conditional Formatting**drop-down >**New Rule**.

- Next, we see a
**New Formatting Rule**window pops up. - After that, go to the
**Use a formula to determine which cells to format**option. - Then, in the formula box, type the formula.

`=OR(ISNUMBER(SEARCH($F$5,$B5)))`

- Afterward, go to the
**Format**option and select the cell background color as we did in the first method. - Lastly, click on
**OK**.

- Finally, we will see the duplicate rows are highlighted.

🔎 **How Does the Formula Work?**

**SEARCH($F$5,$B5):**The**SEARCH function**will return the position of $F$5 in the lookup Range starting with cell $B5.**ISNUMBER(SEARCH($F$5,$B5)):**The**ISNUMBER function**will return the values as**TRUE**or**FALSE**.**OR(ISNUMBER(SEARCH($F$5,$B5))):**The**OR function**will alternate any of the text in the find_value Range.

### 7. Utilize SUM and COUNTIF Functions on Multiple Columns with Conditional Formatting

From the below dataset (**B4:D9**) of employees with their project names and total working hours, we are going to highlight the row containing the values in **F5:F6**. We use Excel **SUM** & **COUNTIF functions** with **Conditional Formatting**.

**STEPS:**

- Firstly, give the range
**F5:F6**a name. Here it is ‘**FIND**’.

- After that, select the dataset.
- Then, go to the
**Home**tab >**Conditional Formatting**drop-down >**New Rule**. - A
**New Formatting Rule**window pops up. - Next, go to the
**Use a formula to determine which cells to format**option. - Now, in the formula box, type the formula:

`=SUM(COUNTIF($B5,"*"&FIND&"*"))`

- After that, select
**Format**option. - Then, hoose the cell background color as we did in the first method.
- Lastly, click on
**OK**.

- Finally, we can see the total information of the matched value.

🔎 **How Does the Formula Work?**

**COUNTIF($B5,”*”&FIND&”*”):**This will count the cell numbers that match only one criterion to the Range starting from the cell**$B5**.**SUM(COUNTIF($B5,”*”&FIND&”*”)):**This will enable it to match all the criteria to the Range.

### 8. Employ AVERAGE Function in Conditional Formatting for Multiple Columns

Suppose, we have a dataset (**B4:E9**) of employee names with their three years salaries. We will apply **Conditional Formatting **on the names of the employees whose average salaries in years **1**, **2** & **3 **are greater than **2000**.

**STEPS:**

- Firstly, select the dataset at first.
- Then, go to the
**Home**tab >**Conditional Formatting**drop-down >**New Rule**.

- Now, the
**New Formatting Rule**window pops up. - After that, go to the
**Use a formula to determine which cells to format**option. - In the formula box, type the formula:

`=AVERAGE($C5,$D5,$E5)>2000`

- Afterward, go to the
**Format**option and select the cell background color as we did in the first method. - Then, click on
**OK**.

- Finally, we can get the desired format applied to the employee names that had an average salary in the years
**1**,**2**&**3**are greater than**2000**.

### 9. Change Alternate Cell Color Using ISEVEN or ISODD & ROW Functions with Conditional Formatting

Here, we have a dataset (**B4:F9**) of employees with their project names and working hours of each day. We are going to highlight the even rows of the multiple columns with **Conditional Formatting**.

**STEPS:**

- In the beginning, select the dataset at first.
- Then, go to the
**Home**tab >> click**Conditional Formatting**drop-down >> select**New Rule**.

- After that, from the
**New Formatting Rule**window, select the**Use a formula to determine which cells to format**option. - In the formula box, type the formula:

`=ISEVEN(ROW())`

- Then, go to the
**Format**option and select the cell background color as we did in the first method. - Click on
**OK**.

- In the end, we can see that all the even rows of multiple columns are highlighted.

- However, we can also highlight the odd rows by applying almost the same procedures. But here in the
**formula box**, type the formula:

`=ISODD(ROW())`

- The final output looks like below.

### 10. Format Empty Cells on Multiple Columns Applying Conditional Formatting in Excel

Sometimes we may have a dataset with blank cells. To highlight the background color of the empty cells dynamically, we can use the **Conditional Formatting** feature**. ** Let’s consider that we have a dataset (**B4:E9**) of employee names with their three years salaries. This dataset contains some empty cells.

**STEPS:**

- Firstly, select the dataset at first.
- Then, go to the
**Home**tab >**Conditional Formatting**drop-down >**New Rule**.

- Next, select the ‘
**Format only cells that contain**’ option, from the**New Formatting Rule**window. - Now, from the ‘
**Format only cells with**’ drop-down, select the**Blanks**option. - After that, go to the
**Format**option and select the cell background color as we did in the first method. - Lastly, select
**OK**.

- Finally, the result is here.

## Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

## Conclusion

These are the quick methods of** Conditional Formatting on Multiple Columns **in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.