Let's take a look at the challenge for this section.
We need to hand in this report and we want to format it before we hand it in.
So it's in a nice readable format.
Let's follow the steps.
Number one: Center "game division" and date across the report without merging. So these two are what we
want featured on top.
We want to center it and I'm not going to use merge.
Instead, I'm going to use center across selection.
So report goes until here.
Just going to highlight these two, use the shortcut key
control+1, go to alignment and select center across selection and click on okay.
Both are centered without merging.
So take a look at this.
No information in the formula bar because game division is not actually sitting here.
It's actually sitting in cell A9 and date is sitting in cell A10. But it looks like I've merged it because
I've used center across selection.
That was number one, number one is done.
Number two: Change date format to just show the month and year, no day.
Okay so I'm going to go and format it.
I have to go to where the data is sitting in.
Which is A10.
I'm going to use the shortcut key Control+1.
This time I'll go to number formatting.
Let's scroll down here to see the different options we have.
Here we have the month and the year.
We can select one of these.
I'll go with this one, click on OK, and I have that reformatted.
That was number two.
Number two is done. Number three: Remove the merged headers from actual, previous year, and percentage change
and apply a better technique instead to center them.
We don't want merge, we want to use center across selection.
So I'm just going to highlight these and take away the merge.
Just click on it to de-merge.
Now let's use center across selection. I could just hold under control key and select the different ones.
Or, I could apply it to one and then use the format painter and apply the formatting to the other ones.
I'll just apply it to the first one first. When I like it,
I'll just apply it to the other ones.
So let's go to alignment.
From text alignment, I'm going to select center across selection. So notice that you have to highlight the area you want centered
before you come and select this. Then click on OK, it's centered. Click on format painter two times because
I want it applied more than once and then apply it to both of these. Press escape to stop the formatting.
Number three is done.
Let's put a checkmark there.
Number four: Calculate percentage change and format all numbers accordingly.
So we want to calculate percentage change here.
This is the actual data.
This is previous year data.
I can use a formula that has new, which is actual, divided by old minus 1. Or, I can use another formula.
I can say actual minus old, which is previous year, divided by previous year.
Right.
So actual minus previous year in bracket. It's really up to you
which one you prefer.
I'll go with actual divided by previous year minus 1 and press enter.
Now before I pull this across and down, do I need to fix anything?
I don't, right? Because when I pull this across, I want the referencing to move and when I pull this down
I wanted it to move as well.
We can just double check, click on the last cell here, go to edit mode.
That's this
divided by this minus 1.
That's perfect.
That's our results but we wanted it as percentage format. So highlight the area,
click on percentage and we can add in decimal place if we want to.
I'm just gonna adjust this a little bit.
I'll just make it a bit smaller and change the text to a lighter grey.
That was that, number four is done.
Last one: Format the table by adding light gray borders and special borders for the headers to make
it stand out.
Usually what I like to do is to go with a very light gray border for the data set so it's easy to read.
And it's not too gray because it's not really necessary.
It's enough to have light borders. So Control+1.
This time we're going to go to border.
Let's go with a light gray border, click in the middle.
You can also click here and the bottom one and then click on
Ok.
Now we have our light gray borders added to this.
Then the other thing I'm going to do is actually also format this data.
Let's go back to format cells, control+1.
Number formatting.
Use a thousand separator and I'm gonna go with zero decimal places.
Now let's deal with the headers.
So here I'm gonna use a different color for actual previous year and percentage change.
You might already have some color guidelines in your company.
I'll just pick one, for actual
I'll go with a dark blue border.
Click on this and click on the bottom one here and say OK.
I also want it applied to up here.
The great thing is I can use a shortcut key F4. That's going to apply the last formatting I used which
was updating the border here. For previous year,
this time let's highlight this, Control+1.
Let's go with a light blue border.
Select a thick version, click on here, not here and here. And then click on OK and that's previous here.
For percentage change, let's go with green for this one and go with the top and the bottom and click
on OK. That looks better.
I want to make game division and date stand out more. So I'm going to highlight both,
make them bigger and bold.
So that's my report, that looks pretty neat.
Just keep in mind that you don't need to do exactly the type of formatting
I did.
You can apply any type of color, any type of formatting that you see fit to this report. For the purpose
of this challenge,
we're done.
Number 5 is done.
Challenge completed!