Updated by Tina Sieber on 8 March, 2017.
You’ll always need a calendar. We show you how to create your own custom template for free with Microsoft Excel.
Microsoft Excel makes many unique formatting tasks easy to automate. You can use Excel to manage your family budget or you can create an auto-updating work schedule. Whatever type of spreadsheet you create, always keep in mind these Excel printing tips.
Here, I’m showing you to make a calendar template in Excel.
Make a Calendar Template in Excel – A Step-By-Step Tutorial
The following calendar sheet is an Excel template that you can open up and print each month.
You have the option to keep it blank and it’ll automatically update to the correct month — all you have to do is print and fill in the days. Or, you can use the last few techniques described at the end of this article to fill in the sheet with the days before printing. It requires a little extra work, but it looks very cool.
Step #1 – Create the Header and Title
The first thing you’ll need to do is create the weekday header as well as the month title. This step will demonstrate the merge-and-fit feature as well as how to set the width of multiple columns at once.
First, type in the weekdays in a row across the top, but leave one row blank for your title. Format the text around 12 point and bold. You’ll notice that some of the weekdays extend beyond the limit of the column.
To fix this, just highlight the weekdays that you’ve typed, go to the Home tab and under Cells select Format > Column Width… and set the width for around 15 to 20.
Now that you’ve got your weekday header nicely formatted, it’s time to add the current month at the top of your calendar sheet. While you could just manually type in the month at the top of the sheet, this wouldn’t be very efficient, as you’d have to change it every time you wanted to print a new calendar sheet. It makes more sense to create an Excel calendar template that knows what month it is and changes the month title for you.
You do this by choosing any cell above your weekdays, and typing in the formula, “=TODAY()“, which tells Excel that you want today’s date in that field. After you do this, you’ll notice that it looks nothing like the month and it’s formatted all wrong.
First, format the title with a font of around 20 to 22 and bold. Then, go into Home > Format > Format Cells…, select Date and choose the format that you’d like for the month title.
Once you’ve got your title formatted correctly, you’ll still notice that it’s not centered properly and exists only within one cell at the top of the spreadsheet. In other words, it looks odd.
To fix this, highlight all of the cells above your weekday header (including the one where your month is displayed) and click on the Merge & Center button.
Clicking Merge & Center turns all of the highlighted cells into one collective cell, and then centers your title in the middle of the highlighted region. Now you’ve got yourself a nicely formatted, automated calendar header.
Step #2 – Create The Calendar Days
The next step to make your calendar template in Excel is to once again use the Merge & Center feature, but this time you’ll merge a few empty cells in order to create one larger cell that represents a single day. This step is where you’ll build the body of your calendar template, but it’s also the easiest step.
Single Day Field
First, highlight about 5 or 6 cells, and click on the Merge & Center button. This will form one cell the perfect size for one day on the calendar.
Then, while this large cell is highlighted, copy it (CTRL + C or Edit > Copy) and paste it into the other days (or just drag the lower right corner of the box over to the right). This will duplicate your box for every day of the week. Do this for five rows.
Your calendar should now look like this:
It still looks barebones for a proper calendar. Time to add grid lines.
Highlight your entire calendar, click on the grid tool in the Home tab, and select All Borders so that every grid line shows — essentially “drawing” out your calendar for you.
Finally, you can either leave your template as it is (so you can write in the days yourself) and move ahead to step 3, or you can add in the days using Excel.
Instead of going through and manually typing in “1” to “30”, you can save a lot of time by using Excel’s auto-fill feature. For example, if the 1st of the month is on Monday, you’d enter “1” into the first Monday box (and of course nicely format it at 14-point, bold, aligned in the top right). Then, for Tuesday you’d type “=” then click on the day before it (A1) and type “+1”.
When you’re finished, highlight the box where you’ve entered this formula, and drag it across the entire week; this will correctly fill in all of the days of the week. Then, do the same thing for the following Monday, but click on the previous Sunday and add 1.
When you’re done, drag this box down each week so that every Monday has the same formula (adding 1 to the previous Sunday). Drag the formula from the previous Tuesday down through the month as well, and then drag from Tuesday across each week. While it may sound confusing, as you give it a try you’ll find that filling in the days only requires a few clicks and less than 5 minutes.
Step #3 – Printing Your Calendar Template
Finally, you’re ready to print out your calendar to a nice clean paper so you can pin it up on your wall. By this point, your calendar should look like this.
While this looks absolutely fantastic within the Excel sheet, it won’t print properly because the edge of the calendar template goes beyond the printable page, as indicated by the dashed vertical line. This means you’ll end up with parts of it on two pages.
To fix this, go to Page Layout and bring out the Page Setup window by clicking on the tiny arrow in the bottom right.
Make sure the Page Setup is configured as shown here.
Set the format for Landscape, and fit the entire calendar to 1 page wide by 1 page high. Click OK, and when you do a print preview you’ll find that your calendar template is formatted on one sheet and ready to print!
The next month, just open up the same Excel file (the month will already be correct), renumber the days and click Print — it’s as simple as that!
Your Custom Calendar Template Done
And there you have it, your custom calendar template that you can reuse over and over again. If you need a quicker solution, you can also download free printable calendar templates.
Do you have any tricks that might make creating a calendar in Excel a little simpler? What unique projects have you created in Excel? Share your experiences in the comments section below.