Creating a Calendar in Google Sheets: Steps and Tips

To create a calendar in Google Sheets, open a blank spreadsheet, set up weekday headings and a date grid, then use a date formula to fill the days automatically. You can also start from a calendar template in the Sheets Template gallery; either way, format the dates, add event details, and share the sheet with the people who need it.

Choose a template or build your own

A template is the quickest option when you want a ready-made annual or monthly layout. From the Google Sheets home screen, open the Template gallery and look for a calendar design. Template names and availability can change, so preview a design before using it. A template may already include colors, date grids, or multiple month tabs, but check its year and date arrangement: an attractive layout can still be set up for a different year or week start.

Building a calendar yourself takes a few more minutes but gives you control over the layout and makes it easier to adapt the sheet for a team schedule, class plan, content calendar, or recurring appointments. You can create a new sheet from Sheets, name it, and design the grid you need. For example, a personal monthly calendar might need large date cells for notes, while a publication calendar may work better as a table with columns for due date, task, owner, and status.

Think about how the calendar will be used before choosing. A wall-calendar-style grid is easy to scan by week, but a list or table is often easier to sort, filter, and search. You can keep both views in one spreadsheet on separate tabs.

Set up the calendar layout

For a basic monthly calendar, reserve two cells for the year and month, then create seven columns for the days of the week. The example below uses Monday as the first day of the week, with the date grid in B5:H10. You can use another location, but update the formula references to match.

In A1, type Year; enter the year, such as 2026, in B1.
In A2, type Month; enter a month number from 1 to 12 in B2. For June, enter 6.
Enter Monday through Sunday in B4:H4.
Use B5:H10 for the six-week date grid. Six rows cover every possible monthly layout, including months that start late in the week and span six calendar rows.
Adjust column widths and row heights so there is room to read dates and add notes.

The month number is useful because it lets one formula-driven grid display different months when you change B2. You can make the month easier to select by choosing B2 and using Data > Data validation to create a dropdown with the numbers 1 through 12, or use month names in a separate dropdown and adapt the formula accordingly. Keeping the input simple reduces accidental edits to the date grid.

Fill the dates with a formula

With the year in B1, the month number in B2, weekday headings in B4:H4, and the first date cell at B5, enter this formula in B5 and fill it across and down through H10:

=DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),2)+1+(ROW()-ROW($B$5))*7+COLUMN()-COLUMN($B$5)

DATE constructs the first day of the selected month. WEEKDAY with a return type of 2 treats Monday as day 1, so the formula backs up to the Monday at the start of that calendar week. The row and column offsets then generate a continuous sequence of dates throughout the grid. With B1 set to 2026 and B2 set to 6, the first grid date is June 1, 2026, because June begins on a Monday. The six-week grid continues through July 12, including the dates that fall outside June.

To fill the grid, select B5, copy it, select B5:H10, and paste. In Google Sheets, you can also drag the small fill handle at the lower-right corner of the selected cell across and down. If your spreadsheet uses a locale with different formula separators, Sheets may expect semicolons instead of commas. The DATE and WEEKDAY functions are available in Google Sheets; the formula also uses ROW and COLUMN to calculate each cell’s position.

If you prefer to type dates rather than use a formula, enter the first date and use the fill handle to continue a series. That works for a one-off calendar, but it is less convenient when you need to change the month or year later. A formula-driven grid updates when the input cells change.

Format dates and distinguish months

Calendar cells may initially display a full date, including the year, which can make the grid look crowded. Select B5:H10 and choose Format > Number > Custom date and time to pick a shorter display, such as a day number. The underlying cells remain full dates even when the visible format shows only the day, which is important for sorting, comparisons, and conditional formatting.

To make dates from the previous or following month less prominent, select B5:H10 and open Format > Conditional formatting. Choose a custom formula rule and use:

=MONTH(B5)<>$B$2

This assumes the grid begins at B5 and the selected range is B5:H10. Choose a muted text color or light fill for dates outside the selected month. Because the reference to B5 is relative, Sheets evaluates the corresponding date in each cell; the reference to B2 stays fixed. If you use a different grid position or month-input cell, revise the references.

You can add a second conditional-formatting rule to make today stand out. For the same B5:H10 range, use the custom formula =B5=TODAY(), then choose a border or fill color. TODAY updates to the current date when the spreadsheet recalculates. If two rules apply to a cell, adjust their order or formatting so the result remains readable. Use a restrained palette and maintain sufficient contrast, especially if the calendar will be printed or viewed on a phone.

Freeze the weekday headings if the calendar is long or part of a larger schedule: select the appropriate row, then choose View > Freeze. For a single-month grid, freezing rows is optional, but it can help when you add notes or extra sections below the calendar.

Add events, notes, and useful details

For a simple personal calendar, type short event names beneath the date number in each cell. Turn on text wrapping with Format > Wrapping > Wrap so longer entries remain visible rather than running into nearby cells. Increase row height to make room for multiple events. You can add a time, location, or contact, but avoid cramming so much text into each box that the month becomes hard to scan.

