How to make a calendar in Excel using different methods

By Indeed Editorial Team

Published 12 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel offers a great platform for creating and customising calendars. Notably, it gives you a lot of flexibility with regard to formatting and entering a variety of events. Knowing how to create an Excel calendar can help you better organise your days and save you money that you might spend on a traditional paper calendar. In this article, we explain how to make a calendar in Excel and find out the different ways to use and customise them.

Related: How to be organised at work (plus benefits and tips)

How to make a calendar in Excel

Although Excel is primarily an accounting and data management software, it has become popular for calendars, which is why it is useful to learn how to make a calendar in Excel. Online calendars are becoming more popular, as they offer a spacious and convenient way to organise your working year. Excel in particular has become a favoured calendar platform because of the level of functionality and customisation it offers. You can choose the exact width of rows and columns for more complex entries and choose different colours to help code certain days for holidays or meetings.

There are also a variety of calendar templates to choose from, which can save you time if you don't want to make your own. If you choose to make your own, then you have the advantage of knowing how it functions, and you can reuse the template year after year. Excel calendars seamlessly connect with business purposes and offer a level of professional appearance and advanced editing functions that aren't available when using paper calendars. Below are some of the different ways you can create your own Excel calendar, with step-by-step instructions:

1. Using a template

The quickest way to create a calendar in Excel is by using a pre-existing template. Microsoft has a vast collection of these, and you can further customise them for your own needs. To make a calendar using a template, simply follow the steps below:

  1. Open a new Excel file.

  2. Click on 'file' in the left corner of the document and click 'New' from the drop-down box.

  3. Type 'calendar' in the search bar.

  4. Choose either a monthly or yearly calendar from the various templates. Consider your needs and uses for the calendar and click 'Create' when you have decided.

  5. When Excel has generated your calendar, you can either type in the months manually or select from the list in the drop-down box. You can also change the year by double-clicking on the box and typing the new year.

Templates are popular as they feature illustrations that you can alter as the month passes, giving the calendar more personality and a greater visual appeal. Some templates even give additional information about each month to help you better work out your professional schedule. Pre-existing templates are ideal if you're looking to save time and focus on entering important dates for the upcoming year.

Related: How to create an Excel graph in five simple steps (with tips)

2. Making a calendar manually

If you feel comfortable with the various functions and complex tasks in Excel, then you may wish to create your own calendar. One advantage of doing so is you have full control over each part, even with minor areas, such as column sizes and colour schemes. It's also possible to recycle this calendar and send it to other colleagues at the start of each year. Follow these steps to create your own calendar in Excel:

  1. Open a new file and highlight columns A1 to G7.

  2. Format the column width by clicking the home tab and 'Format', type '18' in the box that appears. In 'Format', select 'Row Height' and type '75' in the box.

  3. Select A1 and alter the row height to 118 by following the same steps as above. Do the same with A2 but change the row height to 30.

  4. To ensure the page orientation is correct, highlight A1 to G1 and in the 'Page Layout' tab, click 'Orientation' and set to 'Landscape'.

  5. Set the margins by going to the 'Page Layout' tab, select 'Margins' and 'Custom Margins'.

  6. We recommend using the up and down buttons to set 0.25 for the top, left, bottom and right and then 0 for the header and footer boxes. Ensure that both boxes that read 'horizontally' and 'vertically; are checked and press 'OK'.

  7. To set the print area, select A1 to G7 and click 'Page Layout', 'Print Area' and 'Set Print Area'. To set the borders to outline your calendar, click the 'Home Tab', 'Font, 'Border' and select 'Inside' under the options.

  8. Manually enter the days of the week and month in the A2 to G2 cells.

  9. Merge the top cells by selecting A1 to G1, clicking the 'Home' tab and selecting 'Merge and Centre'. You can also further improve your calendar by adding a title.

3. Using code to create a calendar

If coding is something you are comfortable with, then you may wish to create a calendar using Microsoft code. This can be quite fulfilling, as it provides you with an opportunity to practise your coding skills and produce something that you can use throughout the entire year. If you find coding to be a challenge, then Microsoft has a pre-set code you can enter for a basic calendar. The code for the provided calendar has a solid layout and allows for more tailored customisation. Below are the steps you can follow to make this calendar:

  1. Open a new file in Excel. Go to 'File' in the top corner and click options.

  2. Click 'Customise Ribbon' from the left panel. Under the 'Main Tabs' section, check the box that reads 'Developer'. This ensures this ribbon is displayed by default, and you can access the coding sections.

  3. On the main spreadsheet page, select the new 'Developer' tab and the 'Visual Basics' box. Then under the 'Insert' tab, click 'Module'.

  4. Paste the code from the Microsoft website into the module sheet.

  5. Click 'File' and 'Close and Return to Microsoft Excel'. This takes you back to the main spreadsheet screen.

  6. Click the 'Developer Ribbon' and 'Macros', and from here, select the 'Calendar Maker' option and the 'Run' button. In the blank field that appears, type in the month and the year, and scroll through the different months using the Macro tab each time.

Related: 15 basic Excel formulas to learn (with examples)

4. Creating a custom weekly calendar

If you find that organising your work schedule hourly is more suited to you, then it's advisable to create a weekly work calendar. These provide a more specific schedule for your work and personal life and allow you to order activities into hourly blocks, helping you check off important meetings and activities. The following steps show you how to create a weekly calendar in Excel:

  1. Open a new spreadsheet.

  2. Leave column 'A' blank and fill in your working hours in bold text starting from column 'B'. For example, a typical working day may be from 9 a.m. to 5 p.m.

  3. From column A2 to A8, type the days of the week in bold text.

  4. To customise the row heights, select 'Home', 'Format' and 'Row Height'. This way, you can make sure you have enough space to write about your tasks and activities.

  5. In the same way, select the columns with the times in and click 'Home', 'Format' and 'Column Width' to adjust the size.

Related: How to prepare for an Excel assessment test (with tips)

Tips for customising a calendar template

When you have created or used an existing template for your calendar, you may wish to customise this for both practical and aesthetic purposes. Below are some of the areas you can customise and the instructions to do so:

  • Font: Highlight the cells you want to change the font in and click 'Home' and 'Fonts'. From here, you can adjust the style, effects and colour to suit your needs.

  • Colours: In the same way, select the column or row you wish to customise and click the paint bucket icon in the 'Home' tab. Adjusting the colours can help you identify important tasks and prepare in advance.

  • Images: You can add images, illustrations, or logos to your calendar to help personalise it further. Choose the cell where you want the image, click 'Insert' and 'Illustration', and then choose the image you want to import into the spreadsheet.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles