Join Dennis Taylor for an in-depth discussion in this video Creating charts, part of Excel 2016 Essential Training.
- View Offline
- We're looking at a worksheet called Creating Charts in the file 07 - Charting. We can see the numbers, we know what's happening, and yet, a picture can really help. Some of us don't absorb the numbers as easily as others. Let's imagine we'd like to display a chart to accompany this data. Now in Excel, officially and formally, we use the word Chart, not Graph. If you say Graph, I have a good idea you mean Chart, but Chart is the term we look for and we use in the Excel menu system.
If you want a chart quickly, the data's highlighted, press alt + F1. You'll get a chart immediately, right on the same worksheet. That might or might not be the best chart. Notice also, the menu system has changed. We've got a new ribbon called Chart Tools. There's a Design tab, there's a Format tab. The Design tab is more important, for the most part. The Format tab is a lot about the visuals. It's a lot simpler, it's been redesigned from prior versions starting in Excel 2013, so, still, one of the problems you will encounter as you work with charts sometimes is, you've just got way too many choices.
So, we've created a chart here with one method, alt + F1. Alt + F1 always shows what's called a Clustered Column chart. The most common chart type for most Excel users tends to be a chart of this type. Now, I'm going to delete this chart simply by pressing "delete". How about a chart with this data? I didn't say it earlier but do, when you're selecting data for a chart, highlight the row that's typically above it, also the column that's usually on the left side for descriptors, labels.
These will appear in charts as they did in the prior example. As a general rule, but certainly not an iron-clad rule, do not mix totals and details together. Now, we could do this, and I'll show you a little bit later what that looks like, but imagine some of these totals appearing here. Ultimately we've got a total here that's almost 8,000, and yet we've got some numbers here that are not even 200, so we're going to have extremely high columns and very small ones at the same time. So keep those out of there, most of the time. These are highlighted, we can create a chart certainly in some other ways as well.
The more standard way is to go to the Insert tab, and we've got a section over here, a group called Charts on the Insert tab. And off to the left, Recommended Charts. Let's try that. This is another approach to charting. And we see some options, and often we'll see a scroll bar. How does that look? That's called a Clustered Bar chart. Now, I'm not suggesting in any way you memorize these, but you'll start to get familiar, certainly with a few types here. Clustered Bar and Clustered Column are very common as is the Stacked version.
Here's a Stacked Bar. Down here's a Clustered Column. Now as you're looking at these, you're not always sure which type looks best, so, scroll up and down a bit, explore these, take a look. You'll decide on one of them, maybe this one is the one you like, click OK. That's certainly an approach to printing. And once again, we see those two tabs, Design tab and Format tab. Click outside of the chart, they disappear. It's easy to create a chart, and there's another way too. Same data, I've got it highlighted here.
Soon as I highlight these, a little box appears in the lower right corner. It's called the Quick Analysis button, click it, and here we see Charts, along with some other features. Click Charts, and we see some previews here, as we slide across these. So this is pretty fast as well. There's the one I chose already, but I might want to create another one looking like this. And if that were not enough, there's another way to create a chart, a keystroke shortcut for putting a chart on a separate sheet.
I've got my data highlighted. Now maybe this time I don't want to show all the data. Maybe I don't want to show Southeast, but I do want to show Southwest. When your data is not contiguous, you can highlight the data in chunks, using the control key, as I'm doing here, highlighting the data. I don't want to show Southeast, but I do want to show Southwest. When you're highlighting non-contiguous cells here, you use the control key. When you're highlighting the first group you don't need that, but you let go of the left mouse button, then hold down control, highlight the other range or ranges.
Envision these pieces as coming together as a rectangle. If we want a chart on a separate sheet, we'll press "F11". Totally new sheet to the left of the sheet that's got the data. Now, is it any better to have a chart on a separate sheet? If you want to focus just on the chart and fine-tuning it to make it look just the way you want it to, and you want to print it all by itself, that's fine, you can use this feature. I think it's more common to see a chart next to the data, but you can go either way.
If you change your mind and say, "I want to move this," click near the upper perimeter of the chart, up top, right click Move Chart. We can move it to either the same worksheet that we saw the data, or a different worksheet if we wanted to. And when we have charts on a worksheet like we do here, we can right click the chart here, do it in the upper section near the top border, usually the best place, and here too, we could Move a Chart, so, no matter where you put the chart, you could move it later, if you wanted to, but we've seen how to create a chart pretty easy. Another way, too, slightly more methodical, where you know ahead of time which chart type you want, there's not much doubt, you've got your data highlighted, maybe it's this, you go to the Insert tab, and you say, "I want a bar chart." There's some column and bar charts together here.
Click this, click this, all set. All these ways, I think you can see are pretty fast. I think you can also see, too, that, although the charts are not complete, they have most of the elements that we would need. They have a legend that works, the labeling appears either below or to the left of the clusters, we see numbers on the screen. The title isn't what we would want and the numbers aren't explained, but in other respects, the charts are almost there, so it certainly doesn't take long to get started. If you start exploring on the Format tab, you'll eventually see lots of choices here for making this look better in terms of color, but stick to the Design tab for awhile to get the essentials of charting down.
This is where we'll be spending time in the next movie, talking about different buttons here, like Switch Row/Column and Changing the Chart Type, but when you make your initial selection, it can be very fast. Highlight the data, either press alt + F1, F11, or go to the Insert tab and create a chart using some of these choices here.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros