Create a Calendar in Microsoft Excel or Insert a Reference Calendar

Create a Calendar in Microsoft Excel or Insert a Reference Calendar
Page content


Perhaps the simplest way to create a calendar in Microsoft Excel is by using a template. Microsoft Online offers pages of calendar templates made especially for Excel. There are calendar templates by year, academic calendar templates, perpetual calendars and more. There are templates available for Excel versions dating back to Excel 97.

To use a calendar template, open Excel and click New from the File menu in Excel 2003 or from the Office Button in Excel 2007. Type calendar into the search online box and click Go. Select a calendar template from the plethora of choices and click Download.

Calendar Control

Another way to do it is by inserting a Calendar Control. The Calendar Control is an ActiveX control. If you work in Access, you have probably used controls, such as combo and text box controls. This method allows you to insert a calendar to an area of the worksheet. You can refer to this calendar at any time, moving forward or backward from month to month.

To add a Calendar Control to Excel 2003, begin by clicking on a cell in the area of the spreadsheet where you want to make a calendar. Go to the Insert menu and click on Object. On the Create New tab of the Object dialog box, click Calendar Control and then click OK.

Article Image

In Excel 2007, go to the Developer tab of the ribbon and click Insert. Select ActiveX Controls and then More Controls. Select Calendar Control and click OK.

Article Image

If you don’t see the Developer tab, click the Office Button and then click Excel Options at the bottom of the menu. On the Popular tab of the Excel Options window, select Show Developer Tab in Ribbon.

If you do not see the Calendar Control option in either version of Excel, it may be because it is installed with Access. You can download the Control here.

The calendar will open in Design View. To make the calendar a bit more custom, remain in Design View. Right click the Calendar Control, point to Calendar Object and select Properties. Use settings on the Calendar Properties dialog to make the Calendar in your Excel spreadsheet look the way you want. You can use VBA to control how the calendar looks and acts as well. Simply double click the Calendar Control to open Visual Basic.

Article Image

Spreadsheet Calendar

A third method is using the Fill Handle. While it may not look like a traditional calendar, it is a functional calendar that works in many instances. To make a calendar spreadsheet for an entire year, enter Date in cell A1 and Day in cell B1. Select column A, right click and choose Format Cells. Under Category select Date and choose your desired date format. In Cell A2, enter January 1 (in the month, date and year format you selected. Click A2, grab the Fill Handle and drag it down until reaching the cell containing December 31.

Article Image

To quickly and easily add weekdays to the calendar, enter the formula =TEXT(A2,“DDDD”) into cell B2. Again, select the cell, grab the fill handle and copy the formula down to the rest of the calendar.

For just a weekly calendar, download this free Excel 7-Day Template.