How to Add a Drop-Down Calendar in Excel

What a drop-down calendar does and when you need one

A drop-down calendar in Excel is a small calendar picker that appears when you click a cell, letting you select a date instead of typing it. When you click the calendar icon, a month view opens, you click the date you want, and Excel fills that cell with the date automatically. This saves time when you are entering many dates and reduces typing errors.

You need this when you are building a spreadsheet that other people will use — a project timeline, an event sign-up sheet, an appointment log, or an expense tracker where dates matter. It makes the spreadsheet easier to use because the person entering data does not have to remember the correct date format or type carefully. Excel has two ways to do this: using a built-in date picker (the simpler route in newer versions) or creating one with a macro (more control, but requires more setup).

Key Takeaways

Excel 2016 and newer on Windows, and Excel 2016 and newer on Mac, have a built-in date picker that appears automatically when you format a cell as a date and click it.
The built-in date picker works without any code or macros — you only need to format the cells and the calendar appears on its own.
If the built-in picker does not appear or you need more control, you can create a custom calendar using a macro written in VBA (Visual Basic for Applications).
Testing your calendar with actual users before rolling it out catches problems like wrong date formats or cells that do not respond to clicks.

Using Excel's built-in date picker

The easiest method is the one Excel provides automatically. Open your spreadsheet and select the cells where you want dates to go. Right-click and choose Format Cells, or press Ctrl+1 on Windows or Command+1 on Mac. In the Format Cells dialog, click the Number tab, then select Date from the Category list on the left. Choose the date format you want — for example, 1/15/2024 or January 15, 2024 — and click OK.

Now when you or anyone else clicks one of those cells, a small calendar icon appears on the right side of the cell (on Windows) or a calendar picker opens automatically (on Mac). Click the icon or the date you want, and Excel fills the cell. This works in Excel 2016 and later versions. If you are using Excel 2013 or older, the built-in picker does not exist, and you will need to use the macro method instead.

The built-in picker respects the date format you chose, so if you selected "January 15, 2024" format, that is how the date will appear in the cell after you pick it. This keeps your spreadsheet consistent without extra work.

Creating a custom calendar with a macro

If you need more control — for example, to highlight certain dates as unavailable, or to restrict which dates people can pick — you can build a calendar using a macro. A macro is a small program written in VBA that runs inside Excel. This method takes longer to set up but gives you flexibility the built-in picker does not have.

To create a macro, open your spreadsheet and press Alt+F11 on Windows (or Option+F11 on Mac) to open the VBA editor. Right-click on your workbook name in the left panel and select Insert Module. Paste this basic calendar code into the blank editor window:

Sub ShowCalendar() Dim dteDate As Date dteDate = InputBox("Enter a date (MM/DD/YYYY):") ActiveCell.Value = dteDate End Sub

This code opens a text box where the user types a date, and Excel puts that date in the selected cell. Save the file as a macro-enabled workbook (.xlsm, not .xlsx). Then assign this macro to a button: go to the Developer tab (if you do not see it, go to File > Options > Customize Ribbon and check the Developer box), click Insert, choose Button (Form Control), draw a button on your spreadsheet, and select your macro from the list. Now when someone clicks the button, the calendar prompt appears.

This basic version works, but for a true visual calendar that looks like the built-in picker, you would need to build a custom form in VBA — a more advanced task. Many people use pre-built calendar macros from Microsoft's template library or from Excel forums instead of writing one from scratch.

Formatting cells so the calendar works correctly

The calendar picker only appears if Excel recognizes the cell as a date cell. Format the entire column, not just one cell, so that anyone using the spreadsheet can click any cell in that column and get the calendar. Select the column by clicking the column header (the letter at the top), then right-click and choose Format Cells. Set the format to Date and pick your preferred layout.

If you have a header row — for example, a cell that says "Date of Birth" — do not format that cell as a date. Format only the data cells below it. This keeps your labels readable and prevents Excel from trying to convert text into a date.

Test the calendar by clicking a few cells in the formatted column. The calendar should appear when ready. If it does not, check that you are using Excel 2016 or later, and that the cell is actually formatted as a date (not as text that looks like a date).