For schedules with many entries, consider keeping event data in a separate tab as a table. Useful columns include Date, Start time, Event, Owner, and Status. A table keeps each event in its own row, making it easier to sort or filter by date, person, or progress. The visual calendar can then serve as a summary, while the event list holds fuller details. If you manually copy events into the grid, remember that the two views will not stay synchronized automatically.

Google Sheets does not automatically create or synchronize Google Calendar events just because dates or event names appear in a spreadsheet. A spreadsheet is a separate document. Moving information between the products may require manual entry or an approved integration or automation, and those options can have separate access requirements. If you only need reminders and invitations, Google Calendar may be a better primary tool; use Sheets when the schedule needs custom columns, analysis, or a shared planning table.

Share, print, and protect the calendar

Use the Share button to invite specific people or manage link access. Give collaborators Editor access only if they need to change the calendar; Viewer access is safer for people who only need to read it. A shared sheet can be edited by multiple people, so agree on conventions such as date format, event naming, and who is responsible for updates. Protecting a range can reduce accidental edits: select the cells and use Data > Protect sheets and ranges, then configure who can edit them. Protection is an editing control, not a substitute for careful sharing permissions.

Before printing, open File > Print and check the preview. A seven-column calendar may fit better in landscape orientation, while a notes-heavy version may need smaller margins or a larger paper size. Choose the current sheet or selected cells as appropriate, and check scaling so the date boxes are not reduced until text becomes unreadable. You can also export or download a copy in another file format from the File menu when someone needs an offline version, but a downloaded copy will not receive later edits made to the shared Google Sheet.

Be thoughtful about what you put in a widely shared calendar. Meeting names, travel plans, personal appointments, and contact details can reveal information people did not intend to distribute. Review the sharing settings and the contents before enabling access by link, particularly if the sheet includes private or workplace information.

Common problems and fixes

The dates appear as numbers: The cells may be formatted as plain numbers. Select the date grid and choose Format > Number > Date, or select a shorter custom date display.
The week starts on the wrong day: The example formula is designed for Monday-first weeks. For a Sunday-first calendar, change the weekday headings and adjust the start-date calculation; do not just relabel the headings, or dates will be misaligned.
Dates show the wrong month: Confirm that B1 contains a year and B2 contains a month number from 1 to 12. Also check that the formula references point to the cells you actually use.
The formula produces an error: Check for a missing parenthesis or a mistyped cell reference. Some locale settings use semicolons as argument separators instead of commas.
The calendar does not update: If dates are typed manually, changing the year or month inputs will not change them. Use a formula grid, or update the dates yourself.
Events are cut off: Widen columns, increase row height, or enable text wrapping. If a cell contains many events, move the detailed schedule to a separate list tab.

One common misconception is that a calendar grid is automatically a booking system. A shared spreadsheet can show availability, but it does not inherently prevent two people from entering conflicting appointments or send calendar invitations. For bookings that need automatic conflict checks, notifications, or attendee responses, use a dedicated calendar or scheduling service.

When Sheets is a good fit

Google Sheets is useful when a calendar needs to sit alongside structured information: for example, a school activity planner with columns for class and location, an editorial calendar with draft status and writer, or a volunteer rota with shift assignments. Its spreadsheet format makes custom fields, calculations, sorting, and collaboration straightforward. A formula-based calendar is also easy to duplicate for a new month or adapt for a project timeline.

A dedicated calendar app is usually more suitable when the main needs are invitations, automatic reminders, recurring events, or a reliable phone notification. A spreadsheet can be customized more freely, but users must maintain it and understand its sharing permissions. Excel can also create calendar layouts and uses many of the same date functions, but its menus and collaboration environment differ; the steps and menu paths here are for Google Sheets in a web browser. Choose the tool based on how people will use the schedule, rather than on appearance alone.

Frequently Asked Questions

Does Google Sheets have a calendar template?
Yes. Open the Template gallery from the Google Sheets home screen and look for a calendar template. Designs and names can change, so inspect the dates, year, and week-start day before relying on a template.

How do I make a calendar that updates when I change the month?
Put the year and month number in separate input cells and use a date formula in the first grid cell, such as the formula above. Fill the formula across and down, then change the input cells to display a different month.

Can I make Monday the first day of the week?
Yes. The example formula uses WEEKDAY with 2 as its second argument, which numbers Monday as the first weekday. Put Monday in the first heading cell and keep the formula’s Monday-based calculation.

Can a Google Sheets calendar sync with Google Calendar?
Not automatically just because the two contain similar dates or event names. They are separate products. You can transfer information manually or use an appropriate integration, but check its permissions and behavior before depending on it.

How do I make the calendar show only the day number?
Select the date cells and choose Format > Number > Custom date and time. Pick a day-only display. The cell still contains a complete date, so date-based formulas and sorting can continue to work.

Can several people edit the same calendar?
Yes. Share the spreadsheet and assign Editor access to people who should make changes, or Viewer access to people who only need to see it. Set clear editing conventions and review access regularly.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.