Making a weekly calendar in Excel, and when to stop

Building a week by week calendar in a spreadsheet is a reasonable thing to do, and most attempts fail in the same way. The first week gets typed out by hand, it looks correct, and then next week arrives and the whole thing gets typed again. A grid built properly takes about fifteen minutes once and never needs rebuilding, because every date in it derives from a single cell. What follows is that build, the print settings that stop it splitting across pages, and an honest account of where a spreadsheet stops being the right surface for a schedule.

What a spreadsheet week is genuinely good at

There are three jobs a spreadsheet does better than a calendar application, and it is worth being clear about them before deciding anything.

The first is printing. A paper week that goes on a wall or into a folder is still the fastest shared surface in many workplaces, and a spreadsheet gives complete control over what lands on the page. Column widths, row heights, which hours appear, which columns are hidden, all of it is adjustable in a way that a calendar print dialogue does not offer.

The second is columns that are not time. A week grid with an extra column for a job number, a client, a machine or an assigned person is trivial in a spreadsheet and awkward anywhere else. Anything that needs a total at the bottom, such as hours per project across the week, belongs here rather than in a calendar.

The third is that it needs no account and no permission. A file can be handed to someone who has no access to the internal calendar system, and it opens.

Those three jobs share a shape. They are about a fixed week that gets produced, read and filed. The moment the requirement becomes a week that changes during the week, and that other people need to see change, the advantages reverse.

Building the grid so it never gets rebuilt

The whole trick is that exactly one cell holds a date typed by a human. Everything else is calculated from it. Changing that one cell moves the entire sheet to a different week.

The date row

Put the Monday of the week in a single cell, for example B2. In the next column across, enter a formula that adds one day to the cell on its left, then drag it across to Sunday. Seven columns, one typed date, six formulas.

Format those cells to show the date the way it should read on paper. A custom number format keeps the underlying value a real date while displaying only the day number, which matters because the weekday and the weekend shading in the next steps both need the real value.

Add a row above for the weekday name and calculate it from the same cells rather than typing Monday through Sunday. Anything typed by hand is something that will be wrong after the sheet is reused.

The time column

Down the left edge, put the first hour of the working day in a cell and add a fixed increment to it in the cell below, then fill down. Half hour rows suit most schedules. Hour rows print smaller and hold less.

Freeze the panes so the date row and the time column stay visible while scrolling. On a week grid this is the difference between a sheet that gets used and one that gets abandoned after two days.

Marking weekends without doing it by hand

Conditional formatting handles this. Apply a rule across the whole grid that tests the weekday of the date row cell above, and shade the column when it returns Saturday or Sunday. The rule follows the dates, so changing the single date cell in B2 moves the shading to the correct columns automatically.

The same technique marks today's column in a different colour by comparing the date row against the current date. Public holidays need a small list of dates elsewhere on the sheet and a rule that checks membership of that list, which is the only part of this build that needs manual maintenance once a year.

Two habits that make the sheet unusable later

Merged cells are the first. Merging looks tidy on screen and breaks sorting, filling and most formulas that reference the grid. Centring across a selection achieves the same visual result without merging anything, and it leaves every cell addressable.

Dates stored as text are the second. A cell containing the characters 4/7 that a spreadsheet has decided is text will not add, will not compare and will not drive conditional formatting. Anything that should behave as a date has to be a real date value with a display format applied on top, which is why the build above types one date and calculates the rest.

Printing it on one page

A weekly grid that splits across two pages is worse than no grid at all, and the default settings will split it. Four adjustments fix it permanently.

Set the print area to the grid itself rather than letting the sheet decide. Switch the orientation to landscape, which suits seven columns far better than portrait. Set scaling to fit one page wide, leaving the height free so a long day can run onto a second page rather than shrinking the text to nothing. Then set the print titles so the date row repeats at the top of every printed page.

