Skip to main content

How to change font color based on cell value in Excel?

Author: Sun Last Modified: 2024-07-04

In Excel, changing the font color based on cell values can significantly enhance the readability and effectiveness of your data presentation. Whether you're managing budgets, tracking performance metrics, or analyzing sales data, visual cues like color changes can instantly communicate trends and exceptions to users. This article will walk you through two powerful methods to change font colors based on cell values: using Excel's built-in Conditional Formatting and the advanced capabilities of Kutools AI.


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 range of cell values you want to handle, 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, 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.

doc change font color based on cell value 2

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:

doc change font color based on cell value 3

4. Click OK > OK to close dialogs. Now all negative values are changed the font color to red.

doc change font color based on cell value 4

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

doc change font color based on cell value 5

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.

doc change font color based on cell value 6

(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:

doc change font color based on cell value 7

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.

doc change font color based on cell value 8


Use Kutools AI to easily change font color based on cell value

Compared to the multi-step process required by Conditional Formatting, Kutools AI simplifies the task of changing font colors based on cell values to just a single command. Users can directly instruct Kutools AI with a simple command, such as 'Please change the font color of cells greater than 0 in the selection to red.' This action is performed immediately, minimizing the time and effort required and thus increasing productivity.

After installing Kutools for Excel, go to the Kutools AI tab and select AI Aide in the AI group. Then Kutools AI Aide (Preview) pane will appear on the right side of the worksheet. To use it, follow these steps:

  1. Select the range you wish to change font color based on cell value.
  2. In the Kutools AI Aide pane, enter the command describing your desired operation.
    Here I instructed Kutools AI: Please change the font color of cells greater than 0 in the selection to red. Please adjust the command's keywords to suit your specific needs.
  3. Click the Send button or press Enter.
  4. Kutools AI will process the command and provide feedback. Clicking the Execute button will immediately change the font color based on the specified condition.
Notes:
  • You can command Kutools AI to apply multiple conditions simultaneously. For instance, to change the font color of cells greater than 0 to red and those less than 0 to green, simply instruct Kutools AI: "Please change the font color of cells greater than 0 in the selection to red, and cells less than 0 to green." This will enable Kutools AI to apply different colors based on the conditions you specify. See screenshot:
  • Kutools AI can do much more than just basic calculations as above — it can generate formulas, analyze data, explain formulas and so on based on your commands. Explore the full capabilities of Kutools AI with a 30-day free trial and discover how it can streamline your Excel tasks. Download now to start simplifying your data management!

Utilizing Excel's Conditional Formatting and Kutools AI to change font colors based on cell values can dramatically enhance your data management experience. These tools allow you to set visual cues that automatically adapt as your data changes, making spreadsheets not only more visually appealing but also more informative and easier to navigate. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
helpful. thank you for sharing
This comment was minimized by the moderator on the site
buen dia, tengo un probloema respecto al formato condicionar cuando es "menor que" tengo una tabla de datos de inventario, quiero usar la formula en valores menor que 50 kg, el detalle es que si pongo un valor mas alto como 420kg, lo colorea,que se puede hacer ahi ?
This comment was minimized by the moderator on the site
can you change the color of only one word in a cell? does that work?
This comment was minimized by the moderator on the site
Hi, bruna, this tutorial How To Highlight Specific Text Within A Cell Based On Other Text? provides methons on color the font color of the keyword only in the cells, I think it may help you.
This comment was minimized by the moderator on the site
I would like to have the font change color if another cell is blank or not blank. can that be done?
This comment was minimized by the moderator on the site
I would like to change the color of the whole raw if the value is equal to 0. It is explained how to do for a cell, but how to change the whole raw, pls?Example:Number Test Difference Faults 1 Test1 x different than x1 0 2 Test2 y different than y1 1
I would like to change the Raw Number 1 color in Grey.
This comment was minimized by the moderator on the site
I want to format colours based on a number range, eg. if the number is 0-4000 I want it to be green, then if the number is between 4001 - 7000 I want it to be blue and if its is 7001 - 10000 I want it to be purple, and anything over 10000 I want it to be red. And I don't want it to be a blended colour of purple and red, I only want it to specifically be that one solid colour within the value range.
This comment was minimized by the moderator on the site
I am trying to conditional format numbers, and also tried vba. I want to format 4 ways. >0, <0>-100, <-100>-200, <-200. Can get first 2 working but hit a wall with more than 1 negative value to format.
This comment was minimized by the moderator on the site
Thank you.
This comment was minimized by the moderator on the site
Hi,

is there any way to make value as per cell color Example cell red color = 10
This comment was minimized by the moderator on the site
Sorry, I did not get your question, could you describe more details?
This comment was minimized by the moderator on the site
Needed to change the colour of the font based on if the number had gone up or down from its start point. Search Google, found your post and it answered my question immediately. Huge thanks and great work. Neil
This comment was minimized by the moderator on the site
Thank you for the support!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations