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.
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
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
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.
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 .
7. Next you will see the cell has changed the format to be like below.
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
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.
10. Then you will see that the cell has changed to date format only.
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.
12 . As a result, cell B3 contains the continuing date of cell A3 as shown in the figure.
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"
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.
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 .
16. Then navigate to Ribbon Home> Conditional Formatting> New Rule.
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 .
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.
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?