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.
Apply Conditional Formatting on Multiple Columns in Excel: 10 Easy Ways
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: Applying Conditional Formatting 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.
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.
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.
Download Practice Workbook
Download the following workbook and exercise.
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.
Related Readings
- How to Apply Different Types of Conditional Formatting in Excel
- How to Find External Links in Conditional Formatting in Excel
- How to Apply Conditional Formatting for Blank Cells in Excel
- How to Compare Two Columns Using Conditional Formatting in Excel
- How to Remove Conditional Formatting in Excel
- How to Remove Conditional Formatting but Keep the Format in Excel
- How to Do Conditional Formatting in Excel
- Conditional Formatting with Formula in Excel
<< Go Back to Conditional Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
how about if i have months listed in a table, columns A – L ,and item numbers in the rows with sales for each month.
i want to make a conditional formatting rule for color scales, so it evaluates the ranking for each item across all columns so i can rank sales for each item by month. i could only get excel to evaluate by rows (so all item numbers were being ranked by each month instead of individula items being ranked by month).
if i selected a range and applied the rule, it ranked each column, not each row.
i had to write vba code to apply it row by row.
Dear Gary,
Thank you for sharing your problem with us. Yes, you are right that when you apply the conditional formatting (color scale) to the entire range,it ranks by each column, not by each row. Hence, in this case, to rank them by rows, you need to apply the conditional formatting to all the rows one by one. I have created a dataset based on your information and applied the conditional formatting to all the rows separately and got the expected result.
If you need to do this kind of stuff frequently, using VBA is indeed the best option as you mentioned.
Regards
Aniruddah
Team Exceldemy