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 Google Sheets calendar template that would automatically update the calendar when you change the month or the year.
Monthly Google Sheets Calendar Template
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.
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 Google Sheets Calendar Template
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.
Simply click File -> Create a copy, to be able to make our own copy and customize your calendar however you like.
The Functions Used For This Template
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).
Using this Google Sheets Calendar Template will help you to stay organized throughout the year. Yet, if you learn how to build one on your own, you’ll also gain some pretty valuable knowledge toward becoming a spreadsheet master.
Make sure to check out our other templates for Google Sheets too.
You May Also Like the Following Google Sheets Tutorials:
- Calculate the Number of Days Between Two Dates in Google Sheets.
- Calculate Age in Google Sheets (using the Date of Birth).
- How to Get the Last Monday of the Month in Google Sheets.
- Leave Tracker Template in Google Sheets.
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.