Join Dennis Taylor for an in-depth discussion in this video Select data to display as a chart, part of Excel 2016: Charts in Depth.
- We're looking at a file called Chapter Two Chart Building, and we're on the data selection sheet. There's no chart here, but we'd like to create a chart. A chart is based on values, numerical information. If you have no numbers, you can only create what we might call a blank chart. What you wouldn't know at first is that when you select data for a chart, in other words, when you click and drag to highlight data, Excel will know what to do with adjacent labels. So for example, if we were thinking about displaying these numbers here in a chart, we could certainly proceed with this selection, but why not include the information that we typically see above the data and to the left? If we highlight the data this way, Excel will intelligently use those labels in Column A and in Row 3 and put them into the chart.
Without having created a chart before, you might say, well, let's just highlight all this data. As a general rule, you don't want to mix totals and detail. It's not really an ironclad rule, you can do that, and we'll show you what it looks like in a bit. Percentages, by the way, are extremely small. That really is 0.33, this is 0.21 in terms of actual values, and it would not be a great idea to be showing these numbers along with the kinds of numbers we see over here in other columns.
Keep in mind too, if you were showing totals, here's a number 785 and here's a number 40, if those were both turning up in the same chart, you can imagine one column being much much taller than the others. So it helps to experiment of course, but let's consider if we wanted to show this data here, let's ignore the totals for the moment and simply highlight the cells this way. We'll show you different ways to create a chart, but one quick way is simply to press ALT F1, and we have a chart right on the worksheet.
A reasonable looking chart, we'll worry later about the titles and some of the colors and the backgrounds and labeling and so on. There could be times when you select data for a chart that the data you're looking at is not contiguous. In other words, what if you wanted to show domestic in Europe here, and Latin America, but not Asia. In those situations we will highlight the data in pieces. For example I'm highlighting this data here, letting go of the left mouse button, and now holding down the Control key, I'm going to be highlighting Latin America and the numbers this way.
When you highlight non-contiguous ranges, visualize them as coming together as if they were a rectangle. If I press ALT F1 now, we'll see another chart, it will not include Asia. And we see what's happening there, and I'll move that chart off to the side. Or in a different situation, if somehow or other we only wanted to show the totals here along with the labels, we would be highlighting these cells right here, again letting go of the left mouse button, holding down the Control key, and highlighting these cells. Once again, visualize those two sets of data as coming together as a rectangle.
And here too I'll press ALT F1 for a quick chart. Not showing as much data, and probably not the best looking chart, but it does adequately show the data for the totals that we're seeing there. Sometimes when you're working with data, you want to use all the cells. Imagine if we want to put all of these cells into a chart. Because this range is surrounded by empty cells and worksheet borders, we need only click within here, and we can create a chart from here, ALT F1, just like that. That's less likely to happen, but we can do that too.
So we can see that all we need to do is highlight the relevant data, press ALT F1. We'll see other methods later for creating charts, but it's the selection of data that we care about at different times. If you do have data on different worksheets and you want to depict all those different sets of data together, it's best to put that data on one worksheet. It's a lot simpler doing that than later adding the data. Many, many times the data you're going to be highlighting as we see in the examples here is likely to be contiguous data. We saw examples of where sometimes it's not, but many times it's simply, highlight the data, press ALT F1 or use one of the other methods for creating charts.
So, creating charts, simple and straightforward. Highlight the data, press ALT F1.
The training wraps up with lesson on changing data sources for charts and printing and sharing charts.
- Identifying chart elements
- Selecting the right chart type
- Creating basic charts
- Creating sparklines
- Styling charts
- Moving and resizing charts
- Modifying axes
- Adding labels and gridlines
- Analyzing data with trendlines
- Inserting pictures, shapes, and text boxes
- Customizing column, bar, line, and pie charts
- Creating Gantt charts and other specialized Excel charts
- Changing data sources
- Printing and sharing charts
Skill Level Intermediate
Q: This course was updated on 03/01/2016. What changed?
A: We added one tutorial about the new Funnel chart type in Excel 2016.