Join Dennis Taylor for an in-depth discussion in this video Creating charts instantly with shortcuts, part of Excel 2007: Charts in Depth.
- View Offline
After indicating what data you want depicted as a chart, there are two major keystroke shortcuts, one of them new in Excel 2007, that let you create charts instantly. I sometimes refer to these as show-off kinds of features because when you are demonstrating, or showing to others, how easy it is to create a chart, sometimes people are amazed. Let's imagine on the set of data that we're looking at here I'm going to be highlighting this data right here. I'm in a hurry. If you want to create a chart and have it on a separate worksheet from the data-- and sometimes you might want to. I think its more common these days and more likely to want to put the chart on the same worksheet, but if you would like to put this on a separate worksheet, simply press the F11 key.
Now you notice at the bottom of this screen we are currently looking at a sheet called ChartData. I have highlighted the date in question from cells A4 to G8. I'm going to press the F11 key, and here's the chart on a new sheet, and you'll notice at the bottom of the screen it's called Chart1. It's to the left of the data. And if we go back to the original chart datasheet and create another one, it'll be called Chart2, and it'll be to the left to the data, and so on. One advantage of working with charts on there own sheet is you're not looking at the data. You might have a ton of it and the chart might be pretty busy. You're seeing it full screen. You can print it easily, and you can focus strictly on the chart itself as you work with it.
So that's one advantage to working with data on the separate sheet. The F11 shortcut has been around for quite some time. But a new shortcut in Excel 2007, as we go back to the ChartData sheet, is, after having your data highlighted, if you would like it chart right on the worksheet next to the data, it's simply Alt+F1. There we go. It's right there. Now something to point out here that will at times be a little confusing. The data here came up clustered in a certain way here.
Now what if I were to highlight just the first three months? You could imagine, for example, the chart to the right not showing April, May, and June, but looking pretty much the same. Let me move this down and over a little bit, shrink it a little bit from the corner. So we're looking at the data here. I'm going to highlight just this data right here, and I want to create a new chart right here on the worksheet, Alt+F1. And notice the chart is oriented quite a bit differently, and you're saying, "What's going on there? Isn't that strange? It's not grouping these by month any anymore." Based on the number of columns and rows in the data that you select, Excel makes an arbitrary choice, but in the second example here, we had more rows, we had four rows of data, than we had columns, three columns of data.
First time around when we highlighted this data, we had four rows of data and six columns. So depending upon what's more, rows or columns, you can get the different chart type. So what's going to happen if we choose April? Now this is an unlikely combination, but you could choose this. I'm going to press Alt+F1, and you see what's happing there. That's more like the first chart, which is to our lower left. In this case, it's a tie, so Excel goes one way or the other. So you will find that confusing at times, and one of the buttons that we'll be using at different times is anytime you do see a chart that's on the Design tab in the Ribbon, Switch Row/Column, and then you'll be this way, so you'll sense that from time to time.
But getting back to the shortcuts. Anytime you want to show data, for example a different sheet here like on the Line Chart sheet, here since our data is contiguous and everything in column A and B is something that we might want to show in the chart, we get everything there. When you all ready to create a chart, remember you don't highlight all the data although that's not wrong. Simply click on a single cell, and that means don't click on two cells, just a single cell within this cluster of cells here, and we'll simply press Alt+F1 again and we'll get the default type, which is a column chart, and we see the data that way. So, fast and easy.
And there will be times when you are not really trying to create a chart long term, you just want to see a quick visual representation of the numbers, and it gives you a quick read on the data and maybe you will get to the chart later. So charting sometimes is used to just an exploratory way, and these great shortcuts, F11, for creating a charts instantly on the new sheet or to create a chart on the current worksheet, Alt+F1-- two great shortcuts. I think you will use them a lot. Instant creation of charts.
- Identifying the plot area, chart area, gridlines, legends, and more
- Selecting the right chart type
- Creating charts instantly with shortcuts
- Choosing a layout
- Dealing with empty and hidden cells
- Switching rows and columns for a different view of the data
- Moving and resizing a chart
- Inserting pictures and shapes
- Adding labels to a chart
- Analyzing existing and future data with trendlines
- Changing a chart's data source
- Printing charts