How to Make a Calendar Template in Excel

Ryan Dube Updated 30-11-2017

You’ll always need a calendar. We show you how to create your own custom template for free with Microsoft Excel How to Quickly Create a Custom Excel Template to Save Time Excel templates are universally useful — they can help you keep your finances in order, manage a project, and organize your life. We show you how to create your own custom template. Read More .


Microsoft Excel makes many unique formatting tasks easy to automate. You can use Excel to manage your budget and finances 15 Excel Spreadsheet Templates for Managing Your Finances Always keep track of your financial health. These free Excel spreadsheet templates are the tools you need to manage your money. Read More  or you can create an auto-updating work schedule Tips & Templates for Creating a Work Schedule in Excel Creating a work schedule is not easy. Excel can help you fit everyone's hours, meet minimums, and keep your team happy. We show you how and supply you with great templates. Read More . Whatever type of spreadsheet you create, always keep in mind these Excel printing tips 7 Excel Printing Tips: How to Print Your Spreadsheet Step-by-Step Are you having problems printing from Microsoft Excel? This guide will show you how to print an Excel spreadsheet perfectly. Read More .

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 An Excel Template for Every Occasion Skip the steep learning curve and sort your life with the power of Excel templates. We have compiled a selection of auto-updating templates to track budgets, manage projects, and more. Read More  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 blank row for your title. Note that you can also use Excel’s Auto Fill feature How to Use Excel's Flash Fill and Auto Fill to Automate Data Entry When you regularly create Excel spreadsheets and populate them with data, you have to know about Auto Fill and Flash Fill. Let us show you what these features do and how they can help you. Read More to fill in the weekdays for you. Just type Monday, then drag the cell by its bottom right corner across the cells to its right.

Format the text around 12 point and bold. You’ll notice that some of the weekdays extend beyond the limit of the column.

How to Make a Calendar Template in Excel Excel Calendar Template 1 670x240

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.


How to Make a Calendar Template in Excel Excel Column Width 670x242


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.

How to Make a Calendar Template in Excel Excel Calendar Template 2 670x202

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.

How to Make a Calendar Template in Excel Excel Format Cells Date

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.


How to Make a Calendar Template in Excel Excel Merge and Center 670x259

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.

How to Make a Calendar Template in Excel Excel Calendar Template 3 670x321

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:

How to Make a Calendar Template in Excel Excel Calendar Template 4 670x313

It still looks barebones for a proper calendar. Time to add grid lines.

Grid Formatting

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.

How to Make a Calendar Template in Excel Excel Grid Borders 670x351

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. Type in “1” for the first of the month and “2” for the second, then highlight both cells and drag them across the rest of the cells in that week by their bottom right corner. Repeat this for every week.

Alternatively, if the 1st of the month is on Monday, 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”.

How to Make a Calendar Template in Excel Excel Calendar Template 5 670x300

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.

How to Make a Calendar Template in Excel Excel Calendar Template 6 584x500

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.

How to Make a Calendar Template in Excel Excel Calendar Template 7

Make sure the Page Setup is configured as shown here.

How to Make a Calendar Template in Excel Excel Page Setup

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 How to Print an Excel Spreadsheet on One Single Page A beautifully crafted spreadsheet loses all its appeal when it comes printed on nine sheets of paper, with half of them empty. We show you how to get everything nicely on one single page. Read More !

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 check out these Microsoft Office calendar templates The Best Free Microsoft Office Calendar Templates for Staying Organized Whether you need a weekly, monthly, or yearly calendar, these free printable calendar templates for Microsoft Office can help you stay organized this year. Read More  as well as these sites for free printable calendar templates 10+ Sites to Find the Perfect Free Printable Calendar Template All Year Long You can never have enough reminders that your most valuable personal resource is limited. Our selection of printable weekly, monthly, and yearly calendars will help you manage your time wisely. Read More .

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.

