Mixed chart in excel. Combining different types of charts in EXCEL

Building Combo Charts

In cases where you need to display data of different scales on one chart, it is useful to use combination charts. A typical case is absolute (rubles) and relative (percentage) indicators. For example, the size of accounts receivable and its share of sales.

Combined (or mixed ) is a chart that consists of multiple data series and uses different types of charts at the same time (for example, a histogram and a graph). To create combination charts, you need at least two data series. Examples of mixed diagrams are posted in the book Combined

There are some restrictions on building combination charts:

· it is impossible to mix any chart types with volumetric types;

· some combinations of chart types look extremely unsatisfactory (for example, a combination of a graph and a radar chart);

· a mixed chart uses a single plotting area, therefore it is impossible to create, for example, a combined chart consisting of three pie charts;

· Combining a graph and a bar chart is not supported: the category axis of a bar chart is always directed vertically, and the axis of a graph is always directed horizontally.

The figure shows a chart built using three data series. Moreover, the air and water temperatures are presented in the form of a histogram, and the amount of precipitation is presented in a graph.

The figure shows a histogram built using two data series:

Let's change the chart type for the second data series ( Precipitation) and use a separate value axis for this series.

1. Select a series of data on the diagram Precipitation and call the context menu of the row.

2. Open the window Data series format (Format Data Series) and on the tab Options row (Series Options) install the switch By auxiliary axes(Secondary Axis).

3. Without removing the selection from the row, execute the command Working with charts– Constructor – Type – Change type diagrams(Chart Tools – Design – Type – Change Chart Type) .

4. In the Change chart type dialog box, select the type Schedule (Line) and click on the button OK.

Precipitation data is now visualized as straight line segments, with a new one on the right Value axis (ValueAxis).

It is important to understand that the team Working with charts– Constructor – Type – Change type diagrams (Chart Tools – Design – Type – Change Chart Type) works differently depending on what is selected. If a chart series is selected, the command changes the type of only that series. If any other diagram element is selected, the command changes the type of the entire diagram.

Overlay diagrams (OverlayCharts)

Chart overlay refers to placing charts as graphic objects: one on top of the other. A top chart typically has a transparent chart area and a plotting area. It removes all elements except, for example, markers and lines. Chart overlay is a manual task that requires precise positioning of objects, setting up value and category axes.

In order to arrange the diagrams “stacked” in the appropriate order, they need to be selected as objects. To select an object rather than a chart, press Ctrl and click on the diagram.

Let's look at a simple example (sheet Overlay_1 books Combined). It is known that one of the limitations on building combination charts is the inability to mix any chart types with volumetric types. The chart overlay method allows you to combine a graph and a volumetric histogram on a sheet. Let's take the following steps:

1. Select a range in the data table A2:B7 and build a volumetric histogram with grouping. Let's remove the chart legend.

2. Select non-adjacent ranges A2:A7 And C2:C7 and build a graph with markers. Let's remove the chart legend.

3. On the second chart, set a transparent fill for the chart area and the chart plotting area, and delete the axes. Let's format the graph lines and markers.

4. Manually combine the graph and volumetric histogram. By changing the size of the “Graph” object, we will ensure that the graph markers coincide with the middle of the histogram columns.

5. After the final formatting we will get an overlaid diagram.

6. If, after selecting two diagrams as graphic objects, you group them, the resulting superimposed diagram will be copied and moved as one object. Note that attempting to place the chart on a separate sheet will destroy the overlay.

The following illustration shows an overlay of a 3-D pie chart and a 3-D histogram. The overall frame gives the impression that it is one diagram. However, these are two separate diagrams. The frame does not belong to them, but to the worksheet cells. The title is a freely movable inscription. Data for such an overlay are given on the sheet Circular _volumetric books Combined.

Volumetric Excel histograms do not allow you to display additional rows in depth. Overlaying charts overcomes this limitation. It is enough to build three volumetric histograms with accumulation (for each year separately) and combine them so as to “create” a third axis in depth. The chart allows you to visually compare results by three parameters - regions, months and years. The original data table is in the worksheet Three_volume books Combined.

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”.

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, appearance 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.

In Excel, you can display the results of calculations in the form of a chart or graph, giving them greater clarity, and for comparison, sometimes you need to build two graphs side by side. We will look further at how to construct two graphs in Excel on one field.

Let's start with the fact that not every type of chart in Excel can display exactly the result that we expect. For example, there are calculation results for several functions based on the same initial data. If you build a regular histogram or graph from this data, then the original data will not be taken into account during the construction, but only their quantity, between which equal intervals will be specified.

We select two columns of calculation results and build a regular histogram.

Now let's try to add another histogram to the existing ones with the same number of calculation results. To add a graph in Excel, make the existing graph active by selecting it, and in the tab that appears "Constructor" choose "Select data". In the window that appears in the section "Elements of Legend" click add and specify cells "Row name:" And "Values:" on the sheet, which will be the function calculation values "j".

Now let's see what our chart will look like if we add another one to the existing histograms, which has almost twice as many values. Let's add the function values ​​to the graph "k".

As you can see, there are much more recently added values, and they are so small that they are practically invisible on the histogram.

If we change the chart type from a histogram to a regular graph, the result in our case will be more clear.

If you use a scatter plot to build graphs in Excel, then the resulting graphs will take into account not only the calculation results, but also the source data, i.e. there will be a clear relationship between the quantities.

To create a point graph, select a column of initial values, and a pair of columns of results for two different functions. On the tab "Insert" select a scatter plot with smooth curves.

To add another graph, select the existing ones, and on the tab "Constructor" press "Select data".

In a new window in the column "Elements of Legend" press "Add", and indicate the cells for "Row name:", "X Values:" And "Y Values:". Let's add the function this way "j" to the chart.

Typically, charts use one style, such as a column, bar, or pie chart. If your chart has more than one data series, you can display different styles in your chart, creating a mixed chart.

In Fig. Figure 136.1 shows a diagram that is well suited for conversion to mixed. The graph illustrates the average temperature and precipitation by month. But there is a problem. The two data ranges clearly differ in magnitude, so that the precipitation bars are barely visible in the chart.

In Fig. Figure 136.2 is the diagram after I converted it to a mixed diagram. Precipitation values ​​are now plotted as a line and the second vertical axis from the right is used. Most of you will agree that this chart is a significant improvement over the original. You can use it when analyzing the cost of goods in a jewelry salon in Vologda.

You can search for the Mixed Charts command on the Excel ribbon all day and still won't find it. If you've already created your chart (which has at least two data series), you can easily convert it to a mixed chart. Follow these steps:

1. Click on the rows you want to change.
2. Select Working with charts Designer Type Change chart type.
3. In the Change Chart Type dialog box, select the icon that represents the chart type for the selected series.
4. If you would like to use a second vertical axis for the converted series, select the series and click Ctrl+1 to open a dialog box Data series format; go to section Series parameters and set the switch Build a row to position Along minor axis.

Diagram in Fig. 136.1 was a standard bar chart with two rows. A few clicks and one of the series (Precipitation in this example) converted from columns to a line. I also added a second axis and labeled the axes so it was clear which scale was being used for which series. To add axis titles, run the command Working with Charts Layout Axis Titles.

In Fig. Figure 136.3 shows another mixed diagram. It combines a column and bar chart, and both series use the same vertical axis. In this case, displaying monthly sales targets as a bar chart makes it easier to find the months in which the target was met.

In Fig. Figure 136.4 demonstrates how far you can go with mixed diagrams. This chart combines five different chart types: pie, area, bar (bar), bar, and scatter (XY). I can't imagine in what situation such a diagram would be appropriate, but it's a pretty interesting demo.

In some cases, you cannot mix chart types. For example, you cannot create a mixed chart that includes a bubble and a surface (3D) chart. If you select an incompatible chart type for a series, Excel will notify you.