What do you use spreadsheets for?
If you’re anything like me, you likely use them for data collection, to track campaign or blog post analytics, or to keep track of weekly assignments.
But have you ever thought about using spreadsheets to make a calendar?
And, more specifically, have you ever thought about using Google Sheets to make a functional calendar?
If you often work on campaigns for a few different clients, creating individual calendars in Google Sheets to share with clients and track progress could be uniquely useful for ensuring the client understands when certain content will go live. Alternatively, perhaps you need to create an internal Google Sheet calendar for your team to keep track of upcoming projects.
Making a calendar in a tool that’s commonly used for spreadsheets sounds a little intimidating, but don’t worry, the process is actually pretty intuitive. And with the help of some formulas and tips, you can easily make a functional calendar that you can sync your schedule with.
Below, we’ll go over how to make a calendar in Google Sheets and include some tips that’ll help you elevate the design. At the end, your calendar will look something like this:
So start a new spreadsheet and get ready to create your very own calendar. 📅
1. Open a new spreadsheet and choose your month.
First, open a new spreadsheet. Then, delete columns H — Z. They won’t be needed and make grouped functions easier.
Then, choose your month. For this example, I decided to do January 2020, so I filled that into the first cell. What’s great about Google Sheets is that it automatically recognizes dates, so typing in a month, followed by the year in YYYY format will tell Google that you’re going to be working with dates.
2. Begin to format your calendar.
I took this step to format my calendar slightly. I selected the text, January 2020, in Column A, Row 1. I highlighted seven columns (A-G), and clicked Merge to make that cell span across the entire column. You can find this button to the right of the Fill tool.
Here, I also center-aligned my text using the tool next to Merge. Then, I increased the font size and bolded the month.
3. Use a formula to fill in the days of the week.
Moving on, I began to fill in the days of the week in each column (A-G). You can do this manually but I decided to use a formula. Sheets has a function that lets you type in formulas to complete certain actions at once.
To fill in days of the week, in the cell where you want your first weekday to be, type: =TEXT(1, DDDD”). What this tells Google is that your number will be replaced by a date or time and the format you’re using is weekdays.
Highlight the number 1 in the formula and replace it with: (COLUMN(), DDDD”). Then, press enter and select your first day. You’re going to copy the formula in Sunday’s cell, drag the selector to the end of your row, (A-G), and press enter.
Pressing enter should automatically fill in the rest of the week. Remember, if this doesn’t work for you, you can always fill in the days manually.
4. Use a formula to fill in the numbers.
So at this point, you have your days of the week. Now we’re going to fill in the numerical values. Before this step, I took the time to section off my top column with color.
For the numerical values, write an “if/then” statement in your first column to determine the day of the week. Jan 2020 started on the 4th day in my column, or Wednesday.
You’re going to type: =IF WEEKDAY($A1)=COLUMN(),1, “”
What you’re telling Google with this formula is, “If the weekday for the first day of the month starts on a (WEEKDAY), and is also equal to (=) the column (COLUMN) that we’re in, then display a number one (1). ”
Next, you’re going to copy that blank Sunday spot, select the entire row, and paste. You should see a “1” in the correct column.
Because January 2020 doesn’t start on a Thursday, the statement is incorrect, so we’ll have to change it for the next day. I did this manually for Thurs-Sat.
5. Copy and paste to fill the spaces.
This step will fill in the rest of the numbers for your calendar using the formula. Remember, you can always format this by hand.
Note: In this step, I filled in the calendar numbers every other row to help with my formatting later.
The day, (=DAY), is your first column (A1), plus the value in your current column. For me, that’s (A5), so the formula looks like this: =DAY(A1=A5)
Then copy that value, drag the selector to the rest of the row, and paste. The rest of your rows should read consecutively. For example, Tuesday, January 7th’s value is =Day($A1+B5).
For the next row, the process repeats. But for the 12th, I had to change my values because the row and column changed.
So instead of =DAY($A1+B5), I changed the “B” to “G” to make it true that I’m adding one to the previous value, or going from the 11th to the 12th. Then, like washing your hair, you rinse and repeat. So for example, Wednesday, January 15th reads as =DAY($A1+C6).
To fill in the rest of the calendar, the process repeats itself. Make sure your values add to the last day. To fill them in all at once, copy the formula, drag to select the row, and press enter.
6. Reformat your calendar if necessary.
Everything is starting to look like a calendar, right? At this stage, I reformatted things to clean up the look of my calendar a little. For instance, I selected the row for the days of the week and highlighted them to make it stand out.
I also added a border, just to spruce up the functionality of my calendar.
Because I left space in between my numbered days, I could easily add tiny boxes, or spaces, into my calendar. To do this, I simply dragged the number of my row down an extra space to expand that cell.
I also positioned all my number values to the top right using the vertical alignment tool. This is the same tool that centered our month.
7. Add design elements to professionalize the look.
Finally, you can add in some fun design elements to personalize the look and feel of your calendar. If it’s for a client or upcoming project, you’ll want to incorporate necessary launch days here.
For this step, I added in a few fun images, hypothetical calendar events, and played with font sizes.
And then, you’re done!
It’s handy to use Sheets because you can open your calendar to the right of your sheet. You can also keep track of your schedule in a place that’s separate from your phone. If you’re handy with Sheets and want to give it a shot, try it — it can be a fun exercise to get more comfortable with using formulas in the tool, and is a good option if you need to create a clean calendar for an internal marketing campaign, or to organize a client’s upcoming projects and send along to key stakeholders.
