Creating diagrams. Graphs and charts in Microsoft Excel

Diagrams can visualize complex tabular information. It’s easy to decorate your text report with a beautiful graph, Microsoft Word There are some good tools for this. We'll tell you how to make a diagram in - directly in text editor or transfer from Excel, how to configure it appearance.

To make a graph in Word, you will need numerical data on the basis of which the graphic image will be built. How to create a chart: go to the “Insert” tab, in the “Illustrations” section, select “Insert Chart”. In the window that appears, select the type - histogram, line, petal or any other. Click “OK”, the template will appear and Excel spreadsheet below it with numbers for example.

Let's make a pie chart - enter your data into the table, the graph will change automatically. The first column is the labels of the categories, the second is their meanings. After finishing entering, close the sign using the cross button; the information will be saved and available for editing at any time.

How to draw a graph: Select the "Graphs" type when creating. The first column is point marks, the rest correspond to lines. To add another line image, simply enter the numbers in the next column; to make lines smaller, delete last column. Number of rows – number of data for each category.

Import from Excel

If your data is stored in Microsoft Excel, you can build a diagram there, and then copy it into Word. The documents will be linked, and when the source data in the table changes, the appearance of the graphs will be automatically updated.

How to insert a chart from Excel:

• Click on the graph in Excel, select “Cut” or press Ctrl+X. Graphic image will disappear, only the data will remain.
• Go to Word, place the cursor in in the right place, click on “Paste” or Ctrl+V.
• Save the document. The next time you open it, select Yes to update the information.

Settings

We figured out how to build a graph, now let's set up its display. If you need to change the values, right-click on the histogram and go to “Change data” in the menu. A table will appear, available for editing. Through the same context menu You can change the chart type, label format, and value range.

Tools for quickly editing the appearance appear on the right when you left-click on the chart. They will help you add or remove individual elements, apply a style, configure the display of points.

For flexible chart setup, Word has 2 tabs: “Design” and “Format”. They appear in the menu when you click on the created chart. In the Design tab, create a unique look using ready-made templates express layout, style and color schemes.

You can change the details of any fragment manually: click on the desired element of the graph and go to the “Format” tab. In the “Current fragment” section, select “Format selection”, the additional menu. Draw your own style by changing the fill, borders, shadow parameters, effects. For text, you can change the outline, fill, and insert WordArt styles.

Conclusion

We told you how to build diagrams in Word and how to change their appearance. Try making your own charts - clever tools make the process fun.

Diagrams needed to visualize numerical data. It’s one thing to say in words that in a class of 20 people there are 3 excellent students, 10 good students, and the rest are C and D students. And it’s quite another thing to draw beautiful picture. We will look in detail at working with diagrams, because they greatly facilitate the perception of information.

Button in group Diagrams open the Insert Chart window (Figure 4.19).

The left side of this window contains a list of chart types, and the right side contains a list of possible charts within each type. The most common types of diagrams are removed from this window separate buttons to the tape. Let's start with something simple. The simplest chart is a pie chart. Let's take a closer look at an example with students in a class. For example, here is a class (Fig. 4.20).

There are 3 excellent students, 20 good students and 8 C students. To display this data as pie chart, select the drawn table and select this type of diagram in the window shown in Fig. 4.19, or press the button Circular on the tape. I chose this view (Fig. 4.21).

And I got what is shown in Fig. 4.22. The diagram appears in a small window. You can enlarge (reduce) this window by simply stretching its frame. You can move the diagram around the sheet by “grabbing” the center of the window with your mouse.

Please note that it has appeared again new group tabs - Working with charts, which contains three tabs: Constructor, Layout And Format. These tabs are only available when a chart is selected. As soon as you proceed to enter new data, the tabs will disappear from the ribbon.

In Fig. 4.22 tab open Constructor, and I immediately want to draw your attention to the group Location and to the only button in this group.

If you press the button Move chart, you will see such a window (Fig. 4.23).

As you can see, you can leave the diagram on the sheet on which your table is located, or you can define it on a new sheet. Whatever is more convenient for you. Want to see how the chart rearranges itself? Change the numbers in the cells of the second row of the table - the chart will be automatically rebuilt based on the new numbers.

Look, near the diagram there are the words Excellent, Good and C students, as well as small multi-colored squares next to them. They show what color a given category is indicated on the chart. This description is called legend. The legend will change if you enter different text in the first row of the table.

Diagrams are also needed in order to clearly show the dependence of one quantity on another. For this purpose, there are two-dimensional charts with two axes. Let's quickly remember fifth grade math: the X axis is horizontal, the Y axis is vertical.

For example, we planted beans at our dacha and every day we run to measure the height of the sprout. We find out that on the 5th day the sprout was 10 cm, on the 10th day - 20 cm, and on the 15th day - 30 cm. And now we need to clearly show the dependence of the height of the sprout on the number of days that have passed.

Now we will look at a scatter plot. It is best suited for the case where numbers are plotted along the X and Y axes. We enter the data given above into the table, select it and insert a diagram (Fig. 4.24).

Rice. 4.24. Scatter plotFig. 4.24. Scatter plot

As usual, several additional tabs have appeared. In Fig. 4.24 tab open Layout, let's consider it. Just not in order.

Chart labels

This group allows you to label all quantities and axes.

Chart axes

There are such teams in this group.

• Axles- when you click on this button, a list of chart axes appears. You can select the desired axis and configure some of its parameters using the submenu that opens. Please note: in this submenu there is an item Additional options . When you select it, a window will open in which you can decorate the axis as you want.
• Net- for clarity, in the case of complex graphs, you can turn on the grid along the selected axis. By the way, there is also an Additional options line in the menu. If you are an artist at heart, then you will have room to expand.

And now about the other groups of the Layout tab.

• Current fragment. Allows you to select any diagram fragment from the drop-down list, as a result of which it will be highlighted. And then you can format this fragment separately. If you get carried away and want to return everything as it was, you have a button at your disposal Restore style.
• Insert. You can insert a picture, shapes, or text into your diagram. For what? For clarity or just for beauty.
• Background. Allows you to select the background of the construction area. The area where the graph is located will be highlighted in the color you selected.
• Analysis. If you ever need to analyze your schedule, go here. I won’t go into details, there’s a lot of mathematics there. I will say that in the case of the bean sprout, the graph turned out to be a straight line and the analysis showed that this is a linear relationship.

Let me remind you that we considered the case when we need to show the dependence of one quantity on another, and both one and the second quantity are expressed by a number. I described a scatter chart, but you could also use a bubble chart, where instead of a point on the graph there is a large bubble.

Now imagine that you have not one sprout, but two. And you want to draw two graphs. First, of course, you need to enter new data into the table (Fig. 4.28).

And it would seem that the most obvious option for the program to draw new graphs is to select a new table and ask it to draw a new diagram. But... it may not turn out at all what you wanted. Let's go back to the tab Constructor tab groups Working with charts. In a group Data click the button Select data, a window will appear (Fig. 4.29).

This is very important button. Using this window, you can specify what exactly to plot along the horizontal and what along the vertical axis. We need to add another graph, that is, another series or another legend (as they are called in the program).

So, on the left side of the window, click on the button Add(Fig. 4.30).

You do not need to enter data manually into the window that appears; you just need to select the required cells or range of cells in the table. The name of the new row is Length of the second sprout. You can see the absolute address of the cell in the Row Name field. In the Values ​​field, the range of cells containing numbers in the third row. Click OK. (Remember, we talked about how to make a legend appear on the chart? It is with the help of the window Selecting a data source you can determine which cell the series name will be in.)

After this, the Select Data Source window will look as shown in Fig. 4.31.

Now you can change the data of series (graphs), delete any graph, or add another graph. In the right part of the window (see Fig. 4.31) you can change the data that is plotted along the X-axis. Currently, the data located in the first row is plotted along the X-axis. If you want other data to be in their place, click the Change button on the right side, which is called Horizontal Axis Labels. So, let's look at the new diagram (Fig. 4.32).

We now have two graphs and two legends - Length of the sprout and Length of the second sprout. On the Layout tab, in the Captions group, I clicked Data table. And this table appeared on the screen.

At the button Data table there is a menu. You can choose whether to show the data table with or without legend keys. The difference is that a piece of the graph will appear next to the legend in the table. It will be immediately obvious that the Length of the sprout is the red graph, and the Length of the second sprout is the blue graph. I depicted this in Fig. 4.32. You'll have to take my word for it or wait for computer tutorials to come with color illustrations.

I would like to draw your attention to another type of chart - Stacked Chart. You can select it in the group Schedule in the window that appears when you click the Change chart type button in the Tab Type group Constructor. Look at fig. 4.33. The first line and, accordingly, the first graph is salary. Someone's salary in conventional tugriks. The second line of the table is non-salary income in the same tugriks.

Rice. 4.33. Stacked chart

The first graph will simply show the salary. But the second one will be a stacked graph, it will display the sum of the first and second graphs, that is, the program itself will draw the total income. To avoid unnecessary questions, I changed the name of the second chart series using the Select data button. It's in cell A3, which is why the graph is called Total Revenue.

In stacked charts, each subsequent line (shape) is the sum of all previous ones. Naturally, such diagrams can only be used where several diagrams need to be summarized.

The next chart type is histogram. It is very convenient to use when you need to compare something with something. In a histogram, you can “turn on” the third dimension and make it three-dimensional. For example, see what we can do in our case to compare the lengths of the first and second sprouts (Fig. 4.34).

Clearly. Just. The legend shows the color of the column that is responsible for each sprout. You can change the style of the histogram - it can be volumetric, pyramidal, cylindrical, stacked, grouped... Let's return to the tab Constructor. Separately, I would like to talk about the Row/Column button in the Data group. It swaps the rows and columns in the Excel table in which you entered the data for the chart. Let's look at an example. In the diagram in Fig. 4.35 shows the height of three children - Masha, Misha and Pasha.

The legend shows which color column corresponds to which child. Please note: we have three children and four months during which their height was measured. The main parameter is month. It is shown on the horizontal axis. Row titles are plotted along the X-axis, column titles are indicated in the legend. When you press the button, the columns and rows in the table are swapped (Fig. 4.36). Now the column names are plotted along the X axis, and the row names will be written in the legend.

Now the legend is signed not by the child, but by the month. Three children are “laid down” along the horizontal axis, and four columns are allocated for each child. Each bar corresponds to a month - the color is visible in the legend. Main parameter in in this case- child.

If you haven’t completely lost your mind yet, then I suggest you consider the tabs for working with diagrams further on your own. There are still the Chart Layouts and Chart Styles groups on the Design tab, as well as the Format tab. They are for decoration. I hope that I have clearly explained the basic techniques of working with diagrams, and if you encounter them, you will not be afraid, but will successfully cope with all the difficulties.

Excel is a great tool in the software package Microsoft Office to create and work with tabular data of varying complexity. In some cases, a tabular presentation of data is not enough to interpret patterns and relationships in numerical arrays. Especially if they contain several tens or even hundreds of lines. In this case, diagrams come to the rescue; they are very easy and convenient to build in Excel.

1. Select the tabular information you want to express in a chart, starting from the top left cell to the bottom right cell. This data will be used to create the chart.
   
2. In the main menu, activate the “Insert” tab and select the desired chart type in the “Charts” group.

   

   
   
3. In the menu that opens, select the type of diagram you need based on its functional purpose.
   • In a histogram Data categories are typically arranged along the horizontal axis and values ​​along the vertical axis. In volume histograms, data categories are shown along the horizontal and depth axis, while the vertical axis displays the meaning of the data.
     
   • On the charts, allowing you to display changes in data over time, data categories are located along the horizontal axis, and values ​​along the vertical axis.
     
   • Pie charts- display only one row of data, therefore they are formed according to the simplest principle: the share of each sector in the circle depends on the share of the value of each group of data from the total value.
     
   • In bar charts Data categories are located along the vertical axis, their values ​​are located along the horizontal axis.
     
   • Scatter plots initially do not differ in the types of information that are located on their vertical and horizontal axes. When showing relationships between numerical values ​​in data series, they omit differences in the axes. If desired, they can be changed, and the diagram will not lose its information content.
     
   • Stock charts- the most complex type of diagrams based on the principle of constructing information. When constructing stock charts, interrelations, ratios and patterns of changes in several quantities are taken into account.
     
   • Bubble charts- used in cases where it is necessary to display data from a spreadsheet. This uses two columns that distribute values ​​along the X and Y axes, and the size of the bubbles depends on the numeric values ​​in the adjacent columns.
     
4. After selection general type charts, you will be asked to select one of the chart subtypes depending on the required visual design. Make your choice.

   

   
   
5. In the center Excel sheet A diagram will appear that the program has built based on your data.

   

   
   
6. Most likely, it will differ from what you need due to the program’s incorrect selection of data series and values. You need to clarify the presentation of information in the diagram. To do this, click the “Select data” button.
   
7. In the window that appears, select indicators in accordance with your tasks and click the “Ok” button to save the changes.
   

• In the “Designer” tab, it is possible to change the color of the diagram and its overall appearance, thereby changing the method of presenting information. Each sector can be filled with a display of the percentage of the area it occupies of the entire area of ​​the diagram. Here you can completely change the chart type, leaving all captured values ​​the same.
  
• The Layout tab allows you to edit in detail text information on the chart - a legend for each value, the name of the chart, information labels, as well as their location in the chart itself.
  
• In the “Format” tab you can change the appearance of the chart elements. Change the color of the frame, text, design of any elements. Dimensions of the diagram itself, width and length. The background color and the color of each chart element.
  

Excel is one of the most best programs for working with tables. Almost every user has it on their computer, since this editor is needed both for work and for study, while performing various coursework or laboratory assignments. But not everyone knows how to make a chart in Excel using table data. In this editor you can use a huge number of templates that were developed by Microsoft. But if you do not know which type is better to choose, then it would be preferable to use automatic mode.

In order to build such an object, you must perform the following steps.

1. Create some table.

1. Highlight the information on which you are going to build a chart.

1. Go to the "Insert" tab. Click on the "Recommended Charts" icon.

1. You will then see the Insert Chart window. The options offered will depend on what exactly you select (before clicking the button). Yours may be different, since everything depends on the information in the table.

1. In order to build a diagram, select any of them and click on “OK”.

1. In this case, the object will look like this.

Manually selecting a chart type

1. Select the data you need for analysis.

1. Then click on any icon from the specified area.

1. Immediately after this, a list of different object types will open.

1. By clicking on any of them, you will get the desired diagram.

To make it easier to make a choice, just point at any of the thumbnails.

What types of diagrams are there?

There are several main categories:

• histograms;

• graph or area chart;

• pie or donut charts;

Please note that this type suitable for cases where all values ​​add up to 100 percent.

• hierarchical diagram;

• statistical chart;

• dot or bubble plot;

In this case, the point is a kind of marker.

• waterfall or stock chart;

• combination chart;

If none of the above options suits you, you can use combined options.

• superficial or petal;

How to make a pivot chart

This tool is more complex than those described above. Previously, everything happened automatically. All you had to do was select the look and type you wanted. Everything is different here. This time you will have to do everything manually.

1. Select the required cells in the table and click on the corresponding icon.

1. Immediately after this, the “Create PivotChart” window will appear. You must specify:
   • table or range of values;
   • the location where the object should be placed (on a new or current sheet).
2. To continue, click on the “OK” button.

1. As a result of this you will see:
   • empty pivot table;
   • empty diagram;
   • Pivot chart fields.

1. You need to drag the desired fields into the areas with the mouse (at your discretion):
   • legends;
   • values.

1. In addition, you can configure which value you want to display. To do this, right-click on each field and click on “Value Field Options...”.

1. As a result, the “Value Field Options” window will appear. Here you can:
   • sign the source with your estate;
   • select the operation that should be used to roll up the data in the selected field.

To save, click on the “OK” button.

Analyze tab

Once you have created your PivotChart, you will be presented with new tab"Analyze". It will immediately disappear if another object becomes active. To return, simply click on the diagram again.

Let's look at each section more carefully, since with their help you can change all elements beyond recognition.

PivotTable Options

1. Click on the very first icon.
2. Select "Options".

1. This will open the settings window. of this object. Here you can set the desired table name and many other parameters.

To save the settings, click on the “OK” button.

How to change the active field

If you click on this icon, you will see that all tools are not active.

In order to be able to change any element, you need to do the following.

1. Click on something on your diagram.

1. As a result, this field will be highlighted in circles.
2. If you click on the “Active Field” icon again, you will see that the tools have become active.

1. To make settings, click on the appropriate field.

1. As a result, the “Field Options” window will appear.

1. For additional settings go to the "Markup and Print" tab.

1. To save changes made, you must click on the “OK” button.

How to insert a slice

If you wish, you can customize the selection based on specific values. This feature makes it very convenient to analyze data. Especially if the table is very large. In order to use this tool, you need to take the following steps:

1. Click on the “Insert Slice” button.
2. As a result, a window will appear with a list of fields that are in the pivot table.

1. Select any field and click on the “OK” button.

1. As a result of this, a small window will appear (it can be moved to any convenient place) with all unique values(total summary) for this table.

1. If you click on any line, you will see that all other entries in the table have disappeared. All that remains is where the average value matches the selected one.

That is, by default (when all lines are selected in the slice window blue) the table displays all values.

1. If you click on another number, the result will immediately change.

1. The number of lines can be absolutely any (minimum one).

It will change like pivot table, and a diagram that is built according to its values.

1. If you want to delete a slice, you need to click on the cross in the upper right corner.

1. This will restore the table to its original form.

In order to remove this slice window, you need to take a few simple steps:

1. Right-click on this element.
2. After this, a context menu will appear in which you need to select the “Delete ‘field name’” item.

1. The result will be as follows. Please note that the panel for setting the fields of the pivot table has again appeared on the right side of the editor.

How to insert a timeline

In order to insert a slice by date, you need to take the following steps.

1. Click on the appropriate button.

1. In our case, we will see the following error window.

The point is that to slice by date, the table must have the appropriate values.

The operating principle is completely identical. You will simply filter the output of records not by numbers, but by dates.

How to update data in a chart

To update information in the table, click on the corresponding button.

How to change build information

To edit a range of cells in a table, you must perform the following operations:

1. Click on the “Data Source” icon.
2. In the menu that appears, select the item of the same name.

1. Next, you will be asked to specify the required cells.
2. To save the changes, click on “OK”.

Editing a chart

If you are working with a chart (no matter which one - regular or summary), you will see the “Design” tab.

There are a lot of tools on this panel. Let's take a closer look at each of them.

Add element

If you wish, you can always add some object that is missing in this diagram template. To do this you need:

1. Click on the “Add chart element” icon.
2. Select the desired object.

Thanks to this menu you can change your chart and table beyond recognition.

If standard template When you create a chart you don't like, you can always use other layout options. To do this, just follow these steps.

1. Click on the appropriate icon.
2. Select the layout you need.

You don't have to make changes to your object right away. When you hover over any icon, a preview will be available.

If you find something suitable, just click on this template. The appearance will automatically change.

To change the color of the elements, you must follow these steps.

1. Click on the corresponding icon.
2. As a result of this, you will see a huge palette of different shades.

1. If you want to see what this will look like on your chart, just hover over any of the colors.

1. To save changes, you need to click on the selected shade.

In addition, you can use ready-made themes registration To do this you need to do a few simple operations.

1. Expand full list options for this tool.

1. In order to see how it looks in an enlarged form, just hover over any of the icons.

1. To save changes, click on the selected option.

