1
00:00:05,080 --> 00:00:07,900
Let's take a look at the challenge for this section.

2
00:00:07,960 --> 00:00:12,850
We need to hand in this report and we want to format it before we hand it in.

3
00:00:12,870 --> 00:00:15,280
So it's in a nice readable format.

4
00:00:15,280 --> 00:00:16,320
Let's follow the steps.

5
00:00:16,390 --> 00:00:24,520
Number one: Center "game division" and date across the report without merging. So these two are what we

6
00:00:24,520 --> 00:00:26,950
want featured on top.

7
00:00:26,960 --> 00:00:29,930
We want to center it and I'm not going to use merge.

8
00:00:29,950 --> 00:00:34,060
Instead, I'm going to use center across selection.

9
00:00:34,060 --> 00:00:35,650
So report goes until here.

10
00:00:35,650 --> 00:00:38,500
Just going to highlight these two, use the shortcut key

11
00:00:38,500 --> 00:00:46,870
control+1, go to alignment and select center across selection and click on okay.

12
00:00:47,110 --> 00:00:50,150
Both are centered without merging.

13
00:00:50,170 --> 00:00:51,530
So take a look at this.

14
00:00:51,700 --> 00:00:56,970
No information in the formula bar because game division is not actually sitting here.

15
00:00:57,130 --> 00:01:04,780
It's actually sitting in cell A9 and date is sitting in cell A10. But it looks like I've merged it because

16
00:01:04,780 --> 00:01:07,280
I've used center across selection.

17
00:01:07,420 --> 00:01:10,670
That was number one, number one is done.

18
00:01:10,870 --> 00:01:16,720
Number two: Change date format to just show the month and year, no day.

19
00:01:16,720 --> 00:01:18,650
Okay so I'm going to go and format it.

20
00:01:18,700 --> 00:01:20,870
I have to go to where the data is sitting in.

21
00:01:20,900 --> 00:01:22,070
Which is A10.

22
00:01:22,120 --> 00:01:23,550
I'm going to use the shortcut key Control+1.

23
00:01:23,620 --> 00:01:28,200
This time I'll go to number formatting.

24
00:01:28,300 --> 00:01:32,060
Let's scroll down here to see the different options we have.

25
00:01:32,140 --> 00:01:34,180
Here we have the month and the year.

26
00:01:34,180 --> 00:01:36,250
We can select one of these.

27
00:01:36,250 --> 00:01:42,060
I'll go with this one, click on OK, and I have that reformatted.

28
00:01:42,210 --> 00:01:43,550
That was number two.

29
00:01:43,570 --> 00:01:51,880
Number two is done. Number three: Remove the merged headers from actual, previous year, and percentage change

30
00:01:52,360 --> 00:01:55,680
and apply a better technique instead to center them.

31
00:01:55,720 --> 00:02:00,520
We don't want merge, we want to use center across selection.

32
00:02:00,610 --> 00:02:03,950
So I'm just going to highlight these and take away the merge.

33
00:02:04,010 --> 00:02:06,440
Just click on it to de-merge.

34
00:02:06,510 --> 00:02:12,910
Now let's use center across selection. I could just hold under control key and select the different ones.

35
00:02:13,360 --> 00:02:19,840
Or, I could apply it to one and then use the format painter and apply the formatting to the other ones.

36
00:02:19,870 --> 00:02:23,220
I'll just apply it to the first one first. When I like it,

37
00:02:23,320 --> 00:02:25,250
I'll just apply it to the other ones.

38
00:02:25,360 --> 00:02:28,880
So let's go to alignment.

39
00:02:28,900 --> 00:02:35,020
From text alignment, I'm going to select center across selection. So notice that you have to highlight the area you want centered

40
00:02:35,440 --> 00:02:42,970
before you come and select this. Then click on OK, it's centered. Click on format painter two times because

41
00:02:42,970 --> 00:02:49,930
I want it applied more than once and then apply it to both of these. Press escape to stop the formatting.

42
00:02:49,930 --> 00:02:51,080
Number three is done.

43
00:02:51,100 --> 00:02:52,810
Let's put a checkmark there.

44
00:02:52,840 --> 00:02:59,020
Number four: Calculate percentage change and format all numbers accordingly.

45
00:02:59,020 --> 00:03:01,890
So we want to calculate percentage change here.

46
00:03:01,930 --> 00:03:04,080
This is the actual data.

47
00:03:04,090 --> 00:03:05,760
This is previous year data.

48
00:03:06,220 --> 00:03:15,820
I can use a formula that has new, which is actual, divided by old minus 1. Or, I can use another formula.

49
00:03:16,150 --> 00:03:22,280
I can say actual minus old, which is previous year, divided by previous year.

50
00:03:22,300 --> 00:03:22,630
Right.

51
00:03:22,640 --> 00:03:26,110
So actual minus previous year in bracket. It's really up to you

