Manage Advanced Charts

0:00Make sure you've opened up the file that I've given you that accompanies this

0:03skill and let's imagine on this first sheet here we want to plot this data on

0:08a chart and our first general principle that you might remember from the

0:12previous

0:12course the MO 210 is that we do select the titles in our data that's how Excel

0:18can label the chart but we don't generally select the totals because those

0:21numbers are going to be much bigger than the other numbers and that's going to

0:24distort the chart so if we wanted to plot this data on a chart we would

0:27select that lot but I would actually like you to imagine for this example we

0:31only want to plot the pale blue shaded cells on a chart so don't forget to

0:36select data that's not next to each other in the chart you need to hold down

0:39your control key on the keyboard and also here for this example I do actually

0:44still need to select the titles for the years so that Excel labels the axes

0:48correctly so I'm going to hold down my control key again and I've started my

0:52selection from this blank cell here that's to make the shape of the selection

0:57match the other selections so that it lines up properly I think in more recent

1:01versions of Excel it's a bit clever at lining up the data but just to be sure I

1:05did start my selection for that top row with the blank cell and then on the

1:09insert

1:09ribbon tab you might remember we could choose recommended charts which will

1:13give us previews for some of the different chart types or as we will do

1:17throughout the rest of this skill you pick a particular chart type from the

1:21vast array just here so let's just quickly for example choose a straightforward

1:252D

1:25column chart you will then remember when you are clicked on your chart you get

1:30two dedicated ribbon tabs appearing so chart design allows you to change the

1:35appearance of the chart we can change the colors and whatnot so we talked about

1:39many of those options but the options that I do also enjoy with these buttons

1:43just here to change the chart so if I click on the plus and think to myself

1:47no I don't want the chart titles let's switch that off but I would like for

1:52example to add some more grid lines let's add some yeah let's add some vertical

1:56grid lines there we go and also let's just put for example or what else should

2:01we do let's put the legend for example down the you know down the right-hand

2:05side but then also if we want to make more specific changes to any element of

2:10the chart well the golden rule was the general principle is you select the

2:15thing

2:15you want to change so let's imagine this y-axis I want it to go up to not the

2:20automatically generated 600 as the maximum I want it to go up to a thousand

2:25for whatever reason well the rule is you select the thing you want to change

2:28and

2:28then what I tend to prefer to do is right click to say please format that thing

2:33so I'm formatting the axis and it's going to be this bottom option here that

2:37opens up the task pane at the right hand side where I can change the maximum to

2:41a thousand rather than 600 so I'm just going to press tab to move on to kind of

2:46input that and then close down that task pane and you can now see I've made

2:50that change to to the y-axis there if you're feeling confident then a quicker

2:54and even quicker way of doing that would be to double click on the thing you

2:58want

2:58to change and that will open up the formatting options for that thing but in

3:02my opinion a slightly more controlled way of doing it is select the thing first

3:05and then right click and choose to format it but anyway that was just a very

3:09quick reminder of some general principles that we're going to be using

3:12throughout the rest of this skill I'm just going to resize this chart and pop

3:16it down here and let's crack on with some other more interesting examples

3:20working through many of the sheets in this workbook

0:00You'll have seen in the little bit of text before this video, I said something

0:04along the lines of,

0:05"In this video, we will learn some useful options for plotting series of

0:09different magnitudes on the same chart."

0:11And you might be thinking, "Well, what's that all about?"

0:13Well, we're going to try this data just here, and, you know, spoiler alert, we

0:18can see that the Europe figures are much smaller than the US figures.

0:22So that does mean if we were to plot both of these two data series on the same

0:26chart, and again just, let's stick with a straightforward 2D chart.

0:29Well, it's a bit hopeless, isn't it? Because the US figures shown in the orange

0:32here are so huge that that's distorting the whole chart,

0:35so we can't accurately read the other series, the Europe figures, off the chart

0:40because they're so tiny in comparison.

0:42So this is where it's very useful to use dual axes and combo charts, so that's

0:47exactly what we're going to do.

0:49So having started, you know, with this column chart, let's see what we can do

0:53to improve the readability.

0:55And the first thing I'm going to do is very carefully select the Europe series.

0:59So just one click, you can see the selection handles are appearing on all of

1:02the series there, and then you know the rule, I'm going to right click to

1:06choose to format that data series.

1:08And the option I'm going to select is to plot this particular series, the

1:12Europe figures, on a secondary axis.

1:15That means I can use a different scale on that secondary axis because these

1:19little figures here don't work on this great big scale that we're currently

1:23using for the US figures.

1:24So yes, let's choose a secondary axis.

1:27But unfortunately, that hasn't really helped us.

1:29It still looks very peculiar and frankly not yet that readable.

1:33So really the best option for us here is up on the chart design ribbon tab is

1:38to choose this change chart type button.

1:42And this is where you will see the list of all of the different chart types,

1:45many of which we will look at in this skill, but down the bottom we've got a

1:49combo chart.

1:50And it's already showing that Europe is plotted on a secondary axis.

1:54And in fact, rather than putting the Europe data series on a secondary axis

1:58using the task pane at the right hand side like we did a second ago, we could

2:02have just checked this box here directly within this dialogue box to have done

2:06that.

2:07But the extra thing I want us to do in this combo chart section here is to say,

2:12well, having both of these series shown as cluster columns, it doesn't look

2:17that great.

2:18Let's change Europe to instead show as a line chart.

2:23So this is why it's called a combo chart. We are mixing two different chart

2:26types.

2:27We're mixing a clustered column with a line chart and we do that to improve

2:30readability.

