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

Let’s say that we have a spreadsheet that records a purchase date for when a customer places an order and a ship date for when the product ships out. 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

Click here to download this practice file.

Download my Excel Keyboard Shortcuts here.

 

 

 

 

 

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at 5dollardinners.com.
This entry was posted in Excel. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s