Manage Advanced Charts

0:00Make sure you've opened up the file that I've given you that accompanies this skill and let's imagine on this first sheet here we want to plot this data on a chart and our first general principle that you might remember from the previous course the MO 210 is that we do select the titles in our data that's how Excel can label the chart but we don't generally select the totals because those numbers are going to be much bigger than the other numbers and that's going to distort the chart so if we wanted to plot this data on a chart we would select that lot but I would actually like you to imagine for this example we only want to plot the pale blue shaded cells on a chart so don't forget to select data that's not next to each other in the chart you need to hold down your control key on the keyboard and also here for this example I do actually still need to select the titles for the years so that Excel labels the axes correctly so I'm going to hold down my control key again and I've started my selection from this blank cell here that's to make the shape of the selection match the other selections so that it lines up properly I think in more recent versions of Excel it's a bit clever at lining up the data but just to be sure I did start my selection for that top row with the blank cell and then on the insert ribbon tab you might remember we could choose recommended charts which will give 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 vast array just here so let's just quickly for example choose a straightforward 2D column chart you will then remember when you are clicked on your chart you get two dedicated ribbon tabs appearing so chart design allows you to change the appearance of the chart we can change the colors and whatnot so we talked about many of those options but the options that I do also enjoy with these buttons just here to change the chart so if I click on the plus and think to myself no I don't want the chart titles let's switch that off but I would like for example to add some more grid lines let's add some yeah let's add some vertical grid lines there we go and also let's just put for example or what else should we do let's put the legend for example down the you know down the right-hand side but then also if we want to make more specific changes to any element of the chart well the golden rule was the general principle is you select the thing you want to change so let's imagine this y-axis I want it to go up to not the automatically generated 600 as the maximum I want it to go up to a thousand for whatever reason well the rule is you select the thing you want to change and then 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 opens up the task pane at the right hand side where I can change the maximum to a thousand rather than 600 so I'm just going to press tab to move on to kind of input that and then close down that task pane and you can now see I've made that change to to the y-axis there if you're feeling confident then a quicker and even quicker way of doing that would be to double click on the thing you want to change and that will open up the formatting options for that thing but in my opinion a slightly more controlled way of doing it is select the thing first and then right click and choose to format it but anyway that was just a very quick reminder of some general principles that we're going to be using throughout the rest of this skill I'm just going to resize this chart and pop it down here and let's crack on with some other more interesting examples working 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 along the lines of, "In this video, we will learn some useful options for plotting series of different magnitudes on the same chart." And you might be thinking, "Well, what's that all about?" Well, we're going to try this data just here, and, you know, spoiler alert, we can see that the Europe figures are much smaller than the US figures. So that does mean if we were to plot both of these two data series on the same chart, and again just, let's stick with a straightforward 2D chart. Well, it's a bit hopeless, isn't it? Because the US figures shown in the orange here are so huge that that's distorting the whole chart, so we can't accurately read the other series, the Europe figures, off the chart because 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 exactly what we're going to do. So having started, you know, with this column chart, let's see what we can do to improve the readability. And the first thing I'm going to do is very carefully select the Europe series. So just one click, you can see the selection handles are appearing on all of the series there, and then you know the rule, I'm going to right click to choose to format that data series. And the option I'm going to select is to plot this particular series, the Europe figures, on a secondary axis.

1:15That means I can use a different scale on that secondary axis because these little figures here don't work on this great big scale that we're currently using for the US figures. So yes, let's choose a secondary axis. But unfortunately, that hasn't really helped us. It still looks very peculiar and frankly not yet that readable. So really the best option for us here is up on the chart design ribbon tab is to choose this change chart type button. And this is where you will see the list of all of the different chart types, many of which we will look at in this skill, but down the bottom we've got a combo chart.

1:50And it's already showing that Europe is plotted on a secondary axis. And in fact, rather than putting the Europe data series on a secondary axis using the task pane at the right hand side like we did a second ago, we could have just checked this box here directly within this dialogue box to have done that. But the extra thing I want us to do in this combo chart section here is to say, well, having both of these series shown as cluster columns, it doesn't look that great. Let'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 types. We're mixing a clustered column with a line chart and we do that to improve readability. So that does look a lot better. The Europe figures are shown as a line there reading off this secondary axis on the right hand side. The US figures are shown as the column chart, the orange blocks reading the axis off the left hand side. So if I click OK to that, and that is looking good. Let me just close down that task pane. But there are just one or two things that I'd like us to do to improve the read ability of this chart.

