How To Make A Calendar Template In Excel

Ads by Google

excel_logoAs an avid Excel user, I often find myself trying to do things with Excel that most folks would never consider. There are several reasons for this, but the most important is that Microsoft Excel has certain features that make certain unique formatting tasks extremely simple and easy to automate.

Earlier, I introduced MUO readers to my love affair with Excel when I wrote about how you can use it to create an effective household budget. I was also pleased to read some love from other MUO authors, such as Karl’s article about how to split a monster sized Excel spreadsheet, or Eyal’s Excel printing tips.

Today I’m going to share a few additional cool features in Excel that you can use specifically 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 calendar 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.

Ads by Google

make a calendar in excel

To fix this, just highlight the weekdays that you’ve typed and then select Format -> Column -> Width – and set the width for around 15 to 20.

make a calendar in excel

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.

excel calendar template

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 the Format -> Cells -> Number tab, select date and choose the format that you’d like for the month title.

excel calendar template

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 and center” button.

create calendar in excel

Clicking “merge and 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 and center” feature, but this time you’ll merge a few empty cells in order to create one larger cell that represents a single day.

create calendar in excel

This step is where you’ll build the body of your calendar template, but it’s also the easiest step. First, highlight about 5 or 6 cells, and click on the “merge and 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 (control-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:

create a calendar in microsoft excel

Highlight your entire calendar click on the borders tool, and select the “grid” 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.

create a calendar in microsoft excel

Using this technique, you simply enter in a formula for the first Monday and the second Sunday of the month. For example, if the 1st of the month is on Sunday, you’d enter “1” into the first Sunday box (and of course nicely format it at 14-point and bold). Then, for Monday 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 Sunday, but click on Saturday and add 1.

When you’re done, drag this box down each week so that every Sunday has the same formula (adding 1 to the previous Saturday). Drag the formula from the previous Monday down through the month as well – and then drag from Monday 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.

calendar template excel

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, so you’ll end up with parts of it on two pages. To fix this, go to File -> Page Setup, click the Page tab, and make sure it’s configured as shown here.

calendar template excel

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!

Do you have any tricks that might make creating a calendar in Excel a little simpler? Have you ever created any unique projects using Excel? Share your experiences in the comments section below.

Join live MakeUseOf Groups on Grouvi App Join live Groups on Grouvi
Windows Troubleshooting
Windows Troubleshooting
29 Members
Windows_10
Windows_10
36 Members
Windows Hacks & Customization
Windows Hacks & Customization
48 Members
Best Windows Software
Best Windows Software
31 Members
Ads by Google
Comments (21)
  • ranadeep

    thanks for the help

  • household budet template

    I will send this site yo my secretary.

  • household budet template

    Wow…. I will send this site to my secretary to use it because she always forget my time schedule. Thank you

  • ericklamb

    nevermind. i just figured it out.

    • Maria

      Hi, I have been trying to do the same thing, do mind sharing your idea?

    • ericklamb

      you must have a cell that you type in the starting day of the month. assuming it is a sunday thru saturday calendar; put this formula =IF($L$1=D2,1,IF(C3=””,””,C3+1)) where L1 is the starting date D2 is the cell that defines the day of the week and C3 is the date of the previous day. use that formula for monday-saturday of the first week of the month only. use this formula =IF(L1=B2,1,””) where B2 is the cell that defines the sunday column, for the first sunday of the month. for every other day except the last week use this formula =C8+1 where C8 is the date of the previous day. for the last week use this formula =IF(U18=30,””,IF(U18=””,””,U18+1)) where U18 is the date of the previous day. this formula will make the calendar stop at 30 days. i started working on a formula that would recognize the month of the year and how many days are in that month but I lost interest. however it is simple enough to change the 30 in the last formula to a 31 or a 28 each month and just copy and paste to the other cells that need the change. but anyway, I hope this helps.

  • eric

    i got this idea for a calendar and can’t figure out how to get it to work and now I just can’t leave it alone. I want to make a calendar that will automatically number the days of the month after you type in the starting day. the problem i’m having is in the first week. if the month starts on wednesday then the formula needs to recognize that sunday, monday, and tuesday need to be blank and thursday friday saturday need to recognize they are the previous date plus 1. any ideas, it’s got to be possible.

Load 10 more
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.