How to make a calendar in excel

How to make a calendar in excel

  1. It’s a new year, and that means it’s time to invest in a new planner or calendar. Whether you’ve resolved to be more organized or you’re simply looking to stay on top of things in your usual way, the right calendar system is essential for effective time management and organization.

    Luckily, a new calendar is right at your fingertips. All you need to create a custom-made calendar that perfectly suits your needs is a computer and Microsoft Excel.

    Here are the basic options for how to make a calendar in Excel.

    1. Use code from Microsoft.
    2. Create the calendar manually from scratch. This consists of several steps.
      1. Create a new worksheet.
      2. Format the column width.
      3. Format the row heights.
      4. Set the orientation and margins.
      5. Define the print area.
      6. Define the calendar’s borders and page setup.
      7. Add a title and customize elements.
    3. Use a calendar template.

    Let’s take a closer look these options.

  2. Use code from Microsoft

    If you’re comfortable using code, then Microsoft supplies some programming language to help you create a calendar in Excel. If you’re working in Microsoft Excel 2007 or later, here’s the basic procedure.

    1. Create a new workbook.
    2. From the Developer ribbon, select Visual Basic. (You may need to enable the Developer Ribbon. In Excel 2007, it’s in the Popular menu; in Excel 2010, it’s in the Customize Ribbon menu.)
    3. From the Insert menu, select Module.
    4. Copy the Visual Basic for Applications code (which you can access via the link above) into the module sheet.
    5. From the File menu, select Close and Return to Microsoft Excel.
    6. Click the Sheet1 tab.
    7. From the Developer ribbon, select Macros. Select CalendarMaker, then select Run. This should finalize the process of creating your calendar.
    8. Add a title.
  3. Create the calendar manually

    This process involves a lot of steps, but once you master them you’ll have a functional tool and a whole lot of confidence using Excel. Using plenty of inspiration from PCWorld, here’s how to make a weekly calendar in Excel.

    1. Open a new worksheet.
    2. Highlight columns A1 through G7.
    3. Format the column width.
      • Use the Home tab to select the Cells option, then click Format > Column Width. Type 18 in the dialogue box.
    4. Format the row height.
      • Select the Cells group again, then click Format > Row Height. Type 75 in the dialogue box.
      • Click on A1. Change the Row Height to 118.
      • Click on A2. Change the Row Height to 30.
    5. Set the orientation and margins.
      • Highlight A1 through G7.
      • Use the Page Layout tab to navigate to Page Setup > Orientation and then select Landscape.
      • From the same Page Setup tab, select Margins > Custom Margins. Press the tab key one time to navigate to the Top setting.
        • Move from the Top to Bottom field boxes using the tab key. In each box, enter .25. Do the same for the Left and Right field boxes.
        • When the tab button takes you to the Header and Footer boxes, enter 0.
        • The tab button will then take you to the Center on Page option. Select both Horizontally and Vertically. Click OK.
    6.  Define the print area.
      • Make sure A1 though G7 are still highlighted.
      • From the Page Setup tab, select Print Area > Set Print Area. Press the Home key once. You should now see a solid or dotted line around the selected area. This is the section of your spreadsheet that will print if you decide to print your calendar.
    7.  Define the calendar’s borders and page setup.
      • Highlight A2 through G7.
      • From the Home tab, select Fonts > Borders. A drop-down should appear; select All Borders.
      • From A2 through G2, enter the days of the week. Center them both horizontally and vertically. Next, add the days of the current month.
      • Now you’ll want to merge the top cells. To do this, start by highlighting A1 through G1. From the Home tab, select Alignment > Merge > Merge and Center.
    8.  Add a title.
      • Navigate from the Insert tab to Text > WordArt. Choose your preferred text style, and then type the month and year in the field box.

    Now you’re ready to print and write on your calendar or use the digital version in Excel.

  4. Use a calendar template

    If you’re not up to creating a calendar manually, then you can turn to a monthly or yearly calendar template to speed up the process. These templates give you a basic structure and then you have the option to customize the calendar by changing the font, font size, colors, and more. Per Smartsheet, here’s how to go about it.

    1. Start by choosing a template. To browse different template options, open Excel and select File > New. When the search field appears, type in “Calendar.” You can then select from a variety of monthly or yearly calendar options.
    2. Once you’ve chosen a calendar template, a table should appear on the right for Calendar Month, Calendar Year, and 1st Day of Week. You can modify each of these cells to reflect the current month, year, and day.
    3. Give your calendar a title. To do this, start by double-clicking on the title field. Delete the formula that’s already there, and then type in the current month.
    4. If you use a monthly calendar template, you’ll want to add in weekday headers, days, and dates.
      • Type the days of the week into each respective cell in the second row.
      • To add days to the calendar, start by highlighting the entire spreadsheet and select Home > paint bucket icon > white. This will give the calendar a white background. Then, highlight five or six cells under Sunday (the first weekday header). Select Home > borders icon > outside borders. This will make a box. Highlight the box and then copy and paste it under each of the other weekday headers.
      • Finally, add dates. In each row, enter the first two dates of that week (make sure these dates are in the first cells in each of the bordered boxes). So, if the 1st of the month is on a Tuesday, you would enter “1” in the first Tuesday box and “2” in the first Wednesday box. Hold the Shift key and highlight both of these cells, then drag the bottom right corner of the highlighted section. This should auto-fill the dates for the rest of the week. Do this for each week until the month is completely filled in. (If all of this feels too complicated, you can also manually add each date for the month.)
    How to make a calendar in excel
  5. Whether you use programming language, build a calendar from scratch, or use a template, these strategies will yield the same result: a functional, customized calendar that’s tailored to your needs and helps you maintain organization in the new year and beyond.


Is there an office issue YOU know how to resolve that other office workers might benefit from? Write for our blog and get published online. The best part? You get PAID. Learn more now and submit a topic!

Share this infographic on your site

How to make a calendar in excel