2:56So in order to make it very clear that these orange figures are using the axis values on the left hand side, I am going to double click to format those figures. So we know that the double click will give us all of the options available to format the axis. And it's the text options. I want to change the fill color of those figures to orange so that it more obviously matches up with the orange blocks so that people know to read the orange blocks off the orange axis. And 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. Not quite as visually obvious, but yeah, we can see that those figures have now gone blue. And then one last thing just to quickly do, let's imagine now we'd like the chart title to say customer returns. And of course you could click in here and manually type in customer returns. But let's imagine we actually want the chart title to automatically reflect whatever is in cell I too just here. Well, we can do that. We 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 and then just click on the cell. You want it to reflect. You can see the reference here. The name of the sheet is in quotes with the exclamation point and then the cell reference with the little dollar symbols there to fix the reference of the cell I clicked on. So if you just press enter now, you'll see that that does reflect the contents of cell I too. The beauty being, of course, if I change what's in cell I too, so let's change that so it just says returns rather than customer returns then.

4:29Yep, the chart title will also reflect that. Perfect. So a good use of a combo chart with a secondary access to improve readability with 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 sunburst charts and then find they can't make head or tail of what these charts are supposed to do. And I have to say your success with them is going to depend on you having the right type of data. More specifically, it depends on you having what is described as hierarchical data. You can see that these are described as hierarchy charts. So if you take yourself to the next sheet along in this workbook, tree map and sunburst and make sure you're scrolled up to the top because there are two blocks of data, but let's start with the top one.

0:31And these are some examples of hierarchical data. So this first one, let me talk you through it. Let's imagine that you've got some kind of food truck that you travel around in and you sell lunches to people. And you've got data here of the revenue generated by the different lunch offerings that you produce from this food truck. And it's divided up by category. So kind of at the parent level, we've got hot food or cold food. And then within hot food, well, we sell hot sandwiches and we also sell hot pastries and we sell hot soup. But within the hot sandwiches subcategory, we've got further sub subcategories, open eyes, burritos or toasts.

1:05And you'll see that each lunch item that I sell has got a revenue value attached to it. So if I wanted to see, for example, how the sales of, I don't know, pasties compared to the sales of, I don't know, cold sandwiches, it's not that easy to tell from this data, but the tree map chart is one of the hierarchy charts might help us out. So I'm just going to press control A to select this block of data. And let's choose, yeah, we'll start off with a tree map chart. And what it's showing you here is that all of the blue-colored rectangles are showing the sales of hot food.

1:38So that's the top part of the data here. And the orangey color blocks are showing the sales of the cold food. And the relative sizes of each rectangle shows the value in the revenue column. So I can see at a glance here that the sales of cold sandwiches, so the sandwich is rectangle here in the, well, you can see it's orange, so it's in the cold parent category. So the sales of sandwiches in terms of revenue, they generate far exceeds the revenue we get from selling pasties or pasta pots. So that's what it's showing us here. There are a couple of options that's worth considering though. So I don't like the way it says hot and cold within the main block of 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. So I'm going to right click on this blue data series here and choose to format it. And rather than having the label of the series, which is hot, overlapping the actual, you know, first block, I'm going to have it as a banner. So if I just close down that task pane now, I think that looks better. I can now see clearly that all of the blue blocks are showing the hot category and the orange blocks are showing cold. That means I don't really need this legend anymore, do I? So 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 different lunch products that I sell. Let me make it a bit bigger sideways so that the blocks are big enough to show the text labels for each of the products I sell. Perfect. Let's just scroll down and take a look at this other block of data. And what's interesting about this block of data, again, you can see it is hierarchical. We've got years and then quarters and then months, but actually it's only the 2025 parent category that's actually got any breakdown by month.

3:21So if I were to plot this just on a regular tree map chart, so let's just go back and choose tree map, I don't really get the visibility to see that the green blocks here for 2025, well, yeah, that shows some breakdowns by month, but these don't. It's kind of okay. But actually, this is a good example of where the sunburst variation of the hierarchical chart would work, I think a bit better. So let's change the chart type to the sun burst chart. And I like this. I think it shows us quite well that it's the 2025 series, you know, the green data here that's kind of exploded out into another level of detail with 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 the parent category in the inner ring there. It's then broken down into subcategories with the next ring and for 2025 only, that's further broken down again into another subcategory, in this case months, in the outer ring. And it's very obvious that that extra level of subdivision is only present for the green series because it's kind of displayed in this different manner in the sunburst chart. Similar to the tree map though, the actual size of the different wedges reflects the value in the, you know, in column E here from the original data.

4:37So I can see at a glance, for example, for 2025, that November had a bigger figure, a bigger value than December or October, or I can see, for example, in 2023 that Q2, because it's a bigger block there, is a bigger value than we would have generated in Q4 or Q3 or whatever. So these chart types are not going to be suitable for every type of data. But if your data is organized into parent categories and then subcategories in some kind of hierarchical format, then 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 together into the same video because they are all statistical chart types which are going to help us to understand how our data is distributed across a range. Now I have to say if you are a professional statistician you will probably want to research further into these specific formulas and whatnot that Excel is using in these charts. But my goal in this video is for us to work through an example of each in order to get you started. So take yourself to the next sheet along in this workbook, histogram and Pareto.

0:34And looking at this example data here you can see that for each sale that we've made in the last month or whatever it is, we have captured the customer age, the reason that they made the purchase and the value of the purchase. And you can also say I've got a count column but don't worry too much about that right now, we can ignore that so that's going to come into play a bit later on. But let's start off by saying that we want to understand the ages of our customer. You know what's the most popular age bracket that our customers fall into? And we need to know that because that's going to help us with our marketing and it'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 ages. So I need to select just this data here, so I'm clicked in the heading and then control shift down arrow, we'll select all the way to the end of the data in that column. So I've selected the appropriate data, go to the insert ribbon tab, choose, here are the statistical chart types, just choose a straightforward histogram. And you can see here that the histogram has grouped the overall age range. Now the overall age range goes from 19 to 99, so that's the overall range of ages that our customer base represents from this data here.

1:45And it has grouped them into various age brackets, but the correct term is actually bins. So a histogram is a statistical chart type which shows the distribution of our data grouped into bins and bins are simply groupings of data points within the range. Now these bins have been automatically calculated, so you can see the first bin or age bracket is 19 to 35, then it goes 35 to 51, and then 51 to 67, etc. But I can control this if I want to make changes to that, and the key thing to know here is that the bin widths are a property of the x axis along the bottom here.

2:24So if you double click on that x axis or right click and choose to format the axis, that's where you'll see the axis options, which includes the bin options. So at the moment it's obviously automatic, and if you are interested, apparently Excel uses Scott's normal reference rule to actually calculate the automatic bins. But no, I want to have specific bin widths according to what makes sense for my organisation. I would like the age brackets to go in, you know, kind of like 15 year chunks. So let's change the bin width there to show 15. And I also want to have an overflow 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 make these changes, and I need to press tab just to input that last one there, you will see the chart updated. So you can see now the age brackets or the bins go less than 20, and then 20 to 35, 35 to 50, etc. all the way up to over 80. So that's our overflow bin there, our underflow bin there, and then the bins which 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 most popular bin or age bracket is the age bracket 35 to 50. The greatest number of customers from our data falls into that particular bin. So all good to know. But I'm going to delete that and let's try another example. So this time, let's say I want to understand what's the most common reason that people buy from us. So we understand how, you know, what's the most common age ranges, but what's the most common reason that people buy from us. So this time I want to put a histogram for this column here. So I'm going to select that column.

