1
00:00:04,880 --> 00:00:07,640
Let's take a look at the challenge for the chart section.

2
00:00:07,640 --> 00:00:13,210
Our task is to create a treemap for a management overview report.

3
00:00:13,340 --> 00:00:17,780
So management would like to view the following data in treemap format.

4
00:00:17,780 --> 00:00:22,300
Your task is to provide a few options to visualize the data.

5
00:00:22,310 --> 00:00:25,770
One option you test is to show all the details.

6
00:00:25,830 --> 00:00:29,000
So include the division and the names.

7
00:00:29,000 --> 00:00:30,590
Okay so let's just take a look at what we have.

8
00:00:30,590 --> 00:00:33,780
I have division, department, name, compensation.

9
00:00:34,070 --> 00:00:37,080
We have data until line 40.

10
00:00:37,130 --> 00:00:39,030
Let's just see what we get.

11
00:00:39,050 --> 00:00:43,700
If we just highlight this information and add a treemap to it.

12
00:00:43,740 --> 00:00:48,890
So let's go to insert, from the options here,

13
00:00:48,890 --> 00:00:51,180
select treemap.

14
00:00:51,200 --> 00:00:55,470
Now we get this messy treemap.

15
00:00:55,700 --> 00:01:00,020
We have productivity in different colors, utility in different colors.

16
00:01:00,020 --> 00:01:01,640
Doesn't really look good.

17
00:01:01,670 --> 00:01:03,780
What's the problem here?

18
00:01:03,830 --> 00:01:05,360
It's not sorted, right?

19
00:01:05,360 --> 00:01:08,800
Division has utility and productivity then utility again.

20
00:01:08,870 --> 00:01:12,200
Department is all a mess, it's not sorted.

21
00:01:12,200 --> 00:01:16,110
We need to sort our data first.

22
00:01:16,120 --> 00:01:19,640
Now if you forget to sort it like I forgot now, doesn't matter.

23
00:01:19,640 --> 00:01:21,540
We can sort it later.

24
00:01:21,620 --> 00:01:28,460
Let's just right mouse click and go to custom sort because I want to sort division and then

25
00:01:28,550 --> 00:01:31,320
I want to sort by department.

26
00:01:31,370 --> 00:01:36,870
So let's go to custom sort, sort by division first

27
00:01:36,870 --> 00:01:44,130
then let's add another level. And then let's sort by department and that's fine.

28
00:01:44,150 --> 00:01:45,780
Looks much better now.

29
00:01:46,010 --> 00:01:49,940
We just have three different colors because we just have three different divisions.

30
00:01:50,210 --> 00:01:53,600
Productivity, utility, and game.

31
00:01:53,810 --> 00:02:00,460
I'm just going to get rid of this legend right here. And if we want to, we can add banners to this.

32
00:02:00,620 --> 00:02:04,340
Let's just bring up the chart options by using the shortcut key

33
00:02:04,340 --> 00:02:11,590
Control+1 and we can add a banner to it right here. And let's just add a chart title.

34
00:02:11,600 --> 00:02:17,150
Now let's take a closer look at this treemap. Does it look okay to you?

35
00:02:17,150 --> 00:02:25,000
Is this something that management can use and be like: Yeah, I get it, I get what's going on. Not really.

36
00:02:25,000 --> 00:02:31,540
It's not really an overview report because it has the information on division and it has the name.

37
00:02:31,540 --> 00:02:37,720
We could add the compensation in there but it's just too much detail for an overview.

38
00:02:37,720 --> 00:02:40,160
So let's take a look at our other option.

39
00:02:40,180 --> 00:02:42,430
You notice that this might be too detailed for them.

40
00:02:42,430 --> 00:02:49,600
So you decide to create the following. A tree map that shows division and department information only.

41
00:02:49,930 --> 00:02:55,030
Because the management they might not really care about the actual people and their compensation.

42
00:02:55,030 --> 00:03:00,250
They want to know which department is the most costly.

43
00:03:00,250 --> 00:03:05,620
For that it can make sense to just drop one level and just keep these two levels in there

44
00:03:05,620 --> 00:03:08,910
and that's also going to reduce our data set.

45
00:03:08,950 --> 00:03:10,960
So we just want these two.

46
00:03:10,960 --> 00:03:12,150
But here's the thing.

