How To Make A Calendar In Google Sheets

In this tutorial, you will learn how to make a calendar in Google Sheets.

How To Make A Calendar In Google Sheets

Making calendars certainly isn’t the main reason people use Google Sheets, and it might seem counterintuitive when you already have access to Google Calendar and many other calendar applications.

However there are a number of reasons why making a calendar in Google Sheets can help you keep track of your or your work.

Adding Calendar Headers

Step 1

First we need to delete extra columns from the spreadsheet. Select columns H-Z, right click on the selection and choose “Delete Columns H – Z”

Step 2

Select row one, then click on the Merge Cells icon in the toolbar to merge row one into a single header

Step 3

In the merged first row, type the month and year. Eg, “January 2021”. Note that behind the scenes Google Sheets will consider this to be a date, as can be seen in the formula bar

Step 4

Format the header so it stands out. Centered, large font works well

Step 5

In cell A2, type “Monday”, then use the square drag handle in the corner of the cell to drag weekdays across the row

Adding Date Boxes To the First Row

Step 1

In call A3, type the following formula: “=EOMONTH(A1, -1) – WEEKDAY(A1, 3)”. This formula uses the date value stored in the header to calculate the date for the first calendar box

Step 2

Now that we have the start date set, we need to add one to the previous date for each date in the month. Go to cell B2 and type “=A3+1”

Step 3

Use the drag handle to drag the formula across all weekdays

Step 4

Select row four under the date row, right click on the row number and choose Resize Row

Step 5

Type in 150 and click Ok to resize the row

Repeat the process selecting all columns to resize columns to 150

Formatting Numbers

To format dates as numbers:

Step 1

Select the date row and open the Format menu, then select the Number submenu

Step 2

Scroll down to the bottom of the submenu and select the More Formats submenu

From the More Formats submenu, choose More Date and Time Formats

Step 3

Click on the input field to the left of the Apply button and use backspace to clear it

Step 4

Click on the dropdown arrow in the right of the input field and select Day from the menu

Step 5

Click Apply

Adding Additional Rows

To add more weeks:

Step 1

Select rows 3-4 and copy them by hitting Ctrl + c on Windows or Command + c on Mac

Step 2

Click on cell A5 and paste by hitting Ctrl + v on Windows or Command + v on Mac

Update the formula in cell A5 to “=G3+1”. This adds one to the last day of the previous week:

Step 3

Copy the two rows you just pasted and paste into the next two rows to add an additional week

Repeat #4 until until you complete the month

Duplicating The Month Tab

Step 1

Click on the menu arrow beside the January tab name and select Duplicate

Step 2

Give the new tab a month name

In the new tab, double click on the header to bring up the date picker and choose the first day of the month

Step 3

All dates for that month will update accordingly. If the month requires an additional week row, simply copy paste the last week into the first unused row

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to make a calendar in Google Sheets. Want more? Check out all the Google Sheets Tutorials.