## How to Create a Printable 2018 Monthly Calendar Template

## 2018 is Coming!

Dear readers, the new year is just around the corner! Recently I had a colleague coming to me, and asked me if I can create a **monthly calendar template** for him which will **generate automatically** when the year and month are selected so that he can plan his next year’s appointments.

So after getting some inspiration from this Microsoft’s template, here’s my version of a dynamic **printable monthly calendar** template for 2018 (or any year in fact!):

## Are You in for this Project?

If you are just looking to download this printable monthly calendar, here’s the download link.

However, if you are up for the challenge, let’s get our hands dirty by creating this template from scratch. Through this challenge, you will learn and practise some of these Excel features:

- creating a dropdown list
- custom number formatting
- defined names
- VLOOKUP
- working with arrays
- INDEX
- conditional formatting
- setting print area

A word of caution: I would rate this project as 4/5 in difficulty. It is challenging because most of you are not familiar with all of the above Excel features which I utilized. But don’t worry, I will be with you every step of this project.

Once you finished this project, I’m sure you’ve learnt something new, and also have something useful for you to use and amaze your friends and colleagues with!

Ready? Let’s open up Excel and starting at it right away!

## Designing the Calendar Template

- First, I made columns B to H wider (I used a column width of 20).
- Next I made rows 4, 6, 8, 10, 12, 14 longer (I used a row height of 60). This is the area for you to enter your appointments, deadlines etc.
- Then I use outside borders to draw the boxes. Each box consists of a small cell that will display the date later and a large cell for writing.
- Lastly, I turned off the gridlines (View | Show | Gridlines) to make it look elegant.

## Creating a Dropdown List for Month Selection

If you are creating an Excel sheet for others to use, creating a dropdown list for users to select is the best way to prevent them from keying in something else.

- Select cell B1 and go to Data | Data Tools | Data Validation.
- Under
*Allow*, choose List. This will create a dropdown selection list. - Under
*Source*, key the following into the box:

`JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC`

- For cell C1, key in the year that you want for your calendar.

## Applying Makeup to Hide the Numbers

In cells B2 to H2, we are going to write the days of the week. However, later in the calendar dates, we will somehow need to align the dates to their corresponding days of the week. Excel can’t read the days of the week from the text Mon, Tue, Wed, etc.

So we need to have the cells B2 to H2 holding numbers in the background for Excel to do its calculation, while displaying Mon, Tue, Wed, etc. for us. How do we do that?

Enters Number Formatting.

Number Formatting allows us to apply a makeup on top of what is actually in a cell. You may not be aware of this “makeup” effect, but it is actually very commonly used. For example, 0.8 being displayed as 80% or 1234.5 being displayed as $1,234.50.

- To do this, first enter the numbers 1 to 7 into cells B2 to H2.
- Next select cell B2, and use the keyboard shortcut
*Ctrl+1*. In the*Formal Cells*window, select Custom from the*Category*, and under*Type*enter:

`"MON"`

Note: I’m creating a monthly calendar template starting Monday. If you want to create yours starting Sunday, use “SUN” instead of “MON”. - Repeat this 6 more times for the rest of the week.

We have completed the design of the calendar template. Check if yours look similar to the screenshot below.

## Using VLOOKUP to Convert Month Name to Month Number

Now we will start with the heavy work behind the scenes.

First let’s tackle the issue of how we can convert the month name (e.g. NOV) to the month number (11) so that we can use it in our calculations later.

We will use VLOOKUP to lookup the cell B1 which contains the month name, to a reference table and return its corresponding month number. (*I’m not explaining how VLOOKUP works in this article, but if you are not familiar with it, do a Google search or comment below.)

However, because we do not want to display the result (the month number) anywhere on the sheet, we can hide the result in a Defined Name.

Defined Names are like storage boxes that you can put stuff in, label them, and in the future retrieve the boxes easily when you need.

Let’s first try a simple one — storing the value in cell C1 into a box labeled as Year.

Go to Formulas | Defined Names | Define Name. Let’s name this box as *Year* and under *Refers to* (what we want to put into this box), use your mouse and click on cell C1.

