How to Highlight Expired Dates in Excel (Easiest Way in 2024)
In this article, we will show you how to highlight expired dates in Excel. Simply follow the steps below.
Highlight Expired Dates in Excel
Follow the steps below on how to highlight expired dates in Excel.
1. Select the Range of Cells with Dates
Click and drag your mouse to select the range of cells that contain the dates you want to check. Make sure to include all cells that need to be evaluated for expired dates.
2. Navigate to the Conditional Formatting Menu
Go to the “Home” tab on the Ribbon at the top of the screen. In the “Styles” group, click on the “Conditional Formatting” button. This will open a dropdown menu with various formatting options.
3. Start Creating a New Formatting Rule
In the dropdown menu that appears, click on “New Rule.” This will open the “New Formatting Rule” dialog box, where you can specify the condition for highlighting expired dates.
4. Choose the Formula Rule Type
In the “New Formatting Rule” dialog box, select “Use a formula to determine which cells to format.” This option allows you to input a custom formula to identify expired dates based on today’s date.
5. Input the Formula to Identify Expired Dates
In the formula field, type the following formula: =A1<TODAY(), replacing A1 with the reference to the first cell in your selected range.
In this case, we use =B3<TODAY() as this formula represents where the cell is located. Also, this formula checks if the date in each cell is earlier than today’s date, indicating it is expired.
6. Choose and Set the Formatting Style
Click the “Format” button in the “New Formatting Rule” dialog box. This will open the “Format Cells” dialog box. Choose a formatting style, such as a fill color, font color, or border style, that will make the expired dates stand out. Click “OK” to confirm your choices.
7. Apply the New Formatting Rule
Click “OK” again to close the “New Formatting Rule” dialog box. The conditional formatting rule will now be applied to the selected range, and all dates that are earlier than today’s date will be highlighted according to the style you selected.
8. Verify and Adjust the Highlighted Dates
Review your spreadsheet to ensure that the correct dates are highlighted. If needed, you can adjust the formula or the formatting style by going back to the “Conditional Formatting” menu and editing the rule. This step ensures all expired dates are easily noticeable.
We hope that you now have a better understanding of how to highlight expired dates in Excel. If you enjoyed this article, you might also like our article on ways to highlight unique values in Excel or our article on how to highlight selected cell in Excel.
