If you have a list of dates on your Excel spreadsheet and you want to know at a glance which dates have reached their expiration point, you can set up a reminder to automatically indicate which dates have expired.
To set this reminder in Excel, all you need to do is click on the first cell where you want to have the reminder and enter the formula =IF(A1<TODAY()+2,”Expiration Reminder”,””). Press Enter and copy this formula into the rest of the cells by dragging the corner of the cell downward to fill in the rest.
Read on to see a more detailed step-by-step guide of this process.
How to Add a Reminder in Excel with a Formula
1. Click on the cell where you want the reminder to be displayed (B1).
2. Go to the Formula bar and type the formula =IF(A1<TODAY()+2,”Expiration Reminder”,””), where A1 is the cell with your date and 2 is the number of days after the specified date. “Expiration Reminder” is the text that will be displayed in the cell. (You can change this wording to fit whatever you need.)
3. Press the Enter key on your keyboard.
4. Right-click and drag the bottom right corner of the cell to copy the formula into the cells below it.
Voila! Just like that, you have added the Expiration Reminder to each of your dates.
How to Add an Alarm in Excel with Conditional Formatting
Let’s take a look at the second method.
1. Select your data (A1:A12).
2. Go to the Styles menu on the Home tab and choose the Conditional Formatting button.
3. Pick the New Rule option.
4. Select the rule type Use a formula to determine which cells to format.
5. Enter the formula =AND($A1>TODAY(),SA1-TODAY()<=30), where A1 is the cell with your date.
6. Next, click on the Format button.
7. Click on the Fill tab.
8. Choose the color you like.
9. Click OK.
Alternatively, you can choose different or additional formatting adjustments, such as changing the font or adding a border. It all depends on your preferences.
And there you go! Every expired date now has conditional formatting applied to it to help it stand out.
To better understand how to use conditional formatting in Google Sheets, we recommend you read this article.
How to Send an Email Alert Automatically
Now let’s consider the last method.
1. Press Alt+F8 to open the Macro dialog box for your spreadsheet.
2. In the Macro dialog box, type the Macro name (name it as you like—we called it “Reminder”).
3. Then press the Create tab.
4. In the Microsoft Visual Basic for Applications window, enter the following code:
Sub SendReminder() >
Dim Sh As Worksheet > Set Sh = ActiveSheet > Dim Recipient As String > Recipient = Sh.Range(“A1”).Value > Dim Subject As String >
Subject = “Reminder” Dim Body As String > Body = “This is a reminder to” & Sh.Range(“A2”).Value > Call SendEmail(Recipient, Subject, Body) > End Sub.
5. Select the Run tab.
6. Click the Run Sub/UserForm option.
The macro will send an email to the address in cell A1 with the subject line Reminder and the body text “This is a reminder to” followed by the contents of cell A2.
Excel Alarm and Reminder FAQs
When learning how to add alarms or reminders to an Excel document, users may find themselves facing various questions about the task and what to expect. We’ll take a look at some of the common questions to help you get a better feel for the process.
What Is a Reminder in Excel?
A reminder in Excel is a text message that appears in a cell when a specified condition is met—generally to advise that an item requires your attention. You can customize the text to match the nature of the condition (such as an expiration date) and specify when it should appear.
How to Add Comments to a Cell
To add a remark to your reminder, right-click on the cell where you want to comment and select the Insert Comment option.
From that point, simply type the text you want and hit the Enter key on your keyboard to save it.
The Wrap Up
An alarm or reminder can be a helpful tool to ensure that important tasks are completed promptly. By setting an alarm or reminder in Excel, you can ensure that you will receive notification in time, helping you keep on track so you avoid forgetting important deadlines.