Avoiding common chart distortions

- [Instructor] When you work with Excel charts, there are lots of opportunities for creating attractive visuals to accompany your data. You can, however, create distortions. Sometimes, those distortions could even be called enhancements. Sometimes, you'll do these by mistake and other times, it might be intended. We've got different sets of data on the screen here. Let's first talk about the data in rows 1 and 2. We want to create a simple little column chart that shows this data. Because it's surrounded by empty cells, we can just click within that data, and a very fast way to create a chart is to press alt F1.

That is Excel's default chart style. It's a clustered column chart. I think these tend to look better when the columns are wider. Same thing would be true, by the way, if we had a bar chart. We could make the bars taller simply by double-clicking, and that activates the Format Data Series dialog box. This will take you to the set of choices under that icon that has the three columns. If not, click that icon, and within that, you'll see a choice called gap width, the gap between the columns. Change those to a low number like 10 or 20, and it makes those columns a lot thicker.

Tends to make it easier to read. That wasn't truly necessary but nevertheless, it looks better, I think, for most people. Now, how about this logic? All these numbers here start above 100. The chart would be maybe more interesting, we could accentuate the differences between the months, if we started the chart at 100. You can change the minimum starting value here. Typically, it tends to be 0 and many times, you want it to be 0 and you want it to stay there. Let's double-click along the left axis here.

That activates the Format Axis dialog box. Now, you might or might not be seeing the choice minimum and maximum. If not, click the icon with the three columns. We're going to change the minimum here to be 100. All the values, you can see them over in row 2 and you can also see them in the chart, are well above 100. So, we change that to be 100. Close this dialog box. Now, we can always read the numbers down the left-hand side but on the other hand, column charts sometimes seem to suggest volume or quantity.

February looks twice as big as January, but you know from the data, and you can read the scaling, February is 140, January is 120. That's obviously not twice as big. How about November and December? November's 140, December's 180. Obviously, that's not twice as big. Your eye is telling you one thing, the numbers are telling you something else. Now, once again, you can always fall back on that explanation that says well, I can see the numbers, I see what they are. But I think you want to stop and think about this.

If you were trying to use this to persuade people about why you need more of this or more of that, as you're showing this data, you might be providing a misleading picture. If we switch this to a line chart, any time a chart is selected, you'll have a contextual design tab available, click the icon Change Chart Type. Maybe we'll change this to a line chart. Possibly this will show the data in a different way. Now, perhaps this doesn't suggest volume in the same way that a column chart might, but it certainly accentuates that dip.

I'll leave it up to you to decide whether this is viable, but I think unlike that column chart, this perhaps works a little better in not creating that distortion that maybe these volumes are not quite what they seem. All right, let's move that off to the side and look at the second set of data here. In this example, there are totals but let's say we don't want to use them. We do want to use just this data here. I'm going to create a chart again, quickly with alt F1. Looks pretty good. Here, too, I might make the columns wider but what I really might want to consider doing here is use a stacked column chart.

This is a viable alternative to the so-called clustered column chart. It has a couple of advantages and at least one disadvantage. Let's change this chart type here to be a stacked column chart. Now, the big advantage is we can see, at a glance, what the totals are. As I did with the previous column chart, to make this a bit stronger, I'll double-click any column and off to the right in the Format Data Series dialog box, I'll change that gap width again to be 20. Remember, if you don't see this as the set of options, be sure and click the icon with the three columns.

Once again, we can close this after the 20, there we go. Now, the big advantage of a stacked column chart is that we can see, by just looking at the top as I suggested, the totals, April's about 14, June is 20, September is 23, it looks like. December, 22, we can see that. It's a bit trickier to read the various orange entries when they're not adjacent to one another because they're sitting on top of blues. Remember, every little rectangle in here, whether it's blue or orange, represents the accurate proportional quantity that we're being shown here.

That's a simpler, less cluttered look than we saw earlier but it is a bit tricky to read the different pieces. Let's move that aside and look at the data below it here. The difference here is that we've got some negatives here. Let's try this. Alt F1 again to create a clustered column chart. The negatives turn up appropriately. They are negative, they go below the bar. Once again, I'll double-click and reduce that gap width to create a stronger-looking chart. But, if we've got negatives, not bad.

Still kind of busy-looking because it is a clustered column chart with 24 different entries here, but that works. What if we turn this into a stacked column chart? Once again, Change Chart Type, there we are. Now, what we said before about stacked column charts isn't always true anymore. We said before, with stacked column, look at the top edge of the data, that gives us the total. Well, September is 31, it looks like. Yep, that's over there, we can see that. There's that 31 over in cell J12.

