Conditionally formatted data: Examples and guidelines

Office 2010 support is ending soon

Upgrade to Microsoft 365 to work anywhere from any device and continue to receive support.

Upgrade Now

Enhancements to conditional formatting are a popular feature in Microsoft Excel. Analyzing data has never been more interesting and colorful. Now, you can track trends, check status, spot data, and find top values like never before.

Conditional formatting example

We've provided a workbook full of Conditional Formatting Samples and guidelinesĀ that you can download to help you get up-to-speed. Here's a summary of what's in the workbook.

Rule Type


Cell value

Identify specific numbers, dates, and text in a list of products

Cell value (with formula)

Identify a dynamically changed number or text value in a list of products

Top/bottom values
Above/below average

Identify top, bottom, and above average values in a recent book tour report

Top/bottom values

Determine who are the top two students in the class


Find duplicate rows in a list of customers

Icon set

Quickly see revenue status and trends from one quarter to the next

Icon set

Compare different product criteria by using a rating scale

Icon set

Examine profit trends from month to month

Icon set

Identify regional sales below $900,000

Data bars

Compare heights of the tallest mountains

3 color scale

Examine overall sales distributions in key product categories


Shade alternate rows in a range


Compare data in a cell outside the conditionally formatted range of cells


Shade an entire row where several criteria must be true


Shade an entire row if the row is a unique value

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Use conditional formatting to highlight information

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.