OmniDocHow to set the font color of Excel conditional formatting: change the font color when the conditions are met
Explain the method of using conditional formatting in Excel to make the text color change according to the numerical value or content, including the handling of conflicts between preset rules, formula rules and multiple rules, and provide the verification method after completion.
Key points of this article
- Conditional formatting changes the display effect, the values in the cells remain unchanged, and sorting, filtering, and formula calculations are not affected.
- It is faster to use preset rules for numerical comparison; when multiple conditions are combined or to judge based on the entire row, use formula rules instead.
- When multiple rules in the same cell have font colors set, the rule listed above will take precedence, and the order needs to be adjusted in rule management.
- The cell reference in the formula rules must be written relative to the upper left corner of the selected area. If you write a wrong dollar sign, the entire column will be misjudged.
When many people mark data in Excel, they are used to manually changing the numbers that exceed the standard into red letters. Once the data is updated, they have to find and change it again. A safer approach would be to let the font color be determined automatically by the criteria: scores below 60 points become red, low-stock items become orange, and overdue dates become gray. This type of requirement is called conditional formatting in Excel. After setting it up, the color will change as the data changes.
This article is written for readers who often make reports, ledgers, and score sheets. It is also suitable for people who are new to conditional formatting and can only change the cell fill color and don't know how to change only the text color. After reading, you will know: which rule to use for judgment, where to set the font color, how to deal with multiple rules fighting, and how to quickly check whether the result is correct after finishing.
How to make the font color change according to conditions? One sentence conclusion
Use conditional formatting to create a new rule, set the judgment conditions, and then select only "Font Color" in the format settings of the rule; use preset rules for simple numerical comparisons, and use formula rules for complex conditions.
| your situation | Recommended practices | reason |
|---|---|---|
| Only judge based on a numerical range, such as greater than, less than, between | Use preset cell highlighting rules, set thresholds and choose font color | There are fewer steps, no need to write formulas, and the color of the threshold is automatically updated when the threshold is changed. |
| To judge based on text content, such as containing a certain word | Use the "Text Contains" class preset rules | No need for case-sensitive formulas, just enter keywords directly |
| The condition involves multiple columns. For example, two numbers in the same row must be satisfied. | Use formula rules to set the font color when the formula result is true | The preset rules can only judge the current cell and cannot reference other columns. |
| To change the color of the entire line of text, not just a certain box | Select the entire row of data range and use a formula to determine the value of a certain column | If you fix the columns in the formula and let the row numbers change relatively, the entire row will take effect together. |
| The same area needs to display multiple colors at the same time | Create multiple rules separately and adjust their priorities | The font color can only take one value. In case of conflict, it is determined by priority. |
Before you start: Confirm these points first
1. Make it clear whether the judgment is numerical or literal. The rules for numeric comparison and literal inclusion are different. If the number is saved as text, for example, when copied from a web page or other system, there is a small green triangle in the upper left corner. The numerical rules may not judge it correctly. First, convert it into a real number by dividing it into columns or multiplying by 1.
2. Select the area you want to change color first, not the entire table. The judgment range of conditional formatting is the area you select. If the area is selected too large, the header and total rows will be accidentally damaged, while if the area is selected small, data will be missed. It is recommended to select only the main part of the data, excluding the header row.
3. Think about whether the color will conflict with other formats. If you have manually set the font color for certain cells before, the conditional formatting will override the manual setting, but the manual setting will be displayed when the conditions are not met, which can easily make people mistakenly think that the rule has not taken effect. Try to clear these manual colors before setting them.
4. Determine how to write cell references. In formula rules, the reference position is calculated relative to the upper left corner of your selected area. For example, the selected area starts from C2, and the formula is written as =$C2>100, which means that each row will look at the value of column C of its own row. The details of this writing method determine whether the judgment is correct, and will be used repeatedly later.
5. If the form is to be sent to others, consider the other party’s software version. Support for complex formula rules may vary slightly in older versions of spreadsheet software. If the color is wrong after the recipient opens it, first check whether the formula refers to other worksheets, and try to change the reference to a cell in this worksheet.
Steps to set font color conditionally in Excel
- Select the data area to be judged. For example, B2 to B50 in the grade table, excluding the header row.
- Open the conditional formatting menu and select New Rule. Find the conditional formatting-related commands in the Ribbon, which is usually located in the Start-related options.
- Select the rule type. If you are comparing numerical values, select options such as "Less Than" or "Greater Than" under Highlight Cell Rules; if you want to customize conditions, select Use a formula to determine the cells to be formatted.
- Fill in the conditions. Enter the threshold value in the preset rule, such as 60; enter a formula similar to =$B2<60 in the formula rule. Note that the reference should correspond to the upper left corner of the selected area.
- Click the Format button to enter cell format settings. Switch to the font tab and select only the required color in the color drop-down box, leaving other font items unchanged.
- Confirm and apply. After closing the dialog box and returning to the table, the cell text that meets the conditions should change color immediately.
- When multiple colors are needed, repeat the above steps to create a new rule. For example, create another green rule of "greater than or equal to 80". The conditions of the two rules should try not to overlap with each other to reduce conflicts.
- Check the priority of the rules. Open the management rule interface of conditional formatting, use the up and down arrows to adjust the order, and put the rules that need to be displayed first on top.
After completion, check each item one by one
Once the colors are set, don't just look at the first line and assume it's done. The following suggestions are to check one by one:
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Check if the boundary values are correct | Find a number that exactly matches the threshold, like 60, and see if it changes color as you request | If contrary to expectations, whether the inspection condition is "greater than" or "greater than or equal to" differs at the boundary |
| Whether the entire area is in effect | Drag to the end of the area to see if the last few rows also change color | If only part of the line is effective, check whether the formula contains the dollar sign missing or if the selection starting point is set incorrectly |
| Whether the colors overlap with each other | Check the same cell multiple times to see if the color matches the priority | Open management rules, adjust the order, or remove overlapping conditions beyond "Stop if true." |
| Check if the color synchronizes after data changes | Temporarily modify a value to see if the color changes and if it reverts to the correct value | If not updated, the numbers may have been saved as text, so first convert the data type |
| After copying and pasting, do you keep the rules? | Copy the area to another location and see if the color rules follow | Use Paste Selects to keep only the formatting, or reset the rules in a new location |
If a line has a clearly wrong color, first check the data type of that row, then check whether the reference in the formula changes with the line number. Most of the problems stem from these two aspects.
📊 Core Comparison
| Method | Suitable scenarios | Advantages | Limitations |
|---|---|---|---|
| Preset highlighted rules | Single-column numerical comparison, text inclusion | No need to write formulas, quick to get started | It can only determine the current cell, weak in conditional combination |
| Formula rules | Multi-column linkage, full-row color change, complex combination conditions | Flexible, can reference other columns and functions | Misquoting can easily cause the entire piece to be misaligned, requiring some formula foundation |
| Manually change the font color | Fixed data for one-time annotation | Intuitive, not dependent on conditions | Data does not automatically change after updates, resulting in high maintenance costs |
If the condition is fixed at a certain threshold, preset rules are used first; As long as the condition involves two or more cells, use formula rules.
❓ Frequently Asked Questions (FAQ)
1. Can conditional formatting only change the fill color of the cells, but not the text color?
No. In the formatting dialog box, the font tab offers font color options. Selecting it only changes the text color while keeping the fill color unchanged. Many people operate inside the fill tab, so they mistakenly think they can only change the background.
2. Why did the rules remain unchanged, but the text color didn't change?
There are three common reasons: numbers are stored as text; The selected area does not correspond to the starting point of the formula reference; Within the same cell, there is a rule with higher priority, so the color is set in advance. By examining these three points one by one, the cause can usually be found.
3. How can you make the entire line of text change color according to the conditions of a particular column?
Select the entire row of data areas, create a new formula rule, and add a dollar sign to the judgment column in the formula. For example, =$C 2="Overdue", with no sign for row numbers, each row will check its own column C.
4. When changing color according to the text content, should you pay attention to case or spaces?
When using the Text Inclusion Class rule, case sensitivity is usually not applied, but extra spaces can affect the judgment. If there are invisible spaces in the data, you can first use a cleanup function to remove them before making a judgment.
5. Will conditional formatting make files larger or slower?
When there are few rules and the area is small, the impact is minimal. If you set up a large number of complex formula rules on entire columns or even tens of thousands of rows, the table may experience noticeable lag. In such cases, it is recommended to shrink the area or remove unnecessary old rules.
📝 Summary
In Excel, setting font colors to change according to conditions is key: choose the correct rule type, only change the font color, and handle the priority of multiple rules. Simple thresholds use preset rules, complex conditions use formula rules. After setting, check each item by boundary value and the entire area, allowing table annotations to automatically update with the data.
