Join Dennis Taylor for an in-depth discussion in this video Chart display options with blank cells in source data, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Chart display options with blank cells in source data