52
00:03:26,110 --> 00:03:27,480
which one you prefer.

53
00:03:27,580 --> 00:03:33,960
I'll go with actual divided by previous year minus 1 and press enter.

54
00:03:34,030 --> 00:03:39,660
Now before I pull this across and down, do I need to fix anything?

55
00:03:39,850 --> 00:03:46,600
I don't, right? Because when I pull this across, I want the referencing to move and when I pull this down

56
00:03:46,930 --> 00:03:48,430
I wanted it to move as well.

57
00:03:48,670 --> 00:03:53,060
We can just double check, click on the last cell here, go to edit mode.

58
00:03:53,260 --> 00:03:54,410
That's this

59
00:03:54,550 --> 00:03:56,700
divided by this minus 1.

60
00:03:56,740 --> 00:03:57,760
That's perfect.

61
00:03:57,760 --> 00:04:02,680
That's our results but we wanted it as percentage format. So highlight the area,

62
00:04:02,710 --> 00:04:08,080
click on percentage and we can add in decimal place if we want to.

63
00:04:08,090 --> 00:04:09,680
I'm just gonna adjust this a little bit.

64
00:04:09,700 --> 00:04:15,850
I'll just make it a bit smaller and change the text to a lighter grey.

65
00:04:15,910 --> 00:04:18,680
That was that, number four is done.

66
00:04:18,730 --> 00:04:25,480
Last one: Format the table by adding light gray borders and special borders for the headers to make

67
00:04:25,480 --> 00:04:26,580
it stand out.

68
00:04:26,620 --> 00:04:33,040
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.

69
00:04:33,280 --> 00:04:36,930
And it's not too gray because it's not really necessary.

70
00:04:37,000 --> 00:04:40,900
It's enough to have light borders. So Control+1.

71
00:04:40,930 --> 00:04:42,690
This time we're going to go to border.

72
00:04:42,880 --> 00:04:46,230
Let's go with a light gray border, click in the middle.

73
00:04:46,240 --> 00:04:50,380
You can also click here and the bottom one and then click on

74
00:04:50,380 --> 00:04:51,260
Ok.

75
00:04:51,370 --> 00:04:55,140
Now we have our light gray borders added to this.

76
00:04:55,180 --> 00:04:59,180
Then the other thing I'm going to do is actually also format this data.

77
00:04:59,410 --> 00:05:02,610
Let's go back to format cells, control+1.

78
00:05:02,710 --> 00:05:04,110
Number formatting.

79
00:05:04,290 --> 00:05:09,420
Use a thousand separator and I'm gonna go with zero decimal places.

80
00:05:09,420 --> 00:05:11,330
Now let's deal with the headers.

81
00:05:11,490 --> 00:05:17,060
So here I'm gonna use a different color for actual previous year and percentage change.

82
00:05:17,070 --> 00:05:20,920
You might already have some color guidelines in your company.

83
00:05:20,940 --> 00:05:23,070
I'll just pick one, for actual

84
00:05:23,070 --> 00:05:26,380
I'll go with a dark blue border.

85
00:05:26,490 --> 00:05:32,130
Click on this and click on the bottom one here and say OK.

86
00:05:32,190 --> 00:05:35,010
I also want it applied to up here.

87
00:05:35,010 --> 00:05:41,910
The great thing is I can use a shortcut key F4. That's going to apply the last formatting I used which

88
00:05:41,910 --> 00:05:45,670
was updating the border here. For previous year,

89
00:05:45,720 --> 00:05:49,080
this time let's highlight this, Control+1.

90
00:05:49,200 --> 00:05:52,570
Let's go with a light blue border.

91
00:05:52,650 --> 00:06:00,610
Select a thick version, click on here, not here and here. And then click on OK and that's previous here.

92
00:06:00,780 --> 00:06:07,200
For percentage change, let's go with green for this one and go with the top and the bottom and click

93
00:06:07,200 --> 00:06:09,440
on OK. That looks better.

94
00:06:09,440 --> 00:06:14,830
I want to make game division and date stand out more. So I'm going to highlight both,

95
00:06:15,030 --> 00:06:17,550
make them bigger and bold.

96
00:06:17,580 --> 00:06:20,160
So that's my report, that looks pretty neat.

97
00:06:20,160 --> 00:06:23,820
Just keep in mind that you don't need to do exactly the type of formatting

98
00:06:23,820 --> 00:06:24,590
I did.

99
00:06:24,600 --> 00:06:31,350
You can apply any type of color, any type of formatting that you see fit to this report. For the purpose

100
00:06:31,350 --> 00:06:32,340
of this challenge,

101
00:06:32,370 --> 00:06:33,220
we're done.

102
00:06:33,240 --> 00:06:34,600
Number 5 is done.

103
00:06:34,650 --> 00:06:36,050
Challenge completed!