How to Create a Calendar in Excel for Beginners (Complete+Pictures)

Calendar is one of the tools used by most people to view time such as date and day. You can buy it easily in many places. But did you know that you can make it yourself? No, do not use third-party applications such as Photoshop or Corel Draw. You only need one of the applications that you probably open most often, namely Microsoft Excel.

Yes, just by using Microsoft Excel and a few simple formulas you can easily get a simple calendar. In this article, we will give you some steps on how to easily create a calendar in Excel.

How to Create a Calendar in Excel

In this tutorial, I will use Microsoft Excel 2021, but I believe this tutorial is still relevant and can be used in Microsoft Excel in some previous versions. Here are the steps on how to create a calendar in Excel:

1. First of all of course you have to open the Microsoft Excel application on your computer.

2. Next on the excel worksheet, create a table like the one below.

3. Block on cell B1 to F1, then on the ribbon Home, click Merge & Center so that all cells in the area are merged into one.

4. Please write the following formula in the cell that you just merged:

=DATE(G1;A1;1)

This formula or formula intends to display the date with the structure: year, month, date.

5. In the end the above formula will change as below. But unfortunately this is not the format we are looking for. We are looking for a format that shows the name of the month and year.

6. To get the format we want, please right click on the same cell, then select Cell Format. Make sure you are on the tab Number then select Custom. On the field Type write:

[$-21]mmmm yyyy

Code [$-21] in this formula is to display the date format in Indonesian, while mmmm is the formula for displaying the month and yyyy is the formula for displaying the year. If so, please click OK.

7. Next you will see that the cell has changed its format to something like the one below.

8. Then the next step is to put the date in the month. Place the mouse cursor in the first cell below the day of the week, then type this formula:

=B1-WEEKDAY(B1)+1

9. After you click enter on the formula above, the result is a date format dd/mm/yyyy. But we only need the date. To change it, right-click on the cell, then select Cell Format. On tab Number, choose Custom. On the field Type, fill it with dd then click OK.

10. Then you will be able to see that the cell has changed to date format only.

11. To fill in the date, move to the next cell and type the formula:

=A3+1

This formula serves to pass the date in cell A3 to the next cell.

12. As a result, cell B3 contains the date that continues from cell A3 as shown in the figure.

13. Then continue in the next cell with the formula

“=A3+2” 

and the next cell

“=A3+3” 

and so on until cell is below Saturday, then continues in cell A4 under Sunday and so on until

“=A3+35”

14. Please polish a little (Give red color for the font under the column “Sunday”, etc.) to beautify the table. Finally you will get a simple personal calendar created using Excel.

15. But you can still see at the top and bottom of the calendar there are dates outside the month. To make it easier for us to see the calendar, it would be better if we differentiated the dates in color. The method, block all cells containing date.

16. Then navigate to Ribbon Home > Conditional Formatting > New Rule.

17. You will see a window named New Formatting Rule. Please select “Use a formula to determine which cells to format” and in the Format values ​​field where this formula is true, fill in:

=Month(A3)<>$A$1

After filling in the formula, specify the font format by clicking the button Format. Then click OK.

18. Next you can see that the dates outside the month have been given a different format according to conditional formatting previously set.

Those are some steps or steps on how to easily create a calendar in Excel. It’s quite complicated, but this method is really effective if you want to have a personal calendar but don’t have sophisticated applications to create one. How, dare to try?

…in it for your literacy need

Share :