Excel Conditional Formatting on Multiple Columns

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.


Practice Workbook

Download the following workbook and exercise.


10 Easy Methods of Excel Conditional Formatting on Multiple Columns

1. Excel AND Function with Conditional Formatting on Multiple Columns

Assuming, we have a dataset (B4:F9) of employees with their project names and working hours of each day. We are going to use Excel AND function with Conditional Formatting to highlight which cells contain more than 5 hours.

Excel AND Function with Conditional Formatting on Multiple Columns

STEPS:

  • First, select the range D5:F9 of working hours each day.
  • Next, go to the Home tab.
  • Select the Conditional Formatting drop-down.
  • Now select the New Rule.

Excel AND Function with Conditional Formatting on Multiple Columns

  • A New Formatting Rule window pops up. 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)
  • 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.
  • 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. Conditional Formatting with OR Function in Excel

Here, we have a dataset (B4:F9) of employees with their project names and working hours of each day. 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.

Conditional Formatting on Multiple Columns with OR Function in Excel

STEPS:

  • Select the range D5:F9 at first.
  • Now go to the Home tab > Conditional Formatting drop-down > New Rule.

Conditional Formatting on Multiple Columns with OR Function in Excel

  • We can see a New Formatting Rule window. 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)
  • After that, 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 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. Using Excel COUNTIF Function with Conditional Formatting on More Than Two Columns

In the below dataset (B4:F9) of employees with their project names and working hours of each day, we are going to use the Excel COUNTIF function with Conditional Formatting to see which rows contain values more than 4.

Using Excel COUNTIF Function with Conditional Formatting on More Than Two Columns

STEPS:

  • In the beginning, select the range D5:F9.
  • Go to the Home tab.
  • From the Conditional Formatting drop-down, select the New Rule.

Using Excel COUNTIF Function with Conditional Formatting on More Than Two Columns

  • Now here we see a New Formatting Rule window. Go to the Use a formula to determine which cells to format option.
  • 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.
  • Next, 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. Finding Duplicate Rows Based on Multiple Columns

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.

Finding Duplicate Rows Based on Multiple Columns with Excel Conditional Formatting

STEPS:

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

Finding Duplicate Rows Based on Multiple Columns with Excel Conditional Formatting

  • We see a New Formatting Rule window pop up. Go to the Use a formula to determine which cells to format option.
  • 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.
  • Select the cell background color as we did in the first method.
  • Then, click on OK.

  • We can see the duplicate rows are highlighted.

Read more: Conditional Formatting Entire Column Based on Another Column


5. With Conditional Formatting 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. Assuming, we have a dataset (B4:F9) of employees with their project names and some duplicate working hours of each day.

With Conditional Formatting Find Duplicates from Multiple Columns in Excel

STEPS:

  • Select the range D5:F9.
  • Now go to the Home tab > Conditional Formatting drop-down.
  • Select the Highlight Cells Rules option.
  • Then click on the Duplicate Values.

With Conditional Formatting Find Duplicates from Multiple Columns in Excel

  • We can see a Duplicate Values message box. From the drop-down, select the color which will indicate the duplicate values in the end.
  • Click on OK.

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


Similar Readings:


6. Using OR, ISNUMBER and SEARCH Functions with Conditional Formatting on Multiple Columns

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.

Using OR, ISNUMBER and SEARCH Functions with Conditional Formatting on Multiple Columns

STEPS:

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

Using OR, ISNUMBER and SEARCH Functions with Conditional Formatting on Multiple Columns

  • Next, we see a New Formatting Rule window pops up.
  • 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)))
  • 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 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. Excel 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.

Excel SUM and COUNTIF Functions on Multiple Columns with Conditional Formatting

STEPS:

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

Excel SUM and COUNTIF Functions on Multiple Columns with Conditional Formatting

  • Now select the dataset.
  • 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.
  • In the formula box, type the formula:
=SUM(COUNTIF($B5,"*"&FIND&"*"))
  • Select Format option.
  • Choose the cell background color as we did in the first method.
  • 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. Excel Conditional Formatting on Multiple Columns Based on Multiple Values of Another Cell

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.

Excel Conditional Formatting on Multiple Columns Based on Multiple Values of Another Cell

STEPS:

  • Select the dataset at first.
  • Go to the Home tab > Conditional Formatting drop-down > New Rule.

Excel Conditional Formatting on Multiple Columns Based on Multiple Values of Another Cell

  • A New Formatting Rule window pops up.
  • Now 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
  • 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. Alternate Excel Cell Color from Multiple Columns 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.

Alternate Excel Cell Color from Multiple Columns with Conditional Formatting

STEPS:

  • Select the dataset at first.
  • Go to the Home tab.
  • Now Conditional Formatting drop-down > New Rule.

Alternate Excel Cell Color from Multiple Columns with Conditional Formatting

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

Alternate Excel Cell Color from Multiple Columns with Conditional Formatting

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

  • 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. Excel Change Background Color of Empty Cells with Conditional Formatting from Multiple Columns

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:

  • First, select the dataset at first.
  • Go to the Home tab > Conditional Formatting drop-down > New Rule.

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

  • Finally, the result is here.


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

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo