Let's take a look at the challenge for the chart section.
Our task is to create a treemap for a management overview report.
So management would like to view the following data in treemap format.
Your task is to provide a few options to visualize the data.
One option you test is to show all the details.
So include the division and the names.
Okay so let's just take a look at what we have.
I have division, department, name, compensation.
We have data until line 40.
Let's just see what we get.
If we just highlight this information and add a treemap to it.
So let's go to insert, from the options here,
select treemap.
Now we get this messy treemap.
We have productivity in different colors, utility in different colors.
Doesn't really look good.
What's the problem here?
It's not sorted, right?
Division has utility and productivity then utility again.
Department is all a mess, it's not sorted.
We need to sort our data first.
Now if you forget to sort it like I forgot now, doesn't matter.
We can sort it later.
Let's just right mouse click and go to custom sort because I want to sort division and then
I want to sort by department.
So let's go to custom sort, sort by division first
then let's add another level. And then let's sort by department and that's fine.
Looks much better now.
We just have three different colors because we just have three different divisions.
Productivity, utility, and game.
I'm just going to get rid of this legend right here. And if we want to, we can add banners to this.
Let's just bring up the chart options by using the shortcut key
Control+1 and we can add a banner to it right here. And let's just add a chart title.
Now let's take a closer look at this treemap. Does it look okay to you?
Is this something that management can use and be like: Yeah, I get it, I get what's going on. Not really.
It's not really an overview report because it has the information on division and it has the name.
We could add the compensation in there but it's just too much detail for an overview.
So let's take a look at our other option.
You notice that this might be too detailed for them.
So you decide to create the following. A tree map that shows division and department information only.
Because the management they might not really care about the actual people and their compensation.
They want to know which department is the most costly.
For that it can make sense to just drop one level and just keep these two levels in there
and that's also going to reduce our data set.
So we just want these two.
But here's the thing.
We have finance repeated.
We have procurement sales repeated.
So we basically need to create a second data preparation table and get the sum for finance department
and get the totals for sales department and so on.
Let's do that on the bottom right here. And let's get a unique list out of these two.
I'm just going to copy this and paste it right here.
Actually we should bring the headers as well. So division and department.
Now I want to just keep a unique list of these. So let's highlight again.
Let's go to the data tab
and let's remove any duplicates here.
So division, department.
Yes.
Click on
OK.
22 duplicate values were found and removed.
We have 9 unique values.
And this is all sorted which looks great.
Next thing, is to get the total compensation for each department.
Which formula can we use here?
SUMIFS.
Right?
Let's start off with SUMIFS. My sum range is right here
but we need to fix it with the F4 key.
The criteria_range1. That's division and department.
Let's start with division and let's fix it with F4. The criteria itself
is this one right here.
Next is criteria_range2 which is my department. And fixed with F4 and the criteria itself is
sitting in B45, close bracket, press enter. And send the formula down.
I'm just going to copy this formatting with a format painter to these as well.
Now I have the total compensation by department, by division.
Based on this let's insert a treemap. So go back to insert, tree map, and this looks a lot cleaner.
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
is a lot neater I have enough space to add data labels to this. More data label options and put a checkmark
for value as well.
This way we have finance and the amount that they earn in the Finance Department. And instead of a
comma, if you want we can add a new line to it.
This chart is more like an overview report. Because the moment management takes look at this
they notice that the productivity department has the highest compensation. And it's the finance department
in productivity that is getting paid the most.
This treemap is a better overview report for management than this one.
Which version do you prefer?
I would personally go with the second version. I'll just add a title to it, put in my report and present
that.
So that's it. We did all of this.
I forgot to put the checkmarks. I'm going to do that now.
Challenge is completed.