How to Add a Date Picker in Excel

What a date picker does and why you might want one

A date picker in Excel is a small calendar that drops down when you click a cell, letting you select a date instead of typing it. Excel does not include a built-in date picker the way some other programs do, but you can add one using a feature called Form Controls or by using a macro — a small program that runs inside Excel.

Date pickers are useful when you are building a spreadsheet that other people will use. They prevent typos (someone typing "13/32/2024" instead of a real date), they make data consistent across rows, and they are faster than typing if you are entering many dates. If you are the only person using the spreadsheet and you are comfortable typing dates, you probably do not need one.

Key Takeaways

Excel does not have a native date picker, but you can add one using Form Controls or a macro.
Form Controls work in all versions of Excel and do not require coding knowledge, but they are limited to picking from a range you set up.
A macro-based date picker looks and works like the date pickers in other programs, but requires enabling macros when you open the file.
The simplest approach for most people is to use data validation with a dropdown list of dates, which takes five minutes to set up.
If you share the spreadsheet with others, test it on their computer first — date pickers sometimes behave differently depending on Excel version and operating system.

Using Form Controls for a basic date picker

A Form Control is a button or box that you insert into a spreadsheet to control what goes into a cell. To add one, open your spreadsheet and go to the Developer tab. If you do not see it, right-click the ribbon at the top, choose Customize the Ribbon, check the box next to Developer, and click OK.

Once the Developer tab is visible, click Insert, then look for Spin Button under Form Controls. A spin button is a pair of up and down arrows that increment a number. Draw it on your spreadsheet by clicking and dragging. Right-click the spin button, choose Format Control, and set the Minimum Value to 1 and Maximum Value to 31 (for days of the month). Point the Cell Link to an empty cell where the number will appear. Repeat this for month and year, then use a formula to combine them into a real date.

This approach works but is clunky — you are asking someone to spin through 31 days, 12 months, and multiple years separately. It is useful only if you are picking from a small, predictable range.

Using data validation with a pre-built date list

The fastest way to get date-picking behavior without coding is to create a list of dates in a hidden column, then use data validation to turn it into a dropdown. First, create a new column off to the side (column Z works well). Type the dates you want people to be able to pick from, one per row, in order from earliest to latest.

Now go to the cell where you want the date picker to appear. Go to the Data tab, click Data Validation, and choose List from the dropdown. In the Source field, select the range of dates you just created (for example, Z1:Z100). Click OK. A small dropdown arrow will appear in that cell. When someone clicks it, they see your list of dates and can pick one.

To hide the date list so it does not clutter your spreadsheet, right-click the column header (Z), choose Hide, and the column disappears from view but still works. This method works in all versions of Excel and requires no macros or coding.

Building a macro-based date picker

If you want a calendar that looks like a real date picker — a small window with a month view and clickable days — you need a macro. A macro is a set of instructions written in a language called VBA (Visual Basic for Applications) that runs inside Excel.

The easiest way to get a macro date picker is to read a template or example from Microsoft's website or from Excel communities like Stack Overflow. Search for "Excel VBA date picker" and look for a file that includes the code already written. read it, open it in Excel, and enable macros when prompted (you will see a yellow bar asking permission).

Once macros are enabled, test the date picker by clicking the cell it is attached to. A calendar window should appear. You can click a date to fill the cell. If you want to use this macro in your own spreadsheet, you can copy the macro code from the template file into your file. This requires opening the Visual Basic Editor (press Alt+F11), but if you are not comfortable with code, ask someone with Excel experience to do it for you or use one of the simpler methods instead.

Deciding which method to use

Use the data validation dropdown if you have a fixed set of dates (for example, appointment slots on specific days, or a list of project important date). It is the fastest to set up, works everywhere, and requires no coding.

Use a Form Control spin button if you need to pick any date but your spreadsheet is straightforward and you do not mind a clunky interface. It works in all versions of Excel and does not require macros.

Use a macro-based date picker if you want a calendar that looks professional and you are comfortable enabling macros. This is the most user-friendly option, but it requires the person using the spreadsheet to allow macros to run, and it may not work the same way on Mac and Windows.

Testing before you share

Date pickers behave differently depending on whether you are using Excel on Windows or Mac, which version of Excel you have, and whether macros are enabled. Before you send a spreadsheet with a date picker to someone else, test it on their computer if possible.

If you used a macro, ask them to open the file and confirm the calendar appears and works. If you used data validation, make sure the dropdown appears and the dates show correctly. If something does not work, you may need to adjust the method — for example, switching from a macro to a data validation dropdown if the macro does not run on their Mac.

Common problems and fixes

The date picker does not appear. If you used a macro, macros may be disabled. Look for a yellow bar at the top of the spreadsheet that says "Enable Content" or "Enable Macros" and click it. If you used a Form Control, make sure you are in edit mode — double-click the cell or press F2.

The date shows as a number instead of a date. The cell is formatted as a number instead of a date. Right-click the cell, choose Format Cells, click the Number tab, select Date from the category list, and choose a date format. Click OK.

The dropdown list is too long to read. If you used data validation with a long list, you can make the dropdown show only the next 30 days instead of all dates. Edit the data validation source to reference only the dates you actually need, or ask the person using the spreadsheet to type the first few letters of the date to jump to it in the list.

Frequently Asked Questions

Can I use a date picker in Google Sheets?

Google Sheets does not have a built-in date picker either, but you can use data validation with a date range the same way you would in Excel. Go to Data > Data Validation, choose Date, and set the range. Google Sheets will show a calendar when someone clicks the cell.

What if I want the date picker to show only weekdays, not weekends?

If you are using data validation with a pre-built list, create your date list to include only the dates you want (skip Saturdays and Sundays). If you are using a macro, you would need to modify the code to exclude weekends, which requires VBA knowledge or help from someone who knows it.

Can I copy a spreadsheet with a date picker to another file?

Yes, but the method matters. If you used data validation, copy the cells and paste them into the new file — the validation rules come along. If you used a macro, you need to copy both the macro code and the cells it controls. The easiest way is to copy the entire sheet from the original file into the new one.

Does a date picker slow down my spreadsheet?

Data validation dropdowns have no impact on speed. Macros can slow things down slightly if the spreadsheet is very large or the macro runs on every change, but a straightforward date picker macro will not cause noticeable lag.

What if someone opens my spreadsheet and the date picker does not work?

Ask them what version of Excel they use and what operating system (Windows or Mac). If you used a macro, they may need to enable macros — look for a security warning at the top of the spreadsheet. If you used data validation, the dropdown should work everywhere, but the date format might look different depending on their regional settings.

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.