4:06So again, I've clicked in the top cell, control shift down arrow to select all the way to the end of the populated data in that column. Let's go to insert. Let's go again to a histogram, and this is where I'm a bit disappointed. And this is why we've got this extra count column, because when you're trying to create a histogram out of data that isn't numerical, it has a bit more of a problem trying to organize the bins, because it's not numerical data. I mean, with the ages, it had no problem there, because it could sort that out, but 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. I'm just going to hold down my control key to select the count heading and then control shift down arrow to make sure I've got both of those two columns selected. Now let's insert the histogram again. And I'm not yet too optimistic, but if we go back into the options for that X axis, which is where we saw the bin options , it was an axis option for the horizontal axis there. So there we go chart options, axis options. So rather than saying automatically or specifying a bin width, I'm going to say buy category.

5:11And we have to choose that option when the thing that we're plotting, if you like, is text based categories rather than numerical data. And you can see that the most popular reason that people purchase from us is reason type D, whatever that might be. I don't know as a gift or for personal use or whatever, but reason D for purchasing is the most popular reason people buy from us. And again, that's going to be useful for us to know in terms of our marketing and to help us understand our customers better. Now hold that thought, if reason D is the most common or the most popular reason that people buy from us, we've also actually got the value of each purchase.

5:51So while column D is the most popular or the most common reason people buy from us, is it actually the most valuable? So remember that column D or reason D rather is the most popular reason I'm going to get rid of this. And instead we're going to plot another histogram, but this time reason for purchase with its value. So control, shift, down, arrow to select all the way to the end of the column. And let's go back in again and create another histogram and let's again check the 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 take into account the purchase price rather than simply account. You can see it's this time reason A. So that's showing us a different spin on our data. So if reason A is, I don't know, purchasing on behalf of a client, whatever reason A might be, then that's very interesting for us to know that that's the most valuable reason that people buy from us. So this is the sort of information that histograms can tell us. And just to show you what a Pareto chart looks like, well I'm simply going to change this chart type to a Pareto chart.

7:02So within the histogram category just here, the Pareto chart is kind of a sub type of histogram if you like. And a Pareto chart is often described as a sorted histogram. And you will see, when I just move this to one side, you can see that the original histogram, it went D A blah blah blah. But when I click OK to turn it into a Pareto chart, can you see that it has re organized the blocks here into the highest one first? So that's why the Pareto chart is often described as a sorted histogram because it'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 shows the cumulative total. So it's showing how all of these blocks end up adding up to the grand total of the numerical column here, the purchase price. Let's finish up this video with another statistical chart and that's the Box and Whiska chart. And this again, like a histogram is going to show us the distribution of our data, but this is much better optimized if you want to compare multiple data sets. So how the range of data differs across different data sets and I do realize it '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. So we've got the ID, the data, the sale, the category the sale was made in and the value of the sale. So if I want to see how our figures are distributed across a range within each category, then that's where a Box and Whiska chart can help us out. So let me select the category heading and shift right arrow to also select the value heading and then control shift down arrow to select all of those two columns there. And then go to the insert ribbon tab and choose again from the statistical drop down 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 is spread across a range within those four categories. Now, the little X that you can see, which is quite faint because it's kind of like a darkish blue on top of a mid blue there, but the little X in the box of each category here represents the mean value of all of the data in that category. So that value there, that X represents the mean value of values that have the furniture category assigned.

9:23And then the bottom of this Whiska, so these lines sticking out of the box there, the Whiskas, but the bottom of the Whiska up to the Box is the first quartile of our data. And when we talk about quartiles, we're talking about 25%. So four quartiles make up the entirety of our data, you know, four, twenty five percent, makes up a hundred percent. So the first quartile, the first twenty five percent of the figures in the furniture category fall in that range there. And 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 the box up to the top of that Whiska. So the overall range there is from the bottom Whiska all the way up to the top Whiska. And these little dots that you can see here for gifts and accessories, these are what we describe as outliers. So these values are deemed to be beyond the range of the data. And as you might imagine, there is a statistical calculation going on to determine these thresholds. So there we have it, the Box and Whiska chart or the Pareto chart or a straightforward histogram.

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

0:00The three chart types that we'll try out in this video, I've grouped together because they all tend to get used for financial or business information. They are a bit specialised, but you know the drill, we will try an example of each and you can see what you think. So we'll start off with the waterfall chart. So take yourself on to the next sheet along in your workbook and you can see I 've got an income statement here and you can see that this data is showing some kind of starting some of money. So this is my gross revenue for my business and then in red we've got various deductions.