In addition, manipulations with the displayed information are available. For example, you can swap rows and columns.

After clicking this button, you will see that the diagram looks completely different.

This tool is very helpful if you cannot correctly specify the fields for rows and columns when building a given object. If you make a mistake or the result looks ugly, click on this button. Perhaps it will become much better and more informative.

If you press again, everything will go back.

In order to change the range of data in the table for plotting a chart, you need to click on the “Select data” icon. In this window you can

• select the required cells;
• delete, change or add rows;
• edit the horizontal axis labels.

To save the changes, click on the “OK” button.

How to change chart type

1. Click on the indicated icon.
2. In the window that appears, select the template you need.

1. When you select any of the items on the left side of the screen, on the right will appear possible options to build a diagram.

1. To simplify your selection, you can hover over any of the thumbnails. As a result, you will see it in an enlarged size.

1. To change the type, you need to click on any of the options and save using the “OK” button.

Conclusion

In this article, we took a step-by-step look at the technology for constructing diagrams in Excel editor. In addition, special attention was paid to the design and editing of created objects, since it is not enough to be able to use only ready-made options from Microsoft developers. You must learn to change the appearance to suit your needs and be original.

If something doesn't work for you, you may be highlighting the wrong element. It must be taken into account that each figure has its own unique properties. If you were able to modify something, for example, with a circle, then you won’t be able to do the same with the text.

Video instructions

If for some reason nothing works out for you, no matter how hard you try, a video has been added below in which you can find various comments on the actions described above.

Excel provides a significant range of capabilities to the user for graphical presentation of data. You can create diagrams either on the same worksheet with the table or on a separate sheet workbook which is called chart sheet. A chart created on the same worksheet as a table is called implemented. To build charts in Excel use:

Chart Wizard;

Charts panel.

The Chart Wizard allows you to create several types of charts, for each of which you can choose a modification of the main chart option.

Chart elements

The main components of the diagram are presented in the following diagram:

Note: For a 3-D diagram, the components are slightly different.

Chart types

Depending on the selected chart type, you can get different data displays:

bar charts And histograms can be used to illustrate the relationship of individual values ​​or show the dynamics of changes in data over a certain period of time;

schedule reflects trends in data changes over certain periods of time;

pie charts designed to visually display the relationship between parts and the whole

scatter plot displays the relationship between numerical values ​​of several data series and represents two groups of numbers as a single series of dots, often used to represent scientific data;

area chart highlights the amount of change in data over time by showing the sum of entered values, and also shows the contribution of individual values ​​to total amount;

donut chart shows the contribution of each element to the total, but, unlike a pie chart, can contain several data series (each ring is a separate series);

radar chart allows you to compare common values ​​from several data series;

surface diagram used to find the best combination of two data sets;

bubble chart represents a type of scatter plot where two values ​​determine the position of the bubble, and the third determines its size;

stock chart often used to display stock prices, exchange rates, temperature changes, and scientific data

In addition, you can build diagrams of the so-called non-standard types that allow you to combine them in one diagram various types data presentation.

When working with a non-standard chart type, you can quickly view the chart. Each custom chart type builds on a standard type and contains additional formatting and options such as legend, grid, data labels, minor axis, colors, patterns, fills, and placement of various chart elements.

You can use one of the built-in custom chart types or create your own. Non-standard diagram types are found in books.

Building charts using the Chart Wizard

To create a chart on a worksheet, you need to select the data that will be used in it and call Chart Wizard. Either one row of data (or a separate row in a table, or a separate column) or several can be selected.

Note: data not included in the rectangular block is selected when the Ctrl key is pressed.

To call Chart Wizards used:

menu item Insert|Diagram;

button Chart Wizard on standard panel tools

The Chart Wizard involves several steps that the user must take.

The standard view of the Diagram Wizard dialog box is shown in Fig. 1.

Fig.1. Chart Wizard

The created diagrams can be edited. Excel allows you to:

Resize chart

Move charts on a worksheet

Change the chart type and subtype, adjust the color of series or data points, their location, customize the image of series, add data labels