You may need to highlight different dates for different reasons in Excel. This may include past dates, current dates, and due dates. You may track dates for interviews, deadlines, and appointments. Thus, it is crucial to keep track and record these days.
Excel presents different methods of formatting and highlighting different dates. Highlighting past dates helps you visually focus on projects and workloads that are running late. The method used to highlight past dates is known as conditional formatting.
Conditional Formatting in Excel
Conditional formatting is used in Excel to make patterns and trends more outstanding and more noticeable. This feature allows you to highlight different cells with different colors. The highlighted cells are meant to meet certain conditions.
Use conditional formatting to highlight past dates
The following steps will guide you on how to highlight past dates in Excel without a specific range of older days.
1. Select the list of projects with past dates.
2. Select the date cells you want to apply the formatting.
3. Go to the “Home tab,” click on the “style section” of the ribbon and click on the “Conditional Formatting” option.
4. Select “New Rule.”
5. Select the “Format Only Cell With the option.”
6. Select the “Less Than option.” Enter the formula =Today()
7. Click “Format.”
8. Select a color to fill the past date cells. You can choose red or blue or your favorite color. Click OK for the color fill.
9. Click “OK” again for the conditional formatting rule manager.
10. Click “Apply” to apply the changes to the selected cells, and finally, click “close.”
As you can see from the screenshot above. The conditional formatting formula has highlighted all the past dates. =today() formula specifies the date today while the less than rule selects all the cells that are less than the current dates.
Use of Conditional Formatting on Past Dates using a range
This method is the same as the first method. The only difference is that in this method, you use a range of 10 to 20 days. The steps are as follows;
1. Select and highlight the range of past date cells you want to apply the formatting.
2. Go to the “Home tab” >> “Conditional Formatting,” just like before.
3. Select “New Rule.”
4. Select “Use Formula” to determine the cells you want to format. Enter the formula, example =E1<Today(). The today function takes you back to the current date.
5. Click on the “format option.”
6. In the “Fill option,” select the color and click “OK.”
7. Press “OK” again for the conditional formatting.
8. Click “Apply,” and all the past dates will be changed.
Conditional formatting gives the layout and the presentation of your worksheet to a higher level. It makes it easier for you to spot the past dates on your worksheet in a wink of an eye. Thus it saves you time. Also, an advantage of these settings is that once they are on, excel will automatically highlight past dates depending on the range you applied.
So it’s that simple; as you can see, the processes are not that complex. You just use the conditional formatting feature, and you are set. Now you are able to have Excel highlight the past dates.