Hi. In this video we’re going to go over how we can take a basic month calendar in Excel and create the other months of the year. We’ll also
create a conditional format to highlight for us the current day. You can follow along with your own calendar or, to create the one that we're
going to use, please take a look at my video on “How to Create a Calendar in Excel.”
On our screen is a basic month calendar in Excel. If you're wondering, I am in Page Layout view.
I prefer this view when I create calendars - I just find it a bit easier.
The first thing we’ll want to do to start formatting our calendar is to update each date field so that Excel recognizes it as a date.
To do this, we'll highlight the first row with day numbers in it. Then we'll right-click,
we'll go to Format Cells and Custom. And where it says General, we're going to delete General and type the letter "d". Now, the letter
"d" represents the day in a traditional date formatting and you can see a preview of this in your sample box just above it.
And then we click OK.
If you like, you can also create a Conditional Format to highlight for you the current day. Obviously, this is something that will
dynamically update, so this can be useful for electronic use, but maybe not be as practical for a printed calendar -
– unless you need to highlight current day when you print it. So, we’ll add a Conditional
Format to these cells by going to the Home tab, which we're in, and go in to Conditional Formatting.
From Highlight Cell Rules you'll want to choose Equal To.
And, where it says 4, we actually want to delete that and we'll say =TODAY()
and then this will highlight the current day with
the formatting that you selected. Now, I'm happy with the light red fill with dark text, but you can change this, um, to what you like.
So, we'll click OK.
And
now this is formatted. Now,
today is not a day in the first week of January, so we're not going to see this - yet - but we will see this later on as we build out our calendar.
Now, before we do anything else, we need to update our date. Now, when we started this, these numbers were just numbers and we
went back and formatted them as dates. So, this first date defaulted to the year 1900 - which we know it is not.
So, we're going to be creating a 2017 calendar. So we'll change this to 1/1/2017 and these other
dates already have a formula in them to just add +1.
So they should auto update.
Now, once we've done this, this row is already formatted in the date format that we want, with the Conditional Format that we want it to have.
Below it, we still need to update this formatting. So we'll highlight this first row and, still in the Home tab, we'll click on Format Painter,
and then we'll simply paste this format to the other row dates in our calendar.
Now, even though January only has 31 days, we're actually going to update this last week of the calendar even though it's empty
because we want this to be a calendar that we can
copy over and reuse for other months in this year. So, to create the other months, we're going to create a month for each tab. So this
way you can label each tab with the month and year so it'll be easy to identify at a glance, just by looking at your workbook. And then you can
also use the tab name as your header at the top of each month by doing it this way.
So, to have our header reflect the name of our tab, we'll come up here to the header and we'll delete where it says January and, in the
Design tab, we can insert...simply click on Sheet Name,
and that creates a reference so that Exel can choose the sheet name. And then you would come down here
and rename your sheet. So this one will be January 2017. Now we were able to do it this way because we're in Page Layout view.
Now, if you're in any other view, you would just, with your cursor anywhere on your worksheet, you would have come to the Page Layout tab
and, in Print Titles, you would have been able, under Header / Footer, to do the same thing. Now, before we create the other months,
I want to add a little bit of formatting to this because
the other months will mirror this sheet.
So, we just made the header a little bit bigger, we'll add some shading to the names and then, down here,
we're going to make all the date areas - we'll make all the colors blue and we'll left justify.
Now, obviously, you would format this as you prefer, but, for this example, um, I'm going to go with the blue theme. So now you can just
copy this tab over for the remainder of the months. So, to do that, you'll right-click at the bottom...on your tab.
you'll right-click on this, click on Move or Copy, Create a Copy,
then Move to End and click OK.
So, this creates a replica of that first tab. So, now we need to rename this tab to reflect the months that we want.
So, in this case, it'll be February 2017. And, when we do that, you see that your header updates. Now, you need to also
update your days. So, February 2017 started on a Wednesday.
So, we want to delete those first Sunday, Monday, Tuesday. And then on day 1, you want to
actually type 2-1-2017, not just reference the prior month plus 1.
The reason for that is to preserve our Conditional Format so we'll be able to see the current day.
And we scroll down to make sure that it ended on the 28th.
And then you would continue doing this for the remainder of your months for that year. So, once you're done copying over all of the
months, your workbook should look something like this - with each month having its own tab along the bottom.
Now we want to go and check our Conditional Format. This month we're in August 2017. So we'll scroll down and today is
August 28. And you see that the Conditional Format worked; that day is highlighted for us.
Also, don't forget, if you choose to use this calendar electronically, it is fillable, so you'll be able to type in whatever tasks -
so, if you have the dentist, a party, whatever, you can just fill it in right on your calendar. So, to print these, you can either print the current
tab or, if you want to print multiple tabs, you would just highlight them
like I'm doing here. Just Ctrl and click on the tab. And you can do this for the full year, but I've highlighted these 3 tabs. So, if you go
under your File / Print Menu, you see by doing that I have 3 months that will print together. So, I hope you found this tutorial helpful to learn
how to format your month calendar in Excel to create a yearly calendar. If you enjoyed it, please make sure to give it a Like and
don't forget to subscribe to this channel. Thanks!