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. In this article, you will get to know how you can apply conditional formatting entire column based on another column in Excel with some easy easy steps.

Excel Conditional Formatting Entire Column Based on Another Column

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 find out the Actual Sales which were higher than the Sales Target.

Dataset to Apply Conditional Formatting

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.

Selecting Cell Range

Read More: Excel Conditional Formatting on Multiple Columns

Step 2: Open New Format Rule Window from Conditional Formatting

In the second step, you need to open the New Format Rule window from 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.

Selecting Conditional Formatting from Home Tab

  • Now, you will see different rules for applying conditional formatting. You need to choose a rule based on your criteria.
  • therefore, to apply conditional formatting in a column based on another column, we need to select New Rule.

Opening New Rule from Conditional Formatting

  • After that, a window named New Formatting Rule will appear.
  • Here, select Use a formula to determine which cells to format from the Select a Rule Type Box.

Choosing Rule type

Read More: Applying Conditional Formatting for Multiple Conditions in Excel

Step 3: Insert Formula for Conditional Formatting

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

  • Here, type the following formula in the box.
  • After that, you need to fix the formatting style, and to do that click on the Format box.

Inserting Formula for Conditional Formatting

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

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. Here you can choose different formatting styles from different tabs of the window.

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

Choosing Number Format for Selected Column

  • Then, from the Font tab select Bold as the font style. So after applying conditional formatting we will get bold fonts in the formatted cells.

Selecting Font Style

  • Afterward, from the Border tab, you can choose different styles for the border of the formatted cells. We have chosen Outline Presets in our example.

Choosing Presets from Border Tab

  • Finally, from the Fill tab, you can choose your preferred Background Color.
  • Also, feel free to select the Pattern Color, Pattern Style, and Fill Effects for conditional formatting.
  • At last, after setting your preferred formatting style click on OK.

Choosing Background Color for Cells

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

You are all set to apply conditional formatting to an entire 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.
  • Now, click OK to apply conditional formatting in your selected column.

Overview of Settings in New Formatting Rule

  • Finally, after pressing OK, you will see conditional formatting is applied to your selected Column D based on Column C.

Excel Conditional Formatting Entire Column Based on Another Column

Note: If the cell of Column D of a particular row has a greater value than the value of the Column C cell of that row, the cell of Column D is highlighted with a light blue color and the font of the cell is in bold format. For example, D5 has a greater value than C5. So cell D5 is formatted with a light blue fill color and bold font.

Additional Tips

  • If you want to apply Conditional Formatting to more cells of the same column in the future, then initially select some additional cells,(for example, up to 100 rows) and then go through the steps above.
  • Otherwise, you can convert the dataset into a Table (Insert > Table) including additional rows. After that, apply Conditional Formatting to the selective column and it will work automatically for future inserts.

Things to Remember

  • Make sure you 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 on the column reference.

Download Practice Workbook

Download this sample file to practice by yourself.


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

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

  1. Hi, great site you have here !
    i have another problem, lets see if you could help.
    i have 2 columns, which is column A and column B, i want to change cell color of column A based on column B cell value (0, negatives value, and positive value)
    if column B cell value is negative, then column A cell text color is red
    if column B cell value is positive, then column A cell text color is blue
    if column B cell value is 0, then column A cell text color is black.

    please help

    • Thank you, Calvin, for your query. I’m replying on behalf of ExcelDemy. You need to select the values of column A and then apply the conditional formatting to them. The condition will be similar to this image. Steps do the formatting is already mentioned in this article.

      • Here is the output.

Leave a reply

Advanced Excel Exercises with Solutions PDF