How to Create the Calendar in Excel

What this does

Three approaches, in increasing effort. Microsoft ships ready-made calendar templates under File ▸ New — search "calendar" and pick a yearly, monthly or academic layout. A formula-driven calendar is better when it has to be reusable: one cell holds the year, another the month, and every date cell derives from those, so changing the month redraws the grid. And a simple date list — one date per row down a column — is what you actually want for a schedule or a booking sheet, since it filters, sorts and pivots, which a grid layout does not. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “create the calendar in excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a layout choice that keeps the sheet readable and sortable, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

A reusable monthly grid: B1 = 2026, B2 = 8. In A4 put =DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),2)+1 to land on the Monday of the first week, then B4 = A4+1 filled across to G4, and A5 = G4+1 to start the next row. Format the range with the custom number format d so only the day number shows, and add a conditional-formatting rule =MONTH(A4)<>$B$2 with grey text to fade the leading and trailing days. Changing B2 to 9 redraws the whole month. A calendar built from formulas is reusable for every month and every year, while a typed one is thrown away in four weeks — and knowing when to use a date list instead saves rebuilding the whole thing once someone asks for a report. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Here is the takeaway for “create the calendar in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

Common mistakes

• Typing the dates by hand, which makes the calendar single-use and guarantees a wrong weekday somewhere.
• Using TODAY() inside the grid formulas, so the calendar shifts every day instead of staying on the chosen month.
• Building a grid when the real need is a schedule — a grid cannot be filtered, sorted or pivoted.
• Forgetting WEEKDAY's second argument; the default counts Sunday as day 1, which shifts a Monday-start calendar by one.
• Formatting the cells as text so the dates stop being real date serials.

Frequently asked questions