Back
Excel

Samples for learning conditional formatting: icons, formulas, cell values, and more

When we released Excel 2010, we published a workbook for you to download, which contains samples of conditional formatting rules. The samples are really helpful for learning how to track trends, check status, spot data, and find top values. 

But the samples don’t just abstractly explain conditional formatting rules; they apply the rules to real situations–some for busineses and some just for fun. You can find out how to compare bicycles by using a rating scale; calculate sales for a specific region and target; compare heights of the tallest mountains by using data bars; identify specific numbers, dates, and text in a product list, and more.  

Here are the rules included in the workbook: 

  1. Cell value:  Identify specific numbers, dates, and text in a list of products
  2. Cell value (with formula): Identify a dynamically changed number or text value in a list of products
  3. Top/bottom values:  Determine who are the top two students in the class
  4. Above/below average:  Identify top, bottom, and above average values in a recent book tour report
  5. Top/bottom values:  Determine who are the top two students in the class
  6. Unique/duplicate:  Find duplicate rows in a list of customers
  7. Icon set:  Quickly see revenue status and trends from one quarter to the next
  8. Icon set:  Compare different product criteria by using a rating scale
  9. Icon set:  Examine profit trends from month to month
  10. Icon set:  Identify regional sales below $900,000
  11. Data bars: Compare heights of the tallest mountains
  12. 3 color scale:  Examine overall sales distributions in key product categories
  13. Formula-based:  Shade alternate rows in a range
  14. Formula-based:  Compare data in a cell outside the conditionally formatted range of cells
  15. Formula-based:  Shade an entire row where several criteria must be true
  16. Formula-based:  Shade an entire row if the row is a unique value