How to insert calendar in excel?
A calendar is a visual element to pick a date from it. In Microsoft Excel, users can add to their sheet to represent the specific month. It provides an internal function to add a basic calendar layout and customize it.
Excel offers a variety of calendar templates. You can use any of them from it. It is a special feature of Excel that allows the users to add the calendar in an Excel sheet. Additionally, Excel enables various other methods to insert a calendar in an Excel file.
We will define all the possible ways to insert the calendar in an Excel sheet. Follow this chapter till the end to learn all the methods so that you can choose any one of them.
Why add a calendar to spreadsheet?
The calendar is a basic need in Excel. If the users want, they can include it in their sheets. It is beneficial for the users themselves when they are trying to plan the upcoming activities.
Sometimes, a user often needs to enter the date. If you manually enter the date one by one in the Excel cells, it takes too much time. Manual entries for dates require to be entered in a proper format. By adding the calendar to the Excel sheet will resolve this issue, and the user does not need to worry about the format.
After inserting the calendar, you just need to click the dropdown calendar and select a date from it. By using the calendar inside the spreadsheet, the user does not need to about the date format.
Benefits of inserting calendar in Excel
Users get some benefits when you insert the calendar to an Excel spreadsheet, such as –
- The Excel users have to take care of the format when inserting date in an Excel sheet manually. Inserting the calendar into the sheet helps; the users need not to worry about the date format.
- The Excel users have to just select a date from the calendar to insert it into the Excel sheet.
- It also saves users time to insert several dates manually, one by one.
How to insert calendar to spreadsheet?
There are several methods to insert the calendar into an Excel sheet.
- Insert a calendar using online template
- Insert a calendar using Date Picker control
- Download the third-party tool
- Register Calendar control in Excel
Insert a calendar using online template
Excel offers various calendar templates that you can use in an Excel sheet. These calendar templates need to be downloaded, which you want to insert in Excel. You can search and choose any one of them. Remember that you require an internet connection to search these templates.
You do not need to go outside the Excel to search for the calendar templates.
You can insert the calendar into the Excel spreadsheet by following the simple process of inserting the calendar into Excel.
Step 1: Open an Excel sheet or create a new one. At the top left corner, click the File option.
Step 2: Next is to click the New button present on the left side panel of the Excel backscreen.
Step 3: On the Excel backscreen, you see a search box in which you type calendar and hit the Enter key to search the templates.
Step 4: Several calendar templates will appear there, such as Academic calendar, Seasonal calendar, etc. You can choose one from here to insert it into the current worksheet.
For example, We select the second template from here.
Step 5: Here, click the Create button to complete the insertion of the calendar to Excel sheet.
You see a left and a right arrow on the above screenshot. Using these arrows, you can change the template before creating it.
Step 6: A new workbook is created with your selected template as it is successfully inserted into the Excel sheet. This calendar has been inserted for January 2021.
This takes full screen of the Excel worksheet and till H15 cells. It was the only one-month calendar for the year 2021.
Step 7: If you want to change the month of the calendar, select the C1 cell to make it editable and enter the month name. Then, hit Enter to update the calendar for the modified month automatically.
Similarly, one can change the year by changing the targeted year in B1 cell.
Step 8: To change the year of the calendar, select the B1 cell to make it editable. Then, hit Enter to update the calendar for the inserted year automatically.
Insert a calendar using Date Picker control
Another way to insert the calendar in an Excel sheet is Date and time picker control. Inserting a calendar in MS Excel is easy, but several users are not aware with the feature of Date and time picker control as it is hidden.
This feature is not easily available to the users as it is hidden initially in Excel. You can find this feature inside the Developer tab. Using this, one can insert the dropdown calendar to select a date to insert in a cell.
We will show you a complete example for this. Perform the following steps to insert a calendar using Date Picker control.
- Enable the developer tab
- Insert calendar control
- Customize the inserted calendar control
- Link the calendar control to a cell
That’s it.
Now, go through with each step as we explain.
1. Enable the developer console
Usually, the developer console is hidden with the installation of MS Excel. The users have to manually add it to the Excel menu bar if they need it to perform its related operations. Hence, the first step is to enable the developer console.
Perform the steps to enable the developer tab in MS Excel:
Step 1: Right-click anywhere on the Excel ribbon and choose to Customize the ribbon from it.
Step 2: A window of Excel Options will open where mark the Developer option checkbox available in the right pane and then click OK.
Step 3: You can now see that the Developer tab is added to the Excel ribbon between the View and Help tab.
2. Insert a calendar control
A calendar in dropdown form is called as Date and Time Picker Control in Excel. It can be inserted into the Excel sheet easily. You need to follow a simple process. Go through with the following steps:
Step 1: In the Excel ribbon, navigate to the Developer tab where you see the control group.
Step 2: In the control group, click the Insert dropdown button and choose the More Control icon.
Step 3: A dialogue window will open for More Control, select Microsoft Date and Time Picker Control 6.0 (SP6) inside the ActiveX control, and then click OK.
At last, click on a cell wherever you want to insert it in the sheet.
Once the datepicker control is inserted, the EMBED formula will appear inside the formula bar. It indicates which type of control is embedded with the sheet.
“Tip: When you insert any of the ActiveX control, Excel automatically turns into the design mode that allows the users to modify the design and appearance of the recently added control.”
The most common change that a user normally wants is: to resize the inserted calendar control and link with the particular cells so that it can be fully visible and easy to select date.
If the Microsoft Date and Time Picker Control 6.0 (SP6) is not available
In case this option is not found in the More Control list like in the below screenshot –
You need to follow the instruction below at the end of this chapter.
3. Customize the calendar control
Once the calendar control is added to your sheet, you can now customize it. So, firstly move it to the desired location where you want to place it and get it fit in a cell.
- You can resize it by turning it to the design mode and then dragging the corners of the control to resize it.
- You can also modify and set the other properties for the calendar from the Properties option that resides with the Design Mode tab.
4. Link the calendar control to a cell
Till now, you have successfully added a dropdown calendar control to your Excel sheet. Sometimes, you may also want it to link to a particular cell. It becomes mandatory for the users if they want to use the dates in formula for some other operations.
Scenario
Suppose that you want to count the difference between two dates. So, you have inserted the start date and end date using date picker control. When you try to calculate the number of days between the inserted date using COUNTIF(), it returns 0.
The reason for returning 0 is that Excel does not recognize the dates inserted by using the date picker control that is not associated with the cell till now. You have to link the date picker control to cells to fix this issue.
You will find a Properties option for the inserted calendar control near the Design Mode in the Developer tab. Click on this Properties option.
A window will open in which you see Linked Cell property. Enter the cell reference next to the Linked Cell whom you want to link and click OK.
Now, calculate the difference between the two inserted dates. It will now return the correct result.
Download the third-party tool
Additionally, Excel users can also download the third-party tool or add-ins to insert the calendar in an Excel spreadsheet. It is the easiest way to insert a date and picker into Microsoft Excel.
You may find some tools paid and some are free. One can easily download them to MS Excel. If you also want to download the third-party tool, follow the steps below:
- Go to a search engine like Google and search for the third-party add-in/tool for date and picker control. Or Click here.
- Download a tool from the internet and install it on your system.
- Once the tool is installed, you can see that it has been added to MS Excel.
- You will find it in the Excel menu bar at the end; see in the below screenshot.
We have downloaded the third-party tool named Ablebit. - Navigate to the Ablebit Tool tab and click the Date Picker that will activate the Date picker for the sheet.
- You can now enter any date in the desired cell by selecting it from the calendar. Select a date from the calendar.
- When you click a cell to select date, a popup of the calendar will show you from which you can select the date.
These were the different methods to insert the calendar in an Excel sheet. You can choose one which you prefer.
Register calendar control in Excel
In earlier versions of Excel, Date and Picker control had come with the installation of Excel by default. But in Excel version 2016 and above, the users will have to download the Date and Picker control manually and register it in Excel. See how can do it:
Check if mscomct.ocx exists on your system
You can firstly check whether the Microsoft calendar control is available on your system or not. For this, you need to check mscomct.ocx file (date and time picker) in your system. To do this –
- Click the Start button of your system, and
- Type mscomct.ocx inside the search box. Then,
- Hit the enter key find it inside the system.
If you found this mscomct.ocx available in your system, you can skip the next step (download the date and time picker).
Download the date and time picker
If the date and time picker is not found on your system, you have to download it manually. You can download it by using the following link.
Tip: “This control work only on 32-bit version of Excel 2010 and above.”
A zip file will download. Now, extract the downloaded file. You can extract the zip file by right-clicking and selecting the Extract files option like in the below screenshot.
Copy the extracted file folder to the C:WindowsSystem64 folder.
- On Windows 32 bit – C:WindowsSystem32
- On Windows 64 bit – C:WindowsSystem64
The user must have administrative permission to copy the file to the system folder. But in case you still get the Destination Access Denied dialogue box, just click Continue on this.
Register calendar control on your system
The next step is to register the calendar control on your system after downloading and extracting the zip file. The calendar control must be downloaded as well as registered on the system.
Follow the steps to register the calendar control:
Search for the command prompt (CMD) in the start menu and run it as administrator.
In the command prompt, type one of the following commands according to your system configuration –
Our system is a 32-bit system. So, we will run the below command.
C:WindowsSystem64regsvr32.exe mscomct2.ocx
You will get the following message like below on successfully registering the mscomct2.ocx.
Now, restart the MS Excel once the mscomct2.ocx registered.
Then, navigate to the Developer tab that will now contain the Date and Time picker control and click the Insert and then More Controls.
You will see that – this time, Date and Time picker control there in the list.
