How to Change Cell Colors Based on Numeric Values in Excel
In Excel, you can automatically change the color of cells based on their numeric values. For example, you can set the cell color to blue for values “10 or higher” and to red for values “20 or higher.” This tutorial explains how to configure this using Conditional Formatting.
How to Change Colors with Conditional Formatting
In this example, we will configure Conditional Formatting so that values “10 or higher” are displayed in blue, and values “20 or higher” are displayed in red. Changing cell colors based on numeric conditions makes data easier to understand visually.
Steps
- Select the range of cells where you want to change the color (e.g., A2 to A6).
- Click “Conditional Formatting” in the “Home” tab and choose “New Rule”.
- Select “Use a formula to determine which cells to format” and enter the formula corresponding to the condition.
Example of Conditional Formatting Setup
In the table below, column A contains numeric values. We will apply Conditional Formatting to this range to set the color to blue for “10 or higher” and red for “20 or higher.”
| A | B | C | |
|---|---|---|---|
| 1 | Value | ||
| 2 | 5 | ||
| 3 | 10 | ||
| 4 | 15 | ||
| 5 | 20 | ||
| 6 | 25 |
Example 1: Set the Color to Blue for Values 10 or Higher
- With A2:A6 selected, click “New Rule” in the Conditional Formatting Rules Manager.
- Select “Use a formula to determine which cells to format” and enter the following formula:
=A2>=10 - Click “Format,” choose blue under the “Fill” tab, and click “OK”.
- Cells A3, A4, A5, and A6 will be filled with blue.
Note: By specifying =A2>=10, the rule applies not only to A2 but also to A3, A4, A5, and A6.
Example 2: Set the Color to Red for Values 20 or Higher
- Add another “New Rule” and select “Use a formula to determine which cells to format,” then enter the following formula:
=A2>=20 - Click “Format,” choose red under the “Fill” tab, and click “OK”.
- Cells A5 and A6 will be filled with red.
Note
Conditional Formatting is applied in order of priority. In the above example, red takes precedence for values 20 or higher, while blue applies only for values between 10 and 19.
Summary
Using Conditional Formatting in Excel, you can automatically change the color of cells based on numeric conditions. By setting multiple conditions, you can create flexible formatting rules that enhance data visualization.