How to change font color based on cell value in Excel?
Have you ever imaged that change font color based on cell value in Excel? Take instance, when the data is negative, you may want the data font color is red, or you need the data is black. Here I introduce some convenient ways to help you save time to change font color by cell value in Excel.
Change font color based on cell value with Conditional Formatting
Change font color based on cell value with Select Specific Cells 
Change font color based on cell value with Conditional Formatting
In Excel, the Conditional Formatting can do a favor on the changing font color by cell.
(1) Change font color if negative/positive
If you want to change font color if cell values are negative or positive, you can do as below:
1. Select the cell values, and click Home > Conditional Formatting > New Rule. See screenshot:
2. Then in the New Formatting Rule dialog, select Format only cells that contain in the Select a Rule Type: section, and if you want to change font color if cell value is negative, you can choose Cell Value from first list and choose less than from the middle list, and then type 0 into the right text box. See screenshot:
Tip: If you want to change the positive values’ font color, just select Greater than from middle list.
3. Click Format to go to Format Cells dialog, then under the Font tab, select one color you want from the Color list. See screenshot:
4. Click OK > OK to close dialogs. Now all negative values are changed the font color to red.
(2) Change font color if greater than/less than
If you want to change font color when the values are greater than or less than a specific value, you can do as these:
1. Select the cell values, and click Home > Conditional Formatting > New Rule.
2. Then in the New Formatting Rule dialog, select Format only cells that contain in the Select a Rule Type: section, choose Cell value from first list and greater than from the middle list, and then type the specific value into the right text box. See screenshot:
Tip: If you want to change font color when the cell values are less than a specific value, just choose less than from middle list.
3. Click Format to go to Format Cells dialog, then under the Font tab, select one color you want from the Color list. Then click OK > OK to close dialogs. All the values greater than 50 have been change font color to orange.
(3) Change font color if contain
If you want to change font color if the cell values contain a specific text, for example, change the font color if the cell value contains KTE, you can do as these:
1. Select the cell values, and click Home > Conditional Formatting > New Rule.
2. Then in the New Formatting Rule dialog, select Format only cells that contain in the Select a Rule Type: section, choose Specific Text from first list and Containing from the middle list, and then type the specific text into the right text box. See screenshot:
3. Click Format to go to Format Cells dialog, then under the Font tab, select one color you want from the Color list. Then click OK > OK to close dialogs. All the cells containing KTE have been change font color to the specified color.
Change font color based on cell value with Select Specific Cells
If you like to try some handy add-in tools, you can have a try on Kutools for Excel, there is a utility called Select Specific Cells can quickly select the cells meeting one or two criteria and then you can change their font color.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. | ||
After free installing Kutools for Excel, please do as below:
1. Select the cells you want to work with, and click Kutools > Select > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog, check Cell option under Selection type, and select Contains under Specific type, then type the specific text into text box
3. Click Ok > OK to close dialogs.
4. Then all cells containing KTE have been selected, and go to Home > Font Color to select the font color you want.
Note:
1. With Kutools for Excel’s Select Specific Cells utility, you also can select cells meet below criterion:
2. Also, you can select cells meeting two criteria. Select first criterion from first drop down list, then select second criterion from second drop down list, and if you want to select cells meeting two criteria simultaneously, check And option, if you want to select cells meeting one of the two criteria, check Or option.
Click here to know more about Select Specific Cells.
Change Font Color Based on Cell Value
Quickly Count/Sum cells by background or format color in Excel |
| In some cases, you may have a range of cells with multiple colors, and what you want is to count/sum values based on same color, how can you quickly calculate? With Kutools for Excel's Count by Color, you can quickly do many calculations by color, and also can generate a report of the calculated result. Click for free full featured trial in 30 days! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
