How to make a chart in Excel. Charts in Excel

Hi all! Today I will tell you how to build a chart in Excel using table data. Yes, Excel is back on our agenda, and this is not surprising, since the program is very popular. It successfully handles all the necessary tasks related to tables.

We will look at instructions that will help you answer the question: how to build a chart in Excel using table data. We need:

1. Data preparation.

To build a diagram, you need to prepare the data and arrange it in the form of a table. Rows and columns must be signed. It will look like this:

2. Data extraction.

So that the program understands what it has to work with, the data is highlighted, including the names of the columns and rows.

3.Create a diagram.

After filling out the table, select it. Go to the “Diagrams” section. See the set of diagrams?


Using these buttons you can make any kind of chart. There is also a separate button to create a histogram.

The result is the appearance of a diagram.


The diagram can be located in any part of the sheet.

4.Chart setup.

The appearance of the diagram can also be adjusted through the “Design” or “Format” tab. Here you will find many tools with which you can change it. If the adjustment includes the data involved in creating the chart, select it and, through the “Designer” section, click “Select data”.


Next you will see the “Select Data Source” window. Once you have selected the area, click OK.


Changing the source data also changed the diagram itself.

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. 3D histograms display categories of data along a 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 diagrams, you will be asked to select one of the subtypes of graphs 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.
  

Presenting data in visual chart form allows you to quickly evaluate and analyze it. How to build a chart in Excel? The step-by-step sequence of actions will be shown based on the table below showing the monthly income and taxes of the imputed individual, and their ratio in percentage.

Creating Charts

1. First of all, you need to select a section of the table based on the data of which you want to build a chart in Excel. In the example given, all data is highlighted - income, taxes and interest.

2. Go to the “Insert” tab, and in the “Diagrams” section, click the desired view.

3. As you can see, in the “Diagrams” section the user is offered a choice of different types diagrams. The icon next to the name visually explains how the selected chart type will be displayed. If you click on any of them, the user is offered subspecies in the drop-down list.

Sometimes the expression “Charts and graphs” is used, thereby highlighting the graphical view as a separate category.

If the user needs the first of the proposed options - a histogram, then, instead of performing paragraphs. 2 and 3, he can press the key combination Alt+F1.

4. If you look closely at the subspecies, you will notice that everyone belongs to one of two options. They are distinguished by solid (green rectangle) or partial (orange) shading of the diagram elements. The next two screenshots, corresponding to the “green” and “orange” selections, clearly demonstrate the difference.

As you can see, in the first case, the displayed data is arranged in three columns (income, taxes, percentage). The second option displays them as shaded parts of one column.

In both cases, the percentage value is almost invisible. This is due to the fact that the diagrams display its absolute value (i.e. not 14.3%, but 0.143). Against the background of large values, such a small number is barely visible.

To make a chart in Excel for data of one type, you should select them as part of the first step. The next screenshot shows a diagram for percentage values ​​that were practically not visible in the previous ones.

Editing charts

Once you've finished creating your diagrams, you can change them at any time. Simultaneously with the diagram that appears, a group of tabs with the general name “Working with Diagrams” automatically appears, and a transition to the first of them, “Designer,” occurs. New tab tools provide extensive chart editing capabilities.

Design Tab

A pie chart in Excel is often used to display percentage values. To build pie chart, having saved the previous data, you need to click the first ruler tool on the left - “Change chart type”, and select the desired subtype of the “Pie” line.

The following screenshot shows the result of activating the “Row/Column” tool, which mutually replaces data on the X and Y axes. As you can see, the monochromatic histogram of the previous screenshot received colors and became much more attractive.

In the Chart Styles section of the Design tab, you can change the chart style. After opening the drop-down list in this section, the user can choose one of the 40 proposed variations of styles. Without opening this list, only 4 styles are available.

Very valuable last tool– “Move diagram.” With its help, the diagram can be transferred to a separate full-screen sheet.

As you can see, the sheet with the diagram is added to the existing sheets.

If the user has to work on building many other diagrams based on the created and edited one, then he can save it for later use as a template. To do this, just select the diagram, click the “Save as Template” tool, enter a name and click “Save”. After this, the saved template will be available in the “Templates” folder.

Layout and Format tabs

The Layout and Format tab tools primarily relate to external design diagrams.

To add a title, click Chart Title, select one of the two proposed layout options, enter a name in the formula bar, and press Enter.

If necessary, names are added similarly to the X and Y axes of the chart.

The Legend tool controls the display and position of descriptive text. IN in this case these are the names of the months. They can be deleted or moved left, up or down.

Much more common is the “Data Labels” tool, which allows you to add numeric values ​​to them.

If the volumetric version was selected when constructing the diagram, then the “Rotate 3D” tool will be active on the “Layout” tab. With its help you can change the viewing angle of the diagram.

The Shape Fill tool of the Format tab allows you to fill the background of the chart (as shown in the screenshot) or any of its elements (in this case, the bars) with any color, pattern, gradient or texture.

To fill the corresponding element, it must first be selected.

Adding new data

After you create a chart for one data series, in some cases you may need to add new data to the chart. To do this, you will first need to select a new column – in this case “Taxes”, and remember it in the clipboard by pressing Ctrl+C. Then click on the diagram and add the saved new data to it by pressing Ctrl+V. The diagram will show new row"Taxes" data.

What's New for Charts in Excel 2013

The diagrams were considered using the example of a widespread Excel versions 2010. You can also work in Excel 2007. And here the 2013 version has a number of pleasant innovations that make working with diagrams easier:

• in the diagram view insert window it is entered preview in addition to the small icon;
• appeared in the view insertion window new type– “Combined”, combining several types;
• in the view insertion window, a “Recommended charts” page appeared, which the 2013 version recommends after analyzing the selected source data;
• instead of the “Layout” tab, three new buttons are used - “Chart Elements”, “Chart Styles” and “Chart Filters”, the purpose of which is clear from the names;
• the design of the diagram elements is customized using a convenient right panel instead of a dialog box;
• it became possible to format data signatures in the form of callouts and take them directly from the sheet;
• When the source data changes, the diagram smoothly flows into a new state.

Video: Creating charts in MS Office Excel

Information presented in the form of a table is perceived by a person faster than text, and if the same values ​​are shown on a diagram, then they can be easily compared and analyzed.

In this article we will look at how to make a chart in Excel from a table.

Let's take the following range as an example. It displays the number of goods sold by a certain employee for a certain month. Select all the values ​​with the mouse, along with the names of the rows and columns.

Choosing the right type

Go to the “Insert” tab and in the “Charts” group select desired type. For this example Let's build a histogram. Select one of the proposed histograms from the list and click on it.

Excel will automatically produce the result. The axes are labeled on the left and bottom, and the legend is on the right.

How to work with her

appeared on the tape new section "Working with diagrams" with three tabs.

On the Design tab you can "Change chart type", change row and column, choose one of the layouts or styles.

On the Layout tab you can give it common name or for axes only, display legend, grid, and enable data labels.

On the “Format” tab, you can select the fill, outline and shape effect, and style for the text.

Adding new data

Now let's look at how to add new values ​​to it.

If the table is created manually

For example, we added sales information for “June” to our original range. Select the entire column, right-click on it and select from context menu“Copy”, or press “Ctrl+C”.

Select the diagram and press “Ctrl+V”. A new field will be automatically added to the Legend and data to the histogram.

You can add them in another way. Right-click on the diagram and select from the menu "Select data".

In the “Row Name” field, select the month, in the “Values” field, select the column with sales information. Click “OK” in this window and the next one. The schedule will be updated.

If you used a smart table

If you often need to add information to the original range, then it is better to create “ smart table» in Excel. To do this, select everything together with the headings, on the “Home” tab, in the “Styles” group, select "Format as table". You can choose any style from the list.

Put a tick in the box "Table with headers" and click "OK".

It looks like this. You can expand it by pulling the lower right corner. If you pull to the side, a new month will be added; if you pull to the bottom, you can add a new employee. Let's add a new month and fill in the sales information.

New rectangles are added to the histogram as the cells are filled. Thus, from an ordinary one we have a dynamic table in Excel - when it changes, the diagram is automatically updated.

In the example, the “Histogram” was considered. Using the same principle, you can build any other diagram.

To build a circle, select the appropriate item in the “Diagrams” group. From the data table, select only employees and sales for January.

A bar chart is constructed in exactly the same way as a histogram.

Program Microsoft Excel offers the ability not only to work with numerical data, but also provides tools for constructing diagrams based on the input parameters. At the same time, their visual display can be completely different. Let's see how to draw different types of charts using Microsoft Excel.

Construction various types diagrams are practically no different. Only at a certain stage you need to select the appropriate type of visualization.

Before you start creating any chart, you need to build a table with the data on which it will be based. Then, go to the “Insert” tab and select the area of ​​this table that will be expressed in the diagram.

On the ribbon, in the “Insert” tab, select one of six types of main diagrams:

• Histogram;
• Schedule;
• Circular;
• Ruled;
• With areas;
• Spot.

In addition, by clicking on the “Other” button, you can select less common types of charts: stock, surface, donut, bubble, radar.

After that, by clicking on any of the diagram types, you are asked to select a specific subtype. For example, for a histogram, or bar chart, such subtypes will be the following elements: regular histogram, volumetric, cylindrical, conical, pyramidal.

After selecting a specific subtype, a diagram is automatically generated. For example, a regular histogram will look like the image below.

The graph diagram will look like this.

The area chart will look like this.

Working with charts

After the diagram is created, additional tools for editing and changing it become available in the new “Working with Diagrams” tab. You can change the chart type, its style, and many other parameters.

The Chart Tools tab has three additional subtabs: Design, Layout, and Format.

In order to name the diagram, go to the “Layout” tab and select one of the options for positioning the name: in the center or above the diagram.

After this is done, the standard “Chart Title” appears. We change it to any inscription that fits the context of this table.

The names of the chart axes are signed according to exactly the same principle, but to do this you need to click the “Axes Titles” button.

Display the chart as a percentage

In order to display the percentage of various indicators, it is best to build a pie chart.

In the same way as we did above, we build a table and then select the desired section of it. Next, go to the “Insert” tab, select a pie chart on the ribbon, and then, in the list that appears, click on any type of pie chart.

The pie chart displaying data in percentages is ready.

Constructing a Pareto chart

According to Vilfredo Pareto's theory, the 20% most effective actions bring 80% of the total result. Accordingly, the remaining 80% of the total set of actions that are ineffective bring only 20% of the results. The construction of a Pareto chart is precisely intended to calculate the most effective actions that give maximum return. Let's do this using Microsoft Excel.

It is most convenient to construct a Pareto chart in the form of a histogram, which we have already discussed above.

Construction example. The table shows a list of food products. One column contains the purchase price of the entire volume of a particular type of product in a wholesale warehouse, and the second column contains the profit from its sale. We have to determine which products give the greatest “return” when sold.

First of all, we build a regular histogram. Go to the “Insert” tab, select the entire value range of the table, click the “Histogram” button, and select the desired type of histogram.

As you can see, as a result of these actions, a diagram was formed with two types of columns: blue and red.

Now, we need to convert the red bars into a graph. To do this, select these columns with the cursor, and in the “Design” tab, click on the “Change chart type” button.

The window for changing the chart type opens. Go to the “Graph” section and select the type of graph suitable for our purposes.

So, the Pareto diagram has been built. Now, you can edit its elements (name of the chart and axes, styles, etc.), just as it was described in the example of a bar chart.

As we see, Microsoft program Excel provides a wide range of tools for building and editing various types diagrams. In general, working with these tools is simplified as much as possible by the developers, so that users with different levels preparations could cope with them.