How to Insert a Calendar in Google Sheets (2 Effective Ways)

We often require a calendar in Google Sheets to organize our tasks. Having a calendar in Google Sheets can be of great use to track an in-house marketing campaign, categorize a client’s upcoming projects, or allocate an event calendar with pivotal stakeholders. In this article, I’ll demonstrate 2 effective ways of how to insert a calendar in Google Sheets. I’ll also show an easy method to insert a date picker in Google Sheets. The following is an overview of the required output.

An overview of the required output


A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following links. The spreadsheets contain an overview of the datasheet and an outline of how to insert a calendar.


2 Effective Ways to Insert a Calendar in Google Sheets

There are 2 feasible ways to insert a calendar in Google Sheets. One of these is to create a calendar manually and another one is to insert a calendar from the Google Sheets templates. Now, let’s start.


1. Creating Calendar Manually

It’s unfortunate to not have any default function or built-in macro to automate the process of inserting a calendar. However, you can follow the steps discussed below to minimize the tedious process and manually insert a calendar efficiently.

Steps:

  • Firstly, take a dataset like the following in Google Sheets.

Required data table to demonstrate how to insert a calendar in Google Sheets

  • Afterward, select the range B4:H4 and click on the Merge Cells feature.

Merging Cells of a row to Enter Month Name

  • Now, enter the text “January 2023” in the merged cell and format the merged cell with your preferred Fill Color, Text Color, and Text Format.

Entering Month Name and Formatting the Merged Cell

  • Now, we have to enter the weekday titles. For that, first, activate Cell B5 by double-clicking on it and then enter the word “Sunday”. Here, we are following the American style. You can follow your preferred style.

Entering the First Week day

  • Now, hover your mouse pointer above the bottom-right corner of the selected cell. The Fill Handle icon will be visible at this point.
  • Use the Fill Handle icon to replenish other cells of Row 5.

Using Fill Handle icon to replenish other weekdays

  • Thus, we have all the weekday titles.

List of weekdays after using Fill Handle tool

  • Now, format the range B5:H5 with your preferred Fill Color, Text Color, and Text Format.

Formatting the cells of weekdays

  • Consequently, we have to insert the dates now. For the starting date, first, double-click on the merged Cell B4:H4.
  • A calendar for the January month of the year 2023 will pop up. As we can see the 1st of January is on Sunday, we’ll enter this date in the column of Sunday.

Checking the first date of the month

  • Hence, select Cell B6 and enter the date “1/1/2023”. The date format here is in American style i.e. Month/Day/Year.

How to enter the first date of the month to insert a calendar in Google Sheets

  • Again, use the Fill Handle icon to replenish the dates in other cells of Row 6.

Using fill handle icon to replenish the remaining dates of a week

  • Now, we have dates for the first week of January month. Now, we’ll keep a row empty because we might want to insert keynotes for a day there.

  • Therefore, select Cell B8 and enter the date 1/8/2023.

Inserting date to a new row

  • Use the Fill Handle icon to replenish other cells of Row 8.

Replenishing other dates for the second row using Fill Handle tool

  • Continue this process for the rest of the days of January month.

Completed calendar for the first month

  • Now, some of us might want to show only the “Day” instead of the “Month/Day/Year” format since the month name and year number are mentioned in the topmost cell of the data table.
  • Therefore, to show only the Day information despite keeping the date formatting, go to the Format ribbon and from the appeared options, first, select the Number option and then click on the Custom Date and Time feature.

Selecting Custom date and time feature from the Number option of Format ribbon

  • A pop-up window like the following with appear.

Pop-up window to customize the time and date format

  • Now, delete the Month and Year Afterward, click on the Apply command and close the window.

Customizing the date by keeping only Day information

  • As you can see, the dates are formatted with the required custom format. Now, to copy the format to other dates, we’ll use the Paint Format feature.
  • While selecting the range B6:H6, click on the Paint Format feature.

A row in calendar with the newly customized date format

  • Now, select the range B8:H8 to paste the copied format.

Using Paint Format feature to copy the format to other rows

  • Continue this process for other rows as well. The final output for the January month calendar looks like the following.
  • Also, not that the final date of January 2023 is Tuesday. Therefore, the first date of February 2023 must be Wednesday.

