I am a very visual person. I like to glance at something and glean some basic important information. For example, I maintain a spreadsheet that serves as a client dashboard. I designed it in such a way that I can quickly gauge the status of my jobs in relation to certain due dates. To accomplish this, I use the Conditional Formatting function in Excel 2007 to show me a green, yellow, or red icon depending on how close today’s date is to the due date. Note: This only works in Excel 2007 or later.
Here is an example:
Here is how to create this:
- Set cell B1 to today’s date using the formula “=now()” without quotes. That way, the date will always be current.
- List the project due dates in column B.
- In column C next to the due dates, add a formula to calculate the difference between the due date and today’s date. In this example, cell C4 contains the formula “=B4-$B$1” without quotes. Drag the formula down to all project rows.
- In column C, set the font size to 18 and the text color to white so we do not see the actual values.
- Select cells C4 through C7 and go to Conditional Formatting –> New Rule… on the Home ribbon.
- Under “Select a Rule Type,” leave the default selected (Format all cells based on their values).
- Under “Format Style” select Icon Sets
- Set the rules as shown below and click OK. Note: Set the Type to Number first before setting the Value.
Your worksheet should now look like the example. This should be a jumping off point for more creative uses of conditional formatting and the expanded options available in Excel 2007 and later. If you develop something particularly creative, please share in the comments!
CPA Tech Tips 
#1 by chandan kumar on June 30, 2011 - 3:27 am
the article is really cool. I’ve been habituated to use excel as a to do list. Now i could have a priority indicator for each task. Thanks for the stuff.
#2 by chandan kumar on June 30, 2011 - 3:27 am
the article is really cool. I’ve been habituated to use excel as a to do list. Now i could have a priority indicator for each task. Thanks for the stuff.
#3 by Ruth Rutherford on January 25, 2012 - 8:56 am
This is great, thanks!
P.S. Instead of changing text you don’t want to see to white… you can simply click “show icon only.” This is helpful especially if you have colored rows… where you would still see the white text.
Great tips!
#4 by Kiran on March 6, 2013 - 6:28 am
Thank you for the tip. Really helps
#5 by Karin on April 10, 2012 - 1:46 pm
This is great! Thanks!! I’ve been searchibng for a visual just like this…
#6 by Adele Fazzone on January 15, 2013 - 11:24 am
This works great. Thank you!
#7 by Sandy on March 10, 2014 - 1:46 pm
This worked great. thank you for posting this!
I have a question to take it a step further. I have set up the dute dates and color icons, now I added a colomn next to the icons. The coloumn is a Yes?NO? data validation drop down box. Is there a way to have my icons turn GREEN when the drop down is “yes” regardless if it is past the due date? Please help. I am an amature and trying to learn this myself.
Thank you!
#8 by Johnf239 on May 26, 2014 - 5:54 pm
My brother recommended I may like this website. He used to be totally right. This post truly made my day. You can not consider simply how so much time I had spent for this info! Thanks! gfagfbckdebf
#9 by Shevvie on April 13, 2016 - 8:14 am
I’ve tried using the reverse on the 3 icons, but it didn’t do what I wanted namely Amber = pending, Red = overdue, Green = paid, is that possible in Conditional Formatting or would I need mad programming skills in VBA…..many thanks Johanna
#10 by Aventure on November 26, 2018 - 2:01 pm
Thanks for sharing Aventure
#11 by Frances on January 13, 2019 - 3:24 am
Brilliant. I have been puzzling with this for ages. This sorted things out in minutes. Thank you.
#12 by Carlie on March 27, 2019 - 12:57 pm
What about for a date that’s already passed? It’s showing red for any negative (-) numbers.
#13 by Bakare Rasaq on April 5, 2019 - 5:52 am
yes… my current headache is old date and not current date. what do i do?