How to Make a Calendar in Google Sheets From Scratch

A content calendar, a booking planner, a rota for the team: plenty of small businesses run on a calendar that lives in a spreadsheet, because you can colour it, filter it and share it however you like. Google Calendar is brilliant for appointments, but a sheet is better when you want to plan a whole month at a glance.

One thing before we start. Google's help pages explain the building blocks, such as templates, dates and formatting, but I couldn't find an official article called make a calendar in Sheets. So I'll show you the sturdy way to build one by hand from those documented parts, and where to look for a ready-made one.

Starting a Google Sheets calendar from the template gallery

If you'd rather not build from scratch, begin with Google's gallery. According to Google's help, on a computer you open the Google Docs, Sheets, Slides or Forms home screen and use the Template Gallery. Click a template and a copy opens, so the original stays untouched.

Google also says some templates are marked Add-on, and those may need an add-on installed first. I'd skip any that ask for one, because a simple calendar doesn't need it. I can't promise a calendar layout is in the gallery on any given day, so look through the options and use the one that fits. If nothing suits, build your own below.

Building a month grid in Google Sheets by hand

A month calendar is just seven columns, one for each day of the week, with a row per week. Here's a clean way to set one up:

1. Open a new blank spreadsheet in Google Sheets.
2. In the first row, type the month and year as a heading, for example in cell A1.
3. In the second row, type the days of the week across the sheet, starting wherever your week begins.
4. Below each weekday, type the date numbers for that month, leaving blanks where the month starts and ends.
5. Select the whole grid and add borders from the toolbar so each day looks like a box.
6. Make the date cells larger by dragging the row heights, so there's room for notes.

Typing the numbers works fine for one month. If you want a calendar you can reuse, use formulas, as in the next section.

Using formulas so your Google Sheets calendar fills itself in

Google documents the DATE function, which builds a date from a year, month and day: DATE(year, month, day). Put the first day of your month in one cell with that function, then let the next cells add one day each.

=DATE(year, month, day) in the first date cell, then =A2+1 in the cell beside it (swap A2 for whichever cell holds the previous date).

Google also notes that months or days outside the normal range roll over rather than breaking, which is why a formula can keep counting forward into the following month. Format the cells with a short date style from the Format menu, or a custom style showing only the day number, so the grid looks like a calendar rather than a list of long dates.

To change months, you edit the year and month inside the DATE function, and every date after it updates. It's a good way to make twelve months from one template: copy the tab and change one cell.

Adding dropdowns to your Google Sheets calendar

A calendar for a team gets messier fast. Dropdowns keep entries consistent, and Google's help gives several ways to add one: Data, then Data validation, then Add rule; or Insert, then Dropdown; or right-click a cell and choose Dropdown. In the rules panel you choose Dropdown to type your own values, or Dropdown from a range to pull them from cells.

For a content calendar you might create values such as Idea, Drafted, Scheduled and Live. For a rota, use names. Google's help doesn't cover a date picker in that article, so I won't promise one here.

Colour-coding days in a Google Sheets calendar

Colour is what makes a calendar scannable. Google says conditional formatting can change the text or background colour of cells that meet conditions you set, so a cell that says Live could turn green and one that says Waiting could turn amber.

Select the calendar area, open Format, then Conditional formatting, and set a rule for each status. Keep to a few colours and add a small key beside the grid so everyone knows what they mean.

Using timeline view instead of a month grid

If what you're planning has a start and an end, like a campaign or a project, a grid can be awkward. Google's help describes timeline view, which you reach from Insert, then Timeline, and which shows rows of data as cards across a time scale using start and end dates. It needs at least one column in date format.

Google lists it as available in particular Google Workspace editions, so you may not see it on every account. For a step-by-step on the plan-style version, see the Gantt page on my site once it's live, or stick with the grid.

Sharing and tidying your Google Sheets calendar

Once the calendar looks right, tidy it before you share it. If you want a heading to span several columns, my guide to merging cells in Google Sheets covers it, and wrapping text in Google Sheets stops long notes from running off the edge of a day box.

Remember a sheet doesn't send reminders by itself. If you need a calendar that notifies people, my write-up on adding events to Google Calendar with Gemini covers the other side of the coin. Many people use both: Google Calendar for alerts, a sheet for the big picture.

If your Google Sheets calendar looks wrong

1. Dates show as long numbers: change the cell format to a date style from the Format menu.
2. A formula shows an error: check you're using numbers inside DATE, because Google says text inputs return a #VALUE! error.
3. The grid won't line up: widen the columns evenly and set borders again.
4. Timeline option is missing: it depends on your Google Workspace edition.
5. Still stuck: start again from a gallery template and adjust it.

Frequently asked questions

Does Google Sheets have a calendar template?

Google has a Template Gallery on the Docs, Sheets, Slides and Forms home screens, and clicking a template opens a copy. I couldn't confirm which calendar layouts it offers, so browse the gallery to see what's there.

How do I make a calendar from scratch in Google Sheets?

Type the month as a heading, put the days of the week across a row, fill in the dates beneath, then add borders and make the cells large enough for notes. You can use the DATE function so the dates fill themselves in.

Can I make my Google Sheets calendar update automatically?

Partly. Put a start date in with DATE(year, month, day) and add one day to each following cell, and the dates update when you change the first one. Events still have to be typed in.

Can I colour-code events in Google Sheets?

Yes. Google's conditional formatting changes the text or background colour of cells that meet conditions you set, such as a status word.

Is a Google Sheets calendar the same as Google Calendar?

No. A sheet is a grid you design yourself and doesn't send reminders. Google Calendar handles events and notifications.