Conditional formatting is a feature in Excel that allows you to apply different formats (such as colors, fonts, icons, etc.) to cells based on certain criteria or conditions. This can help you to visualize and analyze your data more easily and effectively.
One of the common uses of conditional formatting is to highlight different cells in a range based on another range. For example, you may want to highlight the sales values in one column that are greater than, equal to, or less than the corresponding values in another column. This can help you to compare and identify the changes or trends in your data.
In this article, we will show you how to use conditional formatting to highlight different cells in a range based on another range in Excel. We will also explain the basic theory behind the formulas and procedures, and provide a detailed example with real numbers and an Excel table.
To apply conditional formatting based on another range, you need to use a formula that compares the values in the two ranges and returns TRUE or FALSE. The formula should be entered in the Format values where this formula is true box in the New Formatting Rule dialog box.
The formula should use mixed references, which means that the column references are locked with a dollar sign ($), but the row references are relative. This ensures that the formula can be applied to the entire range without changing the column references, but can adjust the row references according to each cell.
For example, the formula =$D5>$C5 compares the value in cell D5 with the value in cell C5, and returns TRUE if D5 is greater than C5, and FALSE otherwise. If you apply this formula to the range D5:D14, it will highlight the cells in column D that are greater than the corresponding cells in column C.
You can use different comparison operators in your formula, such as =, <>, <, >, <=, >=, etc. You can also use logical operators such as AND, OR, NOT, etc. to combine multiple conditions.
Procedures
To apply conditional formatting based on another range, follow these steps:
- Select the range of cells that you want to format. In our example, we want to format the range D5:D14 based on the range C5:C14, so we select D5:D14.
- Go to the Home tab, and click on Conditional Formatting in the Styles group. Then, select New Rule from the drop-down menu.
- In the New Formatting Rule dialog box, select Use a formula to determine which cells to format from the Select a Rule Type list.
- In the Format values where this formula is true box, enter the formula that compares the values in the two ranges. In our example, we want to highlight the cells in column D that are greater than the corresponding cells in column C, so we enter
=$D5>$C5. - Click on the Format button to choose the format that you want to apply to the cells that meet the condition. You can select from the Font, Border, or Fill tabs, or use the Number tab to change the number format. In our example, we choose a light green fill color for the cells in column D that are greater than the corresponding cells in column C.
- Click on OK to close the Format Cells dialog box, and then click on OK again to close the New Formatting Rule dialog box. You will see the cells in column D that are greater than the corresponding cells in column C are highlighted with a light green color.
Example
To illustrate the use of conditional formatting based on another range, we will use the following example data:
| Product | Sales of 2018 | Sales of 2019 |
|---|---|---|
| A | 1000 | 1200 |
| B | 1500 | 1400 |
| C | 2000 | 2100 |
| D | 2500 | 2400 |
| E | 3000 | 3300 |
| F | 3500 | 3400 |
| G | 4000 | 4200 |
| H | 4500 | 4400 |
| I | 5000 | 5100 |
| J | 5500 | 5400 |
The table shows the sales values for different products in 2018 and 2019. We want to highlight the cells in the Sales of 2019 column based on the following conditions:
- If the sales value in 2019 is greater than the sales value in 2018, highlight the cell with a light green color.
- If the sales value in 2019 is equal to the sales value in 2018, highlight the cell with a yellow color.
- If the sales value in 2019 is less than the sales value in 2018, highlight the cell with a light red color.
To do this, we need to apply three conditional formatting rules to the range D5:D14, each with a different formula and format. The formulas and formats are as follows:
| Formula | Format |
|---|---|
| =$D5>$C5 | Light green |
| =$D5=$C5 | Yellow |
| =$D5<$C5 | Light red |
We can follow the procedures described above to apply each rule. Alternatively, we can use the Manage Rules option in the Conditional Formatting menu to create and edit multiple rules at once.
The result is shown below:
| Product | Sales of 2018 | Sales of 2019 |
|---|---|---|
| A | 1000 | <span style=“background-color: #90EE90”>1200</span> |
| B | 1500 | <span style=“background-color: #FFC7CE”>1400</span> |
| C | 2000 | <span style=“background-color: #90EE90”>2100</span> |
| D | 2500 | <span style=“background-color: #FFC7CE”>2400</span> |
| E | 3000 | <span style=“background-color: #90EE90”>3300</span> |
| F | 3500 | <span style=“background-color: #FFC7CE”>3400</span> |
| G | 4000 | <span style=“background-color: #90EE90”>4200</span> |
| H | 4500 | <span style=“background-color: #FFC7CE”>4400</span> |
| I | 5000 | <span style=“background-color: #90EE90”>5100</span> |
| J | 5500 | <span style=“background-color: #FFC7CE”>5400</span> |
From the table, we can see that the products A, C, E, G, and I have increased their sales in 2019 compared to 2018, while the products B, D, F, H, and J have decreased their sales in 2019 compared to 2018. The products that have the same sales in both years are not present in this example, but they would be highlighted with a yellow color if they were.
