How to build a pie chart in Excel. What is a pie chart? How to make a pie chart in Excel and using office tools? How to make a pie chart

Sequence of actions when constructing a diagram

1. Select in the table the range of data on which the chart will be built, including, if possible, the ranges of labels for this data in rows and columns.

2. To select several non-adjacent data ranges, select while holding down the key .

3. Call Chart Wizards (menu item Insert/Diagram or a button on the standard toolbar).

4. Carefully reading all the tabs of the Diagram Wizard dialog box at each step, reach the end (select “Next” if this button is active) and finally click “Finish”.

After constructing the diagram, you can change:

· dimensions of the diagram by pulling the dimensional symbols that appear when the diagram is selected;

· position of the diagram on the sheet by dragging the diagram object with the mouse;

· font, color, position of any diagram element by double-clicking on this element with the left mouse button;

· chart type, source data, chart parameters by selecting the appropriate items from the context menu (right mouse button).

The diagram can be deleted: select and click .

A diagram, like text and any other objects in MS Office, can be copied to the clipboard and pasted into any other document.

Pie charts- represent a circle divided into sectors (cake), and are used to show the relative value that makes up a single whole. The largest sector of the circle should be the first one clockwise from the top. Each sector of the circle must be labeled (name, value and percentage are required). If it is necessary to focus on a certain sector, it is separated from the rest.

Let's build a three-dimensional pie chart that displays production load throughout the year.

With the help of a pie chart, only one series of data can be shown, each element of which corresponds to a specific sector of the circle. The area of the sector as a percentage of the area of the entire circle is equal to the share of the row element in the sum of all elements. Thus, the sum of all shares by season is 100%. The pie chart created from this data is shown in the figure:

Excel provides 6 types of pie charts:

¾ circular – displays the contribution of each value to the total;

¾ volumetric circular;

¾ secondary pie – part of the values of the main diagram is placed on the second diagram;

¾ cut circular – sectors of values are separated from each other;

¾ volumetric cut circular;

¾ secondary histogram - part of the values of the main diagram is included in the histogram.

If you want to separate slices in a pie chart, you don't have to change the chart type. Simply select the circle and drag any sector away from the center. To return the chart to its original appearance, drag the sector in the opposite direction.

It should be remembered that if you want to separate only one sector, you should make two single clicks on it. The first will select a row of data, the second – the specified sector.

In a pie chart, the sectors can be rotated 360° around the circle. To do this, select a row of data in the context menu and select the tab Data Series Format , go to tab Options and enter the desired rotation angle value:

A secondary pie chart, like a secondary histogram, allows some portion of the data to be displayed separately, in more detail, in a secondary chart or histogram. Moreover, the secondary diagram is taken into account in the primary diagram as a separate share. As an example, consider a chart showing sales volume for the week, where the weekend portion is plotted as a secondary pie chart. When choosing a chart type, indicate Secondary circular .

To set methods for selecting data for a secondary chart, select in the context menu Data Series Format and go to the tab Options. Here we indicate the number of last values in the second part, the size of the second part, the width of the gap between the diagrams and other parameters, for example.

In this pie chart tutorial, you'll learn how to create a pie chart in Excel, how to add or remove a legend, how to label a pie chart, show percentages, how to split or rotate it, and much more.

Pie charts, also known as sectoral, are used to show what portion of a whole individual amounts or shares, expressed as a percentage, make up. In such graphs, the entire circle is 100%, while individual sectors are parts of the whole.

The public loves pie charts, while data visualization experts hate them, and the main reason for this is that the human eye is not able to accurately compare angles (sectors).

If you can’t completely abandon pie charts, then why not learn how to build them correctly? Drawing a pie chart by hand is difficult, with confusing percentages being a particular challenge. However, in Microsoft Excel you can create a pie chart in just a couple of minutes. Then just spend a few more minutes using the chart's special settings to give it a more professional look.

How to build a pie chart in Excel

Creating a pie chart in Excel is very easy and requires just a few clicks. The main thing is to correctly format the source data and choose the most suitable type of pie chart.

1. Prepare the initial data for the pie chart

