From the course: Excel: Charts in Depth

Deal with empty and hidden cells

From the course: Excel: Charts in Depth

Start my 1-month free trial

Deal with empty and hidden cells

“

- [Instructor] On this worksheet called Line Chart in our chapter two file we're seeing a line chart, but it's quite a bit different on a typical line chart. In it, it has gaps on and it reflects the fact that the data over in columns B through H has some empty cells, but let's show some options here on how we can deal with empty cells. If you right-click on a chart and Select Data or if you go to the Chart Design tab up on the ribbon, you'll see a choice called Select Data they both do the same to this dialogue box, lower-left corner Hidden and Empty Cells. The default setting in Excel is to show empty cells as gaps. Now, if you can imagine a column chart here we just wouldn't see columns for these missing points here. But here's a line chart that stands out very obviously. Could we make this be zero or if it made sense to put zero on the data we could either do that or in this case, simply choose zero and all those points will be filled in on the chart with a zero values. And so if we'll look at Arizona that's the blue line you see near the bottom on the chart there, we're going to see that jumping to zero for February 3rd, click OK and OK and now we see those zero values. Every time we see a yellow cell over there on the data, we're seeing the zero value being a representative. And again, depending upon the data that could be appropriate. Let's revisit this again. Let's go back to Select Data, Hidden and Empty Cells. Instead we could choose Connect data points with line, on the idea here could be, well, maybe we just didn't report that day, what if these were meter readings something along those lines, we just missed the day here on there, let's just interpellate the value. So if we're looking there Arizona, no data on February 3rd that's between the value five the value of seven is as if we're putting in the value of six here now that won't really change the data, but that's the way will appear on the chart. Connect data points with line, let's not show any gaps, click OK and OK. Now, there are no points there that's a slight difference, but you see what's happen for example, this gray line right here this represents Colorado that February 5th entry which is blank. We see the February 4th and the February 6th entries connected by a line. Maybe for a moment here on this chart or for a while we don't want to see the data from the Midwestern states are all together here rows five, six, seven and eight I'm going to hide those rows and as I do, watch the chart, we don't see the data. And when you think about it, if you got a chart and is appropriate source data nearby, you want that one to one visual relationship. When we ever want to hide the data and you have see the data in the chart, I doubt it, but if we did, we could right-click on the chart, Chart Design, Select Data and this time Hidden and Empty Cells, show data in hidden rows and columns, click OK and OK. So we are seeing the data even though it's hidden. Let's go back to Select Data, Hidden and Empty Cells and turn off that feature. Let's say that nearly always we would want to not show the hidden data when it's part of it chart source data. Eventually, at some point we probably click on the upper-left corner, right-click any row number and bring back the data by unhiding those rows and then seeing these in our chart. So different techniques here for using features available from that button on a Chart Design tab called Select Data, Hidden and Empty Cells.

Contents