secrets 

Excel. Pie chart with two data sets. Building a pie chart How to build a pie chart with decimal degrees

Build a pie chart with a percentage Amounts payable by quarters.

To build a pie chart, select rolled up sheet table Results columns Period And Amount to be paid (range D1:E15).

On the menu Insert, In chapter Diagrams choose Circular

in the window that appears, select the type of chart - Circular.

On the sheet Results p A pie chart appears. To display percentage Amounts payable quarterly follows:

    Right-click on the diagram and select from the list Add data labels. The data values ​​in rubles appear on the diagram.

    Right-click the chart again and select Format Data Labels from the list. In the window that opens, install:

- "Signature Options" - shares,

- "Signature Position" - At the top, outside.

To place this chart on a separate sheet, you need to in the menu Insert In chapter Location click Move chart. In the window that opens, mark " on a separate sheet”, press OK. name sheet Circular.

Building a histogram.

P
we sharpen the histogram, depicting on it by periods Amount to be paid, Paid And Duty. Creation order diagrams as in the previous task. However, here it is convenient to hide the column Difference on a sheet Results . To do this, activate any cell in this column. Execute the command Format/Column/Hide. Select the required columns:

Let's build a histogram:

To get a mixed chart and present Duty in the form of a line graph, you need to click on one of the columns Debt .

Then run the command with the right mouse button Change the chart type for a series. Select bookmark Schedule, and then choose from the list Schedule. The diagram will look like this.

For a line chart, it is convenient to create an additional Y-axis on the right side of the chart. This is all the more necessary if the values ​​for the line chart are incommensurable with the values ​​of other columns of the histogram. Click on the line graph and execute the command Data series format.

In the window that opens: open a bookmark Row Options and check the box minor axis . After that, an additional Y-axis - (on the right) will appear on the chart. Push button OK .

Columns can be assigned their respective values. To do this, click on one of the columns with the right mouse button and select Add data labels .

Filtering (selecting) data

Go to sheet Autofilter . Filter data in a field Period by value 1 sq. And 2 sq. , in field Duty output values ​​that are not equal zero .

Performance. Make any cell in a sheet table active Autofilter . Run command Data /Filter/ Sort and filter An arrow appears next to each column of the table. Expand the list in the column heading Period and choose Text filters , then equals . A window will appear Custom AutoFilter , in which we perform the installation:

In the column header Duty choose from the list Numeric filters , then Custom Filter. A window will open Custom AutoFilter, in which we will make the settings:

After that we get:

Advanced filter

Team Advanced filter (additional) , as opposed to the command Filter , requires task string selection conditions in a separate worksheet range or on another sheet. The condition range includes the condition column headings and the condition row headings. The column headings in the condition range should match exactly with column headings in the original table. Therefore, it is better to copy the column headings for the range of conditions from the table. The condition range includes only the headings of the columns that are used in the selection conditions. If you need to apply several ranges of conditions to the same table, then it is convenient to assign names to the ranges of conditions (as named blocks). These names can then be used in place of condition range references. Examples of condition ranges (or selection criteria):

If the conditions are located on different lines, then this corresponds to a logical operator OR . If Amount to be paid over 100,000 and Address – any (the first line of the condition). OR if Address - Perm, and Amount to be paid – any, then the list will be selected rows that satisfy one of the conditions.

Another example of a range of conditions (or selection criteria):

Conditions on the same line are considered connected by a logical function AND , i.e. Both conditions must be met at the same time.

Thus, the filtering conditions placed in one line of the range are combined by the logical function AND , conditions specified in different lines, by the function OR . An empty cell in the range of conditions means any values .

Let's create a new sheetFilter.

Example 1. From table to sheet Worksheet use the advanced filter to select records that have Period – 1 sq. And Debt + Penalty >0. The result must be obtained in a new table on the sheet Filter .

On the sheet Filter for output filtering result, create a table header by copying the headers from the table Worksheet . If the selected blocks are not adjacent, then when selecting, use the key ctrl . Arrange starting from cell A5 :

H
on the sheet Filter create a range of conditions at the top of the sheet Filter in cells A1:B2 . Field names and period values ​​must be copied from the sheet Worksheet .

Condition1 .

Let's run the command: Data/Sort and Filter/Advanced . A dialog box will appear:

Insert the initial range and the range of conditions using the key F 3 .

Check box copy the result to another location. Put the results on the sheet Filter in the range A5:C5 (select cells A5:C5 ). We get the result:

Example 2 From table to sheet Worksheet using the advanced filter to select lines with the address Omsk behind 3 sq. with more payable 5000 and with the address Permian behind 1 sq. with any amount payable. On the sheet Filter create a range of conditions at the top of the sheet in the cells D 1: F 3 .

Name this range of conditions Condition_2 .

Field names and period values ​​must be copied from the sheet Worksheet . Then execute the command Data/Sort and Filter/Advanced .

In the dialog box, make the following settings:

We get the result:

Example 3. Select customer details with codes - K-155, K-347 and K-948 whose debt exceeds 5000.

On the sheet Filter in cells H 1: I 4 create a range of conditions named Condition 3.

Field names must be copied from the sheet Worksheet .

After executing the command Data/ Sort & Filter/ Additionally make the following settings in the dialog box:

We get the result:

Computed Conditions

The range of conditions can contain calculated criteria. Rules for creating a range of a calculated condition:

    The column heading of the calculated criterion must not match the table column headings or is not filled at all.

    In the cell where the criterion is formed, the sign "=" (equal) is entered.

    Then a formula is entered that calculates a boolean constant (FALSE or TRUE).

Example 4. From table to sheet Worksheet select rows with values Paid greater than the average for this column. Get the result on the sheet Filter in the new table:

    On the sheet Filter create a header » new table by copying from a sheet Worksheet .

      For the convenience of creating a calculated condition, we place two windows on the screen: one is a sheet Worksheet , other - sheet Filter. To do this, run the command View/Window/New Window . Then command View/Window/Arrange All . Check the box from left to right . Two windows will appear on the screen, in the first of which we will place the sheet Worksheet , and in the second - a sheet Filter. Thanks to this, it is convenient to create a formula for the selection criterion for sheetFilter .

    Make the cell active E 22 sheet Filter, Let's create an expression in it:

    Enter the sign = (equal to), click on the cell F 2 on work sheet (F 2 - the first cell of the Paid column ).

    Let's introduce the > (greater than) sign.

    With the help of the function wizard, we introduce the function AVERAGE .

    In the arguments window of this function, specify the range of cells F 2: F 12 (select it on the sheet Worksheet ). Since the range for which we find AVERAGE , does not change, then the range addresses must be absolute, i.e. $ F $2:$ F $12 . Sign $ can be set using the function key F 4 . In the function window AVERAGE press o TO.

To check if a condition with an average value is met compares the value of each column cell F . Therefore, on the left side of the inequality, the address F 2 - relative (it changes). AVERAGE on the right side of the inequality, the quantity constant. Therefore, the range of cells for this function has absolute addresses $ F $2:$ F $12 .

    In a cell E 22 sheet Filter a constant will form True or Lie :

    Let's make active any free cell of the sheet Filter and run the command Data/Sort and Filter/Advanced .

    Make the settings in the dialog box. The initial range is defined by the key F 3 . For input range of conditions select cells E21:E22 sheet Filter (the column heading of the calculated condition is not filled in, but is highlighted along with the condition). Select cells for the result range A21:C21 on a sheet Filter.

    We get the result:

The sequence of actions when building a chart

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 multiple non-contiguous data ranges, select by holding down the .

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

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

After plotting the chart, you can change:

· dimensions of the diagram by dragging the dimensional designations that appear when the diagram is selected;

position of the chart on the sheet by dragging the chart object with the mouse;

· font, color, position of any element of the diagram 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 first clockwise from the top. Each sector of the circle must be marked (required name, value and percentage). If it is necessary to focus on a particular sector, it is separated from the rest.

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

With a pie chart, only one data series can be displayed, each element of which corresponds to a certain 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:

There are 6 types of pie charts in Excel:

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

¾ volume circular;

¾ secondary circular - part of the values ​​of the main diagram is placed on the second diagram;

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

¾ volume cut circular;

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


If you want to separate slices in a pie chart, you don't have to change the chart type. It is enough to select the circle and drag any sector in the direction from the center. To return the original view of the chart, 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 highlight the data series, the second - the specified sector.

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

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

To set data selection methods for the secondary chart, in the context menu, select Data Series Format and go to the tab Options. Here we specify 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.

Udachny osh 1-3 st. mathematics teacher Sychevskaya L.A.

Udachny secondary school I-III levels

Krasnogvardeisky district

Republic of Crimea

Public lesson

math in 6th grade

on the topic of:

"Pie charts"

Prepared and hosted:

Mathematics teacher

Sychevskaya L.A.

Mathematics lesson on the topic "Pie charts"

Goals:

    Mastering the ways of visualization of numerical data.

    Learning to build a mathematical model using pie charts.

    Application of theoretical knowledge in practical human activities.

Tasks:

    Repeat previously studied material on the topics "Percentage", "Proportions".

    Introduce the concepts of "Charts", "Pie charts".

    Show the practical application of knowledge and skills on the topics "Percentage", when building pie charts.

    Learn how to build pie charts.

    Improving the skills of geometric constructions.

    Achieve accurate and accurate execution of drawings.

    Learn how to build diagrams in the XL program using computer technology.

Lesson forms: combined (generalization and systematization of knowledge, explanation of new material, consolidation of the studied material, verification and correction of knowledge).

Working methods and techniques:

    teachers: frontal work (explanation of the new, dialogue, presentation demonstration, individual assistance in performing independent work);

    student: frontal (dialogue; viewing a presentation) and individual (solving programmed tasks, working in a notebook, working at a computer, solving tests).

Materials and equipment: lesson scenario, presentation "Pie charts", PC, answer forms, Smeshariki's drawings on the board and route sheet

Lesson Plan

Writing on whiteboard and computer monitor

    Organizing time

Friends will gather in the clearing to find out how everyone is doing. We will help them today, let's go on our way. Checking students' readiness for the lesson. Prepare students for the lesson.

I will ask you some riddles. Solve the riddles and check for these items on your desktops and show them to me.

    Who silently teaches? ( Textbook)

    Who will build our drawing?

Well, of course… ( Pencil)

    Two legs conspired

Make arcs and circles. ( Compass)

    She loves directness and is direct herself.

It helps us all to make a new line.

Draw something without her.

Guess, friends, what is this? .. ( Ruler)

    If you give her a job

The pencil worked in vain. ( Eraser)

    Constructing corners

Helps… ( Protractor)

    To homework

We do not forget

We will write it in ... ( Diary)

Kopatych likes to always have everything at hand

We checked the availability of the necessary items in the lesson.

2. Writing in notebooks the number and topic of the lesson.

Now open your notebooks, write down the number, classwork and the topic of the lesson that you will learn from the rebus on the Pie Chart board.




Today in the lesson we have to solve the following tasks and build with the help of the program

    Generalization and systematization of knowledge

Before we move on to the study of new material, we will review the previously studied material. . Krosh is a lazy lover, we won’t let him fall asleep ....

Task number 1

Words are missing from definitions and concepts. Fill in the missing words. Remember that the number of strokes corresponds to the number of letters in the missing word. Write down the answers in the answer sheet No. 1. Before writing down the answer, think carefully, as a corrected answer will be considered a mistake.

The answer sheet for task number 1 looks like this.

№№ definitions

Missing words

Wrong answers

Total:




Mutual verification by a given key. In the wrong answers section, against each question we put as many "minuses" as the number of words was written incorrectly.




In the “Total” cell of the answer sheet for task No. 1, write down the total number of minuses.

    1. Learning new material

    1. Explanation of theoretical material

Often in practice, the results of any human activity, comparing the cost of certain products, the composition of various mixtures or any other numerical data, it is more convenient to present them visually, in the form of a figure. Numbers are easier to compare if they are represented as graphical objects of different sizes. Drawings are better perceived by a person. And, even if the parameters of the depicted object, action, phenomenon are not known in advance, one can visually compare the results according to the drawing and draw certain conclusions. Such drawings are called graphs and diagrams.

Today we will get acquainted with the diagrams.

The method of conditional representation of numerical values ​​​​and their ratios, using geometric means, is called a diagram.

Let's write down the definition of the diagram in a notebook.

A chart is a way of visually representing numerical data.

There are many types of diagrams. Diagrams Can be planar and volumetric. Among the charts, there are: graphs, columnar, line, dot, ring, pyramidal, conical, cylindrical, etc. We will study pie charts. Nyusha will tell us about the types of diagrams, how she wants to get to her friends.

The construction of diagrams is performed according to the following algorithm.

To build a pie chart you need:

    draw a circle

    Convert them to degrees

    Construct angles with a protractor

    Paint sectors with different colors

2. Solving the problem on cards

Construct a pie chart of the distribution of the daily food intake.

We will act in accordance with the algorithm.


Based on the results of the calculations, we build a diagram.

Who has any questions, difficulties in the performance of work?

Physical education "Two hands" with a presentation.

    1. Optimization of data processing with a PC