The Calendar of the month of January 2023

  • Now, rename the Worksheet to January, and consequently, right-click on the mouse to open a list of options like below. Select the “Duplicate” command from the list.

creating a duplicate sheet for the next month calendar

  • A copy of the first worksheet has been created. This will reduce the amount of work for formatting the calendar for February month.

Duplicated Worksheet

  • Now, rename the month to February 2023.
  • Afterward, clear all the values and then, insert the first date of February, e. 2/1/2023 in Cell C6 (column of Wednesday).

Insert the first date of February 2023 Calendar in Google Sheets

  • Now, follow the steps used for January month to replenish the remaining cells for February 2023.

February month calendar created with the same steps as January month

  • And lastly, copy these methods for creating a calendar for the remaining months of 2023.

A view at the manually completed annual calendar

Read More: How to Insert Multiple Rows in Google Sheets (4 Ways)


2. Inserting from Google Sheets Template Gallery

If creating a calendar manually seems too tedious, you can insert a calendar from existing Google Sheets templates. Follow these simple steps to insert a calendar from Google Sheets templates.

Steps:

  • First, open Google Drive from your browser.
  • Afterward, click on the New command.

Opening Google Drive for demonstrating how to insert a calendar from Google Sheets template

  • From the appeared list, first, select Google Sheets and then click on From a Template command.

How to open Google Sheets Template Gallery Insert a Calendar

  • At this time, the Template Gallery should appear. Select the Annual Calendar template from Template Gallery.

Selecting the Annual Calendar template from Template Gallery

  • Consequently, a Google Sheets file with an annual calendar like the following will open.

The annual calendar in opened Google Sheets Template

  • Along with the annual calendar, individual monthly calendars are also present in the Google Sheets file.

Monthly Calendar in Google Sheets Template

  • Now, if you require to change the theme of any worksheet, then, go to the Format ribbon and select the Theme option.

Changing the theme of the Google Sheets Calendar Template

  • At this point, a sidebar like the following will appear. Select your preferred theme. I’ll select the Forest theme here.

Selecting the Forest theme from Sidebar

  • After selecting the Forest theme, the calendar now looks like the following.

The Google Sheets Template Calendar with Forest Theme

Read More: How to Insert Multiple Columns in Google Sheets (2 Quick Ways)


How to Insert a Date Picker in Google Sheets

Another frequently used tool in Google Sheets is a date picker. Here, we’ll discuss the process of inserting a date picker with Data Validation in Google Sheets. Let’s have a look at the following dataset. We have a list of tasks here. We want to insert date pickers for these tasks. Now, let’s start.

Dataset to demonstrate how to insert a date picker from Calendar in Google Sheets

Steps:

  • To start, select range C5:C8 first and then go to the Data ribbon.
  • Select the Data Validation feature from the appeared list of options.

Applying data validation

  • A sidebar like the following will appear. Click on the +Add Rule command.

Adding a validation rule

  • Consequently, the sidebar will change like the following. Click on the drop-down icon of the Dropdown Criteria.

Selecting data validation criteria

  • From the drop-down list, select the Is Valid Date The sidebar has now changed to the following format.
  • Now, select the Reject the Input option for invalid dates.
  • Lastly, click on Done.

Selecting Is Valid Date as validation rule and rejecting invalid inputs

  • At this time, double-click on Cell C5, and a date picker will pop up.
  • Use the “<” and “>” icons for moving to the previous and next month respectively.
  • Finally, pick your required date from the pop-up calendar.

Inserting a date from date picker in Google Sheets

  • If you require changing the formatting of the date, you can change that from the Custom Date and Time feature which is available in the Format ribbon under the Number option.

Customizing the date suing Custom time and date feature

  • Now, similarly, pick a date for the rest of the tasks. The final output looks like the following.

Final output after picking dates from the inserted date picker in Google Sheets

Read More: How to Insert a Drop-Down List in Google Sheets (2 Easy Ways)


Conclusion

This concludes our article to learn how to insert a calendar in Google Sheets. I hope the demonstrated examples were ideal for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.


Related Articles

Seemanto Saha

Seemanto Saha

Hello, I am Seemanto Saha, and working as a Technical Writer and Content Creator at OfficeWheel.com. I completed my B.Sc. in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. Being an enthusiast for solving analytical problems, I aim to leverage my research and analytical skills to create better content for everyone.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo