# How to Apply Conditional Formatting to Multiple Columns in Excel – 10 Easy Methods

The sample dataset (B4:F9) contains names of employees, their projects and their working hours on different days.

### Method 1 – Use the AND Function with Conditional Formatting on Multiple Columns

STEPS:

• Select the range D5:F9 (working hours).
• Go to the Home tab.
• Select Conditional Formatting >> New Rule.

• The New Formatting Rule window will open.
• After that, go to Use a formula to determine which cells to format.
• Enter the following formula.
`=AND(\$D5>5,\$E5>5,\$F5>5)`
• Select Format.

• In Format Cells, go to the Fill tab.
• Select a background color.
• Sample shows the color preview.
• Click OK.

• Click OK again.

This is the result.

Formula Breakdown

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

### Method 2. Applying Conditional Formatting on Multiple Columns with the OR Function in Excel

STEPS:

• Open New Formatting Rule (as shown in Method 1).
• Go to Use a formula to determine which cells to format.
• Enter the following formula.
`=OR(D5>7,D5<4)`
• Go to Format and select the cell background color.
• Click OK.

This is the output.

Formula Breakdown

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

### Method 3 – Using the COUNTIF Function in Conditional Formatting for More Than Two Columns

STEPS:

• Go to New Formatting Rule.
• Choose Use a formula to determine which cells to format.
• Enter the following formula.
`=COUNTIF(\$D5:\$F5,">4")>2`
• Go to Format option and select the cell background color.
• Click OK.

This is the output.

Formula Breakdown

The COUNTIF function will count the number of cells. If it is greater than 4 in the range \$D5:\$F5, it will return TRUE for exact matches; otherwise FALSE. The Conditional Formatting will apply the formula to the whole dataset.

### Method 4 – Find Duplicate Rows Based on Multiple Columns Using the COUNTIFS Function

The COUNTIFS function will count the number of cells from a range based on multiple criteria.

STEPS:

• Select the dataset.
• Go to the Home tab > Conditional Formatting > New Rule.

• In New Formatting Rule, select Use a formula to determine which cells to format.
• Enter the following formula.
`=COUNTIFS(\$B\$5:\$B\$9,\$B5,\$C\$5:\$C\$9,\$C5,\$D\$5:\$D\$9,\$D5)>1`
• Go to Format.
• Select the cell background color.
• Click OK.

This is the output.

### Method 5 – Using Conditional Formatting to Find Duplicates from Multiple Columns in Excel

STEPS:

• Select the range D5:F9.
• Go to the Home tab > Conditional Formatting.
• Select Highlight Cells Rules >> click Duplicate Values.

• In Duplicate Values, select a color to indicate duplicate values.
• Click OK.

All duplicate values will be displayed.

### Method 6 – Combining the OR, ISNUMBER, and SEARCH Functions in Conditional Formatting

STEPS:

• Select the dataset.
• Go to the Home tab > Conditional Formatting > New Rule.

• In New Formatting Rule, choose Use a formula to determine which cells to format.
• Enter this formula.
`=OR(ISNUMBER(SEARCH(\$F\$5,\$B5)))`
• Go to Format and select the cell background color.
• Click OK.

The duplicate rows are highlighted.

Formula Breakdown

• SEARCH(\$F\$5,\$B5): The SEARCH function will return the position of \$F\$5 in the lookup Range starting with \$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.

### Method 7 – Utilizing the SUM and COUNTIF Functions on Multiple Columns with Conditional Formatting

STEPS:

• Give the range F5:F6 a name. Here, ‘FIND’.

• Select the dataset.
• Go to the Home tab > Conditional Formatting > New Rule.
• In New Formatting Rule, choose Use a formula to determine which cells to format.
• Enter this formula.
`=SUM(COUNTIF(\$B5,"*"&FIND&"*"))`
• Select Format.
• Choose the cell background color.
• Click OK.

This is the output.

Formula Breakdown

• COUNTIF(\$B5,”*”&FIND&”*”): will count the number of cells that match only one criterion from the Range starting in \$B5.
• SUM(COUNTIF(\$B5,”*”&FIND&”*”)): will match all criteria with the Range.

### Method 8 – Using the AVERAGE Function in Conditional Formatting for Multiple Columns

STEPS:

• Select the dataset.
• Go to the Home tab > Conditional Formatting > New Rule.

• In New Formatting Rule, choose Use a formula to determine which cells to format.
• Enter this formula.
`=AVERAGE(\$C5,\$D5,\$E5)>2000`
• Go to Format and select the cell background color.
• Click OK.

This is the output.

### Method 9 – Changing Alternate Cell Color Using the ISEVEN or ISODD & ROW Functions with Conditional Formatting

STEPS:

• Select the dataset.
• Go to the Home tab >> click Conditional Formatting >> select New Rule.

• In New Formatting Rule, choose Use a formula to determine which cells to format.
• Enter this formula.
`=ISEVEN(ROW())`
• Go to Format and select the cell background color.
• Click OK.

All even rows are highlighted.

• To highlight he odd rows, enter this formula:
`=ISODD(ROW())`

This is the output.

### Method 10 – Formatting Empty Cells on Multiple Columns with Conditional Formatting in Excel

This dataset contains empty cells.

STEPS:

• Select the dataset.
• Go to the Home tab > Conditional Formatting > New Rule.

• In New Formatting Rule, choose ‘Format only cells that contain’.
• In ‘Format only cells with’, select Blanks.
• Go to Format option and select the cell background color.
• Click OK.

This is the result.

Practice Section

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

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.

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

Advanced Excel Exercises with Solutions PDF