Hi. In this video, we’re going to learn how to create a calendar in Excel. We’ll walk through an example of a month calendar that you can
recreate for any month, for any year. We’ll create the calendar so that it’s fillable and printable.
In our spreadsheet, the first thing we'll want to do is set our margins. And you'll do this by going up
here to the Page Layout tab and choosing Margins.
Now, I like to set my margins at 1 inch. This setting works well with most printers. And, also, if you later decide to create a pdf of the
calendar, and all you have is the free version of Adobe Acrobat
to make your pdf, 1 inch will be the default margin size. And if your spreadsheet is set to anything
other than that, your pdf won't look just like your spreadsheet.
So, my preference for this is to create the calendar while in Page Layout view. Now, it doesn't really matter which view you
choose. This is just my personal choice; it makes it easier for me,
but you choose what you like. Now, to change your view, just go to the View tab
and click on the view that you want to work in. So, in my case, like I said, we'll be choosing Page Layout.
The next thing we'll want to do is layout our days of the week. Now, I'm going to create this from Sunday to Saturday. If you're looking to
create a workweek calendar, you can skip Saturday and Sunday and then you could just make your columns wider.
So, we'll type Sunday in this first cell and then we'll drag it over
to where your auto fill says Saturday.
...right here... Now, if for whatever reason your spreadsheet does not auto fill the days of the week, you can add add them as a new list by
going to your Options or just type them in. So once we have this
we'll now center them - we'll go back to the Home tab. We'll choose center, we'll bold them and then we'll
give them a little bit of color. Let's make them a dark blue.
So, once you're done typing your days of the week, the next thing you want to do is highlight all of the columns that have days of the week
in them. And then we'll drag, we'll just grab up here, um, between any column, and we'll drag them over
to where the days of the week span the width of your spreadsheet.
And you want to highlight and drag all of your columns at the same time so that they all expand to the same size. You can also set the
width of one column and then copy and, using Paste Special, copy over just your column width, but it's easier to drag your columns
together.
The next thing we'll need to do is to create an area for each week.
Now, rather than enlarge the rows, we'll create boxes around them. And we're going to do this using borders. Now, we're going to do it this
way so that we end up with blank cells in each day that you can use to type in or fill in activities if you choose to use your calendar
electronically.
Now, to make sure we cover the full month, no matter the month, no matter the year, we're going to create space for 6 weeks.
Now, for this example, I already premeasured it before...so, I guess I did a little bit of cheating. But,
for the 1 inch margin sheet that we have,
we need to create
an area for the week that has 7 rows and each row is going to be set at 0.21 inches. So, it'll look something like this -
where I'm highlighting the 7 rows and then I'm right-clicking
and, where you see Row Height,
set it at 0.21,
click OK and then we simply add borders around this.
So we come up here to the Home tab
and choose Outside Borders
and we'll put a border around the day just in this first one.
And then we'll repeat this for each of the weeks.
So, once you're done creating your borders for each day and each week,
your calendar template should look very similar to what I have here on my screen with
the days being divided by the borders that we created. Now, the next thing we need to do is we need to add the day number for each of
these days. And, in this example, we're going to create a January 2017 calendar and January 1, 2017 fell on a Sunday. So we'll need to go to
the first Sunday in our calendar, which we happen to be in, and we'll just type the number 1.
Now, for the next several days we'll just say equals that first number plus 1 and we'll copy this formula to the end of our row.
Now, for the second week, we need to remember to reference the end of the week prior. So we'll come to this Saturday, the 7th,
and say plus 1 and then now we can continue and resume the prior day plus 1.
And we copy this down to the end of this row.
Now, once you have the second week, it's much easier to just highlight those formulas and copy
them for the remainder of the weeks for that month.
And, January only has 31 days, so we need to just delete those other days. And we'll come up here to our Header and we'll type January,
we'll
make this bold and
give it a little bit more size. And
there we are! We've created a fillable calendar for one month! To go over how we can format this calendar so we can extend it for a
full year and also to create a marker for the current day, make sure to watch my follow-up video on How to Format a Calendar in Excel.
And I hope you found this tutorial helpful to learn how to make a calendar in Excel. If you enjoyed it, please make sure to give it a
Like and don’t forget to subscribe to this channel. Thanks!