2:32So that does look a lot better.

2:34The Europe figures are shown as a line there reading off this secondary axis on

2:38the right hand side.

2:40The US figures are shown as the column chart, the orange blocks reading the

2:44axis off the left hand side.

2:46So if I click OK to that, and that is looking good.

2:49Let me just close down that task pane.

2:51But there are just one or two things that I'd like us to do to improve the read

2:54ability of this chart.

2:56So in order to make it very clear that these orange figures are using the axis

3:01values on the left hand side, I am going to double click to format those

3:07figures.

3:08So we know that the double click will give us all of the options available to

3:11format the axis.

3:12And it's the text options.

3:14I want to change the fill color of those figures to orange so that it more

3:19obviously matches up with the orange blocks so that people know to read the

3:23orange blocks off the orange axis.

3:25And let's just quickly do the same for the other side there.

3:27So these axis figures, well, let's make them the dark blue color.

3:31Not quite as visually obvious, but yeah, we can see that those figures have now

3:35gone blue.

3:36And then one last thing just to quickly do, let's imagine now we'd like the

3:40chart title to say customer returns.

3:43And of course you could click in here and manually type in customer returns.

3:47But let's imagine we actually want the chart title to automatically reflect

3:51whatever is in cell I too just here.

3:55Well, we can do that.

3:56We can link that chart title to a cell.

3:58So just click the chart title and then up in the formula bar, type your equals

4:03and then just click on the cell.

4:04You want it to reflect.

4:05You can see the reference here.

4:07The name of the sheet is in quotes with the exclamation point and then the cell

4:11reference with the little dollar symbols there to fix the reference of the cell

4:15I clicked on.

4:16So if you just press enter now, you'll see that that does reflect the contents

4:20of cell I too.

4:22The beauty being, of course, if I change what's in cell I too, so let's change

4:25that so it just says returns rather than customer returns then.

4:29Yep, the chart title will also reflect that.

4:31Perfect.

4:32So a good use of a combo chart with a secondary access to improve readability

4:36with that little bonus tip there of linking the chart title to a cell.

0:00It's not uncommon for people to have a play around with the tree map and the

0:04sunburst charts

0:05and then find they can't make head or tail of what these charts are supposed to

0:08do.

0:09And I have to say your success with them is going to depend on you having the

0:12right type of data.

0:14More specifically, it depends on you having what is described as hierarchical

0:19data.

0:19You can see that these are described as hierarchy charts.

0:22So if you take yourself to the next sheet along in this workbook,

0:25tree map and sunburst and make sure you're scrolled up to the top because there

0:28are two blocks of data,

0:29but let's start with the top one.

0:31And these are some examples of hierarchical data.

0:34So this first one, let me talk you through it.

0:36Let's imagine that you've got some kind of food truck that you travel around in

0:40and you sell lunches to people.

0:42And you've got data here of the revenue generated by the different lunch

0:46offerings that you produce from this food truck.

0:48And it's divided up by category.

0:50So kind of at the parent level, we've got hot food or cold food.

0:54And then within hot food, well, we sell hot sandwiches and we also sell hot

0:58pastries

0:58and we sell hot soup.

0:59But within the hot sandwiches subcategory, we've got further sub subcategories,

1:03open eyes, burritos or toasts.

1:05And you'll see that each lunch item that I sell has got a revenue value

1:09attached to it.

1:11So if I wanted to see, for example, how the sales of, I don't know, pasties

1:15compared to the sales of,

1:17I don't know, cold sandwiches, it's not that easy to tell from this data,

1:21but the tree map chart is one of the hierarchy charts might help us out.

1:25So I'm just going to press control A to select this block of data.

1:28And let's choose, yeah, we'll start off with a tree map chart.

1:32And what it's showing you here is that all of the blue-colored rectangles

1:36are showing the sales of hot food.

1:38So that's the top part of the data here.

1:40And the orangey color blocks are showing the sales of the cold food.

1:43And the relative sizes of each rectangle shows the value in the revenue column.

1:47So I can see at a glance here that the sales of cold sandwiches,

1:51so the sandwich is rectangle here in the, well, you can see it's orange,

1:54so it's in the cold parent category.

1:56So the sales of sandwiches in terms of revenue, they generate far exceeds

2:00the revenue we get from selling pasties or pasta pots.

2:03So that's what it's showing us here.

2:04There are a couple of options that's worth considering though.

2:08So I don't like the way it says hot and cold within the main block

2:14of each of the two series here because we've also got a legend.

2:17So there are a couple of things we might want to change about this.

2:20So I'm going to right click on this blue data series here and choose to format

2:24it.

2:25And rather than having the label of the series, which is hot, overlapping the

2:30actual,

2:30you know, first block, I'm going to have it as a banner.

2:33So if I just close down that task pane now, I think that looks better.

2:37I can now see clearly that all of the blue blocks are showing the hot category

2:40and the orange blocks are showing cold.

2:42That means I don't really need this legend anymore, do I?

2:44So let's just, in fact, let's turn off the chart title and the legend.

2:47But yeah, that has accurately reflected the revenue generated by all of these

2:52different

2:53lunch products that I sell. Let me make it a bit bigger sideways so that the

2:57blocks

2:57are big enough to show the text labels for each of the products I sell.

3:01Perfect. Let's just scroll down and take a look at this other block of data.

3:06And what's interesting about this block of data, again, you can see it is

3:09hierarchical.

3:10We've got years and then quarters and then months, but actually it's only the

3:162025

3:17parent category that's actually got any breakdown by month.

3:21So if I were to plot this just on a regular tree map chart,

