How to apply rules when using conditional formatting

Modified on Fri, 20 Mar at 3:26 PM

Conditional Formatting is a tool found on the Home tab in Excel. Simply put, it allows you to highlight specific data within your cells to make information more "scannable," helping you spot patterns and trends instantly.

For example, we can apply a rule to automatically highlight all sales amounts greater than 3,000 in red:

Whether you want to apply conditional formatting to a range of cells, an Excel table, or even a PivotTable report, you do so by creating rules. These rules determine the cell's format based on the values they contain.

To apply these rules, follow the path: Home > Conditional Formatting. From there, you can choose from various preset options—such as highlighting cells that are greater or less than a value—or use visual aids like data bars, color scales, and icon sets. For more advanced needs, you can use "New Rule" to write your own formulas to determine which cells get formatted.

Here are some of the most common rules and their uses:

Rule type	How it works	Common use cases
Highlighting cells rules	Compares the cell value against a number, text, or date you define. Options include "Greater Than," "Less Than," "Between," "Equal To," "Text that Contains," and "A Date Occurring."	Flagging sales below a goal in red. Highlighting specific names in an employee list. Identifying invoices due "Tomorrow" or "This Week."
Top/Bottom rules	Excel analyzes the entire selected range, calculates the average, or identifies the highest/lowest values. Options include "Top 10", "Top 10%", "Above Average", and "Duplicate Values".	Identifying the top 5% of your highest-spending customers. Spotting products with stock levels below the mean. Detecting data entry errors using the Duplicate Values rule.
Data bars	These act as horizontal mini-bar charts inside the cell. The highest value in the range represents a 100% full bar, and others are drawn proportionally.	Visualizing task progress (0% to 100%). Comparing sales volumes without creating a separate chart. Quickly see which inventory bins are nearly full.
Color scales (Heat maps)	Uses a 2- or 3-color gradient (e.g., Green-Yellow-Red). The max value gets one color, the min another, and intermediates blend automatically.	Analyzing temperature or data density. Identifying risk zones in financial tables (Red for loss, Green for profit). Evaluating test score distribution in a large group.
Icon sets	Divides data into percentiles (default is thirds: 33%, 67%). You can edit these thresholds to be fixed numbers instead of percentages.	Arrows: Showing if a metric is up or down vs. last month. Traffic lights: Prioritizing tasks (High, Medium, Low). Indicators: Showing customer satisfaction levels.
Formula-based rules	Use a logical formula to return TRUE or FALSE. If the result is TRUE, the formatting is applied.	Highlighting entire rows: If "Status" is "Paid," shade the whole row gray. Mathematical formulas: Highlight cells only if the sum of two other cells exceeds a limit. Deadlines: Mark dates only if they are past today (using the > operator and the TODAY() function).

There is a rule for every need. If you need to catch data entry errors, use Duplicate Values. To spot quick trends, use Icon Sets. To compare quantities side-by-side, use Data Bars.

However, keep these two tips in mind:

Clarity first: Always establish what you need to highlight so the sheet remains understandable for other users. Over-formatting can lead to "visual noise."
Order of precedence: When applying multiple rules to the same range, Excel processes them in descending order. Use the up and down arrows in the Conditional Formatting Rules Manager to set which rule takes priority.

Interested in more? To create powerful, custom rules, you need to understand how Excel makes decisions. We invite you to read our article on logical operators in Excel to master the foundation of advanced formatting.