How to Create Calendar in Excel

With the new year right around the corner, most of us are thinking about 2019 already. Yearly planning is something that so many of us put off until January 1st though. This is why it’s so important to have all the details about next year figured out before it starts. And if you’re one of the people who just thinks all of this is too overwhelming, I’m about to show you how easy it can be to create your own calendar template in Excel in just a few minutes.

In this article I will be showing you how to.

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.

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

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

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.
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.
Do this for five total rows in your sheet. The calendar should look like this:

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:

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.
Then, hold down Shift and highlight both cells with the numbers.
Drag the bottom right corner of the highlighted cells to auto-fill the rest of the week.
Repeat for the whole month.

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.

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

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

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.
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.

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.

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.
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.
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.

3. Add a Photo

Personalize your calendar by adding images, 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.

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.

Right-click the first row, with your title, and select Insert.
Click Entire Row.
Repeat depending on how many extra rows you want.
To make the background of the new rows white, highlight the new rows, click the paint bucket icon, and select white.
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:

In the Page Layout tab, click Orientation > Landscape.

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:

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

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

Select the cell that says January and click the arrow that appears. In the drop-down menu, select the month for your calendar.
Enter the calendar year in the cell underneath the month.
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.

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

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.

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

In the File menu, click Close and return to Microsoft Excel.
Go back to the Developer tab and click Macros.
Select Sheet1.CalendarMaker and click Run.
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

Bonus

How to Make a Pre-Made Calendar in Excel

You can craft your own calendar in Excel from scratch, but the easiest way to create a calendar is using a pre-made calendar template. Templates are useful because you can edit each day to include special events, and then print each month whenever you like.

A picture of a printed calendar sheet — tigerlily713/Pixabay

Select File > New.
In the search field, type calendar and select the magnifying glass to initiate the search.
Select the calendar style that suites your needs. This example uses the Any year calendar. Once you’ve selected your calendar, select Create.
Each calendar template has unique features. The Any year calendar template in particular lets you type in a new year or starting day of the week to automatically customize the calendar.

How to Make a Custom Monthly Calendar in Excel

If you don’t like the limitations of a calendar template, you can create your own calendar from scratch in Excel.

Open Excel and type the days of the week in the first row of the spreadsheet. This row will form the foundation of your calendar.
Seven months of the year have 31 days, so the first stage of this process is to create the months for your calendar that hold 31 days. This will be a grid of seven columns and five rows.To start, select all seven columns, and adjust the first column width to the size you’d like your calendar days to be. All seven columns will adjust to the same.
Next, adjust the row heights by selecting the five rows under your weekday row. Adjust the height of the first column.To adjust the height of several rows at the same time, simply highlight the rows you’d like to adjust before changing the height.
Next, you need to align the day numbers to the upper-right of each daily box. Highlight every cell across all seven columns and five rows. Right click on one of the cells and select Format Cells. Under the Text alignment section, set Horizontal to Right (Indent), and set Vertical to Top.
Now that the cell alignments are ready, it’s time to number the days. You’ll need to know which day is the first day of January for the current year, so Google “January” followed by the year you’re making the calendar for. Find a calendar example for January. For 2020, for example, the first day of the month starts on a Wednesday.For 2020, starting on Wednesday, number the dates in sequential order until you get to 31.
Now that you have January finished, it’s time to name and create the rest of the months. Copy the January sheet to create the February sheet.Right-click the sheet name and select Rename. Name it January. Once again, right-click the sheet and select Move or Copy. Select Create a copy. Under Before sheet, select (move to end). Select OK to create the new sheet.
Rename this sheet. Right click the sheet, select Rename, and type February.
Repeat the above process for the remaining 10 months.
Now it’s time to adjust the date numbers for each month after the template month of January. Starting with February, stagger the starting date of the month to whichever day of the week follows the last day of the January. Do the same for the rest of the calendar year.Remember to remove non-existent dates from the months that are not 31 days long. Those include: February (28 days—29 days in a leap year), April, June, September, and November (30 days).
As the last step, you can label each month by adding a row at the top of each sheet. Insert a top row by right-clicking the top row and selecting Insert. Select all seven cells above the days of the week, select the Home menu, and then select Merge & Center from the ribbon. Type the month name into the single cell, and reformat the font size to 16. Repeat the process for the rest of the calendar year.

Once you’re finished numbering months, you will have an accurate calendar in Excel for the full year.

You can print any month by selecting all of the calendar cells and selecting File > Print. Change orientation to Landscape. Select Page Setup, select the Sheet tab, and then enable Gridlines under the Print section.

Select OK and then Print to send your monthly calendar sheet to the printer.

How to Make a Custom Weekly Calendar in Excel

Another great way to stay organized is to create a weekly calendar with hour-by-hour blocks. You can create full a 24-hour calendar or limit it to a typical work schedule.

Open a blank Excel sheet and create the header row. Leaving the first column blank, add the hour when you typically start your day to the first row. Work your way across the header row adding hour until your day is complete. Bold the whole row when you’re done.
Leaving the first row blank, type out the days of the week in the first column. Bold the whole column when you’re done.
Highlight all rows that include the days of the week. Once all are highlighted, resize one row to a size that will allow you to write in your daily/hourly agenda.
Highlight all columns that include the hours of each day. Once all are highlighted, resize one column to a size that will allow you to write in your daily/hourly agenda.
To print your new daily agenda, highlight all cells of the agenda. Select File > Print. Change the orientation to Landscape. Select Page Setup, select the Sheet tab, and then enable Gridlines under the Print section. Change Scaling to Fit All Columns on One Page. This will fit the daily agenda to one page. If your printer can support it, change the page size to Tabloid (11″ x 17″).

How to Make a Custom Yearly Calendar in Excel

For some people, a yearly calendar is more than enough for you to stay on task all year. This design concerns the date and month, rather than the day of the week.

Open a blank Excel sheet and, leaving the first column black, add January to the first row. Work your way across the header row until you reach December. Bold the whole row when you’re done.
Leaving the first row blank, type out the days of the month in the first column. Bold the whole column when you’re done.Remember to remove non-existent dates from the months that are not 31 days long. Those include: February (28 days—29 days in a leap year), April, June, September, and November (30 days).
Highlight all rows that include the days of the month. Once all are highlighted, resize one row to a size that will allow you to write in your daily agenda.
Highlight all columns that include the months of the year. Once all are highlighted, resize one column to a size that will allow you to write in your daily agenda.
To print your new yearly agenda, highlight all cells of the agenda. Select File > Print. Change the orientation to Landscape. Select Page Setup, select the Sheet tab, and then enable Gridlines under the Print section. Change Scaling to Fit All Columns on One Page. This will fit the agenda into a single page.

Conclusion

A calendar in a spreadsheet is a schedule that can be used to see meetings, events and other activities on a given day. This type of calendar is more of a visual representation of the time frame it covers, instead of being an exact timetable for appointments. It is also more flexible, allowing you to make changes more easily. Since this type of calendar focuses mostly on the dates and times between them, but does not list entire days or provide any background information, using dates from a standard calendar works best for these types of schedules. This way users will know exactly what the chart refers to when you mention a specific date or day – there won’t be any confusion.