How to Apply Conditional Formatting to a Column Based on Another Column in Excel?

We have always formatted the cells in Excel based on the formula that is dependent on its own row or column, but have you ever tried formatting the rows or columns that are dependent on the other row or column? It is possible by making some simple changes to the formula that we will be using for the formatting.

This tutorial will help you understand how we can apply conditional formatting to a column based on another column in Excel. The whole process will depend on a formula.

Applying Conditional Formatting to a Column Based on Another Column

Here we will first select cells, then click on conditional formatting and add a new rule based on a formula. Let us see a simple process to apply conditional formatting to a column based on another column in an efficient and faster way. We will be highlighting the cells where the sales are greater than 6000, and the formula we will be using in the process is =C2>6000.

Step 1

Consider the following scenario: we have an excel sheet with data similar to the data shown in the image below. 

Now select the data you want to format, then select a new rule under conditional formatting under the Home Excel Ribbon. Then a new pop-up window will be opened, similar to the below image. In the new pop-up window, select Use a Formula, enter the formula as =$C2>6000 in the formula box, and click on Format.

Step 2

After clicking the format button, a new pop-up window will appear; select the colour you want to add, then click OK. Finally, click OK to see the final output, as shown in the image below. 

Conclusion

In this tutorial, we used a simple example to demonstrate how you can apply conditional formatting to a column based on another column in Excel to highlight a particular set of data.