Nuraida Kashmin
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.
