OmniDocWhat are the rule types of Excel conditional formatting: Function Overview
Sort out the six major categories of Excel conditional formatting rules, including highlighted cells, rankings, data bars, color scales, icon sets, and custom formulas, explaining what data each is suitable for, and helping you choose the right rule type before setting it up.
Key points of this article
- Excel conditional formatting is roughly divided into six categories: highlighted cells, rankings, data bars, color scales, icon sets and custom formulas. First select the broad categories according to data characteristics, and then refine the conditions.
- Numerical comparison and text and date matching can be done directly using highlighting rules; use custom formula rules only when flexible judgment is needed.
- Data bars and color scales compare the relative size within the selected area and do not represent the absolute value of the cell.
- When multiple rules are superimposed in the same area, the rule ranked first will take precedence. After setting, you need to check the order in the rule management.
- Conditional formatting only changes the display effect, not the values in the cells, and the sorting and calculation results are not affected.
When making tables, many people want numbers that exceed the target to turn green, duplicate customer lists to be automatically marked red, or a column of data to use gradient colors to indicate high and low levels. These effects are achieved by Excel’s conditional formatting. But after opening the function menu, you will see a series of options such as highlighting, ranking, data bar, color scale, icon set, new rule, etc. I don't know the difference between them, and I don't know which one can solve my problem.
This article gives an overview of the functions of Excel conditional formatting, divides the rule types into several categories according to their uses, explains what data each category is suitable for, what information can be seen, and where it is easy to use it incorrectly. It is not a tutorial on how to operate a certain rule, but helps you see the map clearly before deciding which path to take. After reading, you will be able to judge which category your needs fall into, and also know what points you should check before setting up.
The following content is mainly based on the conditional formatting that comes with Excel, and talks about common rule types and setting ideas. If the table in your hand is an .xlsx file, it can also be processed on the website or client mentioned below, but the effect of conditional formatting is still subject to the actual display in Excel.
What are the rule types for Excel conditional formatting? One sentence conclusion
Conditional formatting rules can be divided into six categories, among which highlighting, ranking, data bars, color scales, and icon sets belong to the "quick mark by value or text" rules, and custom formulas are used for complex conditions that cannot be expressed by other rules. When choosing a major category, first check whether you want to mark values, text, dates, or whether you need to compare the overall distribution.
| your situation | Recommended practices | reason |
|---|---|---|
| Want to mark numbers that are greater than, less than, or equal to a certain value | Highlight cell rules | Just fill in the values directly for the conditions, and it’s quick to set up. |
| Want to find the top 10 or bottom 10% of data | Ranking rules | No need to manually calculate the threshold, the results are automatically updated when the area changes |
| Want to see the size comparison of a series of numbers at a glance | data bar or color scale | Visually display relative size using length or color depth, suitable for browsing |
| Want to use symbols such as arrows and traffic lights to indicate levels | Icon set | Suitable for hierarchical data such as status, progress, and compliance with standards |
| The condition involves multiple column comparisons or requires combined judgment. | Custom formula rules | Formulas return true or false and can express almost any condition |
Highlight cell rules: a commonly used category
Highlighted cell rules are based on the value of the cell. Common conditions include greater than, less than, within a certain interval, equal to a certain value, the text contains certain words, the date occurs within a certain period, and repeated values. The advantage of this type of rules is that the conditions are intuitive, just fill in numerical values or text, and are suitable for daily data checking.
For example, in a sales column, fill cells greater than 10,000 with light green, or mark duplicate job numbers in red in an employee list. This type of requirement does not require formulas. It can be completed by directly setting the conditions and format in the menu after selecting the area.
Rankings, data bars, color scales and icon sets: visually expressing distributions
Ranking rules mark the top N items, the bottom N items, or data above or below the average. The difference with highlighting is that the threshold is determined by the data itself, you don't have to work out in advance what 10th place is.
The data bar draws a horizontal color bar in the cell, and the length is related to the value; the color scale fills the cell with a two-color or three-color gradient, and the color depth represents the level; the icon set places arrows, flags, dots and other symbols on the left side of the cell to divide the data into several levels. All three are suitable for browsing the overall trend, but they can only make rough judgments and are not suitable for reading precise values.
Custom formula rules: flexible, also need to be checked
When creating a new rule, choose to use a formula to determine the cells to be formatted, and you will enter the custom formula rule. When the result of the formula is true, the specified format is applied to the cell; when the result is false, it is not applied. This type of rule can reference other cells, compare multiple fields on the same row, or implement combined conditions such as "amount exceeds budget and status is incomplete."
Pay special attention to cell references in formulas. The reference method determines whether the reference will move when the rule is applied to other cells. If you want a fixed reference to a column, you need to add an absolute reference symbol before the column label. Before setting, manually try out the formula in a cell to confirm whether the result is true or false and meets expectations, which can save a lot of rework.
Several easily confused areas
- Data bars and color levels are relative. They are based on the maximum and minimum values of the selected area. If you change the area, the same number may display a different length or color.
- Colors do not represent exact values. The color scale is suitable for judging where is high and where is low. If you need a more accurate difference, you should combine it with the original numbers.
- Multiple rules will be superimposed. When the same cell matches multiple rules, the first rule will be displayed first, and the later rules may be overwritten. This is the reason why many people encounter "wrong color".
- Conditional formatting does not change the data itself. It only affects display, sorting, filtering and calculations still use the original values.
📊 Core Comparison
| Rule types | Suitable data | What can be seen? | Points to Note |
|---|---|---|---|
| Highlight the cells | Numbers, text, dates | Whether the clear conditions are met | Conditions need to be predetermined with thresholds |
| Ranking category | Numerical values | Front-row or last-place data | Thresholds change with the region, and the results shift |
| Data bars | Numerical values | Relative length of size | Using regional extremes as the baseline |
| Color scale | Numerical values | Gradual distribution of high and low levels | It is difficult to accurately read the difference in color depth |
| Icon set | Tiered data and progress | The level or status to which one belongs | Grading boundaries need to be reasonably set |
| Custom formulas | Any type can be checked across columns | Whether the complex conditions are met | Incorrect citation methods can lead to result bias |
How to actually use it
Below are the general setup steps for most Excel versions of conditional formatting.
- Select the cell area where you want to apply the rule, and try not to select whole columns to avoid slowing the spreadsheet.
- Find the conditional formatting command in the menu bar to view the list of various rules.
- Select major categories based on previous judgments, such as highlighting, ranking, data bars, or creating new rules.
- In the pop-up dialog box, fill in the conditions and format, preview the effect, and confirm.
- Open the rule management interface, check the order of multiple rules in the same area, and adjust their priority if necessary.
If the table you have is a .xlsx file and needs to be organized into another language, you can upload the table to omnidoc.top, choose bilingual or translation-only, and download the results. After translation, please check whether the numbers, formulas, and conditional formatting in the table still meet expectations; important content requires manual review. If you need to edit spreadsheets directly on the desktop, you can download the download.omnidoc.top client, which includes UniCell spreadsheets.
🎯 Which method suits you?
- Just want to quickly mark a few numbers: start by using the highlighted cell rule and fill in the threshold.
- Want to see the overall distribution of a performance or sales: select data bars or color levels, suitable for browsing before a report.
- Indicate completion status or risk level: use a chart set and set the grading boundaries in advance.
- Conditions involving multiple fields or special logic: directly write custom formulas and first perform trial calculations in the cells.
❓ Frequently Asked Questions (FAQ)
1. I set conditional formatting, but the color doesn't appear. What should I do?
First, check if the data is in numeric format. In many cases, if the numbers are stored as text, the comparison criteria will not hold. Next, check whether the rule scope includes the target cell, and finally check if other rules prioritize color coverage.
2. Can formula rules reference data from other worksheets?
Formula rules can reference cells from other worksheets, but for easier checking and maintenance, it is recommended to place reference values in the same worksheet or summarize them through auxiliary columns before reference.
3. Will copying cells copy the conditional formatting along with it?
Yes. When using Format Brush or copy-paste, conditional formatting usually moves with the cells to a new position. If you don't want to bring them over, you can use the method of just pasting the values.
4. How to remove unwanted conditional formatting?
After selecting a cell, you can choose the Clear Rule from the Conditional Formatting menu, or choose the rule to clear the entire worksheet. Before clearing it, it is recommended to check the rule management to confirm which rules need to be kept.
5. Will the many rules in the table slow down?
The number of rules and their scope of application both affect performance. Try to limit it to areas where you actually need it, and avoid overlaying complex formulas on whole rows or lots of blank cells.
📝 Summary
The rules of Excel conditional formatting are divided into six categories: highlight, ranking, data bars, color levels, icon sets, and custom formulas, each with its own suitable data type and judgment method. Before setting up, make sure you want to mark thresholds, rankings, distributions, or complex conditions, then select the right major categories to save a lot of adjustment time.
