How to Apply Conditional Formatting on Multiple Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel conditional formatting multiple columns


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.

Dataset to apply conditional formatting on multiple columns


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.

Selecting cell range and 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.

Using AND function in conditional formatting

  • 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.

Format Cells Box

  • Again, click on OK.

New Formatting Rule Box

  • Finally, we can see the result.

Results found after using AND Function

🔎 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.

Using OR function in conditional formatting

  • In the end, we can see the output.

Results found after using OR Function

🔎 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.

Employing COUNTIF function in conditional formatting

  • Finally, We can see the highlighted rows.

Results found after using COUNTIF Function

🔎 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.

Dataset to find duplicates based on multiple columns

STEPS:

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

Selecting multiple columns to use COUNTIF function

  • 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.

Using COUNTIFS function in conditional formatting

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

Results found after using COUNTIFS Function

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.

Selecting dataset to find duplicates using conditional formatting

  • 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.

Duplicate values toolbox

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

After formatting duplicate values in multiple columns


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.

Dataset to combine OR, ISNUMBER & SEARCH Functions

STEPS:

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

Selecting multiple columns to apply 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.

Combining OR, ISNUMBER & SEARCH functions in conditional formatting

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

Results found after combining OR, ISNUMBER & SEARCH Functions

🔎 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.

Dataset to utilize SUM & COUNTIF function on multiple columns

STEPS:

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

Creating named range

  • 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.

Using SUM & COUNTIF functions in conditional formatting

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

Results found after using SUM & COUNTIF Functions

🔎 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.

Dataset to to employ AVERAGE function

STEPS:

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

Selecting multiple columns to employ AVERAGE function

  • 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.

Using AVERAGE function in conditional formatting

  • 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.

Results found after using AVERAGE Function


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.

Dataset to use ISEVEN, ISODD & ROW functions

STEPS:

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

Select cell range from multiple columns to apply conditional formatting

  • 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.

Using ISEVEN & ROW functions in conditional formatting

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

Results found after using ISEVEN & ROW Functions

  • 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())

Using ISODD & ROW functions in conditional formatting

  • The final output looks like below.

Results found after using ISODD & ROW Functions


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.

Dataset with blank cells

STEPS:

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

Selecting multiple columns to format blank cells

  • 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.

Selecting Blanks to format

  • Finally, the result is here.

Final output after formatting black cells


Practice Section

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

Practice section


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

<< Go Back to Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

2 Comments
  1. 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.
      row wise conditional formatting

      If you need to do this kind of stuff frequently, using VBA is indeed the best option as you mentioned.
      Regards
      Aniruddah
      Team Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo