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:
Click right click mouse over any segment of the circle and click “Add data labels”.
Appear numeric values from the table:
We right-click on any signature - everything should be highlighted. Go to the “Data Signature Format” tab.
IN context menu“Signature parameters” 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. Choose suitable option among those offered with interest.
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.
Information that is displayed visually is much easier to perceive, this is a proven fact. The trend is especially pronounced in different comparisons. Below we will look at how to build a chart in Excel using table data. It will be step-by-step instruction Without further ado, we will also touch on a number of related issues.
Creating a diagram
We will solve the problem different ways, you will choose the most suitable one. So let's get started.
bar chart
This type suitable when we just need to visually display values, or compare them with others.
- In order to start creating a diagram, you should initially have the data that will form its basis. Therefore, select the entire column of numbers from the plate and press the Ctrl+C button combination.
- Next, click on the [k]Insert tab and select the histogram. It will display our data in the best possible way.
- As a result of the above sequence of actions, a diagram will appear in the body of our document. First of all, you need to adjust its position and size. To do this, there are markers that can be moved.
- We configured the final result as follows:
- Let's give the sign a name. In our case, these are [k]Product prices. To get into editing mode, double-click on the chart title.
- You can also get into the editing mode by clicking on the button marked with the number [k]1 and selecting the function [k]Axes names.
- As you can see, the inscription appeared here too.
This is what the result of the work looks like. In our opinion, quite good.
Comparison of different values
If you have several values, you can also add them here, so we can get excellent material for visual comparison.
- Copy the numbers in the second column.
- Now select the diagram itself and press Ctrl+V. This combination will insert data into an object and force it to be organized into columns of different heights.
There are hundreds of other types of graphs in the program, they can be found in the [k]Insert menu. Through trials and combinations, each one must be dealt with separately.
Percentage
In order to more clearly understand the role of the various cells of our table and its values in general, we can compare the results in the form of a pie chart. Moreover, we will do this with the conclusion of the percentage. Let's get started.
- As in previous cases, we copy the data from our plate. To do this, just select them and press the key combination Ctrl+C. You can also use the context menu.
- Click on the [k]Insert tab again and select the pie chart from the list of styles.
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 given the confusing percentages that represent special problem. However, in Microsoft Excel You can create a pie chart in just a couple of minutes. Then just spend a few more minutes on the diagram using 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 initial data and choose the most suitable type pie chart.
1. Prepare the initial data for the pie chart
Unlike others Excel charts Pie charts require organizing the 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.
- None empty lines and 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 manual, 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 (about various we'll talk a little bit 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.
Pie chart The default (Style 1) in Excel 2013 looks like this on the 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, such as .
Secondary Pie or Secondary Bar Chart
If a pie chart in Excel consists of large quantity 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, three latest categories will be moved to the second chart by default, even if these categories are larger than 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 secondary diagram, 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, the threshold, expressed as a percentage, is the most smart choice, although it all depends on the initial data and personal preferences. 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 (Doughnut) chart is used instead of a pie chart when we're talking about more than one series of data. However, in a ring chart it is quite difficult to assess the proportions between elements different rows, 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 quickly look at big picture data, then the standard default chart is quite suitable. 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 types of charts, pie charts in Excel provide largest selection signature locations:
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.
Most quick way do this - choose one of ready-made layouts on 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) on the right top corner 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 labels. 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, donut charts can also be broken.
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. To show data from different points view, you can rotate the pie chart 360 degrees. 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 standard colors pie chart in Excel does not suit you, there are several solutions:
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 color schemes There are not a lot of charts in Excel, and if you need to create a stylish and bright pie chart, you can choose your own 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 color separate 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 grey colour.
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) and others available useful tools formatting:
- Changes the dimensions of the pie chart (height and width);
- Changing the fill and outline of a shape;
- Usage 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 the pie chart consists of a large number of slices, it is best to group them and then use individual 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.
Clarity of information data provision and its accessibility human brain primarily depends on the form of their presentation. Therefore, many try to visualize information as simply as possible and present its necessary aspects to the audience as conveniently as possible. Pie charts are quite effective in this regard. They are used almost everywhere where you need to show the aspect ratio, percentage of total amount etc.
Pie chart - enough convenient tool to display data, since it can be used to divide different columns of data into different sectors of one circle, thereby emphasizing which segment occupies this or that information in the whole. It is worth noting that the feasibility of using this type of diagram applies only when the data want to divide into no more than 6 structural parts.
For example, if you have 5 types of items in your warehouse (books, magazines, pens, covers and pencils) and you need to graphically show the percentage of the warehouse being filled with one or another type of product, then the pie chart will be best choice, at the same time, if you have more than 50 product items, then the feasibility of using this type of diagram is significantly reduced, since graphically such a representation will not be a very clear example.
The easiest tool to create a pie chart is Excel ( table processor, presented by Microsoft). Moreover this application has in its functionality not only this type of diagram, but also many others.
Let's look at how to insert an object such as a pie chart into Excel environment. First of all, you need to enter the data on which the object will be built on the application sheet. Next you need to perform the following sequence of operations.
1. Go to the “Insert” tab.
2. Select and click “Charts”.
3. In the “Insert Chart” window that appears, click on the “Pie” item, select the chart subtype (Pie, Secondary Pie, 3D Pie, Sectional Pie or Secondary Histogram) and click the “OK” button.
4. If you have not previously selected the data on which the pie chart will be built, then a blank canvas called “Chart Area” will appear in front of you on the current sheet. It is worth noting that this Excel time will switch to the “Chart Tools” tab, which has submenus: Format, Layout and Design.
5. To build a chart, right-click on the “Chart Areas” and select the “Select Data” context menu item. In the Select Data Source window, select the range over which you are going to create the pie chart and click OK.
As a result, a chart will appear on the sheet that will correspond to your data. If you don’t like the colors of the chart sectors or its layout, or want to customize the labels, then you can easily choose required parameters in the above submenu items. You can also move the plotted diagram to separate sheet. To do this, just click on the “Move diagram” button in the “Design” submenu.
To create a pie chart in Excel, you will need to fill out a data table consisting of the values of the chart sectors. There can be an unlimited number of segments, but practice shows that pie charts consisting of three to five sectors look clearer.
The values of these sectors are entered into the column. If necessary, you can fill out a table of two columns, where the first indicates the name of the segment, and the second directly its value. In this case, it will be easier to provide the diagram with segment labels.
- circular;
- volumetric circular;
- ring.
The pie chart subtypes differ only in their visual effects. Moreover, from the point of view of visual perception, the most visually accurate are the first and third types of pie charts. A three-dimensional pie chart distorts the segments due to its inclination and height, and therefore cannot claim to be accurate in external perception.
By clicking on the type of chart you like, a chart window appears in the document, containing by default a text field for the title, the pie chart itself, and a legend.
Further work with the pie chart allows you to improve it appearance and filling depending on the task. For example, for clarity, the diagram can be supplemented with data labels by right-clicking on any segment and selecting the appropriate function in the context menu. The base colors of segments can also be changed by double-clicking on the segment and in the context menu and selecting “Data Point Format” – “Fill” – “Solid Fill” – “Color”.
The Format Data Point dialog box also allows you to:
- Series parameters. In this tab you can change the rotation angle of the first sector, which is convenient if you need to move the sectors among themselves. It is important to remember that this function does not allow you to swap sectors - to do this you need to change the sequence of segments in the data table. The “Cut Point” function is also available here, which allows you to separate the selected segment from the center, which is convenient for focusing attention;
- Fill and Border Colors. In these tabs, not only single-color fills are available, but also those familiar to the software package Microsoft Office gradient color fill, as well as texture or image. You can also change the transparency of segments and strokes here;
- Border styles. This tab allows you to change the width of the borders and change the type of lines to dashed, solid and other subtypes;
- Shadow and Shape Format. Additional options are available in these tabs. visual effects, which can be used to supplement a pie chart.
Using Pie Charts
A pie chart is designed to visualize the composition of an object, process or object. With its help, it is easy to visualize the detail of financial indicators, the distribution of income or costs, and sales volume.
Thanks to its shape, a pie chart is intuitively perceived as a solid object. That is why it is not recommended to use it if incomplete data is provided. For example, to present a company's sales volume only for those months in which the largest profits were recorded, it is better to use line graph, not a pie chart.
In addition, a pie chart is not suitable for presenting dynamics or comparing data over time.
