# Conditional Formatting Entire Column Based on Another Column in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

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

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

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

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

## 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.
`=\$D5>\$C5`
• After that, you need to fix the formatting style, and to do that click on the Format box.

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.

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

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

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

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

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

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

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.

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

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.