Locked lesson.
About this lesson
Learn how to build an effective pie chart, and when you should and shouldn’t use them.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Building Pie Charts.xlsx15.3 KB Building Pie Charts - Completed.xlsx
20.2 KB
Quick reference
Topic
Building pie charts in Excel 2016.
Description
How to build an effective pie chart, and when you shouldn’t use them.
Where/when to use the technique
When you need to convey a simple percentage value or a maximum of 3 data series that need to be displayed as a proportion. (If there are more than 3 data series, reach to a bar or column chart instead.)
Instructions
Before you start, be aware
- Your brain is not good at working with angles or areas
- If you have more than 3 data series, move to a different chart style (bar or column)
Creating a Pie Chart
- Select A7:B8
- Go to Insert --> Recommended Charts and select the pie chart
Adding context
- Select the chart title, press the equals key, click on A4 and press Enter
- Click on the pie chart
- Right click and choose Add Data Labels
- Right click the Data Labels and choose Format Data Labels
- Select Percentage and clear the Values
- Set the label position to Center
Reducing ink
- Click the largest area of the pie twice (slowly, not a double click) to select the series
- Change the Outline to a dark green from the mini-toolbar
- Click the smallest area of the pie twice (slowly, not a double click) to select the series
- Change the Outline to a dark red from the mini-toolbar
- Select a worksheet cell, then right click the entire pie
- Set the fill to “No Fill”
- Right click the smallest point on the pie and choose “Format Data Point”
- Set the Point Explosion to 10%
- 00:04 In this video we're going to look at pie charts.
- 00:06 But before we do, I need to tell you why you should try and
- 00:10 avoid using these charts at all costs as much as possible.
- 00:15 The challenge is that your brain is not very good at working out angles or areas.
- 00:21 And the challenge with pie charts is they're all about angles and areas.
- 00:24 So it is very difficult to read the data in a pie chart
- 00:27 without actually having a legend or the specific numbers on it.
- 00:31 And this is nothing personal, it's just something to do with the human brain,
- 00:34 that we're not as good at working with angles as we are with straight lines.
- 00:38 So my rule of thumb is, if you absolutely have to put a pie chart on your worksheet,
- 00:43 it should have no more than three data series.
- 00:45 As soon as you get to three data series or more, You should be flipping it out to
- 00:49 a bar chart or a column chart, because your brain is excellent
- 00:52 at drawing the distinction between two lines of different length.
- 00:56 Having said that, pie charts are asked for by name, and
- 00:59 sometimes you just have to put one in.
- 01:00 So let me show you how to build the most effective one we can.
- 01:04 We're gonna start by selecting our data, and
- 01:06 we're not picking up the headers this time here.
- 01:08 We're just gonna go with the spent, remaining, and the values.
- 01:11 And we're gonna go to insert and recommended charts.
- 01:14 And you'll notice that the pie chart is not the first most recommended chart.
- 01:17 It says use a column chart instead.
- 01:19 Regardless, we're gonna choose pie chart, and we'll say OK.
- 01:25 The challenge with pie charts is that they have this dense color saturation fill,
- 01:29 this is one of the other problems with them.
- 01:31 And it screams for your attention when it's on a report.
- 01:33 No matter what else is on that report, the pie chart is in the corner going hey,
- 01:37 hey I'm over here.
- 01:38 And it wants to keep dragging your attention back to it,
- 01:41 and that's a real challenge.
- 01:42 So what we're gonna do with this is we're still gonna make some changes to give it
- 01:46 some context.
- 01:47 We give it a nice title.
- 01:48 So we'll link this, grab the chart title so
- 01:50 it equals the project spending forecast.
- 01:52 But what we're gonna do is we're gonna soften this chart so
- 01:55 that it's not quite as obnoxious.
- 01:58 So to do that, what I've done is I've selected the chart once and
- 02:02 now I'm going to select the blue series again.
- 02:04 And you can see it makes a very subtle difference in the user interface,
- 02:07 not much at all.
- 02:08 I'm gonna right-click on this and I'm gonna say Format Data Point.
- 02:14 Now at this point, I'm gonna go to the Fill Bucket and
- 02:18 I'm gonna change a couple things.
- 02:20 Number one, I'm gonna change the border to a solid line, and
- 02:22 I'm gonna set it to a solid blue line, no problem.
- 02:27 I'm then also gonna go to Fill.
- 02:30 And I'm gonna set it to No Fill, so that's gonna empty it out just a little bit.
- 02:35 Now we'll select the orange data points.
- 02:38 And on this one, again, I'm gonna go to the Border, I'm gonna say Solid Line,
- 02:42 and you'll notice that it's giving me a blue line.
- 02:45 We don't want that.
- 02:47 Let's go with a dark red.
- 02:50 And we'll go up again to the fill and choose no fill.
- 02:54 This is gonna be less obnoxious, this thing's not gonna be screaming for
- 02:58 your attention with that dense color saturation all the time.
- 03:00 At least this can fade in and fade out of view.
- 03:03 But there's still some things we can do here.
- 03:06 What I'm gonna do now is I'm gonna click outside the chart because I want to get
- 03:09 off the selection of the individual wedge.
- 03:11 And I'll select the pie again.
- 03:15 I'm gonna make the pie explosion go about 10%,
- 03:19 just a little bit to get that wedge out of the middle.
- 03:22 Something to make it sort of seem a little bit more different here.
- 03:26 We're also gonna right-click on this, and we're gonna say, Add Data Labels.
- 03:30 These are really important for a pie chart, because we need to be able to see,
- 03:34 there's no way of knowing what we're actually looking at here.
- 03:38 So, I also would like to change this.
- 03:40 I don't really need the numbers so much, as far as my project spending forecast.
- 03:45 I don't probably want my dollar values here.
- 03:46 What I'm more interested in is the percent complete.
- 03:49 So if I select these numbers, and we go to our Label Options,
- 03:55 you'll notice that we have the option to change some things.
- 03:57 I can add a percentage.
- 03:59 So 83%.
- 04:01 I can also uncheck the value, and I can add say, the series name.
- 04:06 Well, that doesn't give me a lot.
- 04:08 It's series 1.
- 04:08 Well, let's try the category name.
- 04:11 And there we go.
- 04:11 We've got spent and remaining.
- 04:14 We can also now recognize the label position is automatically jumping to
- 04:18 the outside here.
- 04:19 This is what's called best fit.
- 04:21 If the chart's too small to show it, it'll kick this outside.
- 04:24 But if the chart gets bigger and the remaining will fit inside,
- 04:28 it'll fit there, no problem.
- 04:29 So this is how I would go about actually building a chart that's a little bit more
- 04:34 effective and a little bit less obnoxious.
- 04:36 You can put it up in the corner of your worksheet, and
- 04:38 it'll be there when you need it, when you need to look at that pie.
- 04:42 But the challenge is that, say when it's got fully dense saturation
- 04:45 of color in there, it's constantly screaming for
- 04:48 your attention more than anything else on the page, and that's not cool.
- 04:51 We wanna get in when we need it, and get out when we don't, and
- 04:55 not have to focus on it anymore.
Lesson notes are only available for subscribers.