//Create a Status Report with Green Checks and Red Flags in Excel

Create a Status Report with Green Checks and Red Flags in Excel

Imagine we have a spreadsheet that records a purchase date for when your customer places an order and a ship date for when the product ships. We would like to have a red flag in column A for orders that currently have not been shipped. And if the product has shipped we would like a green check mark. See the below the example and then keep reading and I’ll show you how to create this spreadsheet using either Excel 2007, 2010, 2013 or 2016. Note that row 6 and row 10 displays a red flag because those orders have not been shipped.

status report in excel

Step 1: Click in cell A2 and write a formula =IF(G2=””,-1,G2-F2). This IF function checks if the Ship Date is blank. Excel an verify if there is a blank cell by using the formula =”” so if G2=”” is true then make the value equal to -1. Otherwise, if the ship date is not blank, then use the formula to equal ship date minus purchase date [G2-F2]. The answer will be zero if the order has the same ship date as the purchase date. Note that on row 4 and row 7 that the formula equals zero because the ship date is equal to the purchase date which results in 0.

Step 2: Copy the formula down using the auto fill handle.

if formula

Step 3: With the status cells selected in column A, choose the Conditional Formatting command from the Home tab. Select the icon sets menu and then click the 3 symbols (Uncircled).

icon sets 3 symbols uncircled

Step 4: Click Conditional Formatting –> manage rules –> Edit formatting rule.

Step 5: Click show icon only (Screen shot below is from Excel 2013. Will look a little different in Excel 2007)

Step 6: Green check mark icon when value is >=0 Number. (Replace percent with number from the type drown down). Type a zero in the value area.

Step 7: Click no cell icon for when < 0 and >=0 Number. ( the middle range yellow exclamation mark will be replaced with no cell icon.

Step 8: Change the red X with a red flag for when <0 and click OK.

manage rules

By | 2018-06-07T23:51:31-06:00 September 25th, 2017|Excel|6 Comments

About the Author:

Steve Chase grew up in Dayton, OH but now calls San Antonio, TX home along with his entrepreneur wife, Erin, and their 4 sons. Steve founded Sequentia Solutions in 2017 to help small business owners grow their business with practical and effective solutions. Steve's #1 Clifton StrengthsFinder skill is Learner which comes in handy while he teaches others how to best use various business software applications like QuickBooks Online and Excel. Steve is an active volunteer leader with the Boy Scouts and treasures the lessons he learned while participating in Scouts as a youth on his path to Eagle. Steve enjoys drinking coffee, a good book, visiting art museums, and doing math problems using a brand new sharpened #2 pencils.

6 Comments

  1. Ray G October 23, 2017 at 3:50 pm - Reply

    Hello Steve,
    I am totally new to this so please bear with me. I have what is probably to you a simple need but one which is driving me up the wall.
    I want to use Green Amber Red to show an action status.
    My need;
    The action has a start date (e.g. in A1) and a completion date (e.g. in B1). When a start date is entered in A1 and a completion date (which is for fixed periods of 6 months, 12 months or 18 months), is entered in B1, in C1 a green icon appears (I would like this column blank until dates are entered). 30 days before the due date icon turns amber, on due date icon turns red.
    Any help you could provide would be much appreciated.

  2. RayG November 19, 2017 at 12:41 pm - Reply

    Dear Steve,
    Thanks for taking the time to do this, I have had a play and it seems exactly what I need. As said previously, you need to bear with me. I can see and understand the formula in column ‘C’ and the conditional formatting rules which I can adapt for my spreadsheet, Can I just clarify that the extra bit of wording in G/H 2, 3, 4 is some welcome additional explanation. I cannot see that it has any active part in your work.
    You also say that it needs a little more toil, I would be interested to know what more you could be doing to improve this.

    Once again many thanks

    RayG

    • Steve Chase November 20, 2017 at 6:14 pm - Reply

      The extra bit of wording was just some notes for me to look at while I preparing the spreadsheet. They have no impact on the formatting or formulas. The problem that I thought of that needs more work is that I used the TODAY() function which is dynamic to today’s value. So that would create red x’s to appear when for anything in the past. I’m thinking that if the completion date is in the past then there should be a green check, correct? Do you always want a green check to display when it is completed even if it is late?

  3. Ray G November 24, 2017 at 2:52 pm - Reply

    Steve,
    You have hit on something I hadn’t even thought of. Of course there should and will be occasions when the project completes on time, which means green should be the indicator. If I have it right at the moment all entries will end with a red indicator no matter if completed in time or not. I’m thinking this is going to need a manual intervention, would I be able to over ride the formatting in a particular cell to reflect the final status?

    RayG

    • Steve Chase November 28, 2017 at 11:28 am - Reply

      Yes, you are on the right track. One of the powers of Conditional formatting is the “Stop when true” feature when you have multiple conditions. When you click manage rule all the rule will sequence in the order top down. You can arrange them so that the first one is a statement to something that tests the TODAY() to the project end date.

Leave A Comment