How to build a mixed graph in excel. Creating charts with multiple data series in EXCEL

A histogram in Excel is a way to create a visual chart that shows changes in several types of data over a period of time.

A histogram is useful for illustrating and comparing different parameters. Let's look at the most popular types of histograms and learn how to build them.

How to build an updated histogram?

We have data on sales of different types of dairy products for each month for 2015.

Let's build an updated histogram that will respond to changes made to the table. Select the entire array including the header and click on the INSERT tab. Let's find DIAGRAMS - HISTOGRAM and select the very first type. It's called a HISTOGRAM WITH GROUPING.

We received a histogram whose field size can be changed. This diagram clearly shows, for example, that the largest sales were for milk in November, and the smallest for cream in June.

If we make changes to the table, the appearance of the histogram will also change. For example, instead of 1400 in January for kefir, let’s put 4000. We see how the green column flew up.

Stacked histogram

Now let's look at how to build a stacked histogram in Excel. Another type of histogram that allows you to display data as a percentage. It is built in exactly the same way, but a different type is chosen.

We get a diagram where you can see that, for example, in January more milk was sold than kefir or cream. And in August, compared to other dairy products, little milk was sold. And so on.

Histograms in Excel can be modified. So, if we right-click in an empty area of ​​the diagram and select CHANGE TYPE, we can modify it somewhat. Let's change the type of our histogram from stacked to normalized. The result will be the same chart, but the Y-axis will show the ratios in percentage terms.

Similarly, you can make other changes to the histogram, which is what we did:

• changed the font to Arial and changed its color to purple;
• made an underline with a dotted line;
• moved the legend a little higher;
• added captions to columns.

How to combine a histogram and a graph in Excel?

Some data sets require the construction of more complex diagrams that combine several types of data. For example, a histogram and a graph.

Let's look at an example. First, let’s add another row to the table with the data, where the revenue for each month is recorded. It is indicated in rubles.

Now let's modify the existing diagram. Right-click in an empty space and select SELECT DATA. A field will appear asking you to select a different interval. We select the entire table again, but this time including the row with revenue.

Excel automatically expanded the range of values ​​along the Y axis, so the data on the number of sales remained at the very bottom in the form of invisible columns.

But such a histogram is incorrect, because on one diagram we have numbers in ruble and quantitative equivalents (rubles and liters). Therefore changes need to be made. Let's move the revenue data to the right side. Right-click on the purple columns, select DATA SERIES FORMAT and select AUXILIARY AXIS.

We see that the schedule has immediately changed. Now the purple revenue bar has its own value area (on the right).

But this is still not very convenient, because the columns almost merge. Therefore, we will perform one more additional action: right-click on the purple columns and select CHANGE CHART TYPE FOR SERIES. A window will appear in which we select a chart, the very first type.

We get a very clear diagram, which is a combination of a histogram and a graph. We see that the maximum revenue was in January and November, and the minimum in August.

Today's article will be very short. I’ll tell you how to simultaneously display two different types of graphical information on one area of ​​charts, for example, a bar chart and a graph. A simple table with numbers is not very visual, and graphs are often more useful for analysis (at least for me).

Here is my example from work today. Naturally, I came up with the data: it’s not for nothing that when applying for employment they require a signature on non-disclosure of information.

So, there is a rolling protocol, where we have data on the reduction, the average rolling force in each pass and the temperature of the metal at the exit of the rolls after each pass. It would be convenient to see the values ​​of at least two parameters on one graph: in this way we see the combination of these parameters in each pass and at the same time the general picture of the change in each of the parameters along the passes (it is immediately clear that as the temperature of the metal decreases, its resistance to deformation increases).

The question may be understandable: “why not just build two graphs, why two different types of diagrams?” Answer: because the units of measurement and, accordingly, the order of numbers themselves can be different. For example, the temperature is in the range of 700-1000C, and the rolling forces are in the range of 10-70 MN. Let us plot them on one graph, and the rolling force graph will lie significantly below the temperature graph. It is necessary that each parameter has its own vertical axis. This is why we need different types of graphs.

