How to Make Excel Pie ChartSee how to make an Excel pie chart, with step-by-step videos and written instructions. Problems with pie charts, and how to avoid them.
|
Video: How to Build a Pie ChartA pie chart is a circle with one or more slices, and the angle of each slice is sized to show its share of the overall total amount. This video shows the steps to build a pie chart in Microsoft Excel, and there are written instructions, with screen shots, below the video. The full video transcript is also available, below the video, if you'd like to read that. Video Timeline
|
Video TranscriptIf you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript Introduction: How to Build a Pie ChartWith a pie chart in Excel, you can see a representation of the total amount, and then the percentage that each value has of that total. It might be easier to compare the values, if you create a bar chart such as this one, which easily shows the difference between each region, or in a column chart like this one. But if you need to create a pie chart, we'll see the steps for creating one that's easy to read, and presents the data as clearly as possible. Set Up DataThe first step is to set up the data. On this sheet, I have the names of four regions, and in the column to the right,the numbers which represent the sales in each of those regions. A pie chart can only show one set of numbers, so we couldn't compare year to year for each region, but we can see the total sales for the current year. Create Pie ChartTo create the chart, I'm going to select any cell in this table, and on the Ribbon, I'll go to the Insert tab, and click Pie And I'm going to select this first pie chart, which is just a simple pie chart. I don't want an exploded pie or a pie of pie and certainly not a 3D pie, because those can distort the data, with the way that they create angles. I'm going to click this one and it puts a chart right in the middle of the worksheet. Move and ResizeYou can move it, and you can resize it, and to move it just point to one of the borders or point somewhere. You'll notice where I'm pointing, a pop up says Chart Area. So if I point there, I can drag it to the right or left. And I can also make it smaller, by pointing to one of the handles on the sides or in the corner. Pull the handle in or pull it out to make it bigger. Add LabelsWe have colored slices on this pie and a legend tells us what each color represents. To read that, people have to look at the color here, and then try and find it in the legend. So it's better if you have these labels right on the slices, or just beside them. We're going to get rid of this legend, and put data labels onto the pie. Now this pie only has four slices. You shouldn't try to show too much in a pie chart, or it'll just get so crowded you won't even be able to read it. So with this pie chart, I'm going to right click, and click Add Data Labels, and that puts the value. We can see the values here, and it's just put that on each slice. So it's a start. It's not telling us what region it is yet, but we're going to fix that up in a minute. Next I'll delete this legend, because we're not going to need it. I'll right-click on the legend, and click Delete, and that shifts the pie over into the center. Format LabelsAnd now we have a little more room to put things onto the slices. I'll right click on one of these labels and Format Data Labels. In this Format Data Labels window, Label Options is selected, and I have check boxes that I can use to put things onto the label. I don't want to put too much, or it'll just be crowded and hard to read. Right now, it's showing the value. I could also add the percentage. For this slice, it shows 400 as the value, and 35% of the total value. I would also like to see the region name, so I'll check Category, and I'm going to take out the value and just leave it with the region name and the percentage. I can also position those labels. Right now, it came up, the default is Best Fit, so it will put that label where it has most amount of room. You could put them in the center of each slice, the inside end, outside or best fit, and I'll close this window. Now I'm going to do a little more formatting on these labels, so they're easier to read. I'll right click, and in this Formatting ribbon, I'm going to choose a white font, because it will provide a better contrast with the dark background. I'll make those labels bold and maybe go up a little in size, so you can experiment. As I point to each size, it shows what it will look like. So 12 might be a little too big. Maybe go with 11. So now you've got a pie chart where everything's easy to read. The labels are right on the slice, so you don't have to look back and forth from a legend to try and figure things out, so it's a good representation of the data. For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com |
What is Pie Chart (and Example)A pie chart is a circle with one or more slices, and each slice is sized to show its share of the overall total amount, its part of the whole circle. In the screen shot below, you can see a basic Excel pie chart, with 4 slices, one for each sales region in a fictional company. The region names are listed in the chart legend, at the right, with a colour code to match each region to a pie chart slice.
Why Make a Pie Chart?A pie chart can answer questions about the chart data. For example,
Compare SlicesPie charts show an overview of how amounts are distributed, and it's easy to compare very large slices, to very small slices. For example, in the pizza survey pie chart shown below, there are only two slices. There were 58 votes for red, and 42 votes for white. The red slice fills more than half of the pie chart, so it is clear that a larger percentage of people voted for red type pizza, rather than the white type pizza. There are instructions for this chart in the Add Pictures to Pie Chart section, further down the page.
|
How to Build a Pie ChartIn the following sections there are the detailed steps for setting up the data, and creating a pie chart. Then, make a few changes to the chart setup, so it's easy to read and understand. -- 1) Set Up Your Data -- 2) Insert the Chart -- 3) Quick Changes to Pie Chart 1) Set Up Your DataWhat should your data look like, if you want to build a pie chart? It should have the following:
Warning – Only use a few numbers, or the pie chart won’t be readable. I’d pick 6 as the upper limit, but let your conscience (or your boss) be your guide. In the screen shot below, you can see two sets of data showing sales per region.
You could create a pie chart from either set of numbers. |
2) Insert the ChartAfter your pie chart data is set up, follow these steps to insert a pie chart:
Pie Chart Options
Select a Pie Chart Type
See the Pie ChartAfter you click the 2-D pie option, a pie chart will be inserted on the active sheet, in the middle of the Excel window. By default, the new pie chart will show the following:
|
3) Quick Chart ChangesAfter the chart has been added to the worksheet, you can make quick changes to its layout and colour theme, using tools on the Excel Ribbon:
Or, go to the next main section, Make Changes to Pie Chart, to see how to change and format specific parts of the pie chart. A) Quick LayoutThe Quick Layout feature lets you apply several features to a chart, all in one step. That can be a real time saver, if you have several charts to build! To use the Quick Layout options, follow these steps:
In the screen shot below, I'm pointing to the Layout 2 option, and the tooltip explains which features it applies:
|
B) Change ColorsThe Change Colors feature lets you apply a different color theme to a chart, to replace the default color theme that was used in the new chart. To use the Change Colors options, follow these steps:
In the screen shot below, I'm pointing to Colorful Palette 2, and the tooltip lists the colors it applies:
|
C) Quick Chart StylesThe Quick Chart Styles feature lets you apply a variety of layout and formatting settings to a chart, all in one step. Some styles format chart area background color and pie slices, others add chart features such as legend and title. Notes
Use Quick Chart StylesTo use the Quick Chart Styles, follow these steps:
In the screen shot below, I'm pointing to Style 8 and the tooltip doesn't give any details. Check the preview in the selected chart on the worksheet, to see the effects of that style
|
Make Changes to Pie ChartAfter you build a pie chart, you can make changes to the chart, if needed. In the sections below, there are instructions for the following improvements: --5) Change Data Label Contents 1) Move the ChartThe new chart is selected, so you can move it to a different location, if you don’t want it in the middle of the sheet.
2) Resize the ChartWhen the chart is selected, you can also resize it, to make it larger or smaller.
You can resize the chart again later, after making all the formatting changes.
|
3) Add Labels to the ChartBy default, the chart has a legend at the side, that shows the text description for each slice of the pie. To make it easier to read, you should put a label on each slice, and get rid of the legend. That way, people won’t have to look back and forth, from the legend to the pie, trying to match the colours. To add labels, right-click on any slice in the pie, then click Add Data Labels, in the popup menu.
Each slice will show its numeric value. You’ll make those labels look better in a minute.
4) Delete the LegendNow that the pie slices have labels, we don’t need the legend at the right. To delete the legend, right-click on the legend, then click Delete.
|
5) Change Data Label ContentsThe data labels currently show just the value for each slice, in a small, dark font. We’ll change the information that shows, and make the labels easier to read. Right-click on any label, and click Format Data Labels
|
6) Format the Data Label FontNow that the data labels show the content you want, format them, so they’re easier to read.
7) Completed Pie ChartHere is the completed pie chart, after all the changes.
|
Pie Chart ProblemsAlthough pie charts are helpful in answering questions about some data, they can cause problems with other types of data. Small Difference ProblemsIf the pie chart has two or more slices that are similar in size, it can be hard to tell which slice is bigger, just by looking at them. For example, in the pie chart shown below, the green slice (Central) and the red slice (East) are similar in size. Because of these small differences, I'm not sure which region had higher sales - East or Central.
Too Many SlicesAnother situation where there is a problem with pie charts is when there are too many items in the chart data. In the pie chart shown below, there is monthly data for a company's sales, and the chart is difficult to read. Labels are overlapping, and some of the slices have similar colours, which adds to the confusion. My recommendation - try to keep the number of slices at 6 or fewer
|
Percentage DifferencesAnother small problem with pie charts is that they sometimes show a different percentage value than you'd see in a worksheet calculation. This is due to rounding. For example, in the worksheet screen shot below:
Occasionally, someone might notice those differences, but you can assure them it's just a difference in rounding.
|
Avoid Pie Chart ProblemsTo avoid pie chart problems, it's sometimes best to show the data in other chart types, such as a bar chart or a column chart. These chart types are better for showing the differences between amounts. For example, in the bar chart and column chart shown below, it's easy to see that the East region had higher sales than the Central region. Instead of trying to see difference in the sizes of pie chart slices, you're able to compare where each bar or column ends, compared to the other bars or columns.
|
Fix Pie Chart ProblemsSometimes you might need to use a pie chart, despite the known problems, because that's what a client or co-worker needs for their report. So, if you need a pie chart where the data might be a problem, try the following suggestions to fix or limit the problems, as much as possible. Too much data: Suggest ways to reduce the pie chart data. For example, make a pie chart from quarterly data, or bi-monthly data, instead of monthly data. Monthly details could be listed on the worksheet, near the chart, if needed.
Special Effects: Create a simple 2-D pie chart. Avoid special effects like 3-D pie charts, because those settings can distort the data. In the 3-D chart shown below, the chart height makes the green slice at the front (30%) look larger than the blue slice at the back (32%).
Data Labels: Add short data labels and percentages on the slices, when there are small differences in amounts. Be sure to format the label text in a good-sized font, and high-contrast font colour, so the labels are easy to read.
|
Video: Add Pictures to Pie Chart SlicesThis video shows how to add pictures to Excel pie chart slices, and there are written steps below the video. Add Pictures to Pie Chart SlicesWhile flipping through a magazine one weekend, I saw this pizza pie chart. Yes, the pizza looks delicious, but the percentages say 58% vs 42% and the toppings are split down the middle. That makes the vote appear to be 50-50, not 58-42. It's a fun magazine photo, but it's a bad pie chart!
The picture was making me hungry. I didn’t want to ruin my dinner, by going out for a pizza snack, so I decided to make a better pizza pie chart in Excel. |
Build a Chart in ExcelFirst, for this pie chart example, I entered the results on a worksheet (I was using Excel 2013).
|
Format the Pie ChartThe default pie chart had slices in the correct size, so it showed the results better than the original pizza pie chart. But it needed some formatting, to make it look better. If nothing else, it needed a title, and those colours had to be changed to red and white!
|
Add Picture BackgroundI rarely use picture backgrounds on a chart, but this seemed like a good time to make an exception. Here are the steps I followed to put a picture on each slice of pie:
That looks better!
|
The Finished ChartHere is the finished version of the chart, with a pizza topping picture on both slices of the pie chart. Now, it’s easy to see which topping most people prefer. And if that made you hungry, I’m sorry!
|
Different Types of Pie ChartsWhen you create a pie chart, there are different types of pie charts available in the pop-up menu.
2-D Pie ChartsThe main type of pie chart I make is a basic 2-D Pie, like the examples shown on this page. Its simple layout makes the data easy to understand. Pie of Pie ChartOccasionally, I've made a Pie of Pie chart or Bar of Pie chart, if the chart data has 2 or 3 large amounts, and 3 or 4 much smaller amounts. For example, in the Pie of Pie chart shown below, the North and East regions have large sales amounts. The Central, West and South regions have much smaller values.
Here is the same sales data in a Bar of Pie chart, with black font, instead of white.
|
Settings for Pie of Pie or Bar of PieAfter you make a Pie of Pie chart, or Bar or Pie, you can adjust the default settings that Excel used for the new chart. To see the current settings, follow these steps:
Adjust Series Option SettingsExperiment with the series option settings, to see what works best for your chart.
|
Pie Chart Colour MacroSomeone asked me if it was possible to use conditional formatting in an Excel pie chart. The chart's data had two different amounts:
From those amounts, the Percent Invoiced had been calculated. The pie chart needed to show these things:
The completed pie chart should look something like this:
|
Sample Data
Here’s a screen shot of my sample data for the pie chart.
|
Build a Pie ChartUsing the steps shown at the top of this page, I built a simple 2-D pie chart, using the client names, and the % Inv numbers. I removed the legend, and the chart title, and added data labels, to show the client names. The slices were the correct size, but the colours in each slice need to be changed. They should match the conditional formatting colour for each client's % Inv percentage cell.
|
Pie Chart Colour Match MacroThere's no built-in way to match a pie slice colour to a cell's condtional formatting colour, so I wrote a macro to do the job.
Macro VBA CodeHere is the VBA code, and it's also available in the sample file you can download. The sample file also has a bar chart on the worksheet, and the macro colours its bars too. Sub ColorChartDataPoints()
'colour data point based on
'value in rank column
Dim ws As Worksheet
Dim ch As ChartObject
Dim ser As Series
Dim dp As Point
Dim ptnum As Long
Dim rngSD01 As Range
Dim strF As String
Dim strRng As String
Dim CharStart As Long
Dim CharEnd As Long
Dim ColOff As Long
Dim PtColor As Long
ColOff = 3 'offset to Rank column
Set ws = ActiveSheet
For Each ch In ws.ChartObjects
Set ser = ch.Chart.FullSeriesCollection(1)
strF = ch.Chart.SeriesCollection(1).Formula
CharStart = InStr(1, strF, ",")
CharEnd = InStr(InStr(1, strF, ",") _
+ 1, strF, ",")
strRng = Mid(strF, CharStart + 1, _
CharEnd - CharStart - 1)
Set rngSD01 = ws.Range(strRng)
ptnum = 1
For Each dp In ser.Points
PtColor = rngSD01.Cells(ptnum, 1) _
.Offset(0, ColOff).DisplayFormat.Interior.Color
dp.Format.Fill.ForeColor.RGB = PtColor
ptnum = ptnum + 1
Next dp
Next ch
End Sub
|
Running the MacroThe macro runs automatically, if any changes are made in the named range, "ClientAmts". The following event code is on the worksheet code module for the sheet named Chart Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ClientAmts")) Is Nothing Then
ColorChartDataPoints
End If
End Sub
|
The Final ResultHere’s the completed Conditional Formatting Color report, with the sample data, the bar chart, and the pie chart. It was an interesting challenge, and you can download the workbook (below), if you’d like to test it.
|
Get the Sample FilesSimple Pie Chart -- Get this sample Pie Chart workbook with the data to create a simple pie chart. The zipped file is in xlsx format, and does not contain macros. Pizza Pie Chart -- Get the sample Pizza Pie Chart workbook with topping pictures in the chart slices. The zipped file is in xlsx format, and does not contain macros. Chart Colour Based on Rank -- Get the sample Excel Chart Colour Macro workbook, with VBA macro that changes the pie chart and bar chart colours, based on rank. The zipped file is in xlsm format, and contains macros |
More TutorialsSeating Plan, Guest Tables in Donut Chart |
Last updated: December 17, 2022 9:11 PM