Home / Computer / How to Create Calendar in Excel for Beginners (Complete + Image)

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

Calendar is one of the tools used by most humans to see times like date and day. You can buy it easily in many places. But do you know if you can make your own? No, do not use third party application app like photoshop or corel draw. You only need one of the most common applications you can open Microsoft Excel .

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

How to Create Calendar in Excel

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

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

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

 how to create a calendar in excel

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

 how to make a calendar in excel 2007

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

 = DATE (G1; A1; 1) 

This formula or formula intends to display dates with structures: year, month, date

 how to make a calendar in excel 2007

5. In the end the formula above will change as below. But unfortunately this is not the format we are looking for. We look for formats showing the names of months and years.

 Image 005g

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

 [$-21] mmmm yyyy 

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

 how to create calendar in excel 2007 easily

7. Next you will see the cell has changed the format to be like below.

 Image 007

8. Then the next step is to put the date in the month. Place the mouse cursor in the first cell under Sunday, then type this formula:

 = B1-WEEKDAY (B1) +1 

 Image 008

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 Format Cell . On the tab Number select Custom . In the Type field fill in dd and then click OK.

 Image 009

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

 Image 010

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

 = A3 + 1 

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

 Image 011

12 . As a result, cell B3 contains the continuing date of cell A3 as shown in the figure.

 Image 012

13. Then proceed to the next cell with the formula

 "= A3 + 2" 

and the next cell

 "= A3 + 3" 

and so on until the cell is below saturday, then go on cel A4 below day of the week and so on until

 "= A3 + 35" 

 Image 013

14. Please polish a bit (Give red for fonts under the "Week" column, etc.) to beautify the table. Finally you will get a simple personal calendar created using Excel.

 Image 014

15. But you can still see at the top and bottom of the calendar there is a date outside the month. To better facilitate us in viewing the calendar, it would be better if the dates we distinguish the color. The trick, block all cells containing date .

 Image 015

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

 Image 016

17. You will see a window named New Formatting Rule . Please select and in the Format values ​​field where this formula is true, fill in:

 = Month (A3) <> $ A $ 1 

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

 Image 017

18. Then you can see that the dates outside of the month have been given different formats according to conditional formatting which have previously been set.

 Image 018

So some steps how to create calendar in Excel easily. It is quite complicated, but it is really effective if you want to have a personal calendar but do not have sophisticated applications to create it. How, dare to try?


Check Also

How to replace Windows 7 with Linux Mint

Many of you are Windows 7 users. I get it. Windows 7 just works. But …