0:32So there's my gross revenue. We take off some adjustments that gives us a new total doesn't it, you know a net revenue. So it's the gross revenue minus the adjustments and then we've got other deductions and then another subtotal, more deductions, another subtotal etc. And it's the fact that we've got these subtotals which for ease of reference I 've shaded in blue here but these subtotals mean that this is going to be suitable for a waterfall chart. And I have to say these aren't I don't think terribly intuitive to use but I can show you how we can make them work.

1:05So select this data and then on the insert ribbon tab as usual go and choose the appropriate chart type. So this is the category here for some of these chart types. Waterfall is the one we want and at first glance you think oh my goodness that looks absolutely dreadful. What's going on here? So what's it done? Well it has plotted each of these values on the chart and you can see all of the ones in the dark blue color these blocks are marked as increases and the orangey colors as are marked as decreases. But hang on a minute if you look at this figure here so this is the 364-239 value which is one of our subtotals.

1:43So that rather than being shown as an increase as the legend would suggest that should be marked as a total because it is a subtotal in this you know in this figures here. So if you click on the series click again to select just that one data point and then right click to set it as a total. And we need to do that for the other subtotals which have been incorrectly on the waterfall chart been marked as increases. So the gross income here this blue shaded cell the 240,000 that's that one there that should 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 figure there that should be another subtotal and then again that one at the end there marked that as a total. So that if I just click away so that I don't have an individual data point selected that is exactly what a waterfall chart is supposed to look like. Let me get rid of all the data labels so we can see it a bit more clearly but the idea is we have an opening sum of money that big blue column there that's the gross revenue. We take away a small amount that's the that's the little orange bit there which is a decrease to 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 a bit more a bit more a new subtotal etc. So it's a waterfall chart because it's kind of rippling downwards nibbling away to give us you know subtotals according to the figures we've got in the income statement. So as I say quite specialised but that's how it works the key thing I'll say it again where you've got subtotals you need to select the data point and right click to mark it as a total and you can see because this is already a total I get the option to clear it as a total. So if you create these income statements and they don't look as you expect them to look make sure your subtotals are marked as totals.

3:32The next one on the list is funnel chart and these are pretty straightforward I think you would use one to show progressively smaller stages in a process so when you 've got values which are gradually getting smaller and the obvious example when it comes to talking about financial or business figures is going to be some kind of sales pipeline. So as you'll see when we try this is really straightforward so I've selected the figures there which are showing the number of items we've got in the lead stage or prospect stage etc etc. So it's a sales pipeline. Let's choose from that same category there let's choose the funnel and yeah it 's just showing a nice graphical depictation of those figures in a funnel you know the figures getting progressively smaller as the items progress through the sales pipeline.

4:17Now the stock charts the final example for this video these are interesting and they are designed to show stock market information so the trend of a stocks performance over time so very specific financial application here and there are a few different types and the one you choose will depend on what data that you've got. So looking at the data I've got here so over five days in May I was recording for a particular stock how much volume was traded what the opening price of the stock was what the highest price throughout the day was what the lowest price throughout the day was and what the closing price was and I recorded that information for the same stock over you know over these five days.

4:57So if I want that information plotted on a chart that's what I would use a stock chart for so click the drop down again and you will notice that there are a number of different stock charts depending on what your data is showing. So this first one for example will only show you the highest figure the lowest figure and the closing figure of a stock. Well I've got you know volume traded and extra things as well so the next one will show you open high low close the next one will show you the volume traded and the high and the low and the close but the final one which is the one I want is the volume traded the opening 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 normally your first port of call when something doesn't look as you expect is to switch the row and column do you remember this button here which kind of turns the whole chart inside out. So if I choose that now that is looking better you can see that each column or each you know vertical aspect of this chart is representing a day so each row of my original data is being reflected by each column here so that's looking good and the idea is these blue columns reflect the volume traded on that day of that stock so that value there this blue column for the 12th of May is representing that figure there so that's looking good you read these blue columns off the left hand axis but this is an example of a dual axis chart because we have another secondary axis on the right hand side showing a different scale and that scale is what we read these sort of box and whisker type of things off of so the idea with these little box and whiskers things well if the box is black then that means the stock closed at a lower price than it opened whereas if it is white it's closer to high price than it opened so that means the top and bottom of the box represents the opening all the closing