3:25so let's just go back and choose tree map, I don't really get the visibility to

3:29see

3:30that the green blocks here for 2025, well, yeah, that shows some breakdowns by

3:35month,

3:35but these don't. It's kind of okay.

3:38But actually, this is a good example of where the sunburst variation of the

3:43hierarchical chart

3:44would work, I think a bit better. So let's change the chart type to the sun

3:47burst chart.

3:49And I like this. I think it shows us quite well that it's the 2025 series,

3:55you know, the green data here that's kind of exploded out into another level of

3:59detail

4:00with the months because it's showing this extra ring.

4:02But the principles are still the same. It's hierarchical data, so we can see

4:07the parent category

4:08in the inner ring there. It's then broken down into subcategories with the next

4:12ring

4:12and for 2025 only, that's further broken down again into another subcategory,

4:17in this case months, in the outer ring. And it's very obvious that that extra

4:21level of

4:22subdivision is only present for the green series because it's kind of displayed

4:26in this different

4:27manner in the sunburst chart. Similar to the tree map though, the actual size

4:32of the different

4:33wedges reflects the value in the, you know, in column E here from the original

4:37data.

4:37So I can see at a glance, for example, for 2025, that November had a bigger

4:42figure,

4:44a bigger value than December or October, or I can see, for example, in 2023

4:48that Q2,

4:49because it's a bigger block there, is a bigger value than we would have

4:52generated in Q4 or Q3 or

4:54whatever. So these chart types are not going to be suitable for every type of

4:58data. But if your

5:00data is organized into parent categories and then subcategories in some kind of

5:04hierarchical format,

5:06then a sunburst chart like this or a tree map might be a useful option for you.

0:00I'm grouping together histograms, Pareto charts and box and whisker charts all

0:05together into the same video because they are all statistical chart types which

0:10are going to help us to understand how our data is distributed across a range.

0:15Now I have to say if you are a professional statistician you will probably want

0:18to research further into these specific formulas and whatnot that Excel is

0:22using in these charts.

0:24But my goal in this video is for us to work through an example of each in order

0:27to get you started. So take yourself to the next sheet along in this workbook,

0:32histogram and Pareto.

0:34And looking at this example data here you can see that for each sale that we've

0:38made in the last month or whatever it is, we have captured the customer age,

0:44the reason that they made the purchase and the value of the purchase.

0:48And you can also say I've got a count column but don't worry too much about

0:50that right now, we can ignore that so that's going to come into play a bit

0:53later on.

0:54But let's start off by saying that we want to understand the ages of our

0:58customer.

0:59You know what's the most popular age bracket that our customers fall into?

1:03And we need to know that because that's going to help us with our marketing and

1:06it's going to help us to understand our customer base better.

1:09So we're going to start off with just a straightforward histogram of customer

1:12ages.

1:13So I need to select just this data here, so I'm clicked in the heading and then

1:18control shift down arrow, we'll select all the way to the end of the data in

1:22that column.

1:23So I've selected the appropriate data, go to the insert ribbon tab, choose,

1:26here are the statistical chart types, just choose a straightforward histogram.

1:31And you can see here that the histogram has grouped the overall age range.

1:37Now the overall age range goes from 19 to 99, so that's the overall range of

1:41ages that our customer base represents from this data here.

1:45And it has grouped them into various age brackets, but the correct term is

1:49actually bins.

1:51So a histogram is a statistical chart type which shows the distribution of our

1:56data grouped into bins and bins are simply groupings of data points within the

2:02range.

2:03Now these bins have been automatically calculated, so you can see the first bin

2:07or age bracket is 19 to 35, then it goes 35 to 51, and then 51 to 67, etc.

2:13But I can control this if I want to make changes to that, and the key thing to

2:18know here is that the bin widths are a property of the x axis along the bottom

2:23here.

2:24So if you double click on that x axis or right click and choose to format the

2:28axis, that's where you'll see the axis options, which includes the bin options.

2:34So at the moment it's obviously automatic, and if you are interested,

2:37apparently Excel uses Scott's normal reference rule to actually calculate the

2:41automatic bins.

2:43But no, I want to have specific bin widths according to what makes sense for my

2:46organisation. I would like the age brackets to go in, you know, kind of like 15

2:51year chunks.

2:52So let's change the bin width there to show 15. And I also want to have an

2:56overflow bin for everybody over the age of 80.

3:01And an underflow bin is going to be for everybody under the age of 20, and as I

3:05make these changes, and I need to press tab just to input that last one there,

3:10you will see the chart updated.

3:12So you can see now the age brackets or the bins go less than 20, and then 20 to

3:1835, 35 to 50, etc. all the way up to over 80.

3:22So that's our overflow bin there, our underflow bin there, and then the bins

3:27which were the width that I entered of 15 in the middle there.

3:32So that is looking really good. So I can now see that the most popular or the

3:36most popular bin or age bracket is the age bracket 35 to 50.

3:40The greatest number of customers from our data falls into that particular bin.

3:46So all good to know.

3:47But I'm going to delete that and let's try another example. So this time, let's

3:51say I want to understand what's the most common reason that people buy from us.

3:55So we understand how, you know, what's the most common age ranges, but what's

3:58the most common reason that people buy from us.

4:01So this time I want to put a histogram for this column here. So I'm going to

4:04select that column.

4:06So again, I've clicked in the top cell, control shift down arrow to select all

4:09the way to the end of the populated data in that column.

4:13Let's go to insert. Let's go again to a histogram, and this is where I'm a bit

4:17disappointed.

4:19And this is why we've got this extra count column, because when you're trying