And now, for the Month Number:

- Define a new name and call it
*Month*. - Under
*Refers to*, type in the following:=

`VLOOKUP($B$1,{"JAN",1;"FEB",2;"MAR",3;"APR",4;"MAY",5;"JUN",6;"JUL",7;"AUG",8;"SEP",9;"OCT",10;"NOV",11;"DEC",12},2,0)`

*Note: be careful of the comma , and semi-colon ; - What we just did was to VLOOKUP cell B1 in a reference table and return the corresponding month number. The whole table is keyed into the formula with “,” being the next column, and “;” being the next row.

## More Defined Names

Then we will create another defined name called *WeekOption*. In *Refers to*, use 1 if your calendar starts on a Sunday and 2 if it starts on a Monday. We will use this defined name later for Excel to return a numerical value for which day of the week a date falls on.

Next we need to create a matrix with 6 rows (there are a maximum of 6 weeks for any month) and 7 columns (for each day of the week), and to number them 1, 2, 3, and so forth. The reason why we need this is because once we have the first date in the first cell, the next cell is one day after it and so forth.

Creating a matrix like this is not difficult, but once again like the VLOOKUP we did earlier, we need to store this entire matrix into a defined name since we don’t want this to be displayed on the sheet.

So we create a new defined name *Matrix*, and under *Refers to*, we enter the following:

`={1,2,3,4,5,6,7} + 7*{0;1;2;3;4;5}`

This replicates the matrix and stores it into the box.

## Gathering the Ingredients

To recall, we’ve prepared these ingredients in our earlier steps:

*Year*(the year of the calendar)*Month*(the month number of the calendar)*WeekOption*(having the value of 1 or 2, depending on which day of the week to start the calendar)*Matrix*(a 6 x 7 table of sequential numbers)

## The WEEKDAY() function

Just in case you are not familiar with the WEEKDAY() function, it simply returns a number for which day of the week it falls on.

The WEEKDAY() function requires two inputs — a date (of course) and the return_type. We usually use the following return_type:

1 — if the week starts on a Sunday. WEEKDAY() then returns 1 for Sunday, 2 for Monday and so on..

2 — if the week starts on a Monday. WEEKDAY() then returns 1 for Monday, 2 for Tuesday and so on..

E.g. The date 1st January 2018 is a Monday, and if we use return_type 1, we will get a value of 2. If return_type 2 is used instead, we will get the value of 1.

*Note that the return_type we choose here, must be the same as how you setup your days of the week in cells B2 to B5.

## Finding the First Date of Your Calendar

Here’s where the entire magic is.

With everything prepped now, we want to find the first date that should be on our calendar (I’m using Monday as the starting day of the week). This date is usually coming from the previous month, unless the 1st of the month happens to be a Monday.

Consider this: The 1st of November 2017 is a Wednesday (or the numerical value of 3 using the WEEKDAY function). Then the first day on my calendar needs to be 2 days before the 1st November 2017.

Thus: **first_day_of_the_month – WEEKDAY() + 1** is the first day on my calendar.

And what’s the second day on my calendar? It’s **first_day_of_the_month – WEEKDAY() + 2**.

Do you recall where are these 1 and 2 coming from? *hint: it’s in one of our storage boxes.

Right! We already have that from our matrix of numbers from 1 to 42. Hence the calendar dates are calculated by: **Matrix + first_day_of_the_month – WEEKDAY()**.

Now let’s create a new defined name for *Calendar*. Under *Refers to*, enter the following:

`= Matrix + DATE(Year, Month, 1) - WEEKDAY(DATE(Year, Month, 1), WeekOption)`

*Notice how we retrieve the boxes by simply using their label names.

## The INDEX() Function – Getting what we want

Phew~ Now that the toughest obstacle has passed, we can cruise through the rest of this project.

Time to finally add the dates to our monthly calendar.

To recap what we have just did in the previous step, the defined name *Calendar* is now a 6 x 7 matrix listing all the relevant dates. We will call out parts of *Calendar* that we want to display using the INDEX() function.

