From Excel 2010, there’s a way to automatically highlight upcoming and past due dates. For example, if you have expiry dates for stock in a spreadsheet, it’s possible to highlight the stock that has expired as well as the stock that will expire within 30 days. This is achievable with conditional formatting. Below is a demonstration of how this can be done.
You’re also welcome to download the workbook to practice.
1. Select the Home tab in the toolbar at the top of the screen.
2. Then in the styles group, click on the Conditional Formatting drop-down and select Manage Rules.
3. When the Conditional Formatting Rules Manager window appears, click on the “New Rule” button to enter the first condition.
4. When the New Formatting Rule window appears, select the “Format only cells that contain” rule type.
Then select Cell Value in the first drop down, less than in the second drop down, and enter the following formula:
=NOW()+30
Next, we need to select what formatting to apply when this condition is met. To do this, click on the Format button:
5. When the Format Cells window appears, select the Fill tab. Then select the color that you would like to use for the dates that will expire in the next 30 days. In this example, we’ve selected green. Then click on the OK button.
6. When you return to the New Formatting Rule window, you should see the preview of the formatting in the preview box. In this example, the preview box shows green as the fill color. Click on the OK button.
This will return you to the Conditional Formatting Rules Manager window. Select OK.
The stock that has already expired, as well as the stock that will expire in less than 30 days from now, will be highlighted in green.
This is an easy and effective way of automatically highlighting upcoming and past due dates in Excel.