Unlike other Excel graphs, pie charts require you to organize your raw data into a single column or row. After all, only one series of data can be constructed in the form of a pie chart.

In addition, you can use a column or row with category names. Category names will appear in the pie chart legend and/or data labels. In general, a pie chart in Excel looks best if:

The chart contains only one data series.
All values are greater than zero.
There are no empty rows or columns.
The number of categories does not exceed 7-9, since too many sectors of the diagram will blur it and it will be very difficult to perceive the diagram.

As an example for this tutorial, let's try to build a pie chart in Excel based on the following data:

2. Insert a pie chart into the current worksheet

Select the prepared data and open the tab Insert(Insert) and select the appropriate chart type (we’ll talk about the different ones a little later). In this example, we will create the most common 2-D pie chart:

Advice: When highlighting your source data, be sure to select column or row headings so that they automatically appear in your pie chart titles.

3. Select a pie chart style (if necessary)

When the new pie chart appears on the worksheet, you can open the tab Constructor(Design) and in the section Chart styles(Charts Styles) Try different styles of pie charts, choosing the one that best suits your data.

The default pie chart (Style 1) in Excel 2013 looks like this on a worksheet:

Agree, this pie chart looks a little simple and, of course, requires some improvements, for example, the name of the chart, and perhaps it is worth adding more. We'll talk about all this a little later, but now let's get acquainted with the types of pie charts available in Excel.

How to Create Different Types of Pie Charts in Excel

When creating a pie chart in Excel, you can choose one of the following subtypes:

This is the standard and most popular subtype of pie chart in Excel. To create it, click on the icon Circular(2-D Pie) tab Insert(Insert) in section Diagrams(Charts).

3D pie chart in Excel

Volumetric circular(3-D Pie) charts are very similar to 2-D charts, but display data on 3-D axes.

When building a 3D pie chart in Excel, additional functions appear, such as .

Secondary Pie or Secondary Bar Chart

If a pie chart in Excel consists of a large number of small sectors, then you can create Secondary circular(Pie of Pie) chart and show these minor sectors on another pie chart, which will represent one of the sectors of the main pie chart.

Secondary ruled(Bar of Pie) is very similar to Secondary circular(Pie of Pie) chart, except that the sectors are displayed in a secondary histogram.

While creating Secondary circular(Pie of Pie) or Secondary ruled(Bar of Pie) charts in Excel, the last three categories will be moved to the second chart by default, even if those categories are larger than the others. Since the default settings are not always the most appropriate, you can do one of two things:

Sort the raw data on the worksheet in descending order so that the smallest values end up in the secondary chart.
Choose yourself which categories should appear on the secondary diagram.

Selecting data categories for a secondary chart

To manually select data categories for a secondary chart, do this:

Right-click on any sector of the pie chart and select from the context menu Data series format(Format Data Series).
Series parameters(Series Options) in the dropdown list Split row(Split Series By) select one of the following options:
- Position(Position) – allows you to select the number of categories that will appear in the secondary chart.
- Meaning(Value) – allows you to define the threshold (minimum value). All categories that do not exceed the threshold will be transferred to the secondary chart.
- Percent(Percentage value) – the same as Meaning(Value), but here the percentage threshold is indicated.
- Other(Custom) - Allows you to select any slice from the pie chart on the worksheet and specify whether it should be moved to a secondary chart or left in the primary chart.

In most cases, a threshold expressed as a percentage is the most reasonable choice, although it all depends on the input data and personal preference. This screenshot shows the division of a series of data using a percentage indicator:

Additionally, you can configure the following parameters:

Change Side clearance(Gap between two charts). The gap width is set as a percentage of the secondary diagram width. To change this width, drag the slider, or manually enter the desired percentage.
Resize the secondary chart. This indicator can be changed using the parameter Second construction area size(Second Plot Size), which represents the size of the secondary plot as a percentage of the size of the main plot. Drag the slider to make the chart larger or smaller, or enter the percentages you want manually.

Donut charts

Ring A donut chart is used instead of a pie chart when more than one series of data is involved. However, in a donut chart it is quite difficult to assess the proportions between elements of different series, so it is recommended to use other types of charts (for example, a histogram).