So, let's go.

1. First, we build a bar chart that will contain information about the distribution of compression across passes. Path: “insert/histogram/histogram” (see figure).

2. Select the data: on the horizontal axis – the numbers of passes (column B), on the vertical axis – the rolling force (column C).

3. Now let’s add a second row of data – the temperature of the metal at the exit of the rolls (column D). So far we have received a not very digestible picture, because the order of the numbers is different.

4. Now the most important step: you need to change the type of temperature “diagram”. Right-click on any column of the temperature chart, select the “Change chart type for series” menu and select the “scatter chart” type.

5. This is what we got:

We see that now we have two vertical and two horizontal axes, each for its own graph.

6. All that remains is to work with the maximum and minimum values ​​on the axes, otherwise for now the diagram and graph live on their own. The minimum and maximum values ​​on the top axis should be the same as those on the bottom. Right-click on the axis, select the “Format Axis” menu item and set the required values. You can also change the minimum value on the vertical axis (temperature axis): we can set “minimum 700”.

7. You can also plot the values ​​on graphs. Again, right-click on the desired graph and select “add data labels.”


This is what we end up with:

That's all! The schedules are ready.

If you need to display different data series (by value, by type) on one chart, then an auxiliary axis is added. Its scale corresponds to the values ​​of the associated series. Excel allows you to add a value (vertical) and category (horizontal) axis. The latter option is used when constructing scatter and bubble charts.

To visualize different types of data in one plot area, different types of charts are used. Then the values ​​added to the auxiliary axis are immediately visible. Let's look at how to make a chart with two axes in Excel.

Chart with two vertical axes

To build a chart with two vertical axes, for example, let’s build a table like this:

Columns B and C have rows of different types: rubles (numerical values) and percentages. Based on the table, we will build a simple graph with markers:

To add an auxiliary vertical axis to a given plot area, you need to select flat chart types. You cannot add a secondary axis to a 3-D chart.

How to make two axes in a chart:

After clicking OK, the diagrams look like this:

The top points of the histogram bars and the graph markers coincided, because the share of sales for each month is the percentage expression of the volume in rubles for each month. The same meanings, just with different expressions.

Now in the chart editing options it is possible to work with the auxiliary axis:

To delete an auxiliary vertical axis, you can select it, right-click and click “Delete”.

Another way. Go to the "Layout" tab. To change the formatting and layout of each axis, select the Axes tool. Next - “Auxiliary vertical” - “Do not show”.

Secondary horizontal axis

To add a second horizontal (category) axis, a secondary axis must already be displayed in the plotting area.

Adding order:

In the figure it will look like this:

You can delete an additional horizontal value line using the same methods as an auxiliary vertical value line.

Only one row of data can be shown on any secondary axis. If it is necessary to display several rows, then for each row the adding procedure is repeated from the beginning.

For further use, the constructed diagram with two axes can be saved as a template. To do this, click on the construction area. On the “Design” tab, click the “Save as Template” button.

Standard Excel tools for pie charts allow you to use only one set of data. This post will show you how to create a pie chart based on two sets of values.

As an example, I took the Earth's population by continent in 1950 and 2000. (see the “Population” sheet in the Excel file; I removed Australia because its share is negligible, and the diagram becomes difficult to read :)). First, create a basic pie chart: select the range A1:C6, go to Insert → Pie → Pie.

Rice. 1. Create a regular pie chart

Download the note in format, examples in format

Right-click the chart and select Format Data Series from the context menu. Select "Along Minor Axis" and then move the slider towards "Separation" to something like 70% (Figure 2). Sectors of the same row will “spread apart”.

Rice. 2. Along the minor axis

Select individual sectors sequentially (by double-clicking slowly) and change their fill and location, connecting all sectors in the center (Fig. 3).

Rice. 3. Formatting row points (individual sectors)

