[Step-by-Step] How to Make a Calendar in Excel?

creating a calendar in

In this tutorial, we will learn to create a calendar in Excel. There are multiple ways to insert calendars in an Excel sheet. We will look at the different ways to insert or create calendars in Excel.

Steps to create a calendar in Excel

Let’s get started with this step-by-step guide to creating a calendar in Excel.

Automatically create a calendar

  • Open a new blank Excel file.
  • Go to the File tab.
  • Click on More Templates at the right corner.
more templates
More Templates Option
  • Search “Calendar”.
  • Choose your favorite calendar template.
  • Click Create.

Your automatic calendar template is ready.

Manually creating a calendar

If you want to manually create a calendar in Excel, then follow the steps below.

  • Open a new blank Excel sheet.
  • Write S, M, T, W, T, F, S in 7 adjacent cells.
  • Select the cells above the 7 cells.
  • Apply Merge & Center.
  • Type MAX( and select the cells below the 7 cells.
  • Close brackets and press ENTER.
max formula
MAX Formula
  • You should now see a 0 in the cell you put the formula.
  • Apply formatting styles you like in the cell with 0 because that cell displays the month name.
  • Click on the cell with 0.
  • Press CTRL+1.
  • Or, Go to the Home tab.
  • Under the Number section, open Number Formatting options.
  • Click Custom on the left.
  • Change General to MMMM.
MMMM Formatting Applied
  • You should “January” in the Sample box.
  • Click OK to apply.
month name
Displaying Month Name

The cell now displays the first month’s name i.e., January.

  • Now, open your system’s calendar on the right.
  • Go to 1st January of the current year and find out from which weekday did the year start.
  • In our case, the year started on a Friday, so we select the cell under F.
  • Write the full date, for example, 1/1/2021.
  • Select the entire area where the dates are going to be placed.
  • Open Number Formatting again.
  • Or, press CTRL+1.
  • Go to Custom on the left.
  • Change General to D.
  • Click OK.
Applying D Formatting

You can now see that the full date has been changed to 1.

Let’s begin adding the other dates of the month.

  • Move to the next empty cell.
  • Type ‘=’ and press the previous cell with a date in it, i.e., 1 and type ‘+1’ in that cell.
  • Press ENTER to complete the formula.
new date
Adding a New Date

You will now see a 2 in that cell.

  • Repeat the same for the first days of the next week and drag the cells to autofill the dates of the entire week.
autofill week2
Adding Dates in Week 2
  • Now, that we have all the dates in the second week. Select the 2nd week entirely.
  • Drag down the cells to autofill the dates for the entire month.
autofill month
Autofill Dates for the Entire Month

You can now see that the dates are starting from 1st after 31st of January.

  • Simply delete the entries with extra dates in those cells.
clean jan
January Prepared

We now have the proper dates for the month of January.

Let’s begin adding dates for other months up to December.

  • Copy and paste the January month beside it.
  • Notice that the previous month ended on a Sunday. So, the new month must start from a Monday.
  • Click the cell under M.
  • Type ‘=’ and select the last date of January i.e., 31 and type ‘+1’ in that cell.
  • Press ENTER.
day1 feb
Adding Day 1 of February
  • Notice that the month name has changed to February, automatically.
  • Repeat for day 2 of February by applying this formula of 1st February.
day2 feb
Adding Day 2 of February
  • Click on 2nd February and drag that cell to the entire week.
dates applied feb
All Dates Applied

You can see that the month ends on the 28th of February and there are some extra dates left.

  • Remove the extra leftover dates from those cells.
  • You may repeat the same till December to get a complete calendar built manually.
complete calendar


This article was all about the two ways to create a calendar in Excel, manually and automatically. Stay tuned for more amazing content like this one!