47
00:03:12,160 --> 00:03:14,190
We have finance repeated.

48
00:03:14,200 --> 00:03:16,090
We have procurement sales repeated.

49
00:03:16,300 --> 00:03:24,370
So we basically need to create a second data preparation table and get the sum for finance department

50
00:03:24,370 --> 00:03:27,460
and get the totals for sales department and so on.

51
00:03:27,460 --> 00:03:34,820
Let's do that on the bottom right here. And let's get a unique list out of these two.

52
00:03:34,820 --> 00:03:39,940
I'm just going to copy this and paste it right here.

53
00:03:39,940 --> 00:03:46,260
Actually we should bring the headers as well. So division and department.

54
00:03:46,270 --> 00:03:52,480
Now I want to just keep a unique list of these. So let's highlight again.

55
00:03:53,110 --> 00:03:54,650
Let's go to the data tab

56
00:03:55,360 --> 00:03:58,390
and let's remove any duplicates here.

57
00:03:58,470 --> 00:03:59,800
So division, department.

58
00:03:59,800 --> 00:04:00,210
Yes.

59
00:04:00,220 --> 00:04:00,640
Click on

60
00:04:00,700 --> 00:04:01,510
OK.

61
00:04:01,630 --> 00:04:04,620
22 duplicate values were found and removed.

62
00:04:04,630 --> 00:04:07,360
We have 9 unique values.

63
00:04:07,360 --> 00:04:12,100
And this is all sorted which looks great.

64
00:04:12,130 --> 00:04:17,440
Next thing, is to get the total compensation for each department.

65
00:04:18,040 --> 00:04:20,410
Which formula can we use here?

66
00:04:20,410 --> 00:04:21,570
SUMIFS.

67
00:04:21,899 --> 00:04:22,150
Right?

68
00:04:22,150 --> 00:04:27,380
Let's start off with SUMIFS. My sum range is right here

69
00:04:27,490 --> 00:04:30,380
but we need to fix it with the F4 key.

70
00:04:30,380 --> 00:04:35,240
The criteria_range1. That's division and department.

71
00:04:35,290 --> 00:04:41,740
Let's start with division and let's fix it with F4. The criteria itself

72
00:04:41,740 --> 00:04:43,520
is this one right here.

73
00:04:43,750 --> 00:04:52,540
Next is criteria_range2 which is my department. And fixed with F4 and the criteria itself is

74
00:04:52,600 --> 00:05:00,340
sitting in B45, close bracket, press enter. And send the formula down.

75
00:05:00,910 --> 00:05:06,810
I'm just going to copy this formatting with a format painter to these as well.

76
00:05:07,090 --> 00:05:12,070
Now I have the total compensation by department, by division.

77
00:05:12,220 --> 00:05:23,100
Based on this let's insert a treemap. So go back to insert, tree map, and this looks a lot cleaner.

78
00:05:23,110 --> 00:05:31,150
I'm going to remove the legend, bring up the chart options. You can use Control+1, add a banner to this. And since this

79
00:05:31,150 --> 00:05:40,480
is a lot neater I have enough space to add data labels to this. More data label options and put a checkmark

80
00:05:40,570 --> 00:05:42,190
for value as well.

81
00:05:42,250 --> 00:05:48,520
This way we have finance and the amount that they earn in the Finance Department. And instead of a

82
00:05:48,520 --> 00:05:52,480
comma, if you want we can add a new line to it.

83
00:05:52,540 --> 00:05:57,430
This chart is more like an overview report. Because the moment management takes look at this

84
00:05:57,430 --> 00:06:03,820
they notice that the productivity department has the highest compensation. And it's the finance department

85
00:06:03,940 --> 00:06:08,410
in productivity that is getting paid the most.

86
00:06:08,410 --> 00:06:13,450
This treemap is a better overview report for management than this one.

87
00:06:13,510 --> 00:06:15,680
Which version do you prefer?

88
00:06:15,700 --> 00:06:22,150
I would personally go with the second version. I'll just add a title to it, put in my report and present

89
00:06:22,150 --> 00:06:22,740
that.

90
00:06:23,360 --> 00:06:25,000
So that's it. We did all of this.

91
00:06:25,000 --> 00:06:28,820
I forgot to put the checkmarks. I'm going to do that now.

92
00:06:28,840 --> 00:06:30,990
Challenge is completed.