4:23to create a histogram out of data that isn't numerical,

4:27it has a bit more of a problem trying to organize the bins, because it's not

4:30numerical data.

4:32I mean, with the ages, it had no problem there, because it could sort that out,

4:35but it's not going to work for this data.

4:37So what we need to do, that's why I've got this extra count column.

4:41I'm just going to hold down my control key to select the count heading and then

4:44control shift down arrow to make sure I've got both of those two columns

4:47selected.

4:48Now let's insert the histogram again. And I'm not yet too optimistic, but if we

4:54go back into the options for that X axis, which is where we saw the bin options

5:00,

5:00it was an axis option for the horizontal axis there. So there we go chart

5:04options, axis options.

5:06So rather than saying automatically or specifying a bin width, I'm going to say

5:10buy category.

5:11And we have to choose that option when the thing that we're plotting, if you

5:16like, is text based categories rather than numerical data.

5:20And you can see that the most popular reason that people purchase from us is

5:24reason type D, whatever that might be.

5:27I don't know as a gift or for personal use or whatever, but reason D for

5:31purchasing is the most popular reason people buy from us.

5:34And again, that's going to be useful for us to know in terms of our marketing

5:37and to help us understand our customers better.

5:40Now hold that thought, if reason D is the most common or the most popular

5:44reason that people buy from us, we've also actually got the value of each

5:49purchase.

5:51So while column D is the most popular or the most common reason people buy from

5:55us, is it actually the most valuable?

5:58So remember that column D or reason D rather is the most popular reason I'm

6:02going to get rid of this.

6:04And instead we're going to plot another histogram, but this time reason for

6:09purchase with its value.

6:11So control, shift, down, arrow to select all the way to the end of the column.

6:15And let's go back in again and create another histogram and let's again check

6:20the axis options just here and make sure it's by category.

6:25But this time we can see that the most valuable reason people buy from us, so

6:30take into account the purchase price rather than simply account.

6:34You can see it's this time reason A. So that's showing us a different spin on

6:38our data.

6:39So if reason A is, I don't know, purchasing on behalf of a client, whatever

6:42reason A might be, then that's very interesting for us to know that that's the

6:47most valuable reason that people buy from us.

6:50So this is the sort of information that histograms can tell us.

6:53And just to show you what a Pareto chart looks like, well I'm simply going to

6:58change this chart type to a Pareto chart.

7:02So within the histogram category just here, the Pareto chart is kind of a sub

7:07type of histogram if you like.

7:09And a Pareto chart is often described as a sorted histogram.

7:14And you will see, when I just move this to one side, you can see that the

7:18original histogram, it went D A blah blah blah.

7:21But when I click OK to turn it into a Pareto chart, can you see that it has re

7:26organized the blocks here into the highest one first?

7:31So that's why the Pareto chart is often described as a sorted histogram because

7:35it's literally sorted it by most valuable or the highest value first.

7:40And this red line shows, well this is called the Pareto line, but this red line

7:44shows the cumulative total.

7:46So it's showing how all of these blocks end up adding up to the grand total of

7:50the numerical column here, the purchase price.

7:54Let's finish up this video with another statistical chart and that's the Box

7:58and Whiska chart.

8:00And this again, like a histogram is going to show us the distribution of our

8:03data, but this is much better optimized if you want to compare multiple data

8:07sets.

8:09So how the range of data differs across different data sets and I do realize it

8:12's much easier to just show you than try and explain it.

8:16But what you can see here, we've got sales values, you know, a sales record.

8:20So we've got the ID, the data, the sale, the category the sale was made in and

8:23the value of the sale.

8:25So if I want to see how our figures are distributed across a range within each

8:30category, then that's where a Box and Whiska chart can help us out.

8:34So let me select the category heading and shift right arrow to also select the

8:39value heading and then control shift down arrow to select all of those two

8:43columns there.

8:45And then go to the insert ribbon tab and choose again from the statistical drop

8:49down here, but this time choose Box and Whiska.

8:52And what you will see here is the four categories and you can see how the data

8:57is spread across a range within those four categories.

9:02Now, the little X that you can see, which is quite faint because it's kind of

9:06like a darkish blue on top of a mid blue there, but the little X in the box of

9:10each category here represents the mean value of all of the data in that

9:14category.

9:16So that value there, that X represents the mean value of values that have the

9:20furniture category assigned.

9:23And then the bottom of this Whiska, so these lines sticking out of the box

9:26there, the Whiskas, but the bottom of the Whiska up to the Box is the first

9:31quartile of our data.

9:33And when we talk about quartiles, we're talking about 25%.

9:36So four quartiles make up the entirety of our data, you know, four, twenty five

9:40percent, makes up a hundred percent.

9:43So the first quartile, the first twenty five percent of the figures in the

9:47furniture category fall in that range there.

9:50And then the next quartile is the first half of the box up to the line.

9:54The next quartile is the second half of the box and then the final quartile is

9:57the box up to the top of that Whiska.

10:00So the overall range there is from the bottom Whiska all the way up to the top

10:05Whiska.

10:06And these little dots that you can see here for gifts and accessories, these

10:10are what we describe as outliers.

10:12So these values are deemed to be beyond the range of the data.

10:17And as you might imagine, there is a statistical calculation going on to

10:21determine these thresholds.

10:23So there we have it, the Box and Whiska chart or the Pareto chart or a

10:26straightforward histogram.

10:28There are all statistical chart types to help us understand the distribution of

10:32our data.

0:00The three chart types that we'll try out in this video, I've grouped together

0:04because

0:04they all tend to get used for financial or business information.