Top of the columns on June, that's about 16, that's right. Let's take a look at the March data. We see it over in column D. We've got a 5 sitting on top of a -3. The total of the two is the value 2, but as we look at the top of the entries for March, we're seeing the value 5. It's true that each rectangle is here is truly accurate, a proportional representation of the number. Yet, as we look at the top of the columns, it's pretty obvious where the differences are. Now, imagine if we had multiple states here instead of just two and these were stacked, we had some negatives in here, it would be misleading.

I think you can see, and I think in this case, it's fairly obvious that we just have to read this particular stacked column chart only by looking at the pieces and ignoring that idea of looking at the top edge of each column to say that's the total for that month. Another example, and this one is much more obvious and much more glaring. Again, when I said glaring, I didn't necessarily mean wrong. We've got some data in rows 14 and 15. It's surrounded by empty cells, I'll simply click within it. This time, I want to create a line chart.

I'll go to the Insert tab, click the icon for the line charts here, choose the 4th option, and there's a line chart. Now, we don't know what this data stands for. There's no labeling nearby, but maybe the trend being shown here is one that we'd rather not show. It shows growth, does it mean, is this absenteeism in the office, something like that? Is it showing more accidents on the job? I'm going to resize this chart and make it not be as tall.

Well, there's still change and you can still read the scaling, but it doesn't look nearly as dramatic. I'm going to duplicate this chart, and you can do that easily with control D. That's a duplicate. I'm going to move this copy over here and imagine that maybe this data is showing increased profits or sales. Dragging one of its corner handles, I want to make this a lot taller and not as wide. Both of these charts are based on exactly the same data. Let's ignore those other charts in the background, let me move them off to the side there.

These two charts here are showing exactly the same data, but it's pretty apparent there's a different feel about them. The narrower we make that chart on the right side there and/or the taller we make this chart on the left, I think it's pretty clear that we're telling a different story here, even though we're looking at the same data. In the chart on the left, we are accentuating the dips as well but if that were profits or sales, we could say, "Wow, great six months here, despite those three dips that we had, overall, fantastic." On the other hand, if it's data that's showing a negative trend, and we don't mean values negative but we mean concept of what the data means.

Well, things got a little worse but not that much worse. I'll leave it up to you to decide where this boundary is in the sense that any time you've got a line chart, making it taller or not as tall, somewhere in there, it's probably okay but if it looks unusually tall, unusually wide, you might stop and think, am I trying to fool somebody? Or if somebody else is showing this chart, is that person trying to fool you? Maybe, maybe not, but use that at your discretion. Another example here, maybe more uncommon but nevertheless, something you want to keep an eye on.

I want to depict this data and use an unusual chart type called an area chart. I'm sure you've seen them. Insert tab, you'll find it under line charts. There are two examples that we want to contrast. Here's an area chart, right here. Sometimes, when you look at a chart like this, it kind of looks like a picture of mountain scenery. The blue mountains are off in the distance, the orange mountains, maybe they're foothills, are in the foreground. Are you seeing this as the orange in front of the blue? Maybe, maybe not, let's duplicate this chart.

I'll press control D again, move the duplicate off to the right and go back to Change Chart Type but this time, I'll use the 2nd option here, a stacked area chart. Now, it's highly unlikely you would use both of these charts together, but regardless of which one you're using, take into account how people might read these. In the chart on the left, the highest entry that we see, August and September, as we look at the numbers up above in row 18, those represent 12.

That idea of this being similar to a scenery chart is correct, in other words, that's how we see this. The blue is in the background, the orange is in the foreground. The chart on the right, the orange is stacked on top of the blue and yet, I think I could imagine people, if they were seeing just this chart, it would like as if the orange was in the background. First of all, you probably would never show both together. Even if you show the one on the left or the one on the right, stop and think about how it might be interpreted.

Of course, you can always fall back on what the numbers mean but I think you've got a situation here where some confusion could arise as to what these charts are really showing. By the way, if you use a line chart this way too, we could easily change this one to a line chart. Could this one be a stacked line chart? Right here, with markers, we could use that. How about the one to the left, let's make this not be a stacked chart but make it to be a line chart. In this case, not stacked.

There, too, we're getting different results. The one on the right happens to be curved as well, that's not really necessary, but think out those options and how they might apply. A number of different examples here of how you might display charts and either with intention or not, create some distortions or focus from a more optimistic point of view may be enhancements to the way the charts appear.