Format all sectors so that the colors corresponding to the same continent in different rows are of the same range, but of different intensities. Complete the chart with data labels, a legend, and a title (Figure 4).

Rice. 4. Pie chart with two data sets

The diagram clearly shows, for example, that the share of Asia has grown from 55.8% to 60.9 over 50 years, while the share of Europe has decreased from 21.8% to 12.1% over the same time.

If you are not a fan of pie charts, you can use the donut chart, which in the Excel standard works with multiple data sets (Fig. 5); see also the “Ring” sheet in the Excel file. Select the data area (in our example it is A1:C6) and go to the menu Insert – Charts – Other charts – Donut:

Rice. 5. Create a donut chart

All you have to do is edit the diagram a little to make it more visual (Fig. 6)

Rice. 6. Donut chart

The idea was seen in the book by D. Kholey, R. Kholey “Excel 2007. Tricks”.

To make reporting easier to read, especially its analysis, it is better to visualize the data. Agree that it is easier to assess the dynamics of a process using a graph than looking at numbers in a table.

This article will talk about the use of charts in Excel, and discuss some of their features and situations for their best use.

Insert and build

For example, we use a table of revenue and expenses for the year, based on which we will build a simple graph:

Regardless of the type used, be it a histogram, surface, etc., the fundamental principle of creation does not change. On the “Insert” tab in Excel, you need to select the “Charts” section and click on the required icon.

Select the empty area you created to make additional ribbon tabs appear. One of them is called “Designer” and contains the “Data” area, on which the “Select data” item is located. Clicking on it will bring up the source selection window:

Pay attention to the very first field “Data range for chart:”. With its help, you can quickly create a graph, but the application cannot always understand exactly how the user wants to see it. Therefore, let's look at a simple way to add rows and axes.

On the window mentioned above, click the “Add” button in the “Legend Elements” field. The “Change Series” form will appear, where you need to specify a link to the series name (optional) and values. You can specify all indicators manually.

After entering the required information and clicking the “OK” button, the new series will appear on the chart. In the same way, we will add another legend element from our table.

Now let's replace the automatically added labels along the horizontal axis. In the data selection window there is a category area, and in it there is a “Change” button. Click on it and add a link to the range of these signatures in the form:

Look what should happen:

Chart elements

By default, the chart consists of the following elements:

• Data series are of main value because... visualize data;
• Legend – contains the names of the rows and an example of their design;
• Axes – a scale with a certain value of intermediate divisions;
• The plot area is the background for the data series;
• Grid lines.

In addition to the objects mentioned above, the following can be added:

• Chart titles;
• Projection lines – descending from data series to the horizontal axis of the line;
• Trend line;
• Data labels – the numeric value for a data point in a series;
• And other infrequently used elements.

Changing the style

You can use the default styles provided to change the appearance of your chart. To do this, select it and select the “Design” tab that appears, on which the “Chart Styles” area is located.

Often the available templates are sufficient, but if you want more, you will have to create your own style. This can be done by right-clicking on the chart object being changed, selecting “Element_Name format” from the menu and changing its parameters through the dialog box.

Please note that changing the style does not change the structure itself, i.e. the diagram elements remain the same.

The application allows you to quickly rebuild the structure through express layouts, which are located in the same tab.

As with styles, each element can be added or removed individually. In the Excel 2007 version, an additional “Layout” tab is provided for this, and in the Excel 2013 version, this functionality has been moved to the “Design” tab ribbon, in the “Chart Layouts” area.

Chart types

Schedule

Ideal for displaying changes in an object over time and identifying trends.
An example of displaying the dynamics of costs and total revenue of a company for the year:

bar chart

Good for comparing multiple objects and changing their relationship over time.
An example of comparing the performance indicator of two departments quarterly:

Circular

Used to compare the proportions of objects. Cannot display dynamics.
An example of the share of sales of each product category from total sales:

Area chart

Suitable for displaying the dynamics of differences between objects over time. When using this type, it is important to maintain the order of the rows, because they overlap each other.

Let's say there is a need to display the workload of the sales department and its coverage by personnel. For this purpose, indicators of employee potential and workload were brought to a common scale.

Since it is paramount for us to see the potential, this row is displayed first. From the diagram below it is clear that from 11 a.m. to 4 p.m. the department cannot cope with the flow of clients.

Spot

It is a coordinate system where the position of each point is specified by values ​​along the horizontal (X) and vertical (Y) axes. A good fit when the value (Y) of an object depends on a certain parameter (X).

Example of displaying trigonometric functions:

Surface

This type of chart represents three-dimensional data. It could be replaced with several rows of a histogram or graph, if not for one feature - it is not suitable for comparing the values ​​of rows, it provides the opportunity to compare values ​​​​in a certain state with each other. The entire range of values ​​is divided into subranges, each of which has its own shade.

Exchange

From the name it is clear that this type of chart is ideal for displaying trading dynamics on exchanges, but can also be used for other purposes.

Typically, such charts display the fluctuation corridor (maximum and minimum values) and the final value in a certain period.

Petal

The peculiarity of this type of chart is that the horizontal axis of values ​​is located in a circle. Thus, it allows you to more clearly display the differences between objects in several categories.

The diagram below shows a comparison of 3 organizations in 4 areas: Accessibility; Price policy; Product quality; Customer focus. It can be seen that company X is the leader in the first and last areas, company Y in product quality, and company Z provides the best prices.

We can also say that company X is a leader, because The area of ​​her figure in the diagram is the largest.

Mixed chart type

Excel allows you to combine several types in one chart. As an example, the graph and histogram types are compatible.

To begin with, all rows are built using one type, then it changes for each row separately. By right-clicking on the required series, select “Change chart type for series...” from the list, then “Histogram”.

Sometimes, due to strong differences in the values ​​of the chart series, using a single scale is impossible. But you can add an alternative scale. Go to the “Data Series Format...” menu and in the “Series Options” section, move the checkbox to “Along Minor Axis.”

The diagram now looks like this:

Excel Trend

Each row of the chart can have its own trend. They are necessary to determine the main focus (trend). But for each individual case it is necessary to apply your own model.

Select the data series for which you want to build a trend and right-click on it. In the menu that appears, select “Add trend line...”.

Various mathematical methods are used to determine a suitable model. We will briefly look at situations when it is better to use a certain type of trend:

• Exponential trend. If the values ​​on the vertical axis (Y) increase with each change in the horizontal axis (X).
• A linear trend is used if the Y values ​​have approximately the same change for each X value.
• Logarithmic. If the change in the Y axis slows down with each change in the X axis.
• A polynomial trend is used if changes in Y occur both upward and downward. Those. the data describes the cycle. Well suited for analyzing large data sets. The degree of trend is selected depending on the number of cycle peaks:
  • Degree 2 – one peak, i.e. half cycle;
  • Degree 3 – one full cycle;
  • Level 4 – one and a half cycles;
  • etc.
• Power trend. If the change in Y increases at approximately the same rate for each change in X.

Linear filtering. Not applicable for forecasting. Used to smooth out changes in Y. Averages the change between points. If in the trend settings the point parameter is set to 2, then averaging is performed between adjacent values ​​of the X axis, if 3 then after one, 4 after two, etc.

Pivot chart

It has all the benefits of regular charts and pivot tables, without the need to create one.

The principle of creating pivot charts is not much different from creating pivot tables. Therefore, this process will not be described here, just read the article about pivot tables on our website. Moreover, you can create a diagram from an already constructed table in 3 clicks:

• Select the PivotTable;
• Go to the “Analysis” tab (in Excel 2007, the “Options” tab);
• In the Tools group, click the PivotChart icon.

To build a pivot chart from scratch, select the appropriate icon on the “Insert” tab. For the 2013 application it is located in the “Charts” group, for the 2007 application in the table group, the “Pivot Table” drop-down list item.

• < Назад
• Forward >

If the site materials helped you, please support the project so that I can develop it further.