The human mind is designed in such a way that it is easier for it to perceive visual objects, otherwise they have to be represented. It is this fact that has led to the widespread use of diagrams. To create them, you do not need to install separate programs, just remember excel.
The process of creating a diagram
First of all, you will need to enter the initial data, most users try to create it on an empty sheet, which is a mistake. The table should consist of two columns, in the first it is necessary to place the names, and in the second the data.
Next, you need to select the table along with the names of the columns and columns, and then go to the "Insert" tab, selecting the "Charts" item there. Several options will be offered, 4 types are available in the “Pie” menu: the last two are used for complex data sets in which some indicators depend on others, for simple tables the first and second charts are used, they differ only in the presence of cuts. After clicking on the icon, a diagram will appear.
Change data and appearance
For a successful presentation of the material, it is not enough to know only how to build a pie chart inexcel, often it is necessary to edit it at your discretion. There is a special menu for this, which will become active after clicking on the diagram. There you can give it a different shape or save the current settings as a template if you need to create many similar ones.
Also, the color is configured here, for this you should select the desired style in the corresponding menu. To insert inscriptions, you need to go to the “Layout” tab, the “Signatures” menu is located there, you need to select the “Data Signatures” item, an additional window will appear.
To edit the inscriptions, you need to click on one of them and select the “Format signatures” item. In a special window, you can customize their color, background, location, and content, for example, activate relative data instead of absolute data.
Of course, these are not all possible settings, but for most users they are enough to build a pie chartexcel.
Want to create a pie chart but don't know how? No problem. Everything is done very simply. You will need to follow the further instructions and everything will definitely work out for you. Consider an example. To get started, open Excel 2007. And fill in the required number of cells.
To build a pie chart based on data on purchased and sold berries, you need to select the required range of values (a column with the name of the berries and a column with the number of these berries purchased), see below. It is also worth capturing the header of this table.
The next step is to switch to the Insert | Diagrams
From the diagrams section, select a pie.
The main difference between the modern office is its simplicity, compared to previous versions. After clicking on the selected diagram, you will have a ready-made diagram in the middle of the screen, which can be manipulated using additional settings in the top toolbar.
Each part of the chart can be manipulated with the mouse pointer. In the Designer toolbar, you can select different colors, percentages, and some other settings.
In order to display the values of the table on the chart, you need to right-click on the circle itself, after the menu appears, select the following item: “Add data labels”.
In order to make another pie chart for another column, in our case for the second, where there are records of the number of berries sold, it is necessary to select the data necessary to build the chart. To select two columns not located next to each other, you need to hold down the "Ctrl" button on the keyboard and select two areas in turn.
The principle of construction is no different from the previous one, you need to go to the Insert / Chart / Pie Chart tab, the next step, for a change, you can select another type of chart - “volumetric chart”.
If necessary, you can also add data labels.
You can also change the name of the chart, by default, after the chart is built, its name is selected automatically by the name of the column. In order to change its name, you must double-click on the existing inscription, in this case, the inscription “Sold. Kg", right click.
After building a diagram, it can be manipulated, namely, to change the location of each area or change the type, for this you need to right-click on the diagram.
After pressing the "Construction area format..." item, you can set additional visual parameters of the table: fill, border color, border styles, shadow, volumetric figure format, rotation of the volumetric figure.
You can set the shadow, the volume of the frame, change the position of individual sectors of the diagram, etc. For example, let's try to change the location of our chart. To do this, select the "Rotate 3D Shape" item in this menu.
As shown in the figure, changing the x, y and perspective coordinates can change the position of the chart.
Also, after creating a chart, if necessary, you can change its type, for this you need to right-click on the chart and select "Change chart type ...". After clicking, the following menu will appear:
This menu has a larger selection of different charts. This menu has a large selection of different charts.
Charts are an integral part of a variety of presentations, slide shows, business programs and financial reports. With the help of a diagram, you can visually display how this or that aspect of the company's work has changed over the years, or show the percentage of a variety of parameters. A beautiful and clear diagram will be more understandable to the reader and viewer than a vague drawing, so it is very important to learn how to draw beautiful, clear and easy to understand diagrams that will help you in your work.
Instruction
In Microsoft Excel, you can, by specifying your options, create an automatic diagram from program templates, but these diagrams are neither clear nor aesthetically pleasing. Based on the finished chart in Excel, you can create your own diagram, more descriptive, concise and stylish, using the same settings.
In the previous diagrams, get rid of everything superfluous. Your diagram should resemble a light and understandable, free from unnecessary details, transparent and aesthetic. To make the graph clear, do not use more than two colors in it. Red, black and shades of these colors will be the best option for any chart. This will make it easy to read the information on each scale. Be sure to reflect in a noticeable increase in parameters.
If you have two charts whose indicators can be combined into a common graph, use this opportunity - make one common convenient diagram. Color each parameter.
You should not show the ratio of different indicators in one column of the chart - such information will not be read by the viewer. It would be much better to divide each into two - red and black. Each column will show the value of one or another indicator, and in accordance with it, the height will be shown. However, reading information can still be difficult as the number increases and it is difficult for the reader to relate them to the total values on the lower scale.
The same chart will look much neater, in which the main scale (for example, distributing results by ) will be located vertically, and all will be directed sideways. Since the long numbers will also be horizontal, the chart will not look like there is too much data.
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 a 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 per 3 sq. with more payable 5000 and with the address Permian per 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 - leaf 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:
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.
The pie chart type is useful when you want to display the share of each value in the total.
Let's build a three-dimensional pie chart that displays the production load during the year.
Rice. Data for plotting charts.
With a pie chart, only one data series can be shown, 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:
Rice. Pie chart.
There are 6 types of pie charts in Excel:
Rice. Types of pie chart.
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.
Rice.
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.
Rice. Changing the appearance of a pie chart.
In a pie chart, sectors can be rotated 360 around the circle. To do this, select the tab on the ribbon. Layout and paragraph Selection Format.
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.
Rice. secondary chart.
Data that is arranged in columns and rows can be displayed as a scatter plot. A scatter plot shows relationships between numerical values in multiple data series, or displays two groups of numbers as a single series of x and y coordinates.
scatter plot has two value axes, with some numeric values displayed along the horizontal axis (X-axis) and others along the vertical axis (Y-axis). In a scatter plot, these values are combined into a single dot and plotted at irregular intervals or clusters. Scatter plots are commonly used to illustrate and compare numerical values, such as scientific, statistical, or technical data.
