OmniDocHow to use formulas to set colors in Excel conditional formatting: Custom rules
Explain the complete method of using formulas to set colors in Excel conditional formatting, including IF judgment of color change, highlighting of entire rows, referencing other cells and skipping blanks, and gives the writing and checking methods for each situation.
Key points of this article
- The core of formula rules is to apply the format when the formula returns TRUE, and the formula must start with an equal sign.
- The relative reference in the formula is based on the upper left corner cell of the selected range. Use $A2 to lock the column and A$1 to lock the row.
- When the entire row changes color, the entire area is selected, and the formula only refers to one column, for example, $C2>100.
- To skip blanks, add exclusion conditions to the formula, such as AND($A2<>"",$A2>60), otherwise blank rows may also change color.
- After setting up, use "Manage Rules" to check the application scope and rule order, and spot-check a few rows of data.
When many people use Excel to make performance tables, inventory tables, or sales tables, they want numbers that exceed the standard to automatically turn red, or to make a certain row change color as a whole based on the value of one of the columns. The basic "highlight cell rules" can only compare the value of a single cell. When encountering requirements such as "mark column A red if column B is greater than 60" or "turn the entire row gray if the status is completed", you have to write your own formula.
This article is written for readers who already know how to use conditional formatting, but don’t know how to write formulas. After reading, you will be able to distinguish the difference between formula rules and ordinary rules, and you will be able to write formulas with IF judgments, other cell references, whole row discoloration and skipping blanks, and you will know which places to check after the settings are completed. The following are general operations for Excel and have nothing to do with any online tool.
How to set Excel conditional formatting color using formula? One sentence conclusion
Setting the color with a formula essentially creates a new rule that lets Excel calculate a formula for each cell. When the result is TRUE, the font, fill, or border color you specify will be applied. In one sentence: write a judgment that returns TRUE or FALSE, and then bind the format to it.
| your situation | Recommended practices | reason |
|---|---|---|
| Compare only the value of the current cell | Use normal "cell value" rules | Quick to set up, no need to write formulas |
| Judge based on the values of other columns in the same row | Use formula rules to reference columns in the same row | Ordinary rules cannot compare across columns |
| To make the entire row change color together | Select the entire row range, the formula only refers to one column and locks the column | One rule can cover all columns |
| There are blank rows or unfilled fields in the data | Add non-empty judgment to the formula | Avoid empty lines being misjudged as qualifying |
| Requires multiple conditions and colors | Create multiple new rules, pay attention to the order | Rules can be superimposed in the same area, and the order determines which color takes effect. |
Before you start: Confirm these points first
First determine which cells are referenced by the judgment condition. The effect of formula rules depends on the reference position. If what you want to determine is column B, and the discolored column is column A, you have to write the position of column B clearly in the formula. Listing the "judgment columns" and "discolored areas" on paper before starting can save a lot of detours.
Confirm if the data type is numeric or text. Numbers can be compared directly, text comparisons must be in quotation marks, such as = "Completed". If the numbers in the table are saved in text form, the formula comparison results may not be as expected, and there is often a small green triangle in the upper left corner.
Understand relative and absolute references. References in formula rules are relative to the cell in the upper left corner of the selected range. For example, after selecting A2:A100 and writing the formula as B2>60, Excel will automatically make row 3 correspond to B3 and row 4 correspond to B4. If you want all cells to be fixedly compared to B2, you need to write $B$2. The $ symbol determines which parts will change when dragged or applied to other areas.
Prepare a small sample. Find a test table with about 10 rows, try the rules on a sample first, and confirm that the color and range are correct before using the official table. Large tables are set directly, making it difficult to troubleshoot problems.
Steps to use formulas to set conditional color changes in Excel
- Select the area you want to change color. For example A2 to A100 of the grade sheet. Be careful not to include the header unless the header also participates in the color change. The cell in the upper left corner of the selection is the reference point of the formula, and all subsequent writing shall be based on it.
- Open conditional formatting and create a new rule. Select New Rule in the Conditional Formatting menu, and select the rule type to determine the cells to be formatted by formula. This is a type of rule in Excel specifically for custom formulas.
- Enter the formula starting with an equal sign. The simple way to write it is =$B2>=60, which means that when the score of column B in the same row is not less than 60, column A of this row will change color. The equal sign in front of the formula cannot be omitted.4. Set format color. Click the Format button, select the desired color from the font color or fill color, and return to the rules window after confirmation. Click OK when the preview is correct.5. When IF judgment is required, use the IF function to wrap the condition. For example =IF($B2="","",$B2>=60), which means that when column B is empty, it returns empty and does not change color; when it has a value, it will be judged whether it meets the standard. The three parameters of IF are the condition, the result when the condition is true, and the result when the condition is false. The logic is that the color changes only when the result is TRUE.6. When the entire row changes color, select the entire row area and the formula only refers to one column. For example, if A2:E100 is selected, the formula is written as =$C2="Completed". $C locks the judgment column, row number 2, so that each row looks at column C of its own row, so the entire row of columns A to E changes color together.
- Skip blank cells. If there are blank rows in the judgment column, the formula may mistakenly treat the blank value as 0, causing the blank rows to change color. It can be written as =AND($A2<>"",$A2>60), first exclude null values, and then make size judgment.
- Check the application scope and order of the rules. After the settings are completed, return to the conditional formatting rule management window and confirm that the "Apply to" area is correct. When there are multiple rules in the same area, the earlier rule takes precedence. If you need to make the latter rule also take effect, you should check the options other than "Stop if true" and pay attention to the colors of the two rules not to conflict.
Check each item after completion
When formula rules are written incorrectly, a common symptom is that the color does not appear at all, or the entire area changes color together. The table below lists areas that need to be checked.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Application scope | Check whether the area "applied to" is consistent with the selection in rule management | Modify the application scope, or delete and re-select the rules to create another one. |
| Whether the reference is locked | Look at the position of the $ sign in the formula. When the entire row changes color, there should be $ in front of the column. | If necessary, add $ before the column letter, but do not add $ before the row number. |
| Whether the formula returns TRUE | Find a few lines and check manually to determine whether the results are consistent with the color. | Check the types of comparison symbols, numbers and text. Text must be quoted. |
| blank line | Check if blank lines change color | Add a non-empty judgment to the formula, such as $A2<>"" |
| Rule order | When there are multiple rules in the same area, check whether the color is covered | Adjust the top and bottom order in rule management |
| Formula error | Cells may not change color if a formula references the wrong value | Check whether the brackets are matched and whether the quotation marks are English half-width |
📊 Core comparison
| method | Applicable situations | advantage | limitations |
|---|---|---|---|
| Cell value rules | Only look at the numbers or text in this cell | Easy to set up, no need to write formulas | Cannot judge across columns or rows |
| Formula rules (without IF) | Direct comparison, such as =$B2>60 | Write in a short and logical way | Need to add when dealing with null values and multiple conditions |
| Formula rules (with IF or AND) | Need to exclude blanks and combine multiple conditions | Flexible to cover complex needs | Brackets and references are prone to errors and need to be checked one by one. |
❓ Frequently Asked Questions (FAQ)
1. How to skip spaces in conditional formatting?
In the formula, first determine whether the cell is empty, and then make other comparisons. For example =AND($A2<>"",$A2>60), it will change color only when it is not empty and greater than 60. If there is content in a cell that appears to be blank but actually contains spaces, these characters need to be cleared first, otherwise the judgment result may still be incorrect.
2. Can other cells be referenced in Excel conditional formatting?
Can. Formula rules can refer to other cells in the same worksheet, such as =$A2>$B$1, which means that the value of column A changes color when it exceeds the value of B1. When referencing data from other worksheets, the writing method and results must be checked more than once. It is recommended to verify within the same table first.
3. How to use excel conditional formatting if function?
The IF function is used in conditional formatting formulas, and its structure is IF (condition, result when the condition is true, result when the condition is false). In conditional formatting, you usually only need the result to be TRUE or FALSE, so a common way to write it is to write the judgment directly as =$B2>=60. IF is only necessary to use it when you need to distinguish between null values and other situations.
4. When the entire row changes color, why does only one cell change?
Usually the selection does not cover the entire row, or the columns in the formula are not locked. All columns of the entire row should be selected, and use $ to lock the judgment column in the formula, for example =$C2="Complete", so that each row will be judged according to column C of its own row.
5. I wrote the formula correctly, but the color still doesn’t appear. What should I do?
First check the application scope and rule sequence to make sure it is not covered by other rules. Secondly, look at the data type. When numbers are saved as text, the comparison results will be different from expectations. Finally, use a few lines of samples to manually calculate the results of the formula to determine whether it is a formula problem or a setting problem.
📝 Summary
The key to Excel conditional formatting formula rules is to write a judgment expression that returns TRUE or FALSE and use the $ sign in the correct position. Master the writing methods of IF judgment, whole-line quotation and blank exclusion, and you can handle most custom color changing requirements. After the settings are completed, check the application scope, reference method and rule sequence item by item to avoid most problems.
