Making a Calendar in Google Sheets: A Practical Guide

To make a calendar in Google Sheets, enter a date from the month you want in a cell such as B1, add weekday headings, and use a date formula to fill a six-row, seven-column grid. You can then format the dates, highlight weekends or today, and keep appointments in a separate list on the same sheet.

Choose a calendar layout

Before adding formulas, decide what the calendar needs to do. A basic monthly calendar is a useful overview for family plans, class schedules, work deadlines, or shared availability. A year-at-a-glance sheet is better for spotting holidays and long-range dates, while an appointment log is easier to sort and search when you have many events.

For a practical monthly sheet, reserve a small area above the calendar for the selected month and a separate table for event details. For example, use B1 for a date in the chosen month, A3:G3 for weekday names, and A4:G9 for the calendar grid. You can put an event table in I1:J, with dates in column I and event descriptions in column J. Keeping event details outside the date cells makes the calendar easier to update and lets you record more than one appointment on a day.

Simple overview: A date grid with color-coded weekends or important dates.
Planning calendar: A date grid plus a separate list of appointments, deadlines, or tasks.
Reusable template: A formula-driven month selector, so changing one date refreshes the grid.

Set up the month selector and weekday headings

Create a blank Google Sheet in a browser, or open an existing spreadsheet and add a worksheet for the calendar. In B1, type a real date that falls in the month you want, such as 6/15/2026. The day itself does not matter; the formulas will use its month and year. Make sure Sheets recognizes the entry as a date rather than plain text. You can check by selecting the cell and choosing Format > Number > Date.

Type the weekday names across A3:G3. For a Sunday-first calendar, enter Sun, Mon, Tue, Wed, Thu, Fri, Sat. For a Monday-first calendar, use Mon through Sun instead. The heading order and the formula that positions the first date must agree; otherwise, dates will appear under the wrong weekdays.

To show the selected month above the grid, put a formula in a heading cell such as A1. With the selected date in B1, enter =TEXT(B1,"mmmm yyyy"). It displays a label such as June 2026 and updates when B1 changes. The formula returns text, which is suitable for a heading but not for date calculations. Keep B1 as the actual date value.

Fill the calendar with dates

For a Sunday-first grid, click A4 and enter the formula below. It creates 42 consecutive dates: enough for six calendar weeks, including dates from the preceding or following month when necessary. The formula assumes B1 contains a date in the month being displayed and that the weekday headings in A3:G3 run from Sunday to Saturday.

=SEQUENCE(6,7,DATE(YEAR($B$1),MONTH($B$1),1)-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1),1),1)+1,1)

SEQUENCE fills the surrounding cells automatically, so do not type into A4:G9 while the formula is there. The first date is the Sunday on or before the first day of the selected month. For June 2026, for instance, the grid begins on Sunday, May 31, and June 1 appears under Monday. The remaining dates continue across rows in order.

For a Monday-first grid, use this version in A4 instead and set A3:G3 to Monday through Sunday. Its WEEKDAY setting treats Monday as the first day of the week:

=SEQUENCE(6,7,DATE(YEAR($B$1),MONTH($B$1),1)-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1),1),2)+1,1)

Both formulas display dates from adjacent months to keep the grid rectangular. That is normal, not a calculation error. If you prefer empty spaces instead, you can use a longer array formula with IF, but showing neighboring dates often makes a calendar easier to scan from week to week.

Format the dates so the calendar is readable

The formula creates real date values, not just day numbers. To show only the day of the month, select A4:G9 and choose Format > Number > Custom date and time. Choose or enter a day-only format such as d, then apply it. The cells still contain full dates, so date comparisons and conditional formatting continue to work. If the custom-format picker presents format components rather than a text field, select the day component and remove the month and year components.

Give the grid enough room to hold notes if you plan to type directly into the cells; otherwise, keep the cells compact and list event details separately. Use the toolbar to center the dates and weekday names, apply borders, and add a fill color to the heading row. Resize rows by dragging their boundaries, or select rows and use the right-click menu’s Resize rows option. A consistent visual hierarchy—month heading, weekday row, then dates—helps readers find information quickly.

You can dim the dates that belong to neighboring months. Select A4:G9, open Format > Conditional formatting, and choose Custom formula is. With A4 as the top-left cell of the selected range and the month selector in B1, enter =MONTH(A4)<>MONTH($B$1) as the custom formula, then choose a light text color or fill. The relative A4 reference adjusts across the selected grid, while the dollar signs keep the selector fixed. Because the formula checks only the month number, this is appropriate for a single displayed month; the grid itself is generated for that month and its neighbors.

Add appointments without breaking the date grid

A reliable approach is to maintain an event list beside or below the calendar rather than replacing a date with an appointment description. Put each event’s date in I2:I and its name or details in J2:J, with headers such as Date and Event in row 1. For example, I2 might contain June 10, 2026, and J2 might contain “Project review.” Format column I as dates using Format > Number > Date.

