One of the most useful features of Excel is the ability to apply conditional formatting to cells, which means changing the appearance of the cells based on certain criteria. For example, you can highlight cells that are above or below a certain value, or that match a specific text or date.
But what if you want to highlight cells based on the value of another cell? For instance, you might want to highlight the sales figures that are higher than the average, or the expenses that are lower than the budget. In this article, we will show you how to do that using Excel formulas.
The basic idea behind highlighting cells based on the value of another cell is to use a formula that returns TRUE or FALSE as the condition for the conditional formatting. The formula should compare the value of the cell to be formatted with the value of another cell, using operators such as =, >, <, >=, <=, or <>.
For example, if you want to highlight the cells in column B that are greater than the value in cell D1, you can use the formula:
=B1>D1
This formula will return TRUE for the cells in column B that are greater than the value in cell D1, and FALSE for the rest. You can then apply the conditional formatting to the range B1:B10, using the formula as the condition.
Procedures
To highlight cells based on the value of another cell in Excel, follow these steps:
- Select the range of cells that you want to format. In our example, we will select B1:B10.
- Go to the Home tab, and click on Conditional Formatting in the Styles group. Then, click on New Rule.
- In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter the formula that compares the value of the cell to be formatted with the value of another cell. In our example, we will enter:
=B1>D1
- Click on the Format button, and choose the formatting options that you want to apply to the cells that meet the condition. You can change the font, fill, border, or number format of the cells. In our example, we will choose a green fill color.
- Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box.
- You will see that the cells in column B that are greater than the value in cell D1 are highlighted with a green color.
Example
To illustrate how to highlight cells based on the value of another cell in Excel, let’s use a sample data set of monthly sales and expenses for a small business. The data is shown in the table below:
| Month | Sales | Expenses |
|---|---|---|
| Jan | 12000 | 8000 |
| Feb | 15000 | 9000 |
| Mar | 18000 | 10000 |
| Apr | 16000 | 11000 |
| May | 14000 | 9500 |
| Jun | 17000 | 10500 |
Suppose we want to highlight the sales figures that are higher than the average sales, and the expenses that are lower than the average expenses. To do that, we need to calculate the average sales and expenses using the AVERAGE function. We can enter the following formulas in cells D1 and E1, respectively:
=AVERAGE(B1:B6)
=AVERAGE(C1:C6)
The formulas will return the average sales and expenses, which are 15500 and 9750, respectively. We can then use these values as the reference for the conditional formatting. We can apply the following formulas as the conditions for the conditional formatting:
=B1>D1 for the sales column
=C1<E1 for the expenses column
We can then choose the formatting options that we want to apply to the cells that meet the conditions. For example, we can choose a green fill color for the sales column, and a blue fill color for the expenses column. The result is shown in the table below:
| Month | Sales | Expenses |
|---|---|---|
| Jan | 12000 | 8000 |
| Feb | 15000 | 9000 |
| Mar | 18000 | 10000 |
| Apr | 16000 | 11000 |
| May | 14000 | 9500 |
| Jun | 17000 | 10500 |
We can see that the sales figures that are higher than the average sales are highlighted with a green color, and the expenses that are lower than the average expenses are highlighted with a blue color.
Other Approaches
There are other ways to highlight cells based on the value of another cell in Excel, such as using the built-in conditional formatting rules, or using named ranges. Here are some examples of how to use these approaches:
- Using the built-in conditional formatting rules: Excel has some predefined rules that can help you highlight cells based on certain criteria, such as top 10 items, above average, below average, etc. You can access these rules by going to the Home tab, and clicking on Conditional Formatting in the Styles group. Then, click on Top/Bottom Rules, Above/Below Average, or More Rules. You can then choose the rule that suits your needs, and specify the reference cell or range that you want to compare with. For example, if you want to highlight the sales figures that are above the average sales, you can choose the Above Average rule, and enter D1 as the reference cell.
- Using named ranges: Another way to highlight cells based on the value of another cell in Excel is to use named ranges, which are labels that you can assign to cells or ranges of cells. You can create named ranges by going to the Formulas tab, and clicking on Name Manager in the Defined Names group. Then, click on New, and enter a name and a reference for the range that you want to name. For example, you can name the range B1:B6 as Sales, and the range C1:C6 as Expenses. You can then use these names in your formulas for the conditional formatting, instead of the cell references. For example, if you want to highlight the sales figures that are higher than the average sales, you can use the formula:
=Sales>AVERAGE(Sales)
This formula will compare the value of each cell in the Sales range with the average of the Sales range, and return TRUE or FALSE accordingly. You can then apply the conditional formatting to the Sales range, using the formula as the condition.