Restricting dates to a specific range

Sometimes you want to prevent people from picking dates outside a certain range — for example, only dates in the current year, or only weekdays. The built-in date picker does not have this feature, so you need a macro to enforce it.

In the VBA editor, you can add a check that rejects dates outside your range. For example, this code prevents dates before today:

Sub CheckDate() If ActiveCell.Value < Date Then MsgBox "Please enter a date today or later." ActiveCell.Clear End If End Sub

You would run this macro after the user picks a date, either automatically (using an event handler) or by having them click a "Confirm" button. Setting this up requires some VBA knowledge. If you are not comfortable writing code, consider using data validation instead: select your date cells, go to the Data tab, click Data Validation, set the criteria to Date, choose Between, and enter your start and end dates. This does not show a calendar, but it does prevent invalid dates from being entered.

Troubleshooting when the calendar does not appear

If you formatted cells as dates but the calendar icon does not show up, first check your Excel version. The built-in picker requires Excel 2016 or later. If you have an older version, upgrade Excel or use the macro method instead.

On Windows, the calendar icon appears on the right edge of the cell when you click it — look for a small downward arrow. If you do not see it, the cell may be formatted as text instead of a date. Select the cell, go to Format Cells, and confirm the Category is set to Date, not Text. If the cell contains text that looks like a date (for example, you typed "1/15/2024" as plain text), Excel will not recognize it as a date. Delete the content and re-enter it, or use Find & Replace to convert the column.

On Mac, the calendar should open automatically when you click a date-formatted cell. If it does not, try clicking the cell again or pressing Enter. If the calendar still does not appear, check that your Mac version of Excel is 2016 or later by going to Excel > About Excel.

If you created a macro and the button does not work, make sure the file is saved as .xlsm (macro-enabled). If you save it as .xlsx, Excel will delete the macro. Also check that macros are enabled: go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select Enable All Macros (or choose a middle ground like "Disable All Macros Except Digitally Signed Ones" if you are concerned about security).

Testing your calendar before sharing the spreadsheet

Before you send your spreadsheet to other people, test the calendar yourself. Click several cells in your date column and confirm the calendar appears and works the way you expect. Pick a few different dates and check that they appear in the correct format in the cells.

If you used a macro, test it on a different computer if possible — sometimes macros behave differently depending on Excel settings or Windows updates. Ask a colleague to try using the spreadsheet and watch where they click or what they type. Often you will discover that people click in unexpected places or that the instructions are not clear enough.

If your spreadsheet will be used by people who are not familiar with Excel, add a note at the top or next to the date column explaining how to use the calendar. For example: "Click any date cell to open the calendar, then click the date you want." This small step prevents confusion and support requests.

Frequently Asked Questions

Does the built-in date picker work on both Windows and Mac?

Yes, but it looks different. On Windows, a small calendar icon appears on the right side of the cell when you click it. On Mac, a calendar picker opens automatically. Both versions work the same way — you click the date you want and it fills the cell. Both require Excel 2016 or later.

Can I make the calendar show only certain dates as available?

The built-in date picker does not have this feature. You would need to create a macro that checks the date after the user picks it and rejects dates outside your range. Alternatively, use data validation (Data > Data Validation > Date > Between) to prevent invalid dates, though this does not show a visual calendar.

What if someone opens my spreadsheet in an older version of Excel?

The built-in date picker will not appear in Excel 2013 or older. Users can still type dates manually, but they will not see the calendar. If you need to support older versions, use a macro instead, or ask users to upgrade to Excel 2016 or later.

Can I use a drop-down calendar in Google Sheets?

Google Sheets does not have a built-in date picker like Excel. You can use data validation to restrict a cell to dates, but there is no visual calendar. If you need a calendar picker, you would need to use a third-party add-on or convert the file to Excel.

Will the calendar work if I share the file as a PDF or send it to someone without Excel?

No. The calendar only works in Excel. If you convert the file to PDF or share it with someone who does not have Excel, the calendar will not function. The dates will still be there, but people will not be able to use the picker. Share the original Excel file (.xlsx or .xlsm) to preserve the calendar.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.