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.
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.
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).
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.
Click here to download this practice file.
Download my Excel Keyboard Shortcuts here.