Changing the hole size in a donut chart

When creating a donut chart in Excel, the first thing you need to do is change the size of the hole. This is easy to do in the following ways:

Customize and Improve Pie Charts in Excel

If you only need a pie chart in Excel to get a quick look at the big picture of your data, then the default standard chart is fine. But if you need a beautiful diagram for a presentation or for some similar purposes, then it can be improved somewhat by adding a couple of touches.

How to Add Data Labels to a Pie Chart in Excel

A pie chart in Excel is much easier to understand if it has data labels. Without labels, it is difficult to determine what share each sector holds. You can add labels to a pie chart for the entire series or just for a specific element.

Add data labels to pie charts in Excel

Using this pie chart as an example, we'll show you how to add data labels for individual slices. To do this, click on the icon Chart elements(Chart Elements) in the upper right corner of the pie chart and select the option Data Signatures(Data Labels). Here you can change the location of the signatures by clicking on the arrow to the right of the parameter. Compared to other chart types, pie charts in Excel provide the most choice in label placement:

If you want the labels to appear inside callouts outside the circle, select Data callout(Data Callout):

Advice: If you decide to place labels inside the chart sectors, please note that the default black text is difficult to read against the background of a dark sector, as, for example, is the case with the dark blue sector in the picture above. To make it easier to read, you can change the signature color to white. To do this, click on the signature, then on the tab Format(Format) press Fill text(Text Fill). In addition, you can change .

Categories in data labels

If a pie chart in Excel consists of more than three slices, then labels can be added directly to each slice so as not to force users to jump between the legend and the chart to find information about each slice.

The fastest way to do this is to select one of the ready-made layouts in the tab Constructor > Chart Layouts > Express Layouts(Design > Chart Styles > Quick Layout). Layout 1 And Layout 4 contain category names in data labels:

To access other options, click the icon Chart elements(Chart Elements) in the upper right corner of the pie chart, click on the arrow next to the item Data Signatures(Data Labels) and select Extra options(More options). A panel will appear Data Signature Format(Format Data Labels) on the right side of the worksheet. Go to section Caption options(Label Options) and check the option Category name(Category Name).

In addition, you can take advantage of the following options:

Under the heading Include in signature(Label Contains) select the data that should be contained in the labels. In our example this is Category name(Category Name) and Meaning(Value).
In the dropdown list Delimiter(Separator) select how to separate the data in the captions. In our example, the separator is selected New line(New Line).
Under the heading Mark position(Label Position) select where to place the label. In our example we selected At the edge outside(Outside End).

Advice: Now the data labels have been added to the pie chart, the legend is no longer needed and can be removed by clicking on the icon Chart elements(Chart Elements) and uncheck the box next to the item Legend(Legend).

How to Show Percentages on a Pie Chart in Excel

When the original data in a pie chart is expressed as a percentage, the signs % will appear on the diagram automatically as soon as the parameter is enabled Data Signatures(Data Labels) in the menu Chart elements(Chart elements) or parameter Meaning(Value) on the panel Data Signature Format(Format Data Labels), as shown in the example above.

If the original data is expressed in numbers, then you can show in the captions either the original values, or percentages, or both.

Right-click on any sector of the pie chart and select from the context menu Data Signature Format(Format Data Labels).
In the panel that appears, tick the parameters Meaning(Value) and/or Shares(Percentage) as shown in the example. Excel will calculate the percentages automatically based on the assumption that the entire pie chart is 100%.

Dividing a pie chart or highlighting individual sectors

To highlight individual values in a pie chart, you can break it up, i.e. separate all sectors from the center of the diagram. You can also highlight specific sectors by moving only them away from the main diagram.

Fragmented pie charts in Excel can be in 2-D or 3-D format, and donut charts can also be split.

How to split a pie chart in Excel

The fastest way to split a pie chart in Excel is to click on the pie chart to select all the slices and then use your mouse to drag them away from the center of the chart.

To more accurately customize the pie chart layout, follow these steps:

How to highlight individual sectors of a pie chart