Related topics: Calendar, Microsoft Excel.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Amy
    January 1, 2019 at 5:13 am

    Thank you so much for these instructions. You don't know how long I have been trying to do this on my own. And now you showed me such an easy way! I really really appreciate this article!

  2. ranadeep
    May 8, 2015 at 6:01 am

    thanks for the help

  3. household budet template
    November 22, 2009 at 11:16 pm

    I will send this site yo my secretary.

  4. household budet template
    November 22, 2009 at 11:15 pm

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

  5. ericklamb
    November 20, 2009 at 12:03 pm

    nevermind. i just figured it out.

    • Maria
      February 10, 2010 at 3:07 pm

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

      • ericklamb
        February 11, 2010 at 10:06 am

        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.

  6. eric
    November 20, 2009 at 11:50 am

    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.

    • Nathan
      December 30, 2015 at 3:17 am



      use the same formula, but change the =2 to 3 through 7 for the weekday corresponding to each day. (Note, I'm using =TODAY() in A1).

  7. Courtney
    September 23, 2009 at 7:29 pm

    Basically- the date won't appear on the sheet even after typing in =TODAY()this is also after Format Cells DAte

  8. jawahar
    September 3, 2009 at 5:46 am

    How can i add the date at the right top corner of the grid.

  9. Olivia
    September 1, 2009 at 6:21 am

    When Adding the date at the top of the page(ie August 09):

    a. I don't have the option to just chose a month - all the options include an actual day so I end up with 1//9/09 for example rather than just September 09. Any solution to this?

    b. When I hit the centre and merge button the date disappears and I end up with a just a blank row. Again any ideas for this?
    Many thanks!

  10. Tiffany
    August 31, 2009 at 1:02 pm

    Thanks for this tutorial, it was just what I needed for the start of school! I have a question. The month header as i entered =TODAY(), came out correctly for August. But I would like to make a calendar for tomorrow's month, September. It comes out as ##### when I try to manually type in the month. Any way to fix this? Thanks for your help! :)

  11. Scott
    August 31, 2009 at 8:47 am

    First, allow me to apologize for this question, which does not directly apply to this article but concerns Excel worksheet tab formatting. Can Worksheet tab names be tied to cell contents? I want the Worksheet tab names to be FirstLastName1, FirstLastName2, ..., FirstLastNamen. All of the FirstLastName[1:n] are located on a roster Worksheet. I want to change the names on the roster worksheet and the Worksheet names will automatically follow. I am using Excel 2000. I have enjoyed your other articles. Thanks in advance.

  12. Dave
    August 31, 2009 at 8:10 am

    To get the columns to the correct width, just double-click on the dividing line between each header box, and Excel will set the column to the width of the widest cell. Much easier and quicker than having to guess a number.

  13. Jaison
    August 29, 2009 at 12:13 am

    Thanks for the detailed instructions. I was looking for something like this to paste in my cubicle. I went an extra mile by adding a background picture and changing the transparency. It looks very professional now :). Thanks again for the tips.

  14. youthworker
    August 28, 2009 at 7:12 am

    Interesting that in step #2 you merged cells... I've always found it easier to simply highlight the row and drag it to a new height. Selecting multiple rows gives you the chance to stretch them all to the same new size together. (or use the menu bar, in the same way you changed the column width in step #1.)
    I find it less confusing this way, since it is easier to see which cell you are referencing. In your example, the first Sunday could be referred to as A3, A4 etc up to A8. I'd find that a little confusing.
    Still, it's a great looking calendar, and it's nice to see someone using classic excel (rather than 2007 version) in a tutorial!

    • Ryan Dube
      August 28, 2009 at 9:31 am

      youthworker - I like the technique you decribe to select multiple rows and set row-width. The method I included in this article is one that I've used often to combine multiple cells quickly in any spreadsheet, but in this particular case I actually like your suggested approach better for the very reason you listed - the cell labels remain intuitive. Thanks for your comment!

  15. edwardthay
    August 28, 2009 at 5:58 am

    Finally I knew how to do it. Great.

  16. Mike D
    August 28, 2009 at 12:47 am

    I'm not much on Excel and the auto fill instructions for adding the dates aren't making sense to me. Could you break it down to in simpler format?

    • Ryan Dube
      August 28, 2009 at 9:29 am

      Hey Mike - sure...I know I stuffed a few steps into three paragraphs, so I'll great it down here:

      1. Manually number the first week in your calendar up to Saturday. Then, for the following Sunday, enter "=" then click on the previous Saturday, and then type "+1" and press enter.
      2. Hold the "fill" handle at the lower right corner of Sunday's box and slide it down through the rest of the Sundays for the month. This will make sure that every Sunday, 1 is added to the number from the previous saturday. (the numbers will be all messed up at this point - ignore that)
      3. Go back up to the week where you just entered the formula for sunday, and in the blank Monday box enter "=", then click on the sunday before it, and type "+1" and enter.
      4. Now, grab the "fill" handle for that Monday and drag it down in the same way you did for the sundays.
      5. Go back up, grab the same "fill" handle for monday and drag it to the right through the whole week to Saturday.
      6. Do the same thing (the right-drag) for every week of the month, and you're done!

      For strange months where there's a partial week at the beginning or end, you type those manually. If it's more work for you to figure out how to quickly use the fill handle to fill in your calendar, it might actually take less time for you to just go through and type in the numbers...whatever works easiest for you! Good luck.

  17. Ryan Dube
    August 27, 2009 at 7:22 pm

    Hey Skannloh - thanks for the question.

    After you set the date format that you want, then go to Format -> Cells, click the alignment tab, and set the horizontal and vertical alignment to "center" - that should do it! Good luck.

  18. skannloh
    August 27, 2009 at 3:47 pm

    How do I enter a centered large size date (number) in each square box instead of the small font at the top right position?