Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You can base a chart on data from nonadjacent ranges, either on the same worksheet at the time you create a chart, but you can even base a chart on data that's on different worksheets, or even different workbooks. However, you must first create a chart before pasting that data in from other locations. Quick reminder here: in this particular sheet right here where there isn't a chart, if we didn't want to show the data for Domestic and Asia, we don't have to hide column C or delete it--in other words we don't want to show Europe--we can simply highlight this data right here, let go of the left mouse button and then hold down the Ctrl key and highlight the Asia data. And if I press Alt+F1, we are going to see a chart here without Europe.
So that's easy and straightforward. But here's a different situation. I have got a sheet called California with California data on it, and Arizona, same layout--and that's not a prerequisite--and Oregon. They just happen to have the same layout. I would like to create a chart that shows the sales from all three of these together. Let's just start by creating a chart on one of these sheets, say California here. And I am going to highlight just this data right here to create a chart showing sales data January through June.
Once again Alt+F1, a reasonably fast way to get there. There is the chart. The legend to the right says Sales. We'll have to adjust that eventually. Well, let's go to the Arizona sheet, and we want to be highlighting the same relative range, this data right here. Highlight this data, right-click, and copy. Go back to California sheet, and in the chart area near the outer parameter, right-click and paste. Notice the legend, not the way we want it to be. We will adjust that.
Now let's go to the Oregon sheet and do this same kind of thing, highlight these cells right here, right-click, and copy. Over to California sheet where the chart is and in the chart area, right-click and paste. There we go. So the data is all there, and it reflects the data accurately. We can bounce back and forth and check that out for sure, but we do need to make some adjustments to the legend. So let's right-click, for example, on one of the California bars, the blue one, and go to Select Data, and notice we see the word Sales there three times, and that of course is what we are seeing in the legend.
Let's take the first one here and edit and see what this is actually referring to. Its referring to the California data, but what we want to see here instead of the word Sales, you see how it says California A4, its picking up the word Sales, we want it to actually have the word California in it. So I'm editing here, backspacing, and putting California within double quotes, one at the end there and one in front of the C, and then OK, and watch the legend on the right. We now see California there.
So we've got to do this two more times. The second Sales here, we click Edit. It's going to say Arizona. I am going to click on that top panel, put in the trailing double quote and also an initial double quote in front of the A and behind the equal. Click OK and that cleans up that one and then Sales here, the third one, Edit, same idea. Just the word Oregon in double quotes here. There we go. OK. And in a similar fashion, we could have copied and pasted data from other workbooks as well, in this case just other worksheets of the same workbook.
So a chart can be based on data from different locations, not only from the same worksheet, but also from different worksheets, or even different workbooks.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64863 Viewers
80 Video lessons · 124401 Viewers
52 Video lessons · 60325 Viewers
59 Video lessons · 46149 Viewers