How to Create a Monthly Calendar in Excel

January 7, 2018 • Devin Partida

Advertisements

Whether you’re looking for a way to plan your New Year’s resolutions or just a revamp to your everyday schedule, Microsoft Excel can help.

Excel offers many benefits. They mostly relate to managing spreadsheets. However, another perk is that the program allows you to easily work within a grid structure, which is ideal for calendar-making.

If you don’t have Excel on your computer now, someone you know probably does. Alternatively, you can buy access to Microsoft 365 Personal for $6.99 per month or $69.99 per year. It includes Excel, plus other programs you’ve probably heard of, like PowerPoint and Word.

Buy: Microsoft 365 Personal | PC or Mac ($6.99/month or $69.99/year)

How Can You Create a Calendar in Excel?

Through a quick Google search, you can see there are many calendar templates for Excel already. In this post, though, we’re focusing on how to make an Excel calendar from scratch. (You can quickly turn this into a recurring template too.)

This method allows for greater customization, depending on your needs. It’ll also help you get acquainted with Excel.

1. Create Weekday Headers for Your Excel Calendar

To begin your calendar-creation process, open up an Excel workbook on your screen. Notice the letters representing columns and the numbers for the rows. We’ll start making a weekly Excel calendar here.

Go to the second row down and type one weekday name in each of the first seven cells across the top. The weekdays don’t go in the first row, because that one’s for your Excel calendar title.

As you start typing, you’ll see some of the days don’t fit into the cells. That’s because they’re too long for the current column width. You can fix that by clicking the Home tab at the top. Then look for the section on the right relating to cells. Select Format, then AutoFit Column Width.

Alternatively, if you want to add a bit more space to your calendar and make each column consistent, click the Column Width… button instead and set the width to 15.

2. Change Your Calendar’s Colors and Formatting

Now that your calendar has some text, it’s a good time to adjust the font and colors of your calendar. Use the typical Font toolbar at the top of the document to change the text. You can use the options here to make the text bold, italicized, or underlined.

Look for a button featuring the letter A with a long, rectangular block of color underneath it. Clicking the main part of the button changes the font color to the last one used. It’s the same hue as the colored block underneath the letter. There’s also an upside-down triangle on the right of it. By clicking it, you open up the complete color library.

3. Add an Excel Header that Updates Automatically

After adding the weekdays and changing the font, turn your attention to the top of the sheet.

Remember, that first empty row is for the month.

However, changing the header when a new month arrives isn’t very convenient. Thankfully, you can use an Excel formula to make a month-based, automatically updating header.

First, go to any cell in the empty row and click to select it. Then type the following into it: =TODAY().

That formula instructs Excel to put today’s date in that field. The result of the formula looks like the full date, which isn’t exactly what most people want for a calendar. Before going further, format the content in that cell with a large font size: 20-22 works well. Bold the font, too, to help it stand out.

Then, right click on the cell and choose Format Cells. You’ll then see a formatting options box on the screen.

Select the Number tab at the top of the box if it isn’t already open.

Then choose Date from the Category menu on the left. Doing that gives you some relevant selections in the primary part of the box. Choose the desired format from the Type menu. The one with the month spelled out fully and the year after it is a logical pick. Click OK to save your settings.

You’ve just formatted the title, but you’ll see it still looks strange on the spreadsheet. It only populates one cell and isn’t centered.

Tackle that issue by clicking and dragging to select all the cells in the header row. Then, click the Merge & Center button in the Home tab.

This command will combine your highlighted cells. Notice the centered alignment of the Month header, too.

The header you made syncs with your computer’s clock. That means it can tell when the months change, and updates accordingly, so you’ll never have to update it manually again.

I wanted a little more space around my calendar header, so I inserted another row above my weekday headings and above the header itself. I also changed the font and background colors of the header to match my days of the week.

4. Add More Days to Your Calendar

It’s time to fill your calendar with spaces for the days of the week. Start by clicking and dragging to select about five cells underneath the Sunday header. Then, go back to the Merge & Center button and click it.

Doing that creates one large, highlighted cell. With that newly created area selected, use the Ctrl + C keyboard command to copy it to your clipboard. Then, press Ctrl + V and paste the large cell into the sections for the other days.

Next, complete the calendar grid by highlighting all the days in the first week you’ve created and drag them down to create four or five weeks.

5. Add Some Grid Lines

The calendar is coming together now, but still looks pretty basic. Grid lines can improve it.

Click and drag to highlight the entire calendar. Then, go to the small Grid button in the Home menu.

Play around with the borders until your calendar looks the way you want it. I added Outside Borders around the entire calendar. Then I selected the weekday headings row and added a Top and Bottom Border.

Next, I highlighted the individual day cells in the calendar and selected All Borders.

6. Add the Dates for the Month

Next, you’ll use Excel’s Auto Fill feature to enter the numerals for each day.

Enter the number one in the cell representing the first day of the current month. Then, click and drag to fill in the dates for the rest of that week.

Repeat this for the remaining weeks of the month. Type the next date for each Sunday, then click and drag to quickly fill in the dates for the rest of each week.

Put the final touches on your calendar by using the Alignment tools to reposition the dates how you like in the cells. I opted to make my dates appear in the upper right corner of each cell.

If you want to add some additional formatting to your calendar, you can also click the Cell Styles tool to change how the dates are formatted. I went with the Title format for mine.

At last! You can print your calendar, or keep it on your computer to reference. Be sure to save your work in the Excel file, too.

What Will You Plan with Your New Calendar?

These examples showcase a sampling of what’s possible with a homemade Excel calendar.  Creating one can take as little as 15 minutes, depending on how detailed you want to get.

Thanks to the steps listed here, you’ll never have to buy a calendar again!

What do you need to get better at remembering? Tell us below and write it on your calendar!

bg-pamplet-2