Often, the data you depict in a chart is based on a rectangular range of cells that includes a title row on top and a title column on the left side. If you need to depict chart data based on non-adjacent cells, use the Ctrl key to select these cells. Visualize the cells coming together to form a rectangle.
- [Voiceover] The first step to creating a chart in Excel is to identify the data that you wish to show in a chart. In Excel, a chart means you've got numeric data that you'd like to depict graphically. With no numbers you really can't create a chart. We've got some numbers over here on this Worksheet, it's called Data Selection. We're looking at a file called Chapter One Chart Creation. Now, we might not want to show all the numbers here and often if you've got totals and details together, it's probably not a great idea to show both of them. What you wouldn't necessarily know at first is that adjacent labels for example, these labels right here that describe the data in the columns below, and the labels that we see in column A that describe the data in the rows to the right, this information can be used intelligently in charts.
And in the two examples we see to the right we do see how those are being used. So when we select data to put into a chart, we usually include those labels along with the numbers. And most of the time when you're selecting data to go into a chart, it's contiguous data in one rectangular grouping. In this case let's say we do not want the totals along with the detail, just the detail itself for each of the six months for the four regions. Insert tab in the Ribbon has some chart choices right here.
Column, probably the most common chart type in Excel and Clustered Column within that, the most common type. We've got a chart, not perfect on all respects. But let's say as we look at it briefly here we've got a Legend across the bottom that explains what each of the colors mean. Each Cluster of Columns is identified by a month. We don't have a chart title in there. Of course we will click that and type that in later. The numbers down the left hand side, need a description perhaps but with the data nearby we could forgo that even. It doesn't take long to create a chart.
Now, I want to show you what would happen if we did include the totals. Going back to the data, I will include the totals. Nobody's gonna stop you from doing this. I think most of the time it makes for an unsatisfactory chart. Go back to Insert and choose Column and Clustered and there we see what's happening. So, that's why the recommendation was earlier, don't mix totals and details together. Certainly not wrong in one sense because you can do it but not recommended. I just press the Delete key and the chart disappears.
And with that recognition too, you realize that when you create a chart sometimes it doesn't look the way you want. Sometimes you want to start over. Maybe you did select the wrong data. Just by clicking the chart, pressing the Delete key, the chart is gone. Now there will time when you say "well, I don't want to show all this data". Already we've decided that by not showing the total and the average. But what if we wanted to show some of the data here but not Asia? Maybe Domestic, Europe, and Latin America. If we start to highlight the data above this and then stop, let go of the left mouse button, we can that hold down the command key and highlight the appropriate Latin American data.
When you're highlighting non-contiguous ranges you can use the command key to highlight a different range. Visualize these ranges as coming together as a rectangle and usually when you do that you're likely to come up with a sensible chart. Let's go back to the Insert tab, this time we'll choose Column again, Clustered Column and we see the data looking very much like our first chart except this one is Domestic, Europe, and Latin America with no Asia. So fewer Columns in each Cluster there. There could be times too when you say "I want just this data being shown", for example.
How about just the total data here? Let's show that by way of a Pie Chart. We would want the labels over in column A to be included. So once again, letting go of the left mouse button, moving over to cell A4, hold down the command key, highlight these cells right here. This time we'll go to the Insert tab, and off to the right we'll choose Pie. Maybe a 3D Pie, click that, we've got a chart. The Legend tells us, we can later add labels here that gives us percentages. We do see those in column I by the way.
This gives us a breakout too. Notice again, the data that was highlighted over there and I did start by highlighting cell A4 but it has no meaning in the chart. So, highlighting non-contiguous areas makes sense. Sometimes you want to show just some of the data as we saw it here. There could also be times when maybe we want to show the data here and in this example here, this data is surrounded by empty cells and Worksheet boundaries. So, in a case like this we don't even have to highlight the data.
If we click in here and create a chart, Excel will automatically pickup the surrounding data. Now if we tried that up here, Excel would be trying to use all this data and that would make for a very strange chart because those percentage totals for example are actually values below one. And then we've got all kinds of large numbers and small numbers mixed up, that would be a bit of a disaster. But down here in this data, I'll just click in here and I'll go right into Insert, again create a Column Chart here and there we've got a chart based on the data that we see there, fast and easy.
So anytime you do want to create a chart from data that's surrounded by empty cells or Worksheet boundaries, you can create that chart simply by clicking within it and then using the appropriate command on the Insert tab on the Ribbon. So, the process of chart creation is generally pretty easy, pretty straight forward and as we work with charts sometimes we will spend perhaps a lot more time in fine tuning the chart. That's gonna come a bit later but, and let's face it, what we see when we first create a chart is in some cases almost complete. Quick adjustment of the Chart Title, maybe some data down the left hand side to explain what's going on and we could be finished.
So charting is generally pretty straight forward. It all begins with a process of selecting the data to go into the chart. More often than not that's a contiguous set of cells but as we saw in at least one example there, we selected an area or the cells were not adjacent. Then you'll need that from time to time. Using the command key as we highlight non-adjacent ranges and creating charts that way.
- Selecting data for a chart
- Creating charts with keystrokes and ribbon icons
- Selecting the right chart type
- Adding titles, labels, and gridlines
- Choosing a chart layout
- Changing a chart's location
- Adding gridlines, data tables, and trendlines
- Formatting charts
- Creating column, bar, line, pie, and scatter charts
- Changing a chart's source data
- Creating Gantt and frequency charts
- Printing and sharing charts
- Converting a chart to a graphic