Insert Drop Down Calendar in Excel Without Date Picker

Get FREE Advanced Excel Exercises with Solutions!

We need to use the calendar in Excel on daily basis. But for every reason, using a date picker calculator can be quite a hassle. This type of customized calendar is quite handy. In the below article, we will show how you can insert a drop-down calendar in Excel without the date picker.

Download Practice Workbook

Download this practice workbook below.

Step-by-Step Procedure to Insert Drop Down Calendar in Excel Without Date Picker

In a detailed step-by-step procedure, we showed below how you can insert the calendar without the date picker in Excel. For better functionality, try to use the Excel 365 version for this method.

Step 1: Prepare Calendar Layout

Before we delve into creating the schedule, it is imperative that we create the outline of our calendar first, in which we are going to implement the formulas.

  • Firstly, we need to prepare the layout of the outline of the calendar.
  • To make it dynamic, we need to place the date and month on the sheet.
  • The date and the month should be dynamic to today’s date.
  • Our calendar will follow the weekdays starting from the Monday format.
  • We also have all the month’s names in the range of cell J4:K17. This name with serial will be helpful in the next steps.

Add Drop Down List of Months to insert drop down calendar in excel without date picker

Read More: How to Create a Date Picker in Excel Using VBA (with Easy Steps)

Step 2: Insert Drop Down List of Months

After we finalized our outline, we can add a drop-down list of months, which will make our month selection much easier.

  • In order to add a drop-down list, select cell H5.
  • Then go to Data > Data Tools > Data Validation > Data Validation.

Add Drop Down List of Months to insert drop down calendar in excel without date picker

  • In the Data Validation window, go to the Settings tab.
  • Select List in the drop down to allow list.
  • And select the range of cells J5:J16 in the source range box.
  • Press OK after this.

Data validation option to add drop down list

  • Now we can see the month names in cell H5 in the form of a drop-down list.

drop down menu list added by the data validation

Step 3: Formulize Calendar Outline

After we get the outline, it is time to formulate the calendar to make it dynamic. The functions like TODAY, MONTH, DATE, DATEVALUE, and WEEKDAY to achieve this.

  • As we have the structure of the calendar plotted in the worksheet, we formulate the calendar to make it more dynamic.
  • Next to extract today’s date in the dataset, select cell D4, and enter the following formula:


Formulize Calendar Outline to insert drop down calendar in excel without date picker

  • Then select cell H4 and enter the following formula:


  • After clicking enter, we can see the month serial of the month mentioned in cell H4.

Month serial fix using the formula

  • Then enter 2023 in cell D5.
  • Then select cell B8 and enter the following formula:


  • After pressing Enter, you will see that first date appear at the cell B8.

Date of each month extracting

🔍 Formula Breakdown

  • DATE($D$5,$H$4,1)

⮚ This will return the date in the proper date format. The month is from cell I4, and the year mentioned is 2022. And the date is 1.

  • =DATE($D$5,$H$4,1)-WEEKDAY(DATE($D$5,$H$4,1),2)+1

⮚ This will subtract the weekday from the date value. It will make sure that Monday stays at the front of the calendar.

  • Then select cell C8 and enter the following formula:


  • After pressing Enter, you will see the date after the date mentioned in cell C8.

day count increased by one

  • Then drag the fill handle to cell H8 from cell B8.
  • Doing this will fill the range of cells with dates starting from the 29th of October to the 4th of September.

date of one row is now fixed

  • Then again, select cell B9 and enter the following formula:


  • After pressing Enter, the date after the date mentioned in cell B9.

date of first column cell is now estimated

  • Then drag the Fill Handle to cell B13.

date of whole first column is now fixed

  • Now repeat the same process for the rest of the cells.
  • Finally, we got the dates for all of the weekdays in a month.

unformatted date in the calendar

Read More: Make an Alternative to Datepicker in Excel (2 Easy Methods)

Step 4: Reformat Data Values

As we have the date values of the calendar, they are not in a good format. we need to re-format them into c correct conventional calendar format.

  • Then right-click on the mouse, and from the context menu, click on Format Cells.

formatting the unformatted dates

  • In the Format Cells window, go to the Number tab.
  • Then in the Custom options, select the Type field.
  • In the Type field, enter “dd” only.
  • Hence, this will allow the user to see only the day portion of the date.
  • Click OK after this.

Formatting cells

  • After that, our calendar will now have the dates in a double-digit format.
  • Moreover, we have the dates from the previous and next month.
  • But we want to avoid that.
  • To avoid that, we need to conditionally format the values.
  • For this, select the whole calendar and then go to the Home tab > Conditional Formatting > New Rule.

Condinational formatting using to insert drop down calendar in the workbook with date picker

  • In the Edit Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
  • Then enter the following formula:


  • After entering the formula, click on the Format button.

Condition setting on the conditional formatting window

  • In the Format Cells dialog box, click on the Fill tab.
  • Then in the Fill tab, choose the color white as the fill color.
  • Then switch to the Font tab and choose the Color white as the font color.
  • Click OK after this.

cell Format setting on conditional formatting

  • Hence, the dates from the other months are now omitted from the view.
  • Finally, now as we enter our desired year and month from the drop-down list, we get the calendar showing exact same weekdays of months and year.

Final drop-down calendar without date picker

Read More: [Solved!] Datepicker Not Showing in Excel


To sum it up, we showed in this article how we can insert the drop-down calendar in Excel without a date picker. For this problem, a macro-enabled workbook is available to download where you can practice these methods. Feel free to ask any questions or provide feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.

Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF