Create a Calendar in Excel

We have more going on in our lives than ever before. From juggling meetings, projects, and deadlines at work to organizing our personal lives, it can be hard to keep track of everything.

Calendars are an important part of keeping all your activities, meetings, appointments, and events organized. You can do a lot with a calendar - people use them as a timesheet, to make a work or school schedule, to track a project, and more. 

There are a couple different options to make a monthly or yearly calendar. You can create one from scratch using Excel, but that can be a time-consuming, manual task. You can also use a pre-built calendar template in Excel, with the calendar already formatted for you. There a variety of pre-built calendar templates for Excel, ranging from 12-month calendars, monthly calendars, weekday displays, and calendars with notes. These templates have a pre-made, formatted calendar ready for you to add events.

With so many available calendar templates available in Excel, how do you know which one is the easiest to use? We’ve sorted through them all and found the best calendar template for Excel, both a monthly calendar template and a yearly calendar template. 

Download a Monthly or Yearly Calendar Excel Template

 Click here to download a 2016 yearly calendar template by month, for current versions of Excel (2007-2013)

 Click here to download a single month calendar template for current versions of Excel (2007-2013)

 Click here to download a yearly calendar template for older versions of Excel

 Click here to download a single month calendar template for older versions of Excel

How Would You Like to Create Your Calendar?

- or -


2016 Calendar in Excel

Smartsheet

 



How to Use a Monthly or Yearly Calendar Excel Template

Using a calendar template is incredibly easy. All you need to do is choose whether you need a monthly or yearly calendar, and add your scheduled events to the template. You can also customize the font types, font sizes, and colors. If you choose the monthly calendar, you will first need to change the title and the dates for the specific month you want to use. 

Here are more steps for customizing your template for your needs.

1. Formatting the Monthly Calendar Template

  1. To change the title, double-click on the title field, delete the formula, and type the new month. 
     
  2. Then, you will need to re-number the date fields. You can either manually enter the dates, or use the auto-fill feature mentioned in step four of the “How to Make a Monthly Calendar in Excel 2003, 2007 and 2010” section. 
     

2. Adding Events to the Monthly or Yearly Calendar Template

  1. In either template, double-click on a cell in a date box and enter the event. To enter multiple events on the same day, click on another cell in the date box.
     
  2. To center the text, click on the cell. Then, in the Home tab, in the Alignment group, click the Center Text icon (it looks like five lines of text that are centered).
     

Add an Event - Calendar in Excel

 

3. Changing Fonts and Colors 

  1. Click on the cell with the text you’d like to modify, and in the Home tab, you can change the font type, font size, font color, or make the text bold, italicized, or underlined. 
     
  2. To change the background color of the weekday header or of an event entry, highlight the cell, click the paint bucket icon, and select the fill color. 

You can also personalize your calendar template by adding a photo, like your company logo. In the Insert tab, click Pictures. Upload the picture you would like to use. The image will be added to your spreadsheet and you can drag it anywhere in the sheet. 

How to Make a Monthly Calendar in Excel 2003, 2007 and 2010

Here are some step-by-step instructions for making a monthly or yearly calendar in Excel. 

1. Add Weekday Headers

First, you’ll need to add the days of the week as headers, as well as the month title. 

  1. Leave the first row in your spreadsheet blank. On the second row, type in the days of the week (one day per cell). 
     
  2. To format the weekday headers and ensure proper spacing, highlight the weekdays you just typed and on the Home tab, in the Cells group, click Format.  Select Column Width and set the width for around 15-20, depending on how wide you want the calendar. 
     

Calendar in Excel


 

2. Add Calendar Title 

  1. In the first blank row, we will add the current month as the title of the calendar using a formula. Click any cell in the first row and in the fx field above the blank row, enter =TODAY(). This tells Excel you want today’s date in that field. 
     

Excel calendar


 

  1. You’ll see the format of the date is incorrect. To fix this, click the cell with the date. In the Home tab, in the Number group, click the Date drop-down. Select More Number Formats and choose the format you would like for the month title.
     
  2. To center the title, highlight all the cells in your title row (including the one with the month displayed) and click on the Merge and Center button in the Home tab.
     

Merge Center - Calendar in Excel


 

3. Create the Days in the Calendar 

Here is where you will build the body of your calendar. We will use borders to create the date boxes. 

  1. First, highlight your whole spreadsheet. 
     
  2. Click the paint bucket icon in the Home tab and select white. Your spreadsheet should now have a white background.
     


 

  1. Then, highlight five or six cells under the first weekday header, Sunday.
     

Add Border - Calendar in Excel

 

 

  1. While the cells are still highlighted, click the borders icon in the Home tab and select the outside borders option. This will outline the first date box in the row.
     
  2. Highlight the box you just made, and copy and paste it under the other weekday headers. This duplicates your box for the other days in the week. 
     
  3. Do this for five total rows in your sheet. The calendar should look like this:
     

Blank No Dates Calendar in Excel

 

To add borders around the weekday headers, highlight the row with the weekdays, click the borders icon, and choose the all borders. 

4. Add Dates 

We’ve created the framework for the calendar, now it’s time to add the dates. You can either manually enter the dates in each box, or use Excel’s auto-fill feature. Here’s how:

  1. For each row in the calendar, enter the first two dates of that week in the first cells in each box. For example, if the 1st of the month is Wednesday, enter 1 into the first Wednesday box and 2 in the Thursday box. 
     
  2. Then, hold down Shift and highlight both cells with the numbers.
     
  3. Drag the bottom right corner of the highlighted cells to auto-fill the rest of the week.
     
  4. Repeat for the whole month. 

Blank Calendar in Excel


Note: You must manually enter the first two dates for each row before you can drag and auto-fill the rest of the week. 

How to Make a Yearly Calendar in Excel

You have essentially created a monthly calendar template. If you want to use a calendar solely on a month-by-month basis, you can use this same calendar, change the month title, and just re-number the days. 

You could also use this monthly calendar framework to create a yearly calendar. 

  1. On the bottom of the spreadsheet, right-click on the tab that says Sheet1.
     
  2. Click Move or Copy.
     
  3. Select the box for Create a copy and click OK.
     


 

  1. Make a total of 12 copies, one for each month of the year. Note: for months with 31 days, you will need to add an extra row to the calendar.

 


Once you have 12 copies, you will have to go through each one and change the title to the appropriate month. You’ll also have to re-number the calendar according to the specific month, either manually changing the dates or using the auto-fill feature mentioned in step four of the “How to Make a Monthly Calendar in Excel 2003, 2007 and 2010” section. 


Customize Your Calendar in Excel 

It’s easy to customize your monthly or yearly calendar in Excel. You can color-code certain events on the calendar, like meetings or birthdays, or change font sizes. You can even add your company logo to the calendar. 

1. Format Fonts 

  1. To make the title bigger, click the row with the title. In the Home tab, you can change the font type, font size, and make the title bold, italicized, or underlined. 
     
  2. To change the font size of the weekday headers, highlight all the headers. In the Home tab, you can format the font type and size. 
     

Choosing Fonts - Calendar in Excel

 

  1. To format the date markers, highlight all the date boxes. In the Home tab, you can adjust the font type and size. 

2. Change Colors

You can change the font colors or the background colors in your calendar. Color-coding may be especially helpful for labeling certain types of events.

  1. To change the title color, click on the row with the title. In the Home tab, select the color you want from the color drop-down list. 
     
  2. To change the background color of your weekday header, highlight the whole row,  click the paint bucket icon, and select the fill color. You can also just change the text color by repeating step one. 
     
  3. To color code an event, type an event or appointment into a date box. Then, select the text, click the paint bucket icon, and select the fill color.
     

Choosing Colors - Calendar in Excel

 

3. Add a Photo

Personalize your calendar by adding images, like your company logo.

  1. In the Insert tab, click Pictures. Upload the picture you would like to use.
     
  2. The image will be added to your spreadsheet and you can drag it anywhere in the sheet. 
     

Add Picture - Calendar in Excel

 

If you would like to add your logo or picture to the top of the calendar, you will have to add extra space so the image can fit. 

  1. Right-click the first row, with your title, and select Insert
     
  2. Click Entire Row
     
  3. Repeat depending on how many extra rows you want. 
     
  4. To make the background of the new rows white, highlight the new rows, click the paint bucket icon, and select white. 
     
  5. To remove the grid line above the title row, select the title row, click the grid icon, and click the option with the removed gridlines. 


Print a Calendar in Excel

Your customized, formatted calendar can be a challenge to print. The sides of the calendar extend beyond a printable page, so you will end up with parts of a calendar printed on two pages. Here’s how to fix it:

  1. In the Page Layout tab, click Orientation > Landscape
     

Orientation - Calendar in Excel

 

  1. In the Scale to Fit group, change the width to 1 page and the height to 1 page.


Now, your calendar will print on one page.


How to Find a Microsoft Calendar Template 

Microsoft has also created a handful of calendar templates. You can choose from a multi-page calendar, a yearly calendar, a weekly calendar, and more. 

Here’s how to use a pre-made template available in Excel:

  1. Click File > New. 
     
  2. Type Calendar in the search field. 
     
  3. You’ll see a variety of options, but for this example, click the Any year one-month calendar and click Create
     

Office 2016 Calendar

 

You’ll see a table on the right with Calendar Month, Calendar Year, and 1st Day of Week

  1. Select the cell that says January and click the arrow that appears. In the drop-down menu, select the month for your calendar. 
     
  2. Enter the calendar year in the cell underneath the month. 
     
  3. Select the cell that says Monday and click the arrow that appears. In the drop-down menu, select the first day of the month. 


You can also visit Microsoft’s online template gallery by clicking here, and selecting the calendars category on the left-hand side.

How to Insert a Calendar with Visual Basic 

You can insert a pre-made, pre-populated calendar directly into Excel using the CalendarMaker with the Visual Basic Editor. You will need to enable the Developer Mode in Excel, and use a programming language, but it is simple to do and Microsoft offers a sample code for you to use. 

1. Enable Developer Mode 

First, you’ll need to turn on the Developer Mode. 

  1. Click File > Options
     
  2. In the pop-up box, on the left-hand side, click Customize Ribbon.
     
  3. Under Main Tabs, make sure the Developer box is checked. 
     

Developer ribbon in Excel

 

You will now see a new tab in your Excel ribbon at the top of the spreadsheet.

2. Insert the Calendar with the Visual Basic for Applications Code 

Microsoft has a sample Visual Basic for Applications code here for you to use and create the calendar. 

  1. Create a new workbook. 
     
  2. In the Developer tab, click Visual Basic
     
  3. You will see a list of workbooks and sheets (under VBAproject on the left side). Find the Sheet1 entry and double-click. 
     
  4. A blank pop-up box will appear. Copy and paste the Visual Basic for Applications code (found here) into the box.
     

Visual Basics

 

  1. In the File menu, click Close and return to Microsoft Excel
     
  2. Go back to the Developer tab and click Macros.
     
  3. Select Sheet1.CalendarMaker and click Run
     
  4. In the pop-up box, type the full month and year you want for your calendar and click OK. Your calendar should look like this: 
     


 

Smartsheet
 

 

How to Make a Calendar in Minutes with Smartsheet’s Calendar Template 

 

Try Smartsheet for Free

Smartsheet’s pre-formatted templates allow you to instantly create a calendar. Months, days of the week, and dates are pre-formatted, and you have room to add descriptions, comments, and duration in hours of each activity. 

Here’s how to use a calendar template in Smartsheet: 

1. Choose a Calendar Template 

  1. Go to Smartsheet.com and log in to your account (or start a free 30-day trial).
     
  2. From the Home screen, click Create New and choose Browse Templates
     
  3. Type “calendar” in the Search Templates box and click the magnifying glass icon. 
     
  4. You’ll see a handful of results, but for this example, click on 2016 Calendar by Day and click on the blue Use Template button in the upper-right corner. 
     
  5. Name your template, choose where to save it, and click the OK button. 
     

2016 calendar

 

2. List Your Calendar Information 

A pre-made template will open, complete with the months and dates already formatted for the entire year of 2016. There will also be sample content filled in for reference. 

  1. Add your calendar events under the Activity column. You can also add more detail in the Description, Hours, and Comments columns. 
     
  2. To add multiple events for the same date, you must create a new row. Right-click on a row and select Insert Row Above or Insert Row Below. Then, in this new row, add the month, day of the week, date, and activity.
     
  3. If you need to delete a row, right-click on the cell in the row you’d like to delete and select Delete Row
     

Smartsheet 2016 Calendar by Day

 

On the left side of each row, you can attach files directly to a task or start a comment around a certain event, adding more context to your calendar. 

3. Switch to Calendar View

All your calendar information lives in this table. Then, with the click of a button, you can see all the information auto-populated into a calendar. 

  1. On the left-hand toolbar, click the calendar icon to switch to calendar view. 
     

2016 Calendar in Day Adding Attachments


You will now see all your information in a calendar (today’s date will be outlined in blue). You can edit or add an event directly from this calendar view by double-clicking on a green bubble. You can also change the date of any event by dragging and dropping a green bubble to another date box.
 

Calendar View - Smartsheet 2016 Calendar

 

Anything you change in this calendar view will be automatically updated in your table. 
 

Customize Your Calendar in Smartsheet 

It’s easy to customize your calendar in Smartsheet. With just a couple clicks, you can add your company logo or color-code your events.

1. Change the Colors of Events

  1. Right-click on a green bubble and select Color Settings.
     
  2. A color palette will appear, letting you change the color of the calendar entry.
     

Change Color Settings - Smartsheet Calendar

 

2. Change the Calendar Colors

  1. To change the background color of the calendar, click Account in the upper left-hand corner.
     
  2. Select Colors & Logo. Here, you can change the background, tab, and calendar bar colors, as well as choose from preset color options. 
     
  3. Click Save
     


 

You can revert to the standard color settings and logo at any time. 

3. Add a Logo

As a default setting, the Smartsheet logo is placed in the upper right-hand corner. You can change this by uploading your own company logo. 

  1. Click Account in the upper left-hand corner.
     
  2. Select Colors & Logo
     
  3. Under Logo, click Select and choose your image file. Once it’s uploaded, click Save.
     
  4. Your new image will be added to the calendar. 
     

Email, Share, or Print Your Calendar 

Share your calendar with colleagues, friends, or family. You can choose from printing, exporting, or emailing your calendar from Smartsheet. 

To email your calendar: 

  1. On the left-hand toolbar, click the envelope icon.
     
  2. Select Send as Attachment.
     
  3. In the To field, add the recipient(s). Choose from your contact list or type email addresses.
     
  4. The Subject and Message fields are auto-populated for you, but you can choose to delete the text and add your own. 
     
  5. Choose whether to attach the calendar as a PDF or Excel sheet, and whether you want to send the calendar right away or on a recurring basis. 
     

Send Smartsheet Calendar

 

To share your calendar with a link:

  1. On the bottom of the calendar, click Publish.
     
  2. Select the publishing option you would like by clicking the slider.
     
  3. A pop-up box will appear with a publish link. You can copy and paste the link for anyone to view the calendar or use the embed code to embed the calendar into any webpage. 
     


 

To print your calendar:

  1. On the left-hand toolbar, click the printer icon. 
     
  2. Choose the calendar date range, paper size, orientation, and more. Then, click OK
     
  3. A PDF version of your calendar will begin downloading. You can then print this PDF.

The Easiest Way to Make a Calendar 

There are many options for making a calendar, but make sure to choose the one that will save you the most time (like templates with pre-formatting and easy editing features). After all, a calendar should help you stay organized in a simple way, not create more work for you by forcing you to learn how to create one from scratch.

Smartsheet is a robust collaboration and communication tool in a familiar spreadsheet layout. With an extensive range of smart views - Grid, Calendar, Gantt, Sights, and Card-View -- Smartsheet works the way you want.

See how easy it can be to make a calendar. Try Smartsheet for free for 30 days.

 

Try Smartsheet for Free

Categories

Comments

Why insert a '1' in the first day if you can have the calendar do it for you? Here's how... Formula for First Sunday: =IF(WEEKDAY(DATE(%YEAR_CELL%,(MONTH(DATEVALUE(%MONTH_CELL%&"1"))),1))=1,1,"") NOTE: You can use separate cells or reference the year of a cell and the month of the same cell. (YEAR(%CELL_REFERENCE%)) (MONTH(%CELL_REFERENCE%)) Formula for other days of the first week: Monday: =IF(WEEKDAY(DATE(%YEAR_CELL%,(MONTH(DATEVALUE(%MONTH_CELL%&"1"))),1))=2,1,IF(%SUNDAY_CELL%="","",SUM(%SUNDAY_CELL%+1))) Tuesday: =IF(WEEKDAY(DATE(%YEAR_CELL%,(MONTH(DATEVALUE(%MONTH_CELL%&"1"))),1))=3,1,IF(%MONDAY_CELL%="","",SUM(%MONDAY_CELL%+1))) Wednesday: =IF(WEEKDAY(DATE(%YEAR_CELL%,(MONTH(DATEVALUE(%MONTH_CELL%&"1"))),1))=4,1,IF(%TUESDAY_CELL%="","",SUM(%TUESDAY_CELL%+1))) etc..... Formula for second Sunday: =IF(%FIRST_SATURDAY_CELL%="","",SUM(%FIRST_SATURDAY_CELL%+1)) Formula for second Monday: =IF(%SECOND_SUNDAY_CELL%="","",SUM(%SECOND_SUNDAY_CELL%+1)) Use the Formula for second Monday to fill the other days of the second week making sure that each consecutive formula is referencing the day prior. Use the second week formulas to fill weeks 3 & 4. Formula for Week 5 Sunday: =IF(%PREVIOUS_SATURDAY_CELL%="","",IF(%PREVIOUS_SATURDAY_CELL%=(DAY(DATE(%YEAR_CELL%,MONTH(DATEVALUE(%MONTH_CELL%&" 1"))+1,1)-1)),"",SUM(%PREVIOUS_SATURDAY_CELL%+1))) Formula for Week 5 Monday: =IF(%PREVIOUS_SUNDAY_CELL%="","",IF(%PREVIOUS_SUNDAY_CELL%=(DAY(DATE(%YEAR_CELL%,MONTH(DATEVALUE(%MONTH_CELL%&" 1"))+1,1)-1)),"",SUM(%PREVIOUS_SUNDAY_CELL%+1))) Formula for Week 5 Tuesday: =IF(%PREVIOUS_MONDAY_CELL%="","",IF(%PREVIOUS_MONDAY_CELL%=(DAY(DATE(%YEAR_CELL%,MONTH(DATEVALUE(%MONTH_CELL%&" 1"))+1,1)-1)),"",SUM(%PREVIOUS_MONDAY_CELL%+1))) Hopefully you have the idea to fill the remaining for Week 5. Fill Sunday & Monday of Week 6 with the formulas, but referencing the Sunday to the previous Saturday. The remaining of Week 6 cells are not needed. Yes, this is a lot of formulas, but it works!

Hi Kathryn -- Thank you for letting us know and apologies for the mistakes in the 2016 calendar. They have now been fixed. -Emily

I searched but could not find a good calendar template for a school year (September to June). This is how I created my own calendar with the months I wanted: Create 2 calendars using a template you like, one for the first year, and another for the subsequent year. Drag each tab from the 2nd year into the first year calendar (in the appropriate sequence). The month tabs for the second year will have a (2) after their name as you cannot have duplicate names. When you are done, you can close the 2nd year calendar without saving. Right-click and hide the months you do not want to see, but DO NOT delete! The January page contains formulas that are needed for the other pages. You can rename the tabs - I add (1) or (2) to the months that are hidden, then you can display only the month name on the tabs that you can see. If you wish to reuse the calendar the next year, simply go to the January pages and change the years.

I want to create a calendar (Nov - Oct). Yes, November should be the first month. Then i want to start counting the week number. Say for eg, if it's Dec 4th 2015, it should return week number value as 5. Any help or guidance would be appreciated. Thanks, Kamal.

Hello Kamal, We don't currently have a way display week numbers in Calendar view. We have a week number functionality in the Gantt chart, but this is the calendar week number vs the week number of a project. You could possibly build a formula in the Grid to figure out the week number as it pertains to the project, but this wouldn't necessarily be displayed in the calendar view, since it only displays the primary column. For additional support, please contact support@smartsheet.com - Thank you, The Smartsheet Team

I want to base my calendar on UK paint chips which are steps of paper showing shades of colour and would need fairly obviously 7 columns (for the days, and 6 rows to accomplish all permutations of days and months and because the chips are single columns of 6 "rows" I realise this may be fairly wasteful of paper but I do not know how to specify real end measurements of individual cells and then build from there. Any advice? My imagining is of A3 (UK measures) hole punched along the long edge using 6 working sheets and possible cover and back page with other printing. This could work also with 3 sheets of A2 folded to same effect or somehow a rick rack fold so past and present and future months could show hanging from a single hole in the top edge. thanks for thinking about it.

Is there a way to keep yearly events repeating in subsequent years without having to reenter the data. For example: Can Grandma's Birthday be linked to January 1st, regardless of the year? Thank you.

Hi RM, Thanks for reaching out to us. We don't yet have recurring tasks, but this is on our enhancement request list and I've added your vote for it. Thanks, Diana

Thank you so much for all of this information. I need a little help. I am working on a calendar to track open service desk tickets for a customer. I need to be able to hold a large amount of text in a single cell of a calendar day. I am currently using the Data Validation, Input Message feature to do this but my space is quite limited. Might there be some way to do something similar with room for more text? Thanks in advance. Tony

Hi Tony, At this time, there is no way to increase the character limit within the Calendar View in Smartsheet. One workaround is that you could add a comment to the task, within a calendar day, so your customer can click through to additional details. Hopefully this will help in the meantime. Thanks for sending your request, I've forwarded it on to our Product team for future consideration. Thanks, Diana

Add new comment