Well, the Hedgehog is smarter than everyone, he owned a computer

And built a diagram of everyone faster

Did you notice how much time you spent building the chart by hand? Today, in practice, no one builds diagrams manually or build them, but very rarely. There is a very good assistant to do this job. This is a computer. Take your seats at the PC.

You have a Microsoft Excel spreadsheet environment on your screen. The table contains the data of the problem that we have now solved. Our task is to build a chart using the Spreadsheet Processor Chart Wizard MS Excel. Listen carefully to me, try to follow the steps clearly and you will succeed.

    Select cells A2-B5. To do this, place the mouse cursor in cell A2 and, holding the left button, drag the mouse to cell B5. The table will turn black.

    In the menu bar, find the INSERT command and click on this word with the left mouse button. A drop-down menu will open.

    In the drop-down menu, select the DIAGRAM command and click on it with the left mouse button. The CHART WIZARD dialog box opens.

    In the chart type list, select the word PIE. On the right side of the window, various types of diagrams will open. Choose 1 option. And click the NEXT button at the bottom of the dialog box.

    In the next step, click the NEXT button again.

    In the fourth step, click the DONE button.

When doing any job, you should look for the best option. There was such a thing as optimization of activity. Optimization refers to reducing the time it takes to perform an operation.

IV. Independent work

1 subgroup. Think of a diagramming problem. You can get acquainted with the tasks in the textbook, but the condition of the task should not match any of them.

V. Checking the results.

VI. Summing up the lesson.

Let's summarize the lesson. Answer a few questions.

    What is a chart? ( A way to visualize numerical data)

    What diagrams have you learned to build today? ( Pie charts)

    What other types of charts do you remember? ( Bar, Graph, Donut, …)


Application

In this tutorial on pie charts, you will 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 sector numbers, are used to show how much of a whole individual amounts or percentages make up. In such graphs, the whole circle is 100%, while individual sectors are parts of the whole.

The public likes 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? Freehand drawing a pie chart is difficult, given the confusing percentages that present a particular challenge. However, in Microsoft Excel, you can build a pie chart in just a couple of minutes. Then all you have to do is spend a few more minutes on the chart and use the 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, it only takes a few clicks. The main thing is to correctly format the source data and choose the most appropriate type of pie chart.

1. Prepare the initial data for the pie chart

Unlike other Excel charts, pie charts require the source data to be organized in one column or one row. After all, only one data series can be built in the form of a pie chart.

In addition, you can use a column or row with category names. The 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 has only one data series.
  • All values ​​are greater than zero.
  • There are no empty rows 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 tutorial, let's try to build a pie chart in Excel based on the following data:

2. Insert a pie chart on the current worksheet

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

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

3. Choose the style of the pie chart (if necessary)

When the new pie chart has appeared 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 the worksheet:

Agree, this pie chart looks a little rustic and, of course, needs some improvements, for example, the title of the chart, and perhaps more should be added. We will talk about all this a little later, but now we will 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 from the following subtypes:

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

Volumetric 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 three-dimensional pie chart in Excel, additional functions appear, such as.

Secondary Pie or Secondary Bar Chart

If the 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 on the 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 these categories are larger than the others. Since the default settings are not always the best, you can do one of two things:

  • Sort the original 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 chart.

Selecting Data Categories for the Secondary Chart

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

  1. Right-click on any sector of the pie chart and select from the context menu Data series format(Format Data Series).
  2. Row Options(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 below the threshold will be moved to the secondary chart.
    • Percent(Percentage value) - same as Meaning(Value), but the percentage threshold is specified here.
    • Other(Custom) - allows you to select any sector from the pie chart on the worksheet and specify whether to move it to the secondary chart or leave it in the main one.

In most cases, a threshold expressed as a percentage is the most reasonable choice, although it all depends on the initial data and personal preferences. This screenshot shows how to split a series of data using a percentage:

Additionally, you can configure the following settings:

  • Change Side clearance(Gap between two charts). The gap width is set as a percentage of the secondary chart 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 Size of the second construction area(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 bigger or smaller, or manually enter the percentage you want.

Donut charts

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

Resize a hole in a donut chart

When creating a donut chart in Excel, the first thing to do is resize the hole. This is easy to do in the following ways:


Customize and improve pie charts in Excel

If an Excel pie chart is just for getting a quick look at the big picture of your data, then the default pie chart is fine. But if you need a beautiful diagram for a presentation or for some similar purpose, then you can improve it a bit by adding a couple of strokes.

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 signatures, it is difficult to determine what share each sector occupies. You can add labels to a pie chart for the entire series or just for a single 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 sectors. 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 also change the location of the labels by clicking on the arrow to the right of the parameter. Compared to other types of charts, pie charts in Excel provide the most choice of label placement:

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

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

Categories in data labels

If an Excel pie chart has more than three pie slices, then labels can be added directly to each pie slice so that users don't have to jump back and forth between the legend and the chart looking for information about each pie.

The fastest way to do this is to select one of the pre-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:

Click the icon to access other options. Chart elements(Chart Elements) in the upper right corner of the pie chart, click on the arrow next to Data Signatures(Data Labels) and select Extra options(More options). A panel will appear Data Label Format(Format Data Labels) on the right side of the worksheet. Go to section Signature Options(Label Options) and tick 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 to be contained in the labels. In our example, this Category name(Category Name) and Meaning(Value).
  • In drop down list Delimiter(Separator) choose how to separate the data in the labels. In our example, the separator is selected New line(New Line).
  • under the heading Label position(Label Position) choose where to place the label. In our example, we have chosen 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 unchecking the box next to Legend(Legend).

How to show percentages on a pie chart in Excel

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

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

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

Splitting a pie chart or highlighting individual sectors

To emphasize individual values ​​of a pie chart, you can break it, 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 chart.

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

How to split a pie chart in Excel

The quickest way to break a pie chart in Excel is to click on it to select all the slices and then drag them away from the center of the chart with the mouse.

In order to fine-tune the breakdown of a pie chart, you must perform the following steps:

How to highlight individual sectors of a pie chart

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

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

There is another way: select the sector you want to push, right-click on it and in the context menu click Data series format(Format Data Series). Then, in the panel that appears, open the section Row Options(Series Options) and adjust the parameter Point cutting(Point Explosion):

Comment: If you need to select multiple sectors, then you will need to repeat this process with each of them, as shown above. It is not possible to select multiple sectors of a pie chart at once in Excel, but you can split the entire pie chart, 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 built depends on the order in which the data is placed on the worksheet. You can rotate the pie chart 360 degrees to show data from different perspectives. As a general rule, a pie chart looks best if its smallest slices are in front.

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

  1. Right click on the chart and select Data series format(Format Data Series).
  2. On the panel that appears, under Row Options(Series Options) move the parameter slider Rotation angle of the first sector(Angle of first slice) to expand the chart, or enter the desired value manually.

Rotating 3D Pie Charts

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

A panel will appear Chart Area Format(Format Chart Area), where you can set the following parameters for the rotation of a three-dimensional figure:

  • Horizontal Rotation around the X axis(X rotation)
  • vertical Rotation around the 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 (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 the angles of rotation, the diagram will immediately rotate. Thus, it is possible to make small changes in the angle of rotation of the diagram until it is in the desired position.

How to arrange pie slices by size

As a rule, pie charts are more understandable if their sectors are sorted from largest to smallest. The fastest way to achieve this result is to sort the original data on the worksheet. If it is not possible to sort the source data, then 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 ways out:

Change the color scheme of a pie chart in Excel

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

You can also click anywhere on the pie chart to bring up 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 the button Change colors(Change Colors):

Choose colors for each sector separately

As you can see in the picture above, the choice of chart color schemes in Excel is not rich, and if you need to create a stylish and bright pie chart, you can choose your own color for each sector. For example, if they are located inside the chart 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 Shape fill(Shape Fill) and select the desired color.

Advice: If there are a large number of small, not very important sectors on the Excel pie chart, then you can color them in 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 give it a more attractive look.

To open the formatting options, right-click on any sector of the pie chart and in the context menu, click 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:

  • Resizing the pie chart (height and width);
  • Shape fill and outline changes;
  • 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, pie slice, or chart title) and click the Format(Format). Appropriate formatting options will be active, and unnecessary formatting options will not be active.

Now that you know how to build a pie chart in Excel, let's try to make a list of the most important guidelines 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 the sectors. If the pie chart consists of a large number of sectors, it is best to group them, and then use separate colors and shades for each group.
  • Color minor small sectors in gray. If the pie chart contains small sectors (less than 2%), color them gray or group them into a separate category called Other.
  • Rotate Pie Chart so that the smallest sectors are in front.
  • Avoid too many data categories. Too many sectors on the chart looks like a pile. 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 pie slices so that readers don't have to look between the slices and the legend.
  • Don't get carried away with 3-D effects. Avoid a lot of 3-D effects in a diagram, as they can greatly distort the perception of information.

It has long been known that a visual way of presenting information is the most accessible to any audience. That is why presentations and working papers present data in the form of charts.

Frequently used charts

Modern office-type computer programs help to build this or that type of diagrams automatically, without any calculations. All you have to do is enter the information you have. Depending on the type of information provided, one or another type of diagrams is chosen.

Histograms

This type of chart is used when it is necessary to display data in the form of columns or rows. This type of graphical representation illustrates the change in objects over a period of time. Categories are usually located on the horizontal axis, and the vertical axis is used to indicate values. They can be both linear and volumetric, as well as cylindrical, pyramidal and cone-shaped.

Graphs

The same type of data as in histograms can be represented as graphs. They are used to depict continuous changes in data over a period of time.

Pie charts

Also, data can be presented in the form of a pie chart, this is perhaps the most common type of chart. Even in the school curriculum, pie charts are increasingly used to provide students with information. Grade 5 is a difficult period for students - the transition from elementary school to the assimilation of more complex data. And such a representation helps to see as clearly as possible what part of the total is occupied by each individual element of the diagram. The volumes of a particular segment are a percentage of the totality of all elements.

The pie chart has its own characteristics:

  1. Only one series of data is suitable for presentation in this form.
  2. The values ​​provided are not negative.
  3. Values ​​must be greater than zero.
  4. The desired number of segments is no more than 7.

Bar charts

When it is necessary to show how a process occurs over time, while the process is long enough, bar charts are used to graphically represent information. They are horizontally arranged columns, in a comparative form with each other.

What other charts are there

There are many more types of diagrams that are used in a certain area and are not so often in demand. These include area charts, scatter charts, stock charts, surface charts, donut charts, bubble charts, and spade charts.

Features of Pie Charts

A pie chart is one of the easiest graphical representations of data to understand. It shows individual parts of the total and is a useful tool to use when analyzing surveys, building statistics, complex data, illustrating income or expenses.

Along with ease of understanding, the pie chart is very informative - the audience immediately sees the whole picture as a whole and its individual components.

How to build a pie chart?

To begin with, the diagram needs to be calculated, more precisely, to calculate its proportions. It is best to display all available numerical data in one column in descending order. Now it will be very easy to find the sum of all the numbers written in the column - it is for this sum that all pie charts are built. Grade 5 will cope with this task in a couple of minutes.

Then more complicated manipulations will begin - we will distribute the data by percentage. To do this, you need to divide each available numerical value by the amount obtained in the previous step.

Further more! We need to calculate the angle of each slice of the pie corresponding to each element. To do this, we multiply all percentages in the form of a decimal fraction by 360, that is, the number of degrees of the entire circle. Thus, next to the percentage of chart elements, we get the corresponding sector angle, which is occupied by this element in the chart.

Next, with the help of a compass, draw a circle, and with the help of a protractor, draw sectors in degrees, starting with the largest, and move clockwise or counterclockwise. For better clarity, each element of the diagram is painted with a separate bright color.

To the right of the diagram indicate the notation - the color and data that represents the segment of this color on the diagram. In addition, you can display the percentage of data both on the chart itself and in the list of symbols.

Building a Pie Chart in Excel

With a table of data in the Excel office program, it is very easy to create a chart that will provide all the information more clearly. How to build a pie chart in Excel?

For example, we have a table with the name of a product and the number of units sold. In order to build a chart on it, you need to use the "Chart Wizard". You can find it along the following path: "Insert" - "Chart". From the proposed list of standard charts, you need to select a circular one. Then you need to determine what form our pie chart will have. Click "Next" and start filling in the data, on the basis of which the program will build their graphical representation. You need to click on the "Range" button. This tab should contain information about all numerical values, in our example - the entire column with information about the quantity of goods sold. After selecting the area, Excel displays a window to move on to the next step.

The next step is to choose the direction of the table. For our example, you need to select "Rows" in the columns. In the "Row" tab, you need to change the name of the products. In the “Category labels” window that appears, after clicking on the button, select the area of ​​the table with the names of the products.

We return to the "Chart Wizard", click the "Next" button and draw up the resulting table. You can add the name of the table, sign the name of the goods, as well as the share in the total amount of revenue.

The wizard will ask you to choose the placement of the chart. Now that we know how to make a pie chart, we can pay attention to such an aspect as its location. It can be located on the same sheet with the table, or in another tab.