Surface and contour charts - Microsoft Excel Tutorial

- [Instructor] Surface and contour charts are undoubtedly one of the coolest looking charts in Excel. Whether or not they're the most useful, that's up to you. So what these are used for is plotting data in three dimensions to find optimum combinations of values. So whereas most charts like scatter plot, for instance, plot data along an X and a Y asis, surface and contour charts plot data along an X, Y, and Z, to give an indication of depth as well. So some examples here, you could look at accident rates or accident frequencies by both hour of day and day of week to see when those hotspots occur. You could look at elevation by latitude and longitude, which is how these contour charts got their names, or maybe you just want to look at optimum oven temperature and baking time to cook the perfect cookie. In that case, you could plot cookie deliciousness by temperature and baking time, although I'm sure there are better ways to do that. So pro tips here, first and foremost, don't use surface charts if a simple heat map or simpler chart will tell the same story. There's a time and a place to use a contour chart, and there are many more times and many more places where not to use a contour chart. So use it with some discretion. Second, avoid using wireframe chart types when possible. They can be really difficult to interpret, so I'll show you what I mean. Let's head to Excel and take a look. All righty, so here we've got an array or a matrix of car accident frequencies in Cambridge, Massachusetts between the years 2010 and 2013, plotted by day of week, shown along this Y axis, as well as hour of day, shown along the X. So this is a good candidate to use something like a surface or contour chart, because we have three dimensions of data to work with: time of day, day of week, and frequency. So I'll start by selecting all of this data, including the labels in column A and row three. I'm going to go into my Insert menu, and these live along with radar charts. As you can see we have a few options here. We've got a 3D surface, and the wireframe version of the 3D surface, and then we have what's called a contour chart, which is essentially like taking this 3D surface and looking at it from directly overhead. And then again, you've got the wireframe version of the contour as well. In this case let's go with the classic 3D surface, and I'm going to go ahead and blow this out to make it much more readable. Plus it's just really cool looking, so let's see what we got. We'll give it a chart title, Accident Frequency by Time of Day and Day of Week, and we're going to need to give it some axis titles, 'cause it's not very clear at this point. Excel's pretty smart. When you go into the chart elements, and click Axis Titles, as you can see here, not only do I have horizontal and vertical, but I've got a depth axis as well. So for horizontal, that's my zero to 23 scale. That represents hour of day. For my vertical, that's the actual volume of accidents, so I'm going to call this Accident Frequency. And then finally we've got depth, which represents day of week. Okay, so this is starting to be pretty interesting. I can see some peaks and valleys, but it's a little bit tough to read, so we're going to need to do some formatting here. Now, one of the most useful formatting options when it comes to surface and contour charts is to right click Format the Chart Area. I'm going to go into my Effects options here, and drill down into 3D Rotation. Now I can rotate this visual any way that I choose, so as you can see, if I change my Y rotation and increase the degrees, now I'm looking at it more overhead versus more horizontally from the side. And so I'm going to go with something like this because it gives me a decent sense of depth as well as height and width, but you can customize this however you choose. Now the second customization that I'm going to make here is to add some custom colors to make this a little bit more clear, so I'm going to make this a little bit bigger, and now to change the actual colors of these bands, it's really not intuitive. It's kind of tricky. You actually have to go down into the legend itself and right click on each particular band or range of values, and from here you can format the band or change your fill and outline options right here. So for the zero to 20 accident frequencies, that's a good thing because that's the fewest number of accidents in my data sample, so I'm going to give it a dark green, and then 20 to 40, why don't we give it a lighter shade of green? Let's do this middle green here. Then 40 to 60, we're starting to get into dangerous territory here, so let's go with a yellow, and then 60 to 80, I'm going to fill this with an orange, and then 80 to 100, those are my really high accident frequencies. I'm going to give these, plus 100 to 120, dark shades of red. So now once I've done that, it tells an even stronger story by adding the element of color along with height, width, and depth. So that's your basic surface chart. If I copy this, and paste it here, let me just scroll over so we can see it. Now what I can do is right click and change the chart type to either a wireframe, which as you can see, is really just tough to read, or go back in my Chart tools and change it to a contour chart, and again, this is kind of the overhead view, so you can see to some extent where the hotspots are. But again, pretty tough to read. So what I would recommend is that you think about these charts and say okay, what's the story that I'm really trying to tell? In this case, I'm trying to tell the user which combinations of day of week and hour of day tend to lead to the most accidents, and this surface chart does that to some extent, although to be honest, it's a little bit tough to read. So take a step back and say can I accomplish the same thing using a simple conditional formatting rule like a color scale? You know, I can also apply that same pro tip that we did earlier with a custom formatting rule to hide the numbers with three semi-colons, and there you have it, so ask yourself is this heat map all I really need to tell that story? It's very, very clear that that 8:00 to 9:00 a.m. rush hour, especially on Mondays through Fridays, tends to lead to the most accidents, or do I need something fancier like this surface chart? Both accomplish the same thing, two different visual approaches. And there you have it, surface and contour charts in Excel.