Chart display options with blank cells in source data

- [Instructor] On this worksheet we've got two sets of similar data, and columns A and B were showing sales for a company day-by-day for a given month. And you'll see a gap every so often, maybe they don't have sales on Sundays, so we're not showing the 7th, the 14th, the 21st, and so on. In columns L and M, we've got the same data ultimately but we are showing those Sundays here but with no sales. Let's create a chart from the data off to the left. I'll simply click within it. Because it is surrounded by empty cells, we can create a chart quickly, ALT + F1. If you haven't changed the default style, you will get a clustered column chart. And Excel mistakenly uses column A as if it were data. We can easily change that by right-clicking within the chart, selecting select data, and uncheck the box for day. That will be used as labels now across the bottom. So that's a reasonable looking chart. We can make it taller, wider, and so on if we wish. We'll do the same thing with the data to the right, columns L and M. Click within it, it too is surrounded by empty cells. We can press ALT + F1, and get a chart. And here too, first order of business, let's right-click within this. Select data, and uncheck the box for day. There we are, and same general idea. What a different look. And I think you can make a case for either one of these charts being acceptable. Now let's change the look and turn it into a line chart. You can easily change the chart type. Once it's selected, you can go to the design tab. If you're using Office 365, later versions, you will see chart design, same as the old design tab, but off to the right, change chart type. We've cut the line, how about the first option. And you see the preview already, so I double-click this. Looks like that. And I'm not saying that's wrong, that could be acceptable, it does accentuate the fact, well you don't have sales on Sundays, so that's how things would look. In a column chart, like we saw earlier, I'll just press CTRL + Z, we see what's happening. That's reasonable, that accentuates the idea differently. I'll press CTRL + Y, that will undo the undo. Go back to here again. But we do have some options here. I want to leave this here but I'm going to duplicate it by pressing CTRL + D, and just drag one of the duplicates over, move this one left here for a bit. So for the moment they're identical. On one of these, I'll right-click, go to select data, and in the same select data source dialog box that we saw before, down in the lower left corner, hidden and empty cells. We've been showing the empty results here as gaps, and we could show them as zero, and I think as soon as you see this you might say, well I'm not so sure about that. But that could be acceptable in some cases. Now could you have some kind of data where there are zero sales days? Well, probably not, but with certain kinds of data you might have zeroes. Maybe they weren't reported. So think out how with different kinds of data, that could be appropriate. Let me duplicate that, CTRL + D, leave that there. Move it over, we've got two of those for the moment, and show one other option here. Right-click on the chart, go to select data, lower left corner, hidden and empty cells, and this time connect data points with line. Now would that be appropriate? Probably in the data we're looking at, I don't think so, it's connecting them. We didn't have any sales on the 7th and yet this shows that we did. Now if these were for example something like a meter reading and you just missed certain days, you probably don't want them as zero and you might just say, guessing based on the trend here, we'll just interpolate and fill in the numbers. So this is actually, if you could look at the chart to the right, imagine we're filling in the gaps here by simply connecting them, that's what this does. So the real key to all this and how it will make sense is the kind of data you're dealing with. Sometimes appropriate, sometimes not, to use any of these three different options. But do remember, if you do have gaps in the data, you do have the options here by way of right-clicking, select data, and going to those choices we saw under hidden and empty cells.