6:49values depending on whether the box is black or white and then the whiskery things well that extends to the lowest and the highest values which are obviously the high and low values from the data here so as I say really quite specialized but it is a really rather clever way of depicting an awful lot of information visually on the same chart and that makes it much more meaningful to start understanding trends rather than trying to look at a load of figures like this and making sense of it the stock chart will do it all for you so there we go three quite specialist chart types there which much like the other chart types we're exploring in this skill only really makes sense if you've got the right kind of data for them.

0:00As if the chart types that we've already looked at in this skill aren't enough, there are several more chart types that we haven't yet seen examples of. So in this video I'd like us to work through an example of each of a scatter chart, bubble chart, surface chart, radar chart and map chart. We'll probably spend the longest amount of time on the scatter chart and then I want to be really quite quick with some of these other ones because they are not specifically called out on the exam outline but I think it is still going to be useful for you to have an example of each of these chart types. So this is the example data we will use for a scatter chart. A scatter chart is designed to help you see if there's any correlation between two sets of values. So looking at this particular scenario I would like you to take yourself back to your school days and let's imagine that your math teacher has said to the class, okay we're gonna see if there's any correlation between the average noise outside the school, you know, is there a correlation between the volume of the noise and the number of vehicles that are driving past the school. So we're going to record for each hour in the day what the sound is, what the average decibels of the noise is and how many vehicles they were and of course you already know that we are expecting there to be a strong correlation whereby the, you know, the greater the number of vehicles 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 between these two sets of figures but let's plot it on a scatter chart. So if you go to the insert ribbon tab and you will also notice that I've broken one of my own golden rules and I have not selected the titles. Normally we like to select the titles in our data so that Excel labels the charts properly but scatter charts do work better if you just select the two sets of data that you want to plot on the scatter chart and we can work with the, you know, labeling and whatnot afterwards. So having selected the figures click the drop down here to insert just a regular scatter chart and you can already see because of the position of the dots there that it does look like the dots are kind of forming a straight line there in fact we could add a trend line couldn't we?

2:01And yeah that's looking pretty good. So yes unsurprisingly there does seem to be a correlation between these two sets of values that's what the straight line is telling us and each blob on the chart there is showing so for example this first blob or data point is showing an average decibels of 22 and you know the number of vehicle of 10 so that first blob there must be the 6am value because those are the two values that are dictating where the position of that is. But it's going to be helpful if we can label the axes and these data points so we know exactly what we're looking at. Now I'm going to just delete the chart title because we're going to be adding all sorts of other bits and pieces but let's add some axis titles. So let me just switch those on. So along the bottom we've got the average decibels so I'm going to use that trick we already know about to pick up you know a value from a cell so average decibels along the bottom so that means up the side we've got the number of vehicles. There we go. And what about these data points? It might be useful I think to have these indicating the time of day that the measurements were taken from so let's add some data labels and let's add them as a data callout. Now at the moment you can see that these little callouts here while they're quite useful because it gives 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 on. I would rather the actual data it was labeling it with rather than the X and Y values I'd rather it picked up the time of day that the data point was taken from. So let's dive into some more options for our data labels and let's pull out a value from some other cells the other cells being of course the times. So let's check that box and it's saying well where are these other cells? Well it's this lot here please so click okay so there we go 6 a.m. with the two values.

3:52Now let's turn off the X and Y value from the data labels because we can read that off the chart. Now it's looking a bit busy so let's stretch this a little bit and you can take a bit of time if you want to adjusting each individual data label so you can see clearly but let's just quickly move a couple out the way so that we can check that this is showing us the data we expect. So for example there we go. This data point up here is the 8 a.m. figure so 8 a.m. we 're expecting an average decibels of 80 so 80 yes so reading off the the X axis there is 80 and the number of vehicles 161 so reading the value of the Y axis yes so the 8 a.m. figure is up there according to its X and Y values and it's got the correct data label. I'll leave you to fiddle around and adjust all of these data labels to make sure that you can read them properly if you choose to but yeah that's a nice example I think of a scatter chart. Next up we have a bubble chart. Now a bubble chart is well it's kind of similar to a scatter chart but whereby with the scatter chart we were just plotting you know two sets of values to see if there's a correlation with a bubble chart we're able to do a similar sort of thing but with three sets of values so it's kind of like an extra 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 weight loss club so these are all of our people who are trying to lose some weights here is their age this is their original weight that they started off with when they first joined the club and this is how they're doing with their weight loss this is the amount of weight that they have lost. So let's plot these three data sets here on a chart and this time it's going to be a bubble chart and the point being the bubble chart allows us to much like with the scatter chart to plot two sets of values x and y axis but then the third dimension if you like is the size of the bubble. So what this is showing us in fact let's add some data labels so let's add for example we'll start off with some axis titles so along the bottom we have got the age of the dieter so let's change the axis title along there to show age so again I'm just using our little trick there and then up the side we've got the next series along which is the original weight so let's just label that axis accordingly so original weight up the side so therefore the size of the bubble indicates the third data series there the amount 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 the most weight so that data point there yeah the size is 104 you can see on the details there as I hover and the two data points for the x and y axis were 33 and 350 so that does indeed reflect David and much like we did with the scatter chart if we want to label each of these points with another series so in the case of the bubble chart we want to label each bubble with the dieter's name well let's just do that so I'm going to get rid of the chart title and let's add some data points some data labels so yes let's add data labels and again I could show it as a data call out which might make it easier to move them around so we can see what's going on but if I go into the more options to make sure that I pick up a value from other cells which is the list of names here so click okay let's switch off the x and y value because just like the scatter chart we can read the the values off the of the chart so I've just got the extra values which is the names and much like we saw before you can if you choose to do a little bit of fiddling to make sure that all of these are nicely positioned so I'm just individually dragging some of those labels there so we can see what's going on 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 values much like the scatter chart are shown on the x and y axis let's move on to surface charts now these don't get used too often we will create a couple of examples and then try and work out what it's showing us and you can see I've got some lab results here and the reason why I have chosen this type of data is because the most meaningful results for surface charts tend to be gained when you're plotting data from mathematical models so that's pretty niche but anyway let's go ahead and plot the data here so insert and choose yeah the surface chart will start off with a 2D surface chart so just sort of a flat one before we get to clever and what's different about surface charts is that the color is representing the size of the value now normally the colors in a chart represent the data series you know you know north south east west for sales figures or something you know north might be shown as blue blocks and south might be shown as red blocks or whatever but with a surface chart the color is representing the numerical values so let's take a look at sample two shown through the middle of this chart for the different you know points in time at which 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 the different times the results are blue blue meaning less than 20 you know the blue color indicates naught to 20 values and if you look at sample two for all of the values there yeah they're all less than 20 so that's why they're shown as blue whereas sample three has got a little bit of little bit of red color there at the at the top corner so the final 40-minute mark sample three had quite a high value so sample three yeah at 40-minute mark it did have a high value red being the color that's reflecting the 87.47 value there because red is showing values of between 80 and 100 so wow that's really quite different isn't it and if i just take a copy of this so let's just hold down you control key while you click and drag and convert the copy to the 3d version so i'm just going to move this down a little bit and then on the chart design ribbon tab change the chart type to a 3d surface chart and we've got to make it quite a bit bigger so we can see what's going on here oh gosh even bigger again is this going to there we go so what we're trying to see here is the extra dimension that the 3d element gives 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 there for sample three it's it's visually going upwards whereas the 2d version was the bird's eye view you know looking at it from the from here from the top it's just flat whereas the 3d version does show us a bit more meaningfully the size of the values but my goodness i don't really expect you to use these charts too often as i say most commonly used when you know looking at mathematical models but might be useful just for you to have those two examples in this workbook when you save them so you can remember the sorts of applications they might have let's move on to radar chart now this is quite a nice example you can see here in the scenario i've got some recruitment data so let's imagine we've identified certain characteristics that we're looking for in the ideal profile of the candidate and we've we've kind of measured for each of these characteristics what we're looking for so for the ideal candidate we want them to score 10 out of 10 for outgoingness we want them to be smart yes 10 out of 10 yes we do want them to be an original thinker 8 out of 10 but blah blah blah you can see that we're not that worried about whether they are process driven in fact we'd rather that they weren't too process driven we don't really want them to show too much leadership skills maybe just 6 out of 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 for the job if we rank each candidate out of 10 for these different characteristics here we can see how well they match the ideal profile so we are going to plot the ideal profile on a radar chart so let's see how that looks so radar charts we find them here and what it has done it has plotted therefore in blue and in fact let's just add the legend so that we can see yes the blue line indicates the ideal profile let's get rid of the chart title because that's a bit misleading so the blue line indicates the ideal profile and it's measuring each attribute from the middle so you can see the attributes are shown around the edge so therefore outgoing we said yes 10 out of 10 please so that's why the mark is shown there and the same for smart and confident and personable but whereas leader just 6 out of 10 original thinker 8 out of 10 process driven only 2 out of 10 so the shape that is generated from joining up the dots for each score is the shape of the ideal profile but the clever bit is and I love this if we change what the radar chart is mapping to include candidate x and candidate y whoops let's just um do that again so I'm just dragging this to include the correct data

12:48we get to see the shape therefore the other candidates so if the blue is the ideal profile well candidate x represented by the orange line they're a pretty good match aren't they because the shape of the plot that we've got here is very similar to the shape of the plot of the ideal profile whereas in green candidate y or not a very good match for the ideal profile so that's quite a nice application of the radar chart and then finally my goodness we've seen some weird and wonderful things haven't been this video but finally a map chart as you might guess a map chart helps us plot data on a map so I've got some population figures for all of the states here in the US so let's select that data and you guessed it we're going to plot that on a map chart so let's just choose that and bingo you can see there it's plotted that data on the map but what's it actually showing us well it's showing us that the darker the blue color the higher the value so therefore you can see that the California state shown here is the highest population from the list and if you just select the map and right click to format the data series you will see a couple of options that you can set for the map so 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 the data as being specific to the US but if we wanted to see it for example for the whole world then you can change that and you can see there we go it's showing that data but in the context of a full map of the world but let's just put it back to only the regions with data yeah and we could also add labels if we wanted to so let's show all the labels so that means it's going to label all of the states for us wow make sure you save this workbook because my goodness all of these different tabs along the bottom that's going to be a really useful resource for 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 values for both data series can be read because of course these numbers are much bigger than those numbers so it's not going to work that well on a regular chart. So we did look at combo charts but we actually did it the long way around in the videos so I'm not sure if you spotted this button here for combo charts which already gives us pre-prepared combos of blocks you know column charts and lines without or with a secondary axis. So for this particular data series I think the one with the secondary axis is going to work best so the orange line there the smaller values is being read off the additional axis on the right hand side there. Brilliant. Okay task number two says on sheet two adjust this hist ogram here to ensure bin widths of 10 and an overflow bin of over 70 so let's double click on that axis there because the bin widths are a property of the x-axis so rather than automatic let's choose a bin width what did it say of 10 there we go pressing tab to make sure that's inputted and when you do press tab you should see it updating live on the chart there it also said an overflow bin of over 70 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 you to compare the data distribution for the four categories right so the statistical chart types allow us to look at how our data is distributed but if we want to compare for multiple categories then we need to use a box and whisker chart so let's select all of this data and then go to the insert ribbon tab and where were these statistical chart types there we go there's the box and whisker perfect so we can see how the data is spread you know the maximum minimum and mean values you know how they're arranged into their quartiles but we're able to compare that distribution for those four categories and then finally on sheet four it says adjust the waterfall chart here so that the gross profit and the operating profit and the net income figures are correctly shown as totals so on the chart they're all shown as increases not totals so what was it gross profit let's right click oh I need to make sure that the individual data point is selected first then I can right click set that as a total the same for the operating profit and then the same for the net income good so that looks much more like we would expect a waterfall chart to look well done I hope this has been informative for you and I'd like to thank you for viewing