How to Change Font Color Based on Cell Value in Excel?

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.

Dataset with negative values

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:

  1. Select the cells that contain the numbers for which you want to change the font color
  2. Click the Home tab
Click the home tab
  1. In the Styles group, click on Conditional Formatting
Click on Conditional formatting
  1. Hover the cursor over the option – ‘Highlight Cell Rules’
  2. Click on the ‘Less than’ option
Click on the less than option
  1. In the less than dialog box, enter 0 in the ‘Format cells that are LESS THAN’ field
Enter 0 in the less than dialog box
  1. Select ‘Red Text’ as the formatting option from the second dropdown
Select red text as the formatting option
  1. Click OK

The above steps would instantly change the font color of the cells that contain the negative value.

Negative values font changed to red

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.

Dataset with negative values

Here are the steps to do this:

  1. Select the cells that contain the numbers
  2. 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
Click on the dialog box launcher
  1. In the Format Cells dialog box, make sure the Number tab is activated
  2. In the Category options, select the Number option
Select number in the category options
  1. Under the Negative Numbers options, select the fourth option (the one that shows numbers with a negative sign and is in red color)
Select the option where font color is red
  1. [Optional] Adjust the decimal places by using the spin button
Change the decimal points if you want
  1. 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.

Dataset with numbers in red font

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.

Dataset to change font of greater than 700 cells

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:

  1. Select the cells that have the numbers
  2. In the Home tab, click on Conditional Formatting
  3. In the options that show up, how about your cursor over the ‘Highlight Cell Rules’ option
  4. Click on the ‘Greater than’ option
Highlight cell rules greater than
  1. 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)
Enter 700 as the greater than value
  1. Click on the second drop-down, and then select the option ‘Green filled with dark green text’
Specify format as green text
  1. 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.

Dataset with green font color

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’.

Dataset with text data

Below are the steps to do this using Conditional Formatting:

  1. Select the cells that contain the comments
  2. Click the Home tab
  3. In the Styles group, click on the ‘Conditional Formatting’ icon
Click on Conditional formatting
  1. In the options that show up, click on the ‘Highlight Cell Rules’ option
  2. Click on the ‘Text that Contains’ option
Click on text that contains
  1. In the ‘Text that Contains’ dialog box, enter ‘follow’ in the text field
Enter follow in the text that contains dialog box
  1. From the drop-down, select the ‘Green fill with dark green text’ option
Specify the formatting for the cells with the specific text
  1. Click OK

The above steps would instantly highlight all the cells that contain the word ‘follow’.

Text that contains cells highlighted

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.

Data to highlight cells based on other cell value

Below are the steps to do this using Conditional Formatting:

  1. Select the cells that you want to highlight (A2:A10 in our example)
Select the cells you want to change the font color
  1. Click the Home tab
  2. Click on the ‘Conditional Formatting’ icon
Click on Conditional formatting
  1. In the options that show up, click on the ‘New Rule’ option
Click on New Rule
  1. In the New Formatting Rule dialog box, click on the ‘Use a formula to determine which cells to format’ option
Select use a formula to determine which cells to format option
  1. Enter the below formula in the formula text field:
=B2<500
Enter the formula
  1. Click on the Format button
Click on the format button
  1. In the Format Cells dialog box that opens, select the Font tab (if not activated already)
  2. Click on the Color option and select the red color
Specify the font color
  1. [Optional] Select ‘Bold’ as the Font Style
  2. Click OK
  3. Click OK

The above steps would change the font color of the cells in column A based on the value in column B.

Cells with font color changed based on other cell value

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment