Conditional Formatting Entire Column Based on Another Column(6 Steps)

In Excel, conditional formatting is used to highlight any cells based on predetermined criteria and the value of those cells. We can also apply conditional formatting to highlight an entire column based on another column. In this article, you will get to know how you can apply conditional formatting to a column based on another column with 6 easy steps.

Let’s say we have a sales record for a year of a company. Every month there was a sales target. Now we will use conditional formatting to find out the Actual sales which were higher than the sales target.

Download Practice Workbook

6 Steps for Conditional Formatting Entire Column Based on Another Column

1. Selecting a Column for Conditional Formatting

To apply Conditional Formatting first you have to select the cells. If you want to highlight the entire row after applying conditional formatting, you need to select all of your datasets. But to highlight cells from a single column you just need to select the cells of that column.

For our dataset, we want to apply conditional formatting to column C. So, we have selected the cells of column C only.

Read More: Excel Conditional Formatting on Multiple Columns

2. Opening Conditional Formatting Panel

In the second step, you need to open the Conditional Formatting panel to choose suitable formatting rules according to your need. First, go to the Home tab and click on Conditional Formatting to expand it. Now, you will see different rules for applying conditional formatting. You need to choose a rule based on your criteria. To apply conditional formatting in a column based on another column, we need to select New Rule. To know about the other rules of conditional formatting click here.

Read More: Conditional Formatting with Formula for Multiple Conditions in Excel

3. New Format Rule Window

After Selecting New Rule a window named New Formatting Rule will appear. In the Select a Rule Type box you will see different rules for setting the conditions of formatting. For conditional formatting one entire column based on another column, we need to set a formula to compare the two columns. For that, we need to select Use a formula to determine which cells to format from the Select a Rule Type Box.

Read More: Excel Conditional Formatting Formula

Similar Readings

• How to Compare Two Columns Using Conditional Formatting in Excel
• Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)
• Excel Conditional Formatting Dates
• Excel Alternating Row Color with Conditional Formatting [Video]
• How to Copy Conditional Formatting to Another Workbook in Excel

4. Formula for Conditional Formatting One Column Based on Another Column

After selecting Use a formula to determine which cells to format a box named Format values where this formula is true will appear in the below of Select a Rule Type box. Type the following formula in the box,

Here, the formula will compare the value of column C with the value of column B in the same row and if the value of column C of a row is greater than the value of column B of the same row, the cell of column C will be formatted.

After that, you need to fix the formatting style, and to do that click on the Format box.

Read More: How to Use Conditional Formatting in Excel [Ultimate Guide]

5. Determine Formatting Style

After clicking on the Format box a new window named Format Cells will appear. Here you can choose different formatting styles from different tabs of the window. From the Number tab, you can choose different numbering formats for the formatted cells. As we have sales data in our dataset, we have chosen Accounting.

From the Font tab, you can choose different fonts, font styles, effects, and color for the formatted cells. We have selected Bold as the font style. So after applying conditional formatting we will get bold fonts in the formatted cells.

From the Border tab, you can choose different styles for the border of the formatted cells. We have chosen Outline presets in our example.

And finally, from the Fill tab, you can choose the fill color, fill pattern, fill effects for conditional formatting. For our example, we have selected light blue as the fill color.

At last, after setting your preferred formatting style click on OK.

Read More: How to Do Conditional Formatting for Multiple Conditions (8 Ways)

6. Apply Conditional Formatting to the selected Column Based on Another Column

You are all set to apply conditional formatting to an entire column based on another column. After Step 5, you will see your selected formatting style in the Preview box in the New Formatting Rule window. Click OK to apply conditional formatting in your selected column.

After pressing OK, you will see conditional formatting is applied to your selected column C based on Column B. If the cell of column C of a particular row has a greater value than the value of the column B cell of that row, the cell of column C is highlighted with a light blue color and the font of the cell is in bold format. For example, C6 has a greater value than B6. So the cell C6 is formatted with a light blue fill color and bold font.

Read More: How to Compare Two Columns in Excel For Finding Differences

Conclusion

This article gives you the basic guidelines for conditional formatting an entire column based on another column in Excel. So if you follow the steps described in this article you will be able to apply conditional formatting to columns based on the criteria from other columns. Feel free to leave a comment, if you have any confusion.

Related Articles

• How to Do Conditional Formatting Highlight Row Based On Date
• Conditional Formatting on Multiple Rows Independently in Excel
• Apply Conditional Formatting to Each Row Individually: 3 Tips
• Excel Conditional Formatting If a Cell Is Greater Than Another One
• How to Copy Conditional Formatting to Another Sheet (2 Quick Methods)