Quick Excel Tip of the Day: Advanced Conditional Formatting

Hi Everyone, 

Looking to move your Excel reports from impressive to brilliant? Follow these quick excel tips that separate the experts from the rookies.

Earlier this week I did a quick post on Conditional Formatting to discuss how easy it is to apply formatting rules that make your data pop. That post can be found here and below I review some of the top 5 conditional formats that I use in my regular work. 

  1. Color Scales - by selecting any of the scales within the Color Scales options, the data becomes almost like a heat map with different data points receiving a shade depending on how close or far away it is from the max an min value. This is great for displaying sales over the course of the year because it makes peak months and low months really stand out.
  2. Icon Sets - in this menu you can quickly add a symbol next to the data to show how it is performing. For example, if you know that 95% is a good value for yeilds you can make sure that anything above 95% gets a green traffic light. This is a really easy way to give context to numbers.
  3. Icon Sets - Icon Only - for some presentations or reports the numbers themselves tend to distract people from the underlying conclusions behind the data. For this reason, it is sometimes valuable to show only the icon rather than the icon and the number. Conditional Formatting -> Icon Sets -> More Rules ->Click Show Icon Only
  4.  Top/Bottom Rules - this is also a really quick way to make some conclusions on the data. It allows you to highlight cells relative to other cells like the bottom 10%, above or below average. The best part is that this is dynamic so that as your data set changes so do the cells the conditional formatting will highlight.
  5. Data Bars - this is a less common use for Conditional Formatting in my opinion but is a quick way to add a graph next to your data showing significant outliers without needing to print in color. 

Thanks, and talk to you tomorrow