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

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.

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

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

• You should “January” in the Sample box.
• Click OK to apply.

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

• Now, open your system’s calendar on the right.
• Go to 1^st 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.

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.

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.

• Now, that we have all the dates in the second week. Select the 2^nd week entirely.
• Drag down the cells to autofill the dates for the entire month.

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

• Simply delete the entries with extra dates in those cells.

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.

• Notice that the month name has changed to February, automatically.
• Repeat for day 2 of February by applying this formula of 1^st February.

• Click on 2^nd February and drag that cell to the entire week.

You can see that the month ends on the 28^th 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.

Conclusion

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!