0:08They are a bit specialised, but you know the drill, we will try an example of

0:12each and

0:12you can see what you think.

0:14So we'll start off with the waterfall chart.

0:16So take yourself on to the next sheet along in your workbook and you can see I

0:20've got

0:20an income statement here and you can see that this data is showing some kind of

0:24starting

0:25some of money.

0:26So this is my gross revenue for my business and then in red we've got various

0:31deductions.

0:32So there's my gross revenue.

0:34We take off some adjustments that gives us a new total doesn't it, you know a

0:38net revenue.

0:39So it's the gross revenue minus the adjustments and then we've got other

0:43deductions and then

0:44another subtotal, more deductions, another subtotal etc.

0:48And it's the fact that we've got these subtotals which for ease of reference I

0:52've shaded in

0:52blue here but these subtotals mean that this is going to be suitable for a

0:57waterfall chart.

0:59And I have to say these aren't I don't think terribly intuitive to use but I

1:03can show you

1:03how we can make them work.

1:05So select this data and then on the insert ribbon tab as usual go and choose

1:09the appropriate

1:10chart type.

1:11So this is the category here for some of these chart types.

1:14Waterfall is the one we want and at first glance you think oh my goodness that

1:17looks absolutely

1:17dreadful.

1:18What's going on here?

1:20So what's it done?

1:21Well it has plotted each of these values on the chart and you can see all of

1:25the ones in

1:26the dark blue color these blocks are marked as increases and the orangey colors

1:31as are

1:31marked as decreases.

1:34But hang on a minute if you look at this figure here so this is the 364-239

1:40value which is

1:41one of our subtotals.

1:43So that rather than being shown as an increase as the legend would suggest that

1:47should be

1:48marked as a total because it is a subtotal in this you know in this figures

1:52here.

1:53So if you click on the series click again to select just that one data point

1:57and then

1:58right click to set it as a total.

2:01And we need to do that for the other subtotals which have been incorrectly on

2:06the waterfall

2:07chart been marked as increases.

2:09So the gross income here this blue shaded cell the 240,000 that's that one

2:13there that

2:14should also be marked as a total or a subtotal I suppose strictly speaking.

2:18The same for this one here that's the operating income that's the you know that

2:22figure there

2:23that should be another subtotal and then again that one at the end there marked

2:27that as a

2:27total.

2:28So that if I just click away so that I don't have an individual data point

2:32selected that

2:33is exactly what a waterfall chart is supposed to look like.

2:37Let me get rid of all the data labels so we can see it a bit more clearly but

2:40the idea

2:41is we have an opening sum of money that big blue column there that's the gross

2:47revenue.

2:48We take away a small amount that's the that's the little orange bit there which

2:51is a decrease

2:52to give us a new total which is that green subtotal there.

2:56We take off a bit more take off a bit more that gives us new subtotal take off

2:59a bit

2:59more a bit more a new subtotal etc.

3:02So it's a waterfall chart because it's kind of rippling downwards nibbling away

3:06to give

3:06us you know subtotals according to the figures we've got in the income

3:10statement.

3:11So as I say quite specialised but that's how it works the key thing I'll say it

3:15again

3:15where you've got subtotals you need to select the data point and right click to

3:19mark it as

3:20a total and you can see because this is already a total I get the option to

3:24clear it as a

3:24total.

3:25So if you create these income statements and they don't look as you expect them

3:28to look

3:28make sure your subtotals are marked as totals.

3:32The next one on the list is funnel chart and these are pretty straightforward I

3:36think

3:36you would use one to show progressively smaller stages in a process so when you

3:41've got values

3:42which are gradually getting smaller and the obvious example when it comes to

3:45talking about

3:46financial or business figures is going to be some kind of sales pipeline.

3:50So as you'll see when we try this is really straightforward so I've selected

3:53the figures

3:54there which are showing the number of items we've got in the lead stage or

3:57prospect stage

3:58etc etc.

3:59So it's a sales pipeline.

4:01Let's choose from that same category there let's choose the funnel and yeah it

4:06's just

4:06showing a nice graphical depictation of those figures in a funnel you know the

4:11figures getting

4:12progressively smaller as the items progress through the sales pipeline.

4:17Now the stock charts the final example for this video these are interesting and

4:22they

4:22are designed to show stock market information so the trend of a stocks

4:26performance over

4:28time so very specific financial application here and there are a few different

4:32types and

4:32the one you choose will depend on what data that you've got.

4:36So looking at the data I've got here so over five days in May I was recording

4:41for a particular

4:42stock how much volume was traded what the opening price of the stock was what

4:47the highest

4:48price throughout the day was what the lowest price throughout the day was and

4:51what the

4:51closing price was and I recorded that information for the same stock over you

4:56know over these

4:56five days.

4:57So if I want that information plotted on a chart that's what I would use a

5:02stock chart

5:02for so click the drop down again and you will notice that there are a number of

5:06different

5:07stock charts depending on what your data is showing.

5:10So this first one for example will only show you the highest figure the lowest

5:14figure and

5:14the closing figure of a stock.

5:16Well I've got you know volume traded and extra things as well so the next one

5:20will show

5:20you open high low close the next one will show you the volume traded and the

5:24high and

5:25the low and the close but the final one which is the one I want is the volume

5:29traded the

5:29opening price the highest price the lowest price and the close price.

5:33So if I choose that well I look at that and it doesn't look that great but

5:38normally your

5:39first port of call when something doesn't look as you expect is to switch the

5:43row and

5:43column do you remember this button here which kind of turns the whole chart

5:47inside out.

5:47So if I choose that now that is looking better you can see that each column or

