How to How to Highlight Expired Dates in Excel in Excel - Practical Guide

Learn to automatically highlight expired dates in Excel using conditional formatting. This tutorial teaches you to identify past dates instantly, making inventory management, contract tracking, and deadline monitoring effortless. You'll use formulas and color-coding to transform raw date data into visual, actionable information.

Why This Matters

Highlighting expired dates prevents costly oversights and ensures timely action on critical deadlines. This skill is essential for project managers, procurement specialists, and administrative professionals.

Prerequisites

•Basic Excel knowledge and ability to navigate the ribbon
•A spreadsheet containing date values in a recognized date format

Step-by-Step Instructions

Select your date range

Click on the first date cell and drag to select all dates you want to highlight. Alternatively, click the first cell, hold Shift, and click the last cell to select the entire range.

Open Conditional Formatting

Go to Home > Conditional Formatting > New Rule (or Highlight Cell Rules for quick options) in the ribbon.

Choose rule type

Select 'Use a formula to determine which cells to format' and enter the formula: =A1<TODAY() (replace A1 with your first cell reference).

Set formatting style

Click Format > Fill tab, select a highlight color (red recommended for expired dates), then click OK.

Apply and verify

Click OK to apply the conditional formatting rule. All past dates will now display in your chosen color automatically.

Alternative Methods

Using Highlight Cell Rules

Go to Home > Conditional Formatting > Highlight Cell Rules > A Date Occurring for faster setup with predefined options like 'Yesterday' or 'Last Week'.

Using Data Bars or Icon Sets

Apply data bars or icon sets through Conditional Formatting to visually distinguish expired dates without solid color blocking.

Manual sorting and filtering

Sort dates chronologically and use AutoFilter to display only expired dates, then apply background color manually.

Tips & Tricks

✓Use TODAY() function to dynamically compare dates; expired dates update automatically as the current date changes.
✓Choose high-contrast colors (red, orange) for expired dates to ensure visibility for all team members.
✓Apply the same conditional formatting rule to multiple columns by selecting all date columns before opening the rule.

Pro Tips

★Combine multiple conditions: use =OR(A1<TODAY(), A1=TODAY()) to highlight both expired and today's dates in different colors.
★Create a 'soon to expire' rule with =AND(A1>=TODAY(), A1<=TODAY()+7) to highlight dates expiring within 7 days in yellow.
★Apply conditional formatting to entire rows using =$A1<TODAY() with absolute column reference to highlight complete records.

Troubleshooting

Conditional formatting isn't highlighting expired dates

Check that your dates are stored as date values, not text. Use Format Cells (Ctrl+1) to confirm the format is 'Date'. If dates are text, use the DATEVALUE() function to convert them.

All cells are highlighted, even future dates

Verify your formula uses < (less than) not > (greater than). The formula should be =A1<TODAY() for expired dates, not =A1>TODAY().

Formatting stops working after adding new dates

Reapply the conditional formatting rule to the extended range including new rows, or expand the original range before creating the rule.

Related Excel Formulas

TODAY()AUJOURD'HUI()IF()AND()OR()DATEDIF()

Frequently Asked Questions

Can I highlight dates expiring soon but not yet expired?

Yes, use the formula =AND(A1>=TODAY(), A1<=TODAY()+30) to highlight dates expiring within the next 30 days. Adjust the number to your preferred timeframe and apply a different color like yellow.

Will the highlighting update automatically each day?

Yes, because the TODAY() function calculates the current date dynamically. As each day passes, dates transition from 'soon to expire' to 'expired' automatically without manual updates.

How do I remove conditional formatting?

Select the formatted range and go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes all formatting from the selection.

Can I apply this to multiple sheets at once?

No, conditional formatting applies to individual sheets only. You must repeat the process for each sheet, but you can copy the entire formatted range and paste it as 'Formats Only' to another sheet.