To draw the user's attention to a specific sector of a pie chart, you can move this sector out of the general circle of the chart.

I repeat: The fastest way to move individual sectors of the chart is to select them and move them away from the center using the mouse. To select a separate sector, you need to double-click on it.

There is another way: select the sector that you want to extend, right-click on it and click in the context menu Data series format(Format Data Series). Then in the panel that appears, open the section Series parameters(Series Options) and configure the parameter Cutting a point(Point Explosion):

Comment: If you need to select several sectors, you will need to repeat this process with each of them, as shown above. It is impossible to select several sectors of a pie chart at once in Excel, but you can split the chart completely, or select only one sector at a time.

How to Expand a Pie Chart in Excel

When creating a pie chart in Excel, the order in which the categories are drawn depends on the order of the data on the worksheet. You can rotate the pie chart 360 degrees to show data from different perspectives. Typically, a pie chart looks best if its smallest sectors are in the front.

To rotate a pie chart in Excel, you need to follow these steps:

Right-click on the diagram and select Data series format(Format Data Series).
In the panel that appears in the section Series parameters(Series Options) move the option slider Angle of rotation of the first sector(Angle of first slice) to expand the diagram, or enter the required value manually.

Rotating 3D pie charts

An option is available for 3D pie charts Rotate a volumetric figure(3-D Rotation). To access this option, you need to right-click on any sector of the diagram and select from the context menu Rotate a volumetric figure(3-D Rotation).

A panel will appear Chart Area Format(Format Chart Area), where you can configure the following parameters for rotating a volumetric figure:

Horizontal Rotation around the X axis(X Rotation)
Vertical Rotation around Y axis(Y Rotation)
Viewing angle – parameter Perspective(Perspective)

Comment: Pie charts in Excel can rotate around the horizontal axis and vertical axis, but not around the depth axis (Z-axis). Therefore the parameter Rotation around the Z axis(Z Rotation) is not available.

If you click on the up or down arrows in the fields for entering rotation angles, the diagram will immediately rotate. In this way, you can make small changes in the angle of rotation of the diagram until it is in the desired position.

How to Organize Pie Chart Sectors by Size

Typically, pie charts are easier to understand if their slices are sorted from largest to smallest. The fastest way to achieve this result is to sort the source data on a worksheet. If you can’t sort the source data, you can change the arrangement of sectors in the Excel pie chart as follows:

How to change colors in a pie chart

If the standard colors of a pie chart in Excel do not suit you, there are several options:

Changing the color scheme of a pie chart in Excel

To select a different color scheme for a pie chart in Excel, you need to click on the icon Chart styles(Chart Styles), open tab Color(Color) and select the appropriate color scheme. The icon appears when a chart is selected to the right of it.

You can also click on any point in the pie chart to display a group of tabs on the Menu Ribbon Working with charts(Chart Tools) and on the tab Constructor(Design) in the section Chart styles(Chart Styles) click on the button Change colors(Change Colors):

Select colors for each sector separately

As you can see in the picture above, the choice of color schemes for charts in Excel is not rich, and if you need to create a stylish and bright pie chart, you can choose a different color for each sector. For example, if diagrams are located inside sectors, then you need to take into account that black text is difficult to read against a background of dark colors.

To change the color of an individual sector, select it by double-clicking on it with the mouse. Then open the tab Format(Format), press Filling a shape(Shape Fill) and select the desired color.

Advice: If your Excel pie chart contains a large number of small, not particularly important sectors, you can color them gray.

How to customize the appearance of a pie chart in Excel

If you are creating a pie chart in Excel for a presentation or for exporting to other applications, you can make it look more attractive.

To open formatting options, right-click on any sector of the pie chart and click in the context menu Data series format(Format Data Series). A panel will appear on the right side of the worksheet. On the tab Effects(Effects) experiment with the options Shadow(Shadow) Backlight(Glow) and Smoothing(Soft Edges).

On the tab Format(Format) Other useful formatting tools are available:

Changes the dimensions of the pie chart (height and width);
Changing the fill and outline of a shape;
Using various effects for the figure;
Using WordArt styles for text elements;
And much more.

To use these formatting tools, select the pie chart element you want to format (legend, data label, slice, or chart title) and select the tab Format(Format). Appropriate formatting options will be active, while unneeded formatting options will be disabled.

Now that you know how to create a pie chart in Excel, let's try to compile a list of the most important recommendations that will help make it both attractive and meaningful:

Sort sectors by size. To make the pie chart more understandable, you need to sort the sectors from large to small or vice versa.
Group sectors. If your pie chart has a large number of slices, it is best to group them and then use separate colors and shades for each group.
Color minor small sectors gray. If the pie chart contains small sectors (less than 2%), color them gray or group them into a separate category called Other.
Rotate the pie chart so that the smallest sectors are in front.
Avoid too many data categories. Too many sectors on a chart look like a clutter. If there are too many categories of data (more than 7), then separate the small categories into a secondary pie or secondary bar chart.
Don't use a legend. Add labels directly to the slices of your pie chart so that readers don't have to jump back and forth between the slices and the legend.
Don't get carried away with 3-D effects. Avoid a lot of 3-D effects in the diagram, as they can significantly distort the perception of information.

An Excel pie chart is based on the numerical data in a table. Parts of the chart show proportions in percentages (shares). Unlike a graph, a diagram better displays the overall picture of the results of an analysis or report as a whole, while a graph graphically details the presentation of information.

Visual representation of information in the form of a circle is relevant for depicting the structure of an object. Moreover, you can display only positive or zero values, only one set (row) of data. This feature of diagrams is both their advantage and disadvantage. Let's look at the advantages in more detail.

How to build a pie chart in Excel

Let's make a simple table for educational purposes:

We need to visually compare sales of a product over 5 months. It is more convenient to show the difference in “parts”, “shares of the whole”. Therefore, we will choose the type of chart – “pie”.

At the same time, the “Working with Diagrams” - “Design” tab becomes available. Its toolkit looks like this:

What can we do with the existing diagram:

Change type. When you click on the button of the same name, a list with images of diagram types opens.

Let's try, for example, a volumetric cut circular one.

In practice, try different types and see how they look in the presentation. If you have 2 sets of data, and the second set is dependent on some value in the first set, then the following types are suitable: “Secondary pie” and “Secondary histogram”.

Use different layouts and design templates.

Let's make sure that the names of the months and sales figures are displayed directly on the shares.

The constructed graph can be moved to a separate sheet. Click the corresponding button on the “Design” tab and fill out the menu that opens.

You can create a pie chart in Excel using the reverse procedure:

If the choice of program does not coincide with the option we have planned, then select the legend element and click “Change”. The “Edit Row” window will open, where “Row Name” and “Values” are links to cells (put the ones you need) and click OK.

How to change a chart in Excel

All the main points are shown above. To summarize:

Select the diagram - go to the “Design”, “Layout” or “Format” tab (depending on your goals).
Select a chart or part of it (axes, series) - right-click.
“Select data” tab - to change the names of elements and ranges.

All changes and settings should be made in the Design, Layout, or Format tabs of the Chart Tools group. The tool group appears in the window title bar as an additional menu when the graphics area is activated.

Pie chart as percentage in Excel

The simplest option for displaying data as a percentage:

Create a pie chart based on the data table (see above).
Left-click on the finished image. The “Design” tab becomes active.
We select options with percentages from the layouts offered by the program.

As soon as we click on the picture we like, the diagram will change.

The second way to display data as a percentage:

The result of the work done:

How to build a Pareto chart in Excel

Vilfredo Pareto discovered the 80/20 principle. The discovery took root and became a rule applicable to many areas of human activity.

According to the 80/20 principle, 20% of efforts produce 80% of the results (only 20% of the reasons will explain 80% of the problems, etc.). The Pareto chart reflects this relationship in the form of a histogram.

Let's build a Pareto curve in Excel. There is some event. It is influenced by 6 reasons. Let's evaluate which of the reasons has a greater impact on the event.

The result was a Pareto chart, which shows: reasons 3, 5 and 1 had the greatest influence on the result.

Suppose you have data on employees: the number of transactions and revenue. You need to display the data in a visual way, such as a pie chart.

In this case, the circle will mean the sum of all transactions or the sum of all revenue, that is, 100%

When constructing a pie chart, Excel will automatically calculate the share and present it in the form of a beautiful and visual picture.

Instructions on how to make a pie chart in Excel 2007 or 2010

To build a pie chart based on transaction data, you need to select the required range of values B3:C8(column with full name and column with transactions) as in the figure. Don't forget to grab the table header.
Next, go to the section Insert | Diagrams
In the Charts section, select a pie chart
You must select the desired pie chart type from the drop-down list.

That's it - the pie chart is ready.

To display values on a pie chart, right-click anywhere on the circle itself and select “ Add data signatures”

If you need to make a pie chart for another column, in our example it is revenue, then you also first need to select the columns that interest us. To do this, you need to select these ranges by holding down the key Ctrl on keyboard

If necessary, we also insert data signatures.

Pie charts are used to compare multiple values at one point. The sum of all values is 100%. They are not suitable for comparing different quantities. A circle is a whole. Sectors are constituent parts of a whole.

It happens that one of the shares of the circle turns out to be very small. To improve perception, it can be “expanded” using a secondary pie chart. Let's consider construction in Excel.

Features of data presentation

To build a secondary pie chart, you need to select the table with the source data and select the “Secondary Pie” tool on the “Insert” tab in the “Charts” group (“Pie”):

Two diagrams are always placed in the same plane. The main and secondary circle are next to each other. They cannot be moved independently. The main diagram is located on the left.
The main and secondary charts are parts of the same data series. They cannot be formatted independently of each other.
The sectors on the secondary circle also show shares as on the regular chart. But the sum of the percentages does not equal 100, but constitutes the total value of the sector of the main pie chart (from which the secondary one is separated).
By default, the secondary circle displays the last third of the data. If, for example, there are 9 rows in the original table (for a chart there are 9 sectors), then the last three values will appear on the secondary chart. The original location of the data can be changed.
The relationship between two diagrams is shown by connecting lines. They are added automatically. The user can modify, format, delete them.
The more decimal places for fractional numbers in the original data series, the more accurate the percentages in the charts.

How to Create a Secondary Pie Chart in Excel

The following data is available on sales of certain product groups:

They are immediately located so that the secondary pie chart is built correctly: you need to detail the sales of different cabinets.

Select the table along with the headers and go to the “Insert” tab in the “Charts” group. Select “Secondary circular”. The result will be something like this:

Right-click on any segment of the circle and click “Add data labels.”

Numerical values from the table appear:

We right-click on any signature - everything should be highlighted. Go to the “Data Signature Format” tab.

In the “Signature Options” context menu, select “Shares” and uncheck the box next to “Values”. If left, both values and fractions will be displayed. Also check “Category names”. In the “Separator” field set “New Line”.

Delete the legend to the right of the pie charts (select – DELETE). We adjust the sizes of the circles, move the labels on the sectors (select - click with the mouse - move). We get:

We will replace the word “Other” in the main diagram with the word “Cabinets”. Double-click on the signature to make the cursor blink. We change.

You can work on the styles and colors of the diagram parts. Or you can leave it like that.

Now let's see how to drill into a segment of a regular pie chart.

Add signatures in the form of percentages. This can be saddled in another way (not the one described above). Go to the “Design” tab - “Chart Layouts” tool. We choose the appropriate option among those offered with percentages.

The 4% sector is viewed poorly. We detail it using a secondary pie chart from smallest to largest. Find the button “Change chart type on the Design tab”:

The automatic option “transferred” the last two values in the table with the original data to the secondary chart. In this form, the drawing does not solve the problem. Click on any part of any circle so that all segments are selected. Right mouse button – “Format data series”.

In the “Series Options” menu, we suggest that the program divide the series by “percentage”. Let's place values less than 10% in the secondary chart.

As a result, we get the following display option:

Areas with minimal percentages (4% and 8%) are shown in an additional diagram. The sum of these shares formed a separate sector on the main diagram.