How To Add A Date Picker In Google Sheets

Kieran Dixon

Updated: June 4, 2021

Select the cell/s you want a date picker in.

In the main menu go to Data ➜ Data validation.

In the popup menu change the Criteria dropdowns to Date ➜ is valid date and then Save.

Double click on the selected cell/s to bring up the dropdown calendar.

This is a date picker (or dropdown calendar) in Google Sheets:

shows what a date picker or calendar dropdown looks like in google sheets

Here's how to insert or remove one:

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

STEP 1: Select the cell/s you want to have a date picker:

shows the selection of a single cell (B1) by clicking on it

STEP 2: Open the Data validation menu using either:

Data in the top menu ➜ Click the Data validation option
Right click and choose the Data validation option
One of these keyboard shortcuts (Windows only): Alt + A, V or Alt + D, L or Alt + D, V

shows the location of the data validation option in the main data menu

You will see the default options:

shows the default layout and options when the data validation menu is opened on a selection that has no existing rules

STEP 3: Fill out the data validation popup to create a calendar dropdown:

Cell range:

Skip this if you did STEP 1 correctly!

Enter the cell/s you want a date picker in by typing them in or clicking on the icon and selecting the cells directly in the sheet.

shows how to select a different cell range from the data validation menu by clicking on the window icon in the first text box

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.

Criteria:

In the criteria dropdown select Date:

In the dropdown that appears:

shows all of the options available when date is chosen as the relevant criteria in the data validation menu

Leave the selection as is valid date.

Any of the options in the second dropdown will insert a dropdown calendar.

is valid date verifies that the data entered is a date. The others do this and add rules about when the date falls.

If one of the others makes sense for you, you'll still get a date picker.

On invalid data:

You have two choices:

Show warning

This tells users that the data they entered doesn't match the data validation rule but still lets them enter it:

shows the small red and white warning message that appears when a data validation chooses to show a warning when invalid data is entered

This is risky as if other formulas and/or scripts rely on this data being a date, they could break.

My preference is to:

Reject input

When you choose this option users will not be able to enter anything but a date into the selected cell/s:

shows the default message when data is rejected because of a data validation rule

It's a bit of an abrupt user experience but it means that all of the formulas and scripts in your spreadsheet that rely on this data will work as you intended.

Appearance:

You can choose to show or not show validation help text.

I like to show the help text and, if the default isn't descriptive enough, write my own:

with the show validation help text checkbox ticked, you are free to enter any custom message you choose in the text box that appears

For an is valid date rule, the default text 'Enter a valid date' is probably good enough.

This is what it looks like when a user's input is rejected:

shows how the reject message changes from default text to the custom help text when entered

The default text is replaced with the validation help text.

When you're happy with your selection, click the 'Save' button:

shows a correctly completed data validation menu that will lead to a date picker being added to a selection

Now you can double click on the cell you've added the date picker to and it will appear:

shows how to make a date picker appear by double clicking on the cell after the data validation is applied

How To Remove A Date Picker In Google Sheets

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

STEP 1: Select the cell/s you want to have a date picker:

STEP 2: Open the Data validation menu using either:

Data in the top menu ➜ Click the Data validation option
Right click and choose the Data validation option
One of these keyboard shortcuts (Windows only): Alt + A, V or Alt + D, L or Alt + D, V

STEP 3: On the Data validation menu that appears click the 'Remove validation' button:

shows the location of the remove validation button in the data validation menu - it's in the bottom right hand corner

How To Add And Remove A Date Picker In The Google Sheets iPhone And iPad App

You can't add or remove a date picker using the iPhone and iPad apps for Google Sheets.

Worse than that - you can't even use them!

Even if you use another version of the app, on iPhone and iPad… nothing.

This is something that needs to be addressed because the functionality exists on Android:

How To Add A Date Picker In The Google Sheets Android App

STEP 1: Select the cell/s you want to have a date picker by touching them.

STEP 2: Open the Data validation menu by first tapping on the stacked dots:

shows location of the stacked dots icon you must press to access the data validation menu in the android app - it's in the top right corner

Then the Data validation option in the sidebar that appears:

shows location of the data validation option in the sidebar of the android app

You will now see the default Data validation menu:

shows the default layout and options when the data validation menu is opened from a selection that had no existing rules in the android app

STEP 3: Fill out the Data validation menu:

Cell range (on Android)

Skip this if you did STEP 1 correctly!

Type in the cell/s you want to add a date picker to:

cell range section of the data validation menu in the android app

Criteria (on Android)

criteria section of the data validation menu in the android app

From the dropdown, select the Date is valid option:

shows the desired criteria option, date is valid, in the criteria dropdown of the data validation menu in the android app

Any of the date-based options will add a dropdown calendar.

Date is valid verifies that the data entered is a date while the others also add rules about when the date falls.

If one of the others makes sense for you, you'll still get a date picker.

Appearance (on Android)

Toggle the option to show or not show validation help text.

appearance section of the data validation menu in the android app

If you show validation help text, you can customize what's shown:

shows the ability to enter custom validation help text in the android app

Your custom text will appear when a user taps on a cell:

shows custom validation help text being shown when a user selects a cell in the android app

Or if Reject input is chosen and they are not allowed to enter the data they've typed:

shows validation help text when a user input is rejected by the data validation rule in the android app

On invalid data

Choose between Show warning and Reject input.

Show warning

This option allows the user to enter invalid data but tells them when they do:

shows a warning message presented to a user when the data they enter doesn't pass a cell's data validation rule in the android app

This means users could enter data you haven't accounted for which might cause formulas or scripts in the sheet to break.

Think carefully about letting users input whatever they want.

Reject input

This will stop users from entering data that violates the chosen Criteria for the data validation rule.

This protects your formulas and scripts from errors and presents users with a default message (unless custom validation help text has been entered in the Appearance section):

shows the default reject message shown to users when validation help text isn't enabled

When you're happy with your chosen options, click the 'Save' button:

shows a correctly completed data validation menu that will result in a date picker being inserted into the sheet in the android app

You can double tap on the cell you've added the date picker to edit it and an extra calendar icon will appear above the keyboard:

Tap on that icon to access the newly created date picker:

shows what the actual date picker looks like in the android app

Here's a clip of the process from start to finish:

shows the process from start to finish of adding a date picker in the android app using the instructions outlined above