Conditional Formatting Entire Column Based on Another Column in Excel

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 with some easy steps.

Let’s say we have a Sales Report for the year 2022 of a company. Every month there was a Sales Target. Now we will use conditional formatting to highlight where the Actual Sales were higher than the Sales Target.

Step 1 – Select Single Column to Apply Rules

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 D. So, we have selected the cells of Column D only.

Read More: Excel Conditional Formatting on Multiple Columns

Step 2 – Open New Format Rule Window from Conditional Formatting

Let’s choose suitable formatting rules.

• Go to the Home tab and click on Conditional Formatting to expand it.

• Different rules for applying conditional formatting are displayed. Let’s choose a rule based on our criteria.
• Select New Rule.

• A window named New Formatting Rule will appear.
• Select Use a formula to determine which cells to format from the Select a Rule Type Box.

Read More: Applying Conditional Formatting for Multiple Conditions in Excel

Step 3 – Insert Formula for Conditional Formatting

A box named Format values where this formula is true will appear.

• Enter the following formula in the box:

• Now we fix the formatting style. Click on the Format button.

Read More: How to Use Conditional Formatting in Excel

Step 4 – Determine Formatting Style

After clicking on the Format box, a new window named Format Cells will appear, where different formatting styles can be selected from the different tabs of the window.

• From the Number tab, choose Accounting.

• From the Font tab, select Bold as the font style.

• From the Border tab, select Outline Presets.

• From the Fill tab, select a preferred Background Color.
• Additionally, we could select a Pattern Color, Pattern Style, and Fill Effects.
• Click OK.

Step 5 – Apply Conditional Formatting to Selected Column Based on Another Column

• After Step 4, you will see your selected formatting style in the Preview box in the New Formatting Rule window.
• Click OK.

• Conditional formatting is applied to Column D.

Additional Tips

• To apply Conditional Formatting to more cells of the same column in the future, select some additional cells (for example, up to 100 rows) and  repeat the steps above.
• Alternatively, convert the dataset into a Table (Insert > Table) including additional rows. Then apply Conditional Formatting to the selected column and the specified conditional formatting will be automatically applied to future inserts.

Things to Remember

• Make sure to only refer to the columns as Absolute Cell References, not the row numbers.
• Always refer to the top left cell of the selected column where the values start.
• If you copy the rule with Format Painter from one column to another, make sure to check the column references.

Download Practice Workbook

Related Articles

• How to Format Cell Based on Formula in Excel
• How to Use Conditional Formatting Based on VLOOKUP in Excel
• How to Apply Conditional Formatting with INDEX-MATCH in Excel
• Excel Conditional Formatting Formula with IF
• Excel Conditional Formatting Formula If Cell Contains Text
  
• Conditional Formatting If Cell is Not Blank
• How to Change Text Color Based on Value with Excel Formula
• Conditional Formatting Multiple Text Values in Excel
  
• Excel Highlight Cell If Value Greater Than Another Cell

<< Go Back to Conditional Formatting with Multiple Conditions | Conditional Formatting | Learn Excel