Conditional Formatting is a powerful Excel feature that automatically changes the appearance of a cell — such as its background color, text color, or font style — based on the value it contains.
When working with large datasets in Excel, it can be difficult to quickly identify important values — like the highest sales, overdue payments, or low scores. It helps you visually analyze data by automatically applying colors, icons, or styles to cells based on specific conditions.
One of the most useful features of conditional formatting is Highlight Cell Rules — which allows you to highlight cells that meet certain criteria, such as greater than, less than, equal to, between values, or containing specific text.
What are Highlight Cell Rules?
Highlight Cell Rules are the most commonly used option within Conditional Formatting. They allow you to apply formatting rules to cells that meet specific conditions or comparisons.
You can highlight cells based on:
- Greater than or Less than a value
- Between two values
- Equal to a specific number or text
- Text that contains certain words
- Dates that fall within a range
- Duplicate or Unique values
1. Greater Than…
Highlights all cells containing values greater than the specified number., If you want to highlight all sales above ₹50,000, All sales above ₹50,000 will be automatically colored (e.g., light green).
2. Less Than…
Highlights all cells containing values less than a specified number., Highlight all students who scored below 40 marks.
3. Between…
Highlights cells that contain values between two numbers., Highlight sales between ₹30,000 and ₹60,000.
4. Equal To…
Highlights cells equal to a specific number, word, or text., Highlight all orders with status “Pending”.
5. Text That Contains…
Highlights cells that contain certain text or keywords., Highlight all customers whose names contain “Enterprises”.
6. A Date Occurring…
Highlights cells that contain dates falling within a specific time range such as yesterday, today, last week, next month, etc., Highlight all tasks due next week.
7. Duplicate Values…
Highlights cells that contain duplicate or unique values., Highlight duplicate invoice numbers or repeated names.
How to Customize Formatting
When you select a rule (like Greater Than), Excel gives you some pre-made styles such as:
- Light Red Fill with Dark Red Text
- Yellow Fill with Dark Yellow Text
- Green Fill with Dark Green Text
You can also create a Custom Format for font, color, border, or background style.