5:52each you know

5:53vertical aspect of this chart is representing a day so each row of my original

5:59data is being

6:00reflected by each column here so that's looking good and the idea is these blue

6:04columns reflect

6:05the volume traded on that day of that stock so that value there this blue

6:10column for the

6:1112th of May is representing that figure there so that's looking good you read

6:16these blue

6:17columns off the left hand axis but this is an example of a dual axis chart

6:21because we

6:22have another secondary axis on the right hand side showing a different scale

6:26and that scale

6:27is what we read these sort of box and whisker type of things off of so the idea

6:33with these

6:33little box and whiskers things well if the box is black then that means the

6:38stock closed at a

6:39lower price than it opened whereas if it is white it's closer to high price

6:44than it opened so that

6:46means the top and bottom of the box represents the opening all the closing

6:49values depending on

6:50whether the box is black or white and then the whiskery things well that

6:54extends to the lowest

6:56and the highest values which are obviously the high and low values from the

6:59data here so as I

7:01say really quite specialized but it is a really rather clever way of depicting

7:05an awful lot of

7:06information visually on the same chart and that makes it much more meaningful

7:10to start understanding

7:12trends rather than trying to look at a load of figures like this and making

7:15sense of it the

7:16stock chart will do it all for you so there we go three quite specialist chart

7:21types there which

7:23much like the other chart types we're exploring in this skill only really makes

7:27sense if you've got

7:28the right kind of data for them.

0:00As if the chart types that we've already looked at in this skill aren't enough,

0:04there are several more chart types that we haven't yet seen examples of. So in

0:08this video I'd like us to work through an example of each of a scatter chart,

0:12bubble chart, surface chart, radar chart and map chart. We'll probably spend

0:17the

0:17longest amount of time on the scatter chart and then I want to be really

0:20quite quick with some of these other ones because they are not specifically

0:23called out on the exam outline but I think it is still going to be useful for

0:27you to have an example of each of these chart types. So this is the example

0:31data

0:31we will use for a scatter chart. A scatter chart is designed to help you see if

0:36there's any correlation between two sets of values. So looking at this

0:40particular

0:40scenario I would like you to take yourself back to your school days and

0:44let's imagine that your math teacher has said to the class, okay we're gonna

0:48see

0:48if there's any correlation between the average noise outside the school, you

0:53know,

0:53is there a correlation between the volume of the noise and the number of

0:56vehicles that are driving past the school. So we're going to record for each

0:59hour

0:59in the day what the sound is, what the average decibels of the noise is and how

1:03many vehicles they were and of course you already know that we are expecting

1:07there to be a strong correlation whereby the, you know, the greater the number

1:12of

1:12vehicles there are driving past the school the greater the amount of noise.

1:15So we're already expecting because it's just an example scenario, a correlation

1:19between these two sets of figures but let's plot it on a scatter chart. So if

1:23you go to the insert ribbon tab and you will also notice that I've broken one

1:27of

1:27my own golden rules and I have not selected the titles. Normally we like

1:32to select the titles in our data so that Excel labels the charts properly but

1:36scatter charts do work better if you just select the two sets of data that you

1:41want to plot on the scatter chart and we can work with the, you know, labeling

1:45and whatnot afterwards. So having selected the figures click the drop down

1:49here to insert just a regular scatter chart and you can already see because

1:54of the position of the dots there that it does look like the dots are kind of

1:58forming a straight line there in fact we could add a trend line couldn't we?

2:01And

2:01yeah that's looking pretty good. So yes unsurprisingly there does seem to be a

2:06correlation between these two sets of values that's what the straight line is

2:10telling us and each blob on the chart there is showing so for example this

2:15first blob or data point is showing an average decibels of 22 and you know the

2:21number of vehicle of 10 so that first blob there must be the 6am value because

2:25those are the two values that are dictating where the position of that is.

2:29But it's going to be helpful if we can label the axes and these data points so

2:33we know exactly what we're looking at. Now I'm going to just delete the chart

2:36title because we're going to be adding all sorts of other bits and pieces but

2:39let's add some axis titles. So let me just switch those on. So along the

2:44bottom we've got the average decibels so I'm going to use that trick we already

2:48know about to pick up you know a value from a cell so average decibels along

2:54the bottom so that means up the side we've got the number of vehicles. There we

2:58go. And what about these data points? It might be useful I think to have these

3:03indicating the time of day that the measurements were taken from so let's

3:07add some data labels and let's add them as a data callout. Now at the moment

3:13you

3:13can see that these little callouts here while they're quite useful because it

3:16gives us a little bubble there to point at the data label which means we can

3:20kind of move them around and adjust them to make sure we can see what's going

3:23on.

3:23I would rather the actual data it was labeling it with rather than the X and Y

3:28values I'd rather it picked up the time of day that the data point was taken

3:32from. So let's dive into some more options for our data labels and let's

3:38pull out a value from some other cells the other cells being of course the

3:43times.

3:43So let's check that box and it's saying well where are these other cells? Well

3:47it's this lot here please so click okay so there we go 6 a.m. with the two

3:52values.

3:52Now let's turn off the X and Y value from the data labels because we can read

3:57that off the chart. Now it's looking a bit busy so let's stretch this a little

4:02bit and you can take a bit of time if you want to adjusting each individual

4:06data label so you can see clearly but let's just quickly move a couple out the

4:10way so that we can check that this is showing us the data we expect. So for

4:14example there we go. This data point up here is the 8 a.m. figure so 8 a.m. we

4:20're

4:20expecting an average decibels of 80 so 80 yes so reading off the the X axis

