Calendar Template in Google Sheets (Monthly and Yearly)

Wondering how to make a calendar in google sheets?

You can easily create a simple static calendar template in Google Sheets. However, you would need to update it every time the month of the year changes.

To save you some time (and using the power of Google Sheets), I have already created a calendar template that would automatically update the calendar when you change the month or the year.

Monthly Calendar Template in Google Sheets

Below is the demo where the monthly calendar updates when the selection is changed. You can make the month selection from the drop-down list (in the case of months) in cell B2, or manually enter the year in cell B1.

Using Monthly Calendar template in Google Sheets

Click here to access the calendar template. Note that it will ask you to create a copy. You will need to be logged in to your Google account to do it. Once a copy is created, you can use the calendar.

Yearly Calendar Template in Google Sheets

Similarly to the monthly calendar, there is a yearly calendar in this template (in a separate tab).

In the yearly calendar, you can enter the year in cell B1, and the entire calendar would update for that year.

To make it more readable, the cells that contain a number are highlighted in a light orange color, while the ones that are empty don’t have any background color. This can easily be changed using conditional formatting.

Using Yearly Calendar in Google Sheets

Click here to access the calendar template.

Simply click File -> Create a copy, to be able to make our own copy and customize your calendar however you like.

For the spreadsheet enthusiasts, here is what goes into making these calendar templates in Google Sheets:

  • Date functions: It uses functions such as DAY, DATE, and WEEKDAY.
  • Lookup Function: It uses VLOOKUP to fetch the month’s number which is used in calculations in the calendar. Note that there is a hidden sheet where this function is used.
  • Conditional Formatting: It is used to color the cells that have a day number.
  • Custom Number Formatting: This template uses a smart number formatting technique, where month numbers are displayed as month names. In the yearly calendar template, while you see January, it’s actually 1 in the backend (select the cell and have a look at the formula bar).

You May Also Like the Following Google Sheets Tutorials:

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

Popular posts:

4 thoughts on “Calendar Template in Google Sheets (Monthly and Yearly)”

  1. when the 2020 yearly cell is changed to 2019 and ENTER is pressed, the calendar doesn’t refresh..

Comments are closed.