Calendar Template in Google Sheets (Monthly and Yearly)

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

To save you some time (and using the power of Google Sheets), I have created a 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 into 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 doesn’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. Note that it will ask you to create a copy. You will need to be logged into your Google account to do it. Once a copy is created, you can use the calendar.

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:

FREE EBOOK - GOOGLE SHEETS TIPS

10 Google Sheets Tips that will save you hours every week

Bar Sparkline in Google Sheets - rtl
Google Sheets

Sparkline in Google Sheets – The Only Guide You Need

Sparklines are miniature charts that you can create within a cell. These are quite useful when you’re creating a dashboard and want to quickly show a trend, seasonal increase or decrease, or outliers (maximum/minimum) visually. According

Google Sheets

How to Insert Timestamp in Google Sheets

Google Sheets is a great tool when you’re collaborating and working with people on the same sheet. It allows people to work in the sheet simultaneously. In this tutorial, you’ll learn various to insert timestamp in Google

Google Sheets Keyboard Shortcuts - Spreadsheets
Google Sheets

101 Google Sheets Keyboard Shortcuts

Google Sheets is a relatively new spreadsheet tool in the market. It has quickly gained a lot of fan following and I see a lot of people migrating from other spreadsheets applications to Google Sheets.

>