4:26there is 80 and the number of vehicles 161 so reading the value of the Y axis

4:31yes so the 8 a.m. figure is up there according to its X and Y values and it's

4:37got the correct data label. I'll leave you to fiddle around and adjust all of

4:41these data labels to make sure that you can read them properly if you choose to

4:44but yeah that's a nice example I think of a scatter chart. Next up we have a

4:49bubble chart. Now a bubble chart is well it's kind of similar to a scatter

4:54chart

4:55but whereby with the scatter chart we were just plotting you know two sets of

4:59values to see if there's a correlation with a bubble chart we're able to do a

5:03similar sort of thing but with three sets of values so it's kind of like an

5:06extra third dimension and we will see quite quickly how that third dimension

5:09is displayed and the scenario I've got here imagine we've got some kind of

5:14weight loss club so these are all of our people who are trying to lose some

5:18weights here is their age this is their original weight that they started off

5:22with when they first joined the club and this is how they're doing with their

5:25weight loss this is the amount of weight that they have lost. So let's plot

5:28these

5:29three data sets here on a chart and this time it's going to be a bubble chart

5:35and

5:35the point being the bubble chart allows us to much like with the scatter chart

5:40to plot two sets of values x and y axis but then the third dimension if you

5:46like

5:46is the size of the bubble. So what this is showing us in fact let's add some

5:51data

5:52labels so let's add for example we'll start off with some axis titles so along

5:56the bottom we have got the age of the dieter so let's change the axis title

6:02along there to show age so again I'm just using our little trick there and

6:06then up the side we've got the next series along which is the original weight

6:12so let's just label that axis accordingly so original weight up the side so

6:16therefore the size of the bubble indicates the third data series there the

6:21amount of weight that the dieter has lost obviously the bigger the size of the

6:26bubble the more weight they've lost so it looks like David here must have lost

6:30the most weight so that data point there yeah the size is 104 you can see on

6:35the

6:35details there as I hover and the two data points for the x and y axis were 33

6:39and

6:39350 so that does indeed reflect David and much like we did with the scatter

6:44chart

6:44if we want to label each of these points with another series so in the case of

6:50the bubble chart we want to label each bubble with the dieter's name well let's

6:53just do that so I'm going to get rid of the chart title and let's add some data

6:57points some data labels so yes let's add data labels and again I could show it

7:04as

7:04a data call out which might make it easier to move them around so we can see

7:07what's going on but if I go into the more options to make sure that I pick up

7:13a value from other cells which is the list of names here so click okay let's

7:18switch off the x and y value because just like the scatter chart we can read

7:22the the values off the of the chart so I've just got the extra values which is

7:27the names and much like we saw before you can if you choose to do a little bit

7:31of fiddling to make sure that all of these are nicely positioned so I'm just

7:35individually dragging some of those labels there so we can see what's going

7:38on but but yeah can you see the sort of thing we're aiming for here the third

7:42set of values is represented by the size of the bubble and the first two set of

7:46values much like the scatter chart are shown on the x and y axis let's move on

7:51to surface charts now these don't get used too often we will create a couple of

7:56examples and then try and work out what it's showing us and you can see I've

7:59got

7:59some lab results here and the reason why I have chosen this type of data is

8:03because the most meaningful results for surface charts tend to be gained when

8:07you're plotting data from mathematical models so that's pretty niche but anyway

8:12let's go ahead and plot the data here so insert and choose yeah the surface

8:18chart will start off with a 2D surface chart so just sort of a flat one before

8:23we get to clever and what's different about surface charts is that the color

8:28is representing the size of the value now normally the colors in a chart

8:33represent the data series you know you know north south east west for sales

8:37figures or something you know north might be shown as blue blocks and south

8:40might be shown as red blocks or whatever but with a surface chart the color is

8:44representing the numerical values so let's take a look at sample two shown

8:50through the middle of this chart for the different you know points in time at

8:54which these lab results or whatever they were were taken and you can see for

8:57sample two through the middle here for all of the different results for all of

9:01the different times the results are blue blue meaning

9:05less than 20 you know the blue color indicates naught to 20 values and if you

9:10look at sample two for all of the values there yeah they're all less than 20 so

9:15that's why they're shown as blue whereas sample three has got a little bit of

9:19little bit of red color there at the at the top corner so the final 40-minute

9:23mark sample three had quite a high value so sample three yeah at 40-minute mark

9:28it did have a high value red being the color that's reflecting the 87.47

9:33value there because red is showing values of between 80 and 100 so wow

9:37that's really quite different isn't it and if i just take a copy of this so

9:41let's just hold down you control key while you click and drag and

9:45convert the copy to the 3d version so i'm just going to move this down a little

9:50bit and then on the chart design ribbon tab change the chart type to a

9:543d surface chart and we've got to make it quite a bit bigger so we can see

9:59what's going on here oh gosh even bigger again is this going to there we go

10:03so what we're trying to see here is the extra dimension that the 3d element

10:07gives us does show us also the size of the value so this is where we can see

10:12that the data is going upwards so that's where we see that that red data point

10:16there for sample three it's it's visually going upwards whereas the 2d version

10:20was the bird's eye view you know looking at it from the

10:23from here from the top it's just flat whereas the 3d version does show us a

10:27bit more meaningfully the size of the values but my goodness i don't really

10:32expect you to use these charts too often as i say most commonly used when you

10:36know looking at mathematical models but might be useful just for you to have

10:40those two examples in this workbook when you save them so you can remember

10:43the sorts of applications they might have let's move on to radar chart now this

10:49is quite a nice example you can see here in the scenario i've got some

