If you deal with excel in your job or daily life, conditional formatting is work for formatting cells based on certain criteria. One common use cases for conditional formatting is to format cells based on the value of another cell. In this way, you can quickly highlight important data and identify trends. You can also compare values in different cells and get an insight.
Table of Contents
What is conditional formatting between two cells?
Here you can learn how to use conditional formatting based on another cell value. We will cover the basics of conditional formatting. Such as including how to create simple rules based on cell values and how to use more advanced rules based on formulas. You can read and re-do some examples of common use cases for conditional formatting as well.
Basics: how to do conditional formatting in excel
The simplest way to use conditional formatting is to create a basic rule. So it formats cells based on their value. You can do this if you follow these steps:
- First, you will select the cells that you want to format.
- Then, you can click on Conditional Formatting button in the Home tab.
- Now, you will choose “Highlight Cell Rules” from menu. And then you can choose “Less Than” or “Greater Than” depending on how you want to use.
- In the “Edit the Rule Description” window, you will enter the value that you want to use as the basis for the formatting rule. For example, if you want to format cells that contain values less than 10, you can enter 10 in the box.
- You can choose the formatting options you want to use. Also it is possible to change the font color, background color and other formatting options.
- Now, you click OK to apply the rule to your selected cells.
excel conditional formatting formula Based on Another Cell Value
If you want to use more advanced conditional formatting rules, you can use the New Rule option in the Conditional Formatting menu. This creates rules based on complex formulas. So you can include references to other cells and functions.
To create a new rule based on a formula, follow these steps:
- Now, first you select the cells that you want to format.
- You can click on the Conditional Formatting button again.
- Again, you should choose New Rule from the dropdown menu.
- It is time to choose Use a formula to determine which cells to format and enter your formula in the box. if you want to format cells that contain a value greater than the value in cell A1, you can use the formula “=B1>A1”.
- Now you can look at formatting options that you want to use. You can change the font color, background color, and other formatting options.
- And click OK to apply the rule to your selected cells.
Common Use Cases for excel conditional format based on another cell: excel if a cell is greater than 0 highlight color
Conditional formatting based on another cell value is quite helpful for a wide variety of use cases. Here are some examples:
- Sales people use conditional formatting to highlight cells containing sales data above or below a certain threshold. For example, you can format cells that contain sales data less than 10% of the total sales.
- Project Management people can apply conditional formatting to highlight cells containing project data behind schedule or over budget. Here, you can format cells containing project data over 10% over budget.
- You can use conditional formatting to highlight cells containing budget data over or under a certain amount. Also, you can format cells with budget data more than $100,000.
Tips and Tricks for Using Conditional Formatting Based on Another Cell Value
Here are some additional tips and tricks for using conditional formatting based on another cell value in Microsoft Excel:
- When creating formulas for conditional formatting, you can use absolute references for any cell references to remain constant. In this way, your formulas work correctly when they are applied to other cells.
- You should experiment with different formatting options. Conditional formatting customizes the formatting of your cells in many different ways. Hence, you can experiment with different font colors, background colors and other formatting options
- Also you can apply data bars and icon sets. In addition to basic formatting options, Excel also includes data bars and icon sets to represent your data using bars or icons visually. These options can be especially useful when working with big data.
- Before applying your conditional formatting rules to your entire sheet tough, you should test them on a smaller sample of data.
- You can combine multiple conditional formatting rules to create more complex formatting options. Here, you can use one rule to format cells less than 10 and another one to format cells with values greater than 50.
Conclusion on conditional formatting between two cells
Conditional formatting is a quite useful feature for both data analysis and presentations. Because it can help you highlight important data as well as show what is going on between different tables. If you create rules based on cell values or formulas, you can quickly format cells accordingly. You may be working with sales data, project management data or budget data. In any ways, conditional formatting can help you make sense of your data. From now on, you can start using conditional formatting for presentations and tables. You can take advantage of this basic tool and start practicing.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