Check the result in print preview before printing rather than after. The single most common cause of a broken layout is a column that is a few pixels too wide, pushing Sunday onto a page of its own, and preview shows it immediately.

If the sheet will be shared as a file rather than on paper, export to PDF instead. That preserves the layout for a recipient who does not have the same fonts, and it removes the risk of someone editing a cell in the copy and reporting the result as the schedule.

Where the spreadsheet week stops

A grid holds a plan. It does not hold a schedule, and the difference shows up in five places.

It does not notify anyone. A meeting written into a cell arrives with no reminder attached, so the sheet has to be looked at deliberately for it to work.

It cannot invite. There is no mechanism to send a request, collect an acceptance or see a decline, so the coordination happens in email alongside the sheet and the two disagree within days.

It does not detect conflicts. Two things typed into the same cell are simply two things in a cell. A calendar shows an overlap as an overlap, which is the single feature that most often prevents a double booking.

It does not sync. A file on a Mac is a file on a Mac. Anyone else is reading a copy, and the copy is correct only until the next change.

And it needs producing again. Even with the date cell trick, someone has to open the file, change the date and re export it every week. That is a small task that never ends, which is a fair description of most administrative debt.

None of that makes the grid wrong. It makes it a document rather than a system, and documents are the correct tool for things that get printed and filed.

Getting real events in and out

The natural next question is whether the events already in a calendar account can be poured into the grid, or the grid poured into a calendar. Both directions are possible and both have rules worth knowing before spending an afternoon on them.

Going out of a calendar, an export produces an ICS file or a CSV file that opens in a spreadsheet, one row per event. That is a usable starting point for a grid, though the layout work still has to be done.

Going in, the import path has strict formatting. Only the first two headers are required, Subject and Start Date, and everything else is optional. The headers must be in English regardless of the interface language, which catches out anyone building the file in a localised spreadsheet. File size is capped.

This usually happens when the file is too big. Google Calendar works with files that are one megabyte (1MB) or smaller. Source: support.google.com

One behaviour surprises people who build weekly grids specifically. A repeating event imported from a CSV file does not arrive as a repeating event. It lands as a series of separate one time events, which then have to be edited one by one if the pattern changes. Anything genuinely recurring should be created as a recurring event in the calendar rather than imported as rows.

It is also worth knowing that the calendar itself is not something that gets installed alongside the spreadsheet.

Tip: While you can't download and install Calendar on your computer, you can use it offline. Source: support.google.com

Offline use in the browser is limited rather than full, and creating or editing events is not part of it. A desktop application that keeps a local copy is a separate piece of software, which is the choice described in the next section.

Choosing between three surfaces

The decision is not spreadsheet against calendar in general. It is which of three surfaces suits a given task.

Surface	Best at	Weak at	Weekly effort
Spreadsheet grid	Printing, custom columns, totals	Reminders, invitations, conflicts	Rebuild or re export each week
Calendar app week view	Changes during the week, notifications, invitations	Extra columns, printed layout control	None
Printed template	Handing to someone with no computer	Everything else	Print each week

Many working weeks need two of these rather than one. The live schedule sits in a calendar, and the spreadsheet is generated from it when a printed or annotated version is required. That order is the right way round. Producing the spreadsheet first and treating the calendar as a copy guarantees they diverge, because changes arrive through invitations rather than through the file.

For anyone whose reason for reaching for a spreadsheet was that entering events into a calendar felt slower than typing into cells, that specific complaint has a specific answer, since a line of ordinary text can become an event without opening a form. How that works on a Mac is described in Entering events.

What to change first

Build the grid once with the single date cell and the conditional formatting, then stop building. Use it for the printed week and nothing else. Keep the live schedule where reminders and invitations work, and generate the sheet from it when paper is needed. If the reason for choosing a spreadsheet in the first place was speed of entry rather than printing, look at how a Mac calendar handles typed input before rebuilding the grid again, which Caltimate sets out in plain terms.