The INDEX() function is like referring to a map and trying to call out a location using the longitude and latitude coordinates. We provide a map for Excel to refer to, state which row and column to look, and Excel will tell you what’s there with your given Excel “GPS” co-ordinates.

A typical INDEX() function will look like:

= **INDEX(Calendar,** *row_number*, *column_number***)**

where the *row_number* is 1 to 6, depending on which week of the month it is, and the *column_number* is 1 to 7, corresponding to the underlying numbers of cells B2 to H2 underneath the “makeup”.

But we will may experience an issue if we do this:

Users may accidentally overwrite the cell containing the formula when they are entering their appointments into the calendar.

## Trick to Prevent Changes to a Cell

We want to prevent changes to a cell, i.e. no deleting, overriding cell contents.

How can we do that?

Have you tried breaking a single wooden chopsticks or toothpick? It’s pretty easy right?

But if you need to break a bunch of chopsticks like the picture above, it’s so much harder.

The key is being united.

We will utilize the same concept of making the formula an array formula (meaning a formula for a group of cells) rather than a normal formula for a single cell.

If the user tries to modify part of the array, they’ll see this error message:

## Putting Dates into the Calendar

Alright, so we need to use the INDEX function to pull out parts of the defined named *Calendar*, and somehow enter the INDEX function as an array formula so that users will not be able to delete or edit any single cell containing the date.

Here it goes:

- Select cells B3 to H3.
- In the formula bar, type in the following formula:

`=INDEX(Calendar,1,B$2:H$2)`

- Press Ctrl + Shift + Enter instead of Enter to register it as an array formula.
- Excel will insert the curly braces in the formula bar to show you that it understands it as an array formula.
- Repeat it for Weeks 2 to 6 of your calendar, using the following formulas:

`=INDEX(Calendar,2,B$2:H$2)`

`=INDEX(Calendar,3,B$2:H$2)`

`=INDEX(Calendar,4,B$2:H$2)`

`=INDEX(Calendar,5,B$2:H$2)`

`=INDEX(Calendar,6,B$2:H$2)`

At this point, you should see something like this. Don’t freak out, the dates are there.

Remember our point that number formatting is just a makeup? The usual date format that we see is just applying makeup to the serial date numbers.

## The Final Magical Touches

For the final touches, we will first apply makeup to the serial date numbers to just display the day of the month.

To do so:

- Select all the dates, 42 of them. (*hint: you can hold down the Ctrl key for multiple selections.)
- Go to Home | Number, click the dropdown menu, go to More Number Format. (hint*: or just simple use the keyboard shortcut Ctrl + F1.)
- In the Format Cells window, go to Number | Category | Custom.
- Inside Type, key
`d`

for day of the month. Press OK.

The final (optional) thing to do is whether you want to hide the dates of the previous and next months, or to show them in a lighter color.

To do a custom Conditional Formatting:

- Select all the 42 dates again, making sure to start from cell B3.
- Go to Home | Styles | Conditional Formatting | New Rule.
- In the New Formatting Rule window, click on Use a formula to determine which cells to format.
- In the field Format values where this formula is true, type in the following formula:

`=MONTH(B3)<>Month`

- Under Edit the Rule Description | Format, in the Format Cells window, go to Font | Color. Choose a light grey (or white if you want to hide the dates of previous and next months).

## Set Printing to A Single Page

I have previously covered how to print to a single page easily on **Top 10 Excel tips for Excel Beginners **(Tip #9).

Here, we want to specify the area for printing. To do that:

- Select cells B1 to H14.
- Go to Page Layout | Page Setup | Print Area | Set Print Area to specify these cells to be printed.
- Then choose Page Layout | Page Setup | Orientation } Landscape.
- Finally, select Page Layout | Scale to Fit. Set Width and Height to 1 page.

## The Final Product

Yes, we have finally reached the end of this project to build a Month Calendar template to generate automatically for any year month!

Here’s the finally calendar.

Let me know in the comments below if you have any problems getting to the final product!

And wishing you a blessed festival holidays!

P/S: Special thanks to Martin Loughman from Spreadsheeti.com for highlighting that the maximum number of weeks in a month is 6!