I have seen many people manually change the cell’s font color based on the cell value.
While this is all right if you have to do it once in a while and have a small data set, in case you have a large data set where you want to change the font color based on the cell value, doing it manually would be tedious and error-prone.
In this tutorial, I will show you a couple of ways you can use to quickly change the font color of the cell based on the value in it.
I will cover different scenarios such as highlighting negative numbers in red color or highlighting numbers above or below a specific value in a different color.
I will also show you how you can change the font color of a cell based on the value in some other cell.
Change the Font Color Based on Cell Value
When it comes to changing the font color of the cells that have a numeric value, there could be two scenarios:
- Highlighting negative values in a different color
- Highlighting values above or below a certain value in a different color
Positive/Negative Values
Below I have a data set where I have positive as well as negative numbers, and I want to highlight only the negative numbers in red color.
Using Conditional Formatting
The easiest way to do this would be by using the inbuilt option in custom number formatting.
Below are the steps to quickly highlight negative numbers in red color:
- Select the cells that contain the numbers for which you want to change the font color
- Click the Home tab
- In the Styles group, click on Conditional Formatting
- Hover the cursor over the option – ‘Highlight Cell Rules’
- Click on the ‘Less than’ option
- In the less than dialog box, enter 0 in the ‘Format cells that are LESS THAN’ field
- Select ‘Red Text’ as the formatting option from the second dropdown
- Click OK
The above steps would instantly change the font color of the cells that contain the negative value.
Note that in this example, we selected the ‘Red Text’ formatting.
You can also choose any other formatting options, or you can create your own formatting to apply to the cells that contain numbers less than 0.
Using Custom Number Formatting
Another way you can quickly change the font color of the cell based on the value is by using Custom Number Formatting.
Below I have the same data set where I have some cells that contain negative values that I want to show in red color.
Here are the steps to do this:
- Select the cells that contain the numbers
- Hold the Control key and press the 1 key. This will open the Format Cells dialog box. You can also get this by clicking on the Home tab and then clicking on the small dialog box launcher in the Number group
- In the Format Cells dialog box, make sure the Number tab is activated
- In the Category options, select the Number option
- Under the Negative Numbers options, select the fourth option (the one that shows numbers with a negative sign and is in red color)
- [Optional] Adjust the decimal places by using the spin button
- Click OK
The above steps would apply the specified formatting on the selected cells, which means that all the positive numbers would be shown in black font color, and the negative numbers would be shown in red font color.
Pro Tip: If you want to show negative numbers in red color but without the – (minus sign), you can select the second option in step 5
While this method is slightly faster than using Conditional Formatting, using Conditional Formatting gives you a lot more flexibility when it comes to formatting the cells that meet the condition.
With Conditional Formatting, you can change the cell color, the font size, the font color, and the font type.
But with custom number formatting, you can only change the font color (and even in the font color you can only choose a red color for negative numbers)
Also read: Change Cell Color Based on Text Input in Excel
Value Greater/Less than a Specific Value
Another common situation where you may want to change the cell’s font color based on the cell value could be when you want to highlight cells that have a value above or below a given specific value.
Have a data set where I have the sales values for different stores in different quarters. In this data, I want to highlight all the cells that have the ‘Sales’ value above 700.
And I want to do this by changing the font color of these cells to Green.
Doing this would quickly help me identify stores that are doing well.
Below are the steps to do this using Conditional Formatting:
- Select the cells that have the numbers
- In the Home tab, click on Conditional Formatting
- In the options that show up, how about your cursor over the ‘Highlight Cell Rules’ option
- Click on the ‘Greater than’ option
- In the ‘Greater Than’ dialog box that opens, enter 700 (which is the value of a which you want to change the font color of the cells)
- Click on the second drop-down, and then select the option ‘Green filled with dark green text’
- Click OK
The above steps would instantly change the formatting of the cells with a value above 700, by changing the font color to dark green and the cell fill color to light green.
In case you want to specify your own custom formatting (such as making the font color green and making it bold without any background color), you can do that by clicking on the ‘Custom Format’ option in the drop-down. It will open the format cells dialog box where you can specify the formatting
Pro Tip: In the above steps, we hard-coded the value 700 in Step 5. Instead of manually entering the value, you can also enter the cell reference of a cell that contains that value. Since Conditional Formatting is dynamic, if you change the value in the cell that you refer to in Step 5, your Conditional Formatting rules would automatically update and so will the formatting on the cells. For example, if you change the cell value from 700 to 500, then all the cells that have a Sale value of more than 500 would be highlighted.
Change the Font Color If Cell Contains Specific Text
While Excel is an exceptional tool for data analysis, most Excel users use it as a database and a record-keeping tool.
For such users, Excel also has a lot of amazing text-based functionalities that can be used.
And one such option is to allow the user to quickly change the font color of the cells that contain specific text strings in a cell.
Below I have an example data set where I had the client in column A and their comments in column B, and I want to change the font color of all the cells that contained the text ‘follow’.
Below are the steps to do this using Conditional Formatting:
- Select the cells that contain the comments
- Click the Home tab
- In the Styles group, click on the ‘Conditional Formatting’ icon
- In the options that show up, click on the ‘Highlight Cell Rules’ option
- Click on the ‘Text that Contains’ option
- In the ‘Text that Contains’ dialog box, enter ‘follow’ in the text field
- From the drop-down, select the ‘Green fill with dark green text’ option
- Click OK
The above steps would instantly highlight all the cells that contain the word ‘follow’.
In our data set, this would be helpful as the sales Rep would now know which client needs to be contacted for a follow-up.
And again, I choose one of the pre-made conditional formatting options, but you can create your own format by using the ‘Custom Format’ option in step 7
Also read: Change Cell Color Based on Value of Another Cell in Excel
Change the Font Color Based on Other Cell Value
So far, we have used simple inbuilt rules in Conditional Formatting. but it can also do advanced stuff such as creating your own formatting rules.
One common situation where you might need this is when you want to highlight or change the font color offer cell based on the value in another cell.
Below I have a simple data set where I have the store in column A and the sales value in column B. In this data set, I want to change the font color of the store names in column A to red if the sales value is less than 500.
Below are the steps to do this using Conditional Formatting:
- Select the cells that you want to highlight (A2:A10 in our example)
- Click the Home tab
- Click on the ‘Conditional Formatting’ icon
- In the options that show up, click on the ‘New Rule’ option
- In the New Formatting Rule dialog box, click on the ‘Use a formula to determine which cells to format’ option
- Enter the below formula in the formula text field:
=B2<500
- Click on the Format button
- In the Format Cells dialog box that opens, select the Font tab (if not activated already)
- Click on the Color option and select the red color
- [Optional] Select ‘Bold’ as the Font Style
- Click OK
- Click OK
The above steps would change the font color of the cells in column A based on the value in column B.
If the sale value is less than 500, the name of that store would automatically be highlighted in red color.
How does this work?
In this scenario, we have used a formula that would be assessed by Conditional Formatting for each cell in this selection.
To do this, we selected the cells A2:A10 and then use the formula =B2<500
In the back end, conditional formatting is going to analyze each cell from A2 to A10, and check what’s the value that is returned by the formula. If the formula returns a true, that cell’s font color is changed to read, and if the formula returns false, then nothing happens.
So for cell A2, the formula would be =B2<500
For cell A3, the formula would be =B3<500
For cell A4, the formula would be =B4<500
The trick here is in the fact that as conditional formatting goes down the cells in column A, the conditional formatting formula reference also changes accordingly. So for cell A2, the formula uses cell B2, and for cell A3, the formula uses B3, and so on.
So this is how you can use conditional formatting to change the font color of a cell based on another cell value as well.
In this tutorial, I showed you a couple of ways you can use to change the font color of a cell based on its value in it. In most cases, it can easily be done using Conditional Formatting, and I’ve also covered another method using Custom Cell Formatting to do this.
Other Excel articles you may also find helpful:
- How to Move Row To Another Sheet Based On Cell Value in Excel?
- How to Hide Rows based on Cell Value in Excel (2 Easy Methods)
- How to Hide Columns Based on Cell Value in Excel
- Highlight Cell If Value Exists in Another Column in Excel
- How to Set the Default Font in Excel (Windows and Mac)
- How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
- How to Change Theme Colors in Excel?
- Show Negative Numbers in Parentheses/Brackets in Excel
- Fill Color in Excel (Keyboard Shortcut)
- How to Filter by Color in Excel?
- Count Colored Cells in Excel