Hi. In this tutorial we'll go over how to create a calendar using Excel that reveals a picture in the background as the date changes. This is
something that you could use, for example, when counting down to a big event. The first thing we want to do is create a calendar. I've
already started creating one for the current month, which is November 2017 and, FYI, I am in Page Layout View. This is my favorite view
when making calendars, but, obviously, you can use whichever view you like best. I've already done a little bit of pre work on this
calendar. So far, I've set my margins to 1 inch and I've also added the days of the week along the first row of the calendar. Next, we'll want to
have the header reference the sheet name so that we can label the calendar. And we'll make this a little bit bigger...
To begin formatting the weeks for our November calendar, we're going to need 6 rows. So we'll highlight the 6 rows just
beneath the days of the week. And then these 6 rows will need to expand to cover the rest of the page. So I've already premeasured these
to 1.5 inches. So I'm going to just right click and choose Row Height and change this to 1.5 inches.
And to double check, we'll just scroll down to make sure that it fits in 1 page. And it does.
Next, we want to type the date in each cell. Now, I want to format my calendar so that it only shows the day number. To do that we'll
need to highlight all of the cells except for that first row where the days of the week are written. Once you do that, right click and
choose Format Cells. Under Category, choose Custom and then, under Type:, erase what's there and type the letter D. This lets Excel
know that we want to display only the day number. And the reason we want to do Custom formatting is because, currently, under the
Date category there's no preset option for just the day. So, we click OK. Next, we can start typing in the days of the week.
So, November 2017 began on a Wednesday, so we'll type 11-1-2017 on that first Wednesday. And we can drag this over to fill in
the rest of that week. At the beginning of the next week, we have to type in the date for that
first day of the week and then we can drag this over to finish that week. And we'll continue repeating this until we complete the calendar
for November. I want to also change the position of where the date is showing on each cell. So I'm going to highlight everything and, in
the Home tab, under Alignment, I'm going to choose Top Align and then Right Align.
Next, we need to insert our image. So, I'm going to insert my image as part of the Header. You could also use the Footer for this. I
like doing it this way better than using the Background Image button in case I decide to print later.
Using the Background Image button doesn't let you actually print the image. So, I want to insert my image in this middle part of the
Header, where November 2017 currently is. And by placing my cursor in the Header, I get the Header & Footer Tools Design tab.
So, back in this Header, I'm going to Enter a couple of times to add more space. And then, in the Design tab, click on Picture.
And, from here, simply select the image that you'd like to use.
Next, I want to resize my image so it fits a bit better on my calendar page. So, with my cursor back in the Header, I can go into the Design
tab and now I have the option to Format Picture. From the Size tab, I can scale this down. And I'm going to scale it to 55% of its
original size. And I think I still need to trim a little bit, especially here on the right side. So, I can go
into the Picture tab and Crop From / from the Right. I'm going to say -1.5
and that centers it a little bit more. I'm going to Enter one more time
to have a little bit of a bigger gap. And let's scroll down. I'm happy with how this looks. Now that we've set our image, we need to
hide our image. To do that, I'm going to select all of the cells in our calendar month and, going back into the Home tab, in the Fill
options there's any number of colors to choose from or you can do your own custom colors. But I'm going to choose white for this
example. And, when I do that, you see that the image is now hidden and our November 2017 calendar looks very much
like a traditional calendar. And now that we've hidden the image, we need to create a way for the image to again reveal itself as each day in
November goes by. And, to do this, we'll need to insert a couple of Conditional Formatting rules. So, for this example, the big event that
we're waiting for is on November the 28th. So, keeping this in mind, for our first Conditional Formatting rule, we'll highlight all of the cells
from the very first blank cell, before the first of the month, all the way until the last day of the
month, which, for November, will be November the 30th. So, then we go into to Conditional Formatting / Highlight Cells Rules and choose
Between. Here, we'll want to say between 0 and =TODAY()
And for Formatting, we'll choose Custom Format.
And, under the Fill tab, choose No Color and click OK. And what this will do is it will unshade each day up until the current day. So, we
choose OK. The second Conditional Formatting rule that we're going to insert will help us unshade any dates after the goal date
and any blank cells, such as these, that happen to be in our calendar page beyond the goal date. So, what we'll do is we'll go into
Conditional Formatting / Highlight Cells Rules / and More Rules. This time we'll
choose "Use a Formula to Determine Which Cells to Format". And, here, we'll type =TODAY()>=11/28/2017 And then, under
Format, under Fill, we'll say No Color. Click OK. And click OK again. Now, you're not necessarily going to see anything now because
it's not yet November 28th, which is our goal date. But what this formula will do is, once November 28th is the current date, it will
unshade the remaining cells, allowing us to see the full picture behind our calendar. So, those Conditional Formatting rules
can be a little bit tricky. I've included them in the description for this video as well. So, if you
need them, make sure to go back and look for them in the video description. And I hope you found this tutorial helpful to learn how to make
a calendar in Excel that reveals an image. If you enjoyed it, please make sure to give it a Like and don't forget to subscribe to this
channel. Thanks!
Không có nhận xét nào:
Đăng nhận xét