To flag calendar days that have at least one event, select A4:G9 and add another rule under Format > Conditional formatting. Choose Custom formula is and use the following expression. It assumes event dates are in I2:I, the calendar starts at A4, and the rule applies to A4:G9:

=COUNTIF($I$2:$I,A4)>0

Select a fill color that stands out without obscuring the day number. This rule marks a date if it appears anywhere in the event-date column. It does not display event names inside the calendar; it simply signals that the date has an entry in the list. A separate event table is useful when several appointments share one day, since one calendar cell cannot conveniently show a long list while preserving the grid.

For a small calendar, you can also type a brief note directly in a date cell, but doing so overwrites the formula-generated date in that cell. Avoid this if you want the grid to update cleanly when you change months. If you want notes displayed in the grid, consider using cell notes or comments for short context, or create a more advanced design that keeps date calculations and event text in separate cells.

Make the calendar reusable and easy to share

Once the layout works, change B1 to any date in another month and the formula will regenerate the dates. For instance, entering a date in July 2026 produces a July grid without rebuilding the worksheet. If you want separate printable pages for several months, duplicate the worksheet and change the selector in each copy. Give each tab a clear name, such as June 2026 or July 2026, so readers can navigate between them.

To keep the weekday headings and month label visible while scrolling, freeze the appropriate rows with View > Freeze. The precise number of rows to freeze depends on your layout; if the month label is in row 1 and the weekday names are in row 3, freezing through row 3 keeps both visible. For printing, use File > Print to check the preview, then adjust page orientation, scale, and margins so the full calendar fits. A monthly grid usually works well in landscape orientation, but preview the result because the best setting depends on column widths and any event table.

Google Sheets supports simultaneous editing and sharing through the Share button. Choose whether collaborators can view, comment, or edit, and share only with the people who need access. If the sheet contains private appointments or personal details, review the sharing permissions before sending a link. A calendar is not a substitute for a confidential scheduling system: anyone with sufficient access to the spreadsheet may be able to see its contents.

Common problems and how to fix them

The formula gives a parse error: Check that the formula begins with an equals sign, the parentheses are balanced, and the spreadsheet’s locale accepts commas as argument separators. Sheets locales can use different separators; check File > Settings if the formula syntax does not match your locale.
The formula will not fill the whole grid: SEQUENCE needs empty cells to spill into. Clear any existing content or merged cells in A4:G9, then enter the formula in A4 again.
Numbers appear instead of dates: Select the grid and choose Format > Number > Date, or apply the custom day-only format. The numbers may be valid date serial values displayed with a general number format.
Dates are under the wrong weekday: Match the weekday headings to the formula. The first formula is Sunday-first; the WEEKDAY option 2 formula is Monday-first.
The month label does not change: Confirm that B1 contains a recognized date and that the label formula refers to B1. A date typed as text may not behave as expected in date functions.
Events are not highlighted: Check that event dates in column I are actual dates, not text, and that the conditional-formatting rule applies to A4:G9 with A4 as its relative reference.

Google Sheets calendar versus other options

Google Sheets is a good fit when you want a customizable grid, a shared planning document, or a calendar combined with lists, budgets, or project information. Its date formulas make it possible to reuse one layout across months, and collaborators can edit the same file. It does require some setup, however, and a spreadsheet does not automatically provide all the reminders, invitation handling, and scheduling features of a dedicated calendar app.

Google Calendar is generally more suitable when the main need is timed appointments, invitations, recurring events, notifications, or viewing schedules across devices. Sheets can hold an event list and visually mark dates, but it will not behave like a full appointment service just because dates appear in a grid. You can use both: manage invitations and reminders in a calendar app, and use a spreadsheet for a printable overview, staffing plan, editorial schedule, or other structured information.

Excel can also create formula-driven calendar grids, but its interface and sharing setup differ. The instructions here use Google Sheets in a web browser, including its Format and View menus; they are not Excel ribbon instructions. If you open or export the file in another spreadsheet program, check that formulas, conditional formatting, date formats, and sharing behavior still meet your needs.

Frequently Asked Questions

Can Google Sheets make a calendar automatically?
Yes. A formula such as SEQUENCE can fill a monthly date grid from one selected date. You still need to set up the weekday headings and formatting, but changing the month selector updates the dates.

How do I make a Monday-start calendar?
Put Monday through Sunday across the heading row and use the Monday-first formula above, which calls WEEKDAY with the return-type argument 2. The order of headings and formula must match.

Can I make a calendar for the whole year?
Yes. You can create a separate monthly grid for each month on one or more worksheets, or build a yearly overview with a different layout. For a simple maintainable setup, duplicate a monthly sheet and change its date selector for each month.

Can more than one event go on a calendar date?
Yes, but it is usually clearer to keep each event as a separate row in an event list and use conditional formatting to mark the corresponding date. That avoids squeezing multiple descriptions into a small date cell.

Can I print a Google Sheets calendar?
Yes. Open File > Print and review the preview. Adjust orientation, scale, margins, and page settings so the grid fits; the right combination depends on the calendar’s dimensions and whether you include an event list.

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.