One of the best visualization tools we have in Excel are the charts. But charts don't all have to be dull and boring. You can actually get really creative and produce some amazing charts in Excel. We're going to have a look at two examples here, but bear in mind these are just examples. The first thing one we're going to look at is this percentage dial and then we're going to look at how to create a gauge chart, or a speedometer, if you prefer. So let's start with looking at how we can present our resolve rate as a dial rather than a dull box. Coming back to our Calcs, We're going to start by scrolling down a little bit, and you'll see first of all, in the data, we have the resolution rate. But we've also created a little calculation to get the inverse, or if you prefer, the complement of it. So this will make up the total of 100%, and that will help us create our chart. Now, we're going to start by clicking in a blank cell and then we're going to come to Insert and the chart type we want, is a doughnut chart. So insert your doughnut chart, and now we're going to manually go and add the series we need. And the first one we're going to add is actually some background. So we're going to click Add, and I'm just going to call this background. And then all we want is 20 ones to make up that little background grid. And to make life a little easier, I've actually popped them in the workbook, but you could manually type them in here if you wanted. Okay, I'm just going to say OK so we can now go see what this looks like and make a few changes to it. So that's our starting point, but obviously, we want these all to be the same color. So I'm going to click on the series, come to my Format > Shape Fill, and choose my color. I'm also going to get rid of this heading and get rid of my series. And then one really useful option is you can actually change how thick the ring is by changing your hole size. So I'm going to click on my series, and I'm going to right-click, and Format Data Series, to bring up my format options. And you'll see the doughnut hole size is 75, but I'm going to change that to a 65. And finally, I'm just going to resize this so it will fit nicely on my dashboard. And I've worked out that 4.5, Works quite nicely. At this point, if I was creating multiple versions of this chart I would copy this. So I'd create the other two so I didn't have to redo this background over again. The next step, though, is to add the foreground. And what I really want to do is where they have achieved the value, I actually want to see the background. But where they haven't, where there's still more to go, I want to see it semitransparent. So I'm going to add another Doughnut chart on top of this one. Let's see how it's done. Coming back to Design, we're going to click Select Data again. And we're going to add another series. The series name in this case is going to be my resolution. And my series values are going to be the resolve rate and the inverse. And then I'm just going to say OK. So you can now see the rings it's created, but I'm going to want to change the fill. So I'm going to click once to select the series and a second click to select just the values I want. I'm then going to come to my Fill and for this one, I'm going to choose No fill. Then my smaller amount, the amount I haven't actually achieved, I'm going to change the fill color to white. And then I'm going to change the transparency to 25%. It's hard to see at the moment, so the next step is to come up to Change Chart Type. We're not actually changing the chart type, we're just putting it on a secondary axis. And in other versions, you may find it easier to right-click Secondary Axis, or we're just going to click OK, that simple. Now, with a couple of little tweaks, so let's just come up and make sure that there's no chart fill and that there's no outline. We're now ready to put this on the dashboard. So I'm going to copy that, come to my dash, and just for a moment I'm going to pop it over here. Before I'm going to put it on here, I want this to be formatted the same way as I've done with the other values here. So I'm just going to click on one of those, and you can see it's the same shape, I've just made it no fill. So I'm going to take my Format Painter, and click on the shape. I can then grab my doughnut chart, And position it nicely over the value. And I'll obviously use my alignment tools to get it properly lined up. But that's pretty much what we're looking for. Now, the next chart type we're going to look at is what's called a gauge chart, or sometimes a speedometer. And that allows us to actually see an improvement, or a decline, in a certain metric. And in this case, we'd actually like to see how our service level is progressing from one month to the next. And we're going to pop the speedometer chart in this section over here. Let's go back to our Calcs sheet, though, to get it all set up. We're going to scroll down now to row 43, and the first thing we need to do is set up our data. And again, we are going to have a background and then we are going to impose our series on top of that. The background can be anything you like, so if you want to see it going up in quarters, you could go for 25%. We actually want to see it going up in thirds. So to make life easier, I'm just going to say 100% divided by 3. And then I'm going to copy that down. But with a speedometer, I actually only want to see half a circle. So I need to somehow balance this out. And I'm going to do that by adding up all of these values, To get my other 100%. And this will form the bottom half of the circle, which I'll just make no fill. Now we want to calculate our Pointer. Now the Pointer's going to be the percentage amount its actually got to. And in this case, we're looking at the selected month, and we're looking for service levels. Let's scroll up slightly so this is the value we want here. So I'm going to type = and click on our Service Level and Enter. That will get us to the correct place, but I now need a tiny little dial so I can actually see it as a dial rather then a great big chunk. And for that, I'm just going to put 1%, don't want it to be too thick at all. And finally, I need to balance out the rest of the chart. And we've established that our chart is actually 200% round. So we can have 200% less whatever our Service Level is, less our Pointer. So now our data is set up, we can go about actually creating the chart. I'm going to start by selecting our background values. And again, I'm going to use the doughnut chart. This time, however, I need the hole to be quite a bit smaller, so I'm going to come to my Series Options, and I'm going to change the hole to 50%. You'll notice, however, that it's unfortunately putting it around the side, and we actually need that all to be rotated backwards through 90 degrees. And the trick to doing that is again in our Series Option, we're going to change our Angle for the slice to be 270, and now it's looking pretty good. Okay, let's make some changes to the colors. So I'm going to come up to Format, and first of all, I'm going to start by removing the outlines so no outline on any of those. Then I'm going to single-click on my first segment and choose a fill. Usually, I recommend thin colors but if I go for a thin color and I change my theme, my speedometer may change to a color that doesn't make sense. So I'm actually going to force the issue by selecting standard colors which won't adapt. I'm going to go for a simple red, Yellow, and green. And then the bottom section, I don't want to see at all. So I'm just going to come up to Shape Fill and choose No fill. And then another quick tidy up, I'll just get rid of the chart title and the legend. So that's starting to look quite good, but that's just the backdrop. I now need to put the actual Pointer on. And for that, I'm going to add another series. So once again, up to our Design > Select Data and Add. The series is going to be called our Pointer and then our values are going to be the ones we calculated previously. And say OK, and OK again. Now with this series, the only bit we want to see is our dial. So I'm going to single-click and click again, and I'm going to make that No fill. And then I'm going to do the same with the other section. Then clicking on our Pointer specifically, we can change that as well. So we're going to make that black, and we're going to change the border to No line. Looking a little silly at the moment, but we know what to do with that. Once again, we need to put it on our secondary axis. But we're also going to change our chart type to a pie so we don't just get it showing over here but it actually starts from the center. So we're going to click Change Chart Type. And we're going to change this to a pie chart, and we're going to put it on a secondary axis, and say OK. Now, unfortunately, it's lost where it's supposed to be. So once again, let's just go and adjust that to make that start at 270. And there is our gauge chart, ready to go. All we need to do is remove the border and the fill and pop in onto our dashboard. So really easy when you know how. So there are just two ideas of how you can create great visualizations on your dashboards just using Excel charts. [SOUND]
