Adjust a chart's source data and adjust its series order

- [Voiceover] When you work with charts you often want to be reminded of where the source data is. And very often, that's not a concern, because charts typically are near the source data. But as we work with charts and move data around, and sometimes take out data temporarily, sometimes the indicators are a bit off. I've got some data here. I'm about to create a chart with this data. Now that is surrounded by empty cells on all sides in worksheet boundaries so I don't even have to highlight it. I can click inside and to create a chart quickly, right on the same worksheet, it's Alt + F1.

The default chart type in Excel is a stacked column chart. That's what we see. I haven't changed it. You can change it if you wish. So, I more-or-less like the look of this, but I'm about to make a presentation, and notice because it is selected the corresponding source data is highlighted as well. But, I want to add a little bit of flare here. I'll go to the Design tab, I like that background, stands out a bit better. I'll quickly put in the title by using a nice little shortcut here. I'm going to click Chart Title, type =, and click on Cell A1.

That's a merge cell so I actually can click anywhere in this area here, and simply press Enter. And I also want a heading down the left-hand side, an axis title, so to left on the Design tab here, Quick Layout, I'm going to choose an option that has a vertical axis title. There's one right there, I'll click there. And here, too, for my axis title, I want millions of dollars so I'll click right here, type =, click on this cell, this too is a merge cell, that's not a requirement, click that cell and Enter.

So we've got our titles in place. But I don't need a title on the bottom so I get rid of that. So, I'm ready to make the presentation. It occurs to me that maybe for this presentation I don't want to show China, for whatever reason. The source data is off to the left. If I don't want to show China, all I need to do is drag that fill handle to the left, like that. And we're not seeing China in the chart. That's fine. I'll change my mind later or when I'm finished with the presentation I'll come back and anytime I click on the chart, I see the selected source data, I'll move that fill handle in the lower right-hand corner from cell E16, move it to the right.

Now, for another presentation, it turns out I don't want to show Canada or Australia. Either or both, let's say just Australia for the moment. So, clicking in the actual chart itself on one of the Australia columns, Australia is yellow, I'll click one of the yellow columns, it could be any one of them. All of the yellow columns are selected, I'll press Delete. In no way does this change the source data but as I click outside of this chart and then back in the chart, watch the source data. It's not highlighted at all.

And nothing's seriously wrong with that but if this is passed on to another user who happens to highlight this, and what if there are other charts that have been placed here in the meantime, maybe this is farther off to the right, other charts are in the intervening space, it's a little confusing at times and you say, well, why isn't that highlighted? Why isn't the source data highlighted? Because it's no longer contiguous. It's not exactly a satisfactory answer but that's why. So, what if, at a later time, we say let's bring Australia into the mix? I'm gonna highlight Australia and press Ctrl + C to copy.

Now, oddly enough, if you right click in the chart area, you do have a Paste option. But if you right click on the plot area and you think that's where you want to paste it, there's no option for Paste. However, you can press Ctrl + V, do it that way. And it's been added. But it's been added on the right-hand side. Australia is on the right-hand side of the chart now and that's probably not where you want it and when we come back later and click on the chart, is the source data highlighted? No, it isn't. Even though it's got all of this data here being accounted for within the chart.

But because we took out the data then brought it back in, it's in a different location, we can't get to the data. Now, for the sake of continuity, if we're looking at the chart and the data, we might say, can we somehow move Australia to the left of China? You can do that. You can right click any column, it doesn't make any difference, doesn't have to be Australia necessarily, I'll right click any column in here, and choose Select Data. And I want to change the order of Australia. So, there's an arrow right here.

Now notice, US, Europe, Canada, China, Australia. As we look at the chart, that's the order that it's in right now. That's not the order the data is in so we want Australia to move up and put it between Canada and China. So here's the up arrow here and watch the chart, there'll be a pause, it will get adjusted, we will see that yellow or gold column there move to the left. It just happened. That way. And so we can complete that. Now is the data contiguous? Yes, it is.

And our data is highlighted again properly. But here's another thought. The columns are going to be a bit easier to read if, not only do we made this chart wider, that helps sometimes, but not always enough, if we could have the taller columns more-or-less all together. So, in general, as we look at the data it looks like China is between US and Europe. If not in every single case it looks like in most cases. So, let's move China. Now, if we do this, it means in the future when we click the chart it won't be highlighting our source data.

But if that's not gonna be an issue with this, we don't necessarily care. So, once again, we'll right click any column, why not China, good enough. And then Select Data. Here's China. Let's move it up the list and put it between US and Europe. And I'm doing this three times. The chart doesn't exactly react immediately. Actually, I've moved it beyond US, you see what's happened? It's the second column, now I'll move it down. And so, US, China, Europe, Canada, Australia. That's the order we see in the chart. Perhaps it's easier to read now.

And there it is. Now, we can also add other data as well here. Suppose I want to do an average? Type in Average right here. And then, for all these cells, and I can do this all at once, I want an average for each of these, and I want to add that to the chart. Each one of these is going to be an average of the data to its left, these cells right here, and I'll complete the entry by pressing, not Enter, but Ctrl + Enter, since we've got multiple cells highlighted. We've got averages there.

And I'm going to paste this into the chart. Copy this format here to the right, that's minor, of course, with the right mouse button, copy the format, there we go. Highlight all this data here, Ctrl + C, I'm going to click in the chart and Ctrl + V. So we've added Average but it comes in as a column. Now there's nothing seriously wrong with that but it would be a lot more interesting since that data truly is different in the nature of the data than the others. Let's turn that into a line. And we can do this by going to the Design tab.

Change the Chart Type to be a Combo chart. You might not be familiar with this. And what do we see below the data here? The US, China, and Europe are considered Clustered Column. The preview we're getting here is showing us line chart entries for Canada, Australia, and Average. Let's say let's keep the Average as a line chart but the others, let's change them into Clustered Column. So let's make Canada be a Clustered Column choice, that's the first one there.

And the same thing with Australia, Clustered Column. But let's keep Average as a Line Chart. And here's our preview right above. We're seeing that there. We click OK. And there it is. As we click the chart, we're not seeing the highlighting the we did before. That might have been a surprise because we are looking at contiguous data but nevertheless there's that sort of disconnect that we get from time to time. So we're seeing different examples here of how to manipulate the source data. I've decided I don't want the Average after a while, I can click that Average line and press Delete.

I've got some data on another sheet. Sheet 1 here, Japan data. Ideally I'll put that with the other data but I'm in a hurry here so I highlight this, press Ctrl + C, jump back to the yearly data sheet, click in the plot area, Ctrl + V, and now I've got Japan on the list. That's how it comes in as a green column. And here, too. For sake of order, possibly, I might want to move that left to right. Remember, once again, we can right click any column in here, select data, we're back here, click Japan, let's move it up, keep an eye on the chart, probably want to move it a couple of times.

In general it falls between Europe and Canada, number-wise. Again, sometimes these numbers are not perfect, sometimes they're much more erratic. Looks like in these examples here it's fairly consistent in terms of how the countries are stacking up month by month. So, we've seen a number of different techniques here for working with source data in a chart and adding data, taking out data in different ways. And in one example we saw how to add an average and then by way of a combo chart, have that average be displayed as a line instead of a column.