Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In this course, Dennis Taylor shows how to analyze and communicate the value of data with charts in Excel. The course starts with the foundations: what the parts of a chart are, what the different types of charts are, and which charts work best for your data. The course then shows how to create a presentation-ready chart in minutes and offers dozens of in-depth tutorials on formatting and fine-tuning charts so they represent data clearly and accurately.
Pie charts, although ideal for certain kinds of data, many times are inappropriate. Nevertheless, when you're using pie charts, you need to make some adjustments to them, and the two pie charts that we see here certainly are lacking in explanatory information. Let's point out a couple of deficiencies, particularly with regard to 3D pie charts. As you look at the chart on left here, which is reflective of the data above it, for the Domestic row, we see the breakout for the first six months.
Now, without looking at the numbers and looking at the wedges, is the green wedge bigger than the blue wedge to the left, in other words, this one here, the one over there? The green wedge is March. The blue one over there is May. You look at the numbers: March is 125, May is 140. Can you see that difference? I pretty much doubt it? Most people can't. And depending upon it the order in which these colors appear here, you might or might not be giving precedence to one wedge over another without necessarily knowing it.
If you've created a pie chart without concern for any order, these will be in a circular order moving from the back in a clockwise order. So the blue that I'm pointing to here is January and then February. But let's talk about how we can change the appearance of this, because there are some tools specifically for 3D charts. And the general rule, as we've been saying throughout this course in terms of Excel 2007, when you want to make a change to an object or a chart element, begin by right-clicking on it if you have not found any choices in the menu.
So if we were to click this chart and then right-click on one of the wedges here, go to Format Data Series, we begin to see some of our choices here. I'm going to focus first on 3D Format and lots of choices in here and also, as we start to adjust some of these, you can change the material. These are things that I think a lot of us never even thought we would ever need, and maybe you still won't, but a lot of different choices here under 3D Format. We can certainly fill these in with different kinds of colors.
We can change the border color, the border styles. We've got shadows. Let's change the entire chart. Now, under 3D Format, we're not seeing any choices here regarding rotation. So let's click back on the chart and simply, without picking one of the wedges here, just right-click in the plot area and choose 3D Rotation, and here we have some choices. Best approach here probably now as I'm clicking these arrows, watch the chart.
We see what's happening. If you want to move January more toward the front, it's the blue that's next to the red, so it's about to move towards-- it's on the right side. Now, it's moving more and more forward. So January is in the front. That might or might not be what you want. What happens here with these buttons? You can begin to see how that tilts the chart in different ways, and we can change the perspective as well too, either with this or with the arrows. And again, for a lot of people, this is very distortive. April in the background there, the purple color, looks a lot smaller than January here, and it's actually a lot bigger.
So we've got some problems with this, but again, it is vivid. It's colorful and if you're trying to show the breakout, say, for budget type numbers, sometimes this is the appropriate chart. I know some people who say, "Don't use 3D pie charts at all. You should use 2D pie charts like the one we were seeing here." The approach here is because we are not appearing to be looking at it from an angle, the sizes of the pieces appear to be more accurate. But in either of these cases, I think it's appropriate to say let's add some labels.
One rule of thumb I've heard is don't be using these pie charts in either case if you have more than five or six or seven wedges, and I know this is not quite exact, but it will depend on the number of wedges you're using. Let's make some adjustments here. Once again, we might start by right- clicking, and where do we need to go this time if we want to add labels? Add Data Labels. And some people like the approach of just using these here. Now we've got some other choices here too. If you go to the Layout tab and choose Data Labels, you can see some choices here related as to where those labels up here.
Some people like them toward the outer edge. Now we got a problem with that black wedge, don't we? Yes, we do. Inside, Outside, Outside the End, Best Fit. Try it that way too. So we've got quite a few choices here related to where these labels are going to go. Some people want them this way as well too. So you do have some different choices here. You can also, on a one-by-one basis, take these labels and drag them off the chart, like this. And you will see, without even trying or setting anything, what are called leader lines.
Now trying to do this on black might be a little tricky. I did happen to catch it there, but you don't always do that so easily too. So we can do this here too. This can get a little old too, can't it? Notice how, depending on where you might drag this, you don't always see the leader lines. So that might cause you to right-click here and say, "What's going on?" or, "What can we do with these labels?" Do a right-click here and talk about the Data Labels, and we can see positions here. Include legend key in labels, that's another option. Show Leader Lines is always on, but you will not see them necessarily unless the positioning is at a certain place.
And so here and there, sometimes try as you might, you can't get them disappear, but sometimes they won't be there, and if you drag it onto the wedge, you see what's happening there. So this is just something you have to experiment with and come up with your own conclusions as to what works best for you. But I think you can readily see, on pie charts, you will need to add labels most of the time. And the labels here are the values. You might not always want to use values. So once again in the dialog box there, you do see Value, but how about Percentage? You might want both. I think a lot of people who would want only one or the other, but you can certainly do that if you wish, choose both.
I'm going to uncheck Value, and now we see percentages. So there are quite a few choices there. And similarly, we could do the same thing with a 3D pie chart. So even though there is limited use here, I think you can see you do need to make some adjustments. And one more point about this too: if you select data here and create a pie chart, if you haven't worked with pie charts at all, you're going to be a little bit surprised. For example, if I go to the Insert tab and choose Pie and whether it's 2D or 3D, it doesn't make any difference, you might be scratching ahead a little bit saying, "Why does it say Domestic?" A pie chart, unlike most other chart types, can't handle more than one range.
So we're looking at this data automatically. Now if you were dead set on showing a pie chart with Asia only, you would be selecting this range and then with the Ctrl key, select this range. And then in the Ribbon, Insert > Pie, pick either one, you want, maybe 2D pie this time, and you see what we're choosing here. So regardless of how much data you're highlighting, Excel can only handle a single portion of a row or column, not multiple rows and columns, in a pie chart.
One more aspect of this too. I'm going to change this data and make it negative. Now, not just for fun, but just based on the idea, what if the sales adjustment number here for February, because of returns or whatever, was -10? I'm going to change this number to -10. That's in cell C2 there. As I press Enter, watch all these charts. Now what will happen in all of them is that the February wedge will become much smaller, but is there any indication whatsoever on any of these charts-- the Asia chart doesn't include this we don't see it there-- but on the other charts here, the February wedge is certainly smaller, but is there any indication whatsoever that it's negative? Now over here, we take a look on this chart where we actually have titles, and it's a little tricky to move the side.
You can see where it's going on there. That would certainly get our attention, but the concept is totally inappropriate. I guess we could say there is no such thing as a negative piece of pie. And it is just a little bit strange that Excel doesn't really prevent you from creating the pie; it just treats the data as if it were a positive entry. So in wrapping up the whole discussion here about pie charts, certainly valuable for certain kinds of data. They definitely have limitations. I would suggest a 2D probably better than the 3D if you're looking for accuracy and display.
And in either case, I think it makes good sense to put in data labels on pie charts.
There are currently no FAQs about Excel 2007: Charts in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.