10:53recruitment data so let's imagine we've identified certain characteristics

10:58that we're looking for in the ideal profile of the candidate

11:02and we've we've kind of measured for each of these characteristics what we're

11:06looking for so for the ideal candidate we want them to score 10 out of 10 for

11:10outgoingness we want them to be smart yes 10 out of 10

11:14yes we do want them to be an original thinker 8 out of 10 but blah blah blah

11:17you can see that we're not that worried about whether they are process

11:20driven in fact we'd rather that they weren't too process driven

11:23we don't really want them to show too much leadership skills maybe just 6 out

11:27of 10 but anyway we have kind of rated all of these attributes

11:30out of 10 for our ideal profile that means when we get candidate supplying

11:34for the job if we rank each candidate out of 10 for these

11:38different characteristics here we can see how well they match the ideal profile

11:42so we are going to plot the ideal profile on a radar chart

11:47so let's see how that looks so radar charts we find them here

11:51and what it has done it has plotted therefore in blue and in fact let's just

11:56add the legend so that we can see yes the blue line indicates the ideal profile

12:00let's get rid of the chart title because that's a bit misleading so

12:03the blue line indicates the ideal profile and it's measuring each attribute

12:08from

12:08the middle so you can see the attributes are shown around the edge

12:11so therefore outgoing we said yes 10 out of 10 please so that's why

12:15the mark is shown there and the same for smart and confident and

12:19personable but whereas leader just 6 out of 10

12:22original thinker 8 out of 10 process driven only 2 out of 10

12:26so the shape that is generated from joining up the dots for each score

12:32is the shape of the ideal profile but the clever bit is

12:35and I love this if we change what the radar chart is mapping to include

12:40candidate x and candidate y whoops let's just um

12:44do that again so I'm just dragging this to include the correct data

12:48we get to see the shape therefore the other candidates

12:51so if the blue is the ideal profile well candidate x represented by the orange

12:56line they're a pretty good match aren't they because the shape

12:59of the plot that we've got here is very similar to the shape of the plot of the

13:02ideal profile whereas in green candidate y

13:06or not a very good match for the ideal profile so

13:09that's quite a nice application of the radar chart

13:13and then finally my goodness we've seen some weird and wonderful things haven't

13:16been this video but finally a map chart as you might guess a map chart

13:22helps us plot data on a map so I've got some population figures for all of

13:26the states here in the US so let's select that data

13:30and you guessed it we're going to plot that on a map chart

13:33so let's just choose that and bingo you can see there

13:37it's plotted that data on the map but what's it actually showing us

13:40well it's showing us that the darker the blue color

13:43the higher the value so therefore you can see that

13:47the California state shown here is the highest population from the list

13:51and if you just select the map and right click to format the data series

13:56you will see a couple of options that you can set for the map

13:59so for example yeah the map area now it's currently set to automatic

14:04so you can see that it did zoom in on the United States because it identified

14:08the data as being specific to the US but if we wanted to see it for example

14:12for the whole world then you can change that and you can see there we go

14:16it's showing that data but in the context of a full map of the world but let's

14:20just

14:20put it back to only the regions with data

14:23yeah and we could also add labels if we wanted to so let's show all the labels

14:27so that means it's going to label all of the states for us

14:29wow make sure you save this workbook because my goodness all of these

14:34different tabs along the bottom that's going to be a really useful resource

14:37for you to have an example of all of these different chart types

0:00Task number one says on sheet one here create a combo chart to ensure that

0:05values for both data series can be read because of course these numbers are

0:08much

0:08bigger than those numbers so it's not going to work that well on a regular

0:13chart. So we did look at combo charts but we actually did it the long way

0:17around

0:17in the videos so I'm not sure if you spotted this button here for combo charts

0:21which already gives us pre-prepared combos of blocks you know column charts

0:26and lines without or with a secondary axis. So for this particular data series

0:31I think the one with the secondary axis is going to work best so the orange

0:35line

0:35there the smaller values is being read off the additional axis on the right

0:39hand

0:40side there. Brilliant. Okay task number two says on sheet two adjust this hist

0:46ogram

0:46here to ensure bin widths of 10 and an overflow bin of over 70 so let's double

0:52click on that axis there because the bin widths are a property of the x-axis so

0:57rather than automatic let's choose a bin width what did it say of 10 there we

1:02go

1:02pressing tab to make sure that's inputted and when you do press tab you should

1:06see it updating live on the chart there it also said an overflow bin of over

1:1170 so yeah that looks good close that perfect I think that's task number two

1:16done so task number three says on sheet three create a chart which will allow

1:21you

1:21to compare the data distribution for the four categories right so the

1:26statistical

1:27chart types allow us to look at how our data is distributed but if we want to

1:31compare for multiple categories then we need to use a box and whisker chart so

1:35let's select all of this data and then go to the insert ribbon tab and where

1:40were these statistical chart types there we go there's the box and whisker

1:44perfect so we can see how the data is spread you know the maximum minimum and

1:49mean values you know how they're arranged into their quartiles but we're able

1:52to

1:52compare that distribution for those four categories and then finally on sheet

1:57four

1:57it says adjust the waterfall chart here so that the gross profit and the

2:04operating profit and the net income figures are correctly shown as totals

2:09so on the chart they're all shown as increases not totals so what was it

2:13gross profit let's right click oh I need to make sure that the individual data

2:16point is selected first then I can right click set that as a total the same for

2:20the operating profit and then the same for the net income good so that looks

2:25much more like we would expect a waterfall chart to look well done I hope

2:30this has been informative for you and I'd like to thank you for viewing