How to create a blend chart in Excel. Mixed charts in Excel How to combine different types of charts

Also articles about charts in Excel:

• How to make a chart in Excel?
• How to make a pie chart in Excel?
• How to create a graph in Excel?
• Building a Scatter Plot in Excel

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 build 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 on the “Designer” tab that appears, select “Select data.” In the window that appears, in the “Legend Elements” section, click add, and indicate the “Series name:” and “Values:” cells on the sheet, which will be the calculation values ​​of the “j” function.

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 values ​​of the function “k” to the graph.

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 dotted plot, let's select a column of initial values, and a couple of columns of results for two different functions. On the “Insert” tab, select a scatter plot with smooth curves.

To add another graph, select the existing ones, and on the “Design” tab, click “Select data”.

In the new window, in the “Legend Elements” column, click “Add”, and specify the cells for “Series Name:”, “X Values:” and “Y Values:”. Let us thus add the function “j” to the graph.

Now let’s add function “k”, which has completely different initial data and their quantity.

As you can see, on the scatter plot the function “k” is almost invisible, but it is built taking into account the values ​​​​on both axes.

Some time ago, we published the first part of our tutorial on creating charts in Excel for beginners, which gave detailed instructions on how to create a graph in Excel. And the very first question asked in the comments was: “How to show data located on different worksheets in a chart?” I want to thank the reader who asked this great question!

In fact, the source data that needs to be shown in the diagram is not always located on the same worksheet. Fortunately, Microsoft Excel allows you to display data located on two or more sheets on one graph. Next we will do this step by step.

• Creating a chart from several Excel sheets
• Customizing a chart created from several worksheets

How to create a chart from multiple Excel sheets

Suppose you have several Excel sheets containing several years of income data, and you want to create a chart using this data to show the overall trend.

1. Create a chart using the data from the first sheet

Open the first Excel worksheet, select the data you want to display in the chart, open the tab Insert(Insert) and in the section Diagrams(Charts) select the desired chart type. For our example we will choose Stacked volume histogram(Stack Column).

2. Add a second row of data from another sheet

Click on the diagram you just created so that a group of tabs appears on the Menu Ribbon Working with charts(Chart Tools), open the tab Constructor(Design) and press the button Select data(Select Data). Or click on the icon Chart filters(Chart Filters) to the right of the chart and at the very bottom of the menu that appears, click the link Select data(Select Data).

In the dialog box Selecting a data source(Select Data Source) click the button Add(Add).

Now let's add a second row of data from another worksheet. This point is very important, so follow the instructions carefully. After pressing the button Add(Add) dialog box will open Row change(Edit Series), here you need to click the range selection icon next to the field Values(Series values).

Dialog box Row change(Edit Series) will collapse. Click on the tab of the sheet containing the next piece of data that you want to show in the Excel chart. When switching to another sheet, the dialog box Row change(Edit Series) will remain on the screen.

On the second sheet, select the column or row of data that you want to add to the Excel chart and click on the range selection icon again to open the dialog box Row change(Edit Series) returned to original size.

Now click on the range selection icon next to the field Series name(Series name) and select the cell containing the text you want to use as the data series name. Click on the range selection icon again to return to the original dialog box Row change(Edit Series).

As you can see in the image above, we have associated the row name with the cell B1, which contains the column header. Instead of referencing the column header, you can enter the name as a text string surrounded by quotation marks, for example:

="Second row of data"

Data series names will appear in the chart legend, so it's best to come up with meaningful and meaningful names. At this stage the result should be something like this:

3. Add even more data series (optional)

If the chart needs to show data from more than two worksheets, then repeat the previous step for each series of data that you want to add to the chart. When you're done, click OK in the dialog box Selecting a data source(Select Data Source).

4. Customize and improve the chart (optional)

When you create charts in Excel 2013 and 2016, items such as the chart title and legend are typically automatically added. Our chart, which was compiled from the contents of several sheets, did not automatically add a title and legend, but we will quickly fix this.

Select the chart, click the icon Chart elements(Chart Elements) in the form of a green cross near the upper right corner of the chart, and check the required parameters:

How to customize other chart options, such as the display of data labels or the format of the axes, is covered in detail in the separate article on customizing Excel charts.

Creating a chart from a summary table

The solution shown above is only useful if the data you want to display in the chart is arranged in the same order on all worksheets, i.e. in the first line - Oranges, in the second - Apples etc. Otherwise, the graphs will turn into something illegible.

In this example, the data layout is the same on all three sheets. If you need to build a graph from much larger tables, and you are not sure that the data structure in these tables is the same, then it would be wiser to first create a summary table, and then create a chart from the resulting summary table. To fill the final table with the necessary data, you can use the function VLOOKUP(VLOOKUP).

For example, if the worksheets discussed in this example contain data in different orders, then we can make a summary table from them using this formula:

VLOOKUP(A3,"2014"!$A$2:$B$5,2,FALSE)
=VLOOKUP(A3,"2014"!$A$2:$B$5,2,FALSE)

Setting up a chart in Excel created from several worksheets

It may happen that after you have completed creating a diagram from two or more worksheets, it becomes clear that it should be constructed differently. And since creating such a chart in Excel is not as fast a process as creating a chart from a single sheet, it is likely that it will be easier to redo the created chart than to create a new one from scratch.

In general, the options for an Excel chart created from multiple worksheets are no different from the options for a regular Excel chart. You can use a group of tabs Working with charts(Charts Tools), or the context menu, or the settings icons in the upper right corner of the chart to customize basic elements such as the chart title, axis titles, legend, chart style, and more. For step-by-step instructions on setting these options, see the article on setting up charts in Excel.

If you need to change the data series shown in the diagram, you can do this in one of three ways:

• Select Data Source Dialog Box
• Chart Filters Icon
• Data series formulas

Changing a data series using the Select Data Source dialog box

Open dialog box Selecting a data source(Select Data Source), for this on the tab Constructor(Design) click button Select data(Select data).

To change a data series, click on it, then click the button Change(Edit) and edit the parameters Series name(Series Name) or Meaning(Series Values) as we did earlier in this article. To change the order of data series in a chart, select the data series and move it up or down using the appropriate arrows.

To hide a row of data, simply uncheck the box in the list Legend Elements(Legend Entries) on the left side of the dialog box. To remove a data series from the chart completely, select it and click the button Delete(Remove).

Hide or show data series using the “Chart Filters” icon

Another way to control the data series that appears in an Excel chart is to use the icon Chart filters(Chart Filters). If you click on the diagram, this icon will immediately appear on the right.

• To hide data, click on the icon Chart filters(Chart Filters) and uncheck the corresponding data series or category.
• To change a data series, click the button Change row(Edit Series) to the right of the series name. The familiar dialog box will appear Selecting a data source(Select Data Source), in which you can make the necessary settings. To button Change row(Edit Series) has appeared, just hover your mouse over the name of the series. In this case, the data row that the mouse is hovering over is highlighted in color on the diagram to make it easier to understand which element will be changed.

Changing a series of data using a formula

As you probably know, every data series in Excel is defined by a formula. For example, if we select one of the data series in the chart we just created, the formula for the data series will look like this:

ROW("2013"!$B$1;"2013"!$A$2:$A$5;"2013"!$B$2:$B$5;1)
=SERIES("2013"!$B$1,"2013"!$A$2:$A$5,"2013"!$B$2:$B$5,1)

Each data series formula consists of several basic elements:

RANGE(;;data_range;row_number)

That is, our formula can be deciphered as follows:

• The row name ('2013'!$B$1) is taken from the cell B1 on a sheet.
• The category names ('2013'!$A$2:$A$5) are taken from the cells A2:A5 on a sheet.
• Data ('2013'!$B$2:$B$5) taken from cells B2:B5 on a sheet.
• The row number (1) indicates that this row occupies first place on the chart.

To change a specific data series, select it in the chart and make the necessary changes in the formula bar. Of course, you need to be very careful when changing the formula of a data series, since it is easy to make a mistake, especially if during editing the source data is contained on different sheets and not in front of your eyes. However, if you are more comfortable working with formulas than with a regular interface, then this method of making small corrections may well be suitable.

That's all for today! Thank you for your attention!

The lesson was prepared for you by the team of the site office-guru.ru

Now let’s talk about visualization and comfortable perception of information, which are enormously important in data analysis. It is very convenient when you can compare several data at once on one diagram, track the dynamics of these indicators, while everything should be clear and not merge into a “mush”. To do this, you can create composite graphs from different types in Excel. What is a mixed diagram and how to create it, we will consider further.

So we've created a chart. Now we need to put a second scale on the diagram. For example, the percentage of logistics costs from the cost of sales or what share this indicator occupies in the overall total. Those. This is the case when it is necessary to combine several types in a chart, for example, Histogram and Line graph.

First, all diagrams are of the same type. Now we select one series and change the chart type for it. Right-click on the series, select “Change Series Chart Type” and select the “Graph” type for the series with percentages.

If the graph turns out to be invisible on the diagram, you need to add an auxiliary axis - right-click on the line or on the name in the legend, in the window that appears, select - Format data series.

In the window that opens, look for Row Parameters and change the checkbox to Along auxiliary axis.

Such a diagram demonstrates the ratios of indicators very well (see Figure 1).

If the revenue indicators are very high (how much was spent per 1 ruble of income), then the indicators for the 12th month can be considered ideal.

Shading a chart area in Excel

This is an interesting trick in Excel, I don’t remember where I learned it. It turns out very clearly if you paint over part of the diagram for visualization. For example, separate the beginning of a new period, in the example the beginning of a new year (month 1)

It is necessary to add another histogram from the beginning of the year with data equal to 1% (the full indicator for the column). We make a separate column or row with 1% data, starting in the new year.

Click Select source - Add - Add data from 1%, i.e. add another row of data. We repeat all the steps to get another histogram along the auxiliary axis.

For such a histogram, right-click Row Options - Side Gap value 0 so that the columns stretch across the entire area.

Standard Excel tools for pie charts allow you to use only one set of data. This note 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 of constructing a visual chart that reflects 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. Etc.

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.

Using Excel, you can create a simple and three-dimensional graph, a graph with markers, cylindrical, cone and column histograms, bubble, radar, scatter and bar charts. All of them facilitate the perception of statistical data in one or another area of ​​human activity.

If the values ​​of different series differ significantly from each other, it is useful to display them using different types of charts. Excel allows you to do this in one plot area. Let's look at combination (mixed) charts in Excel.

How to build a combo chart in Excel

Ways to build a combination chart in Excel:

• converting an existing chart into a combined chart;
• adding an auxiliary axis.

Let's create a table with the data that needs to be displayed in a combination chart.

Let's select the columns of the range, including the headers. On the “Insert” tab, in the “Charts” group, select the usual “Graph with markers”.

Two graphs appeared in the plotting area, displaying the number of units sold and the sales volume in rubles.

How can you combine different types of charts? Let’s right-click “Qty., pcs.” In the window that opens, select “Change type for series.”

A menu with chart types will open. In the “Histogram” section, select the flat column “Histogram with grouping”.

Click OK. By default, the height of the bars corresponds to the vertical axis of the values ​​on which sales are plotted. But the histogram should show the quantity.

Select the histogram by clicking on it with the mouse. Let's go to the "Layout" tab. Group "Current fragment" - tool "Format selected fragment".

The Format Data Series window opens. On the “Series Parameters” tab, check the box next to “Build series along auxiliary axis”.

Click the “Close” button.

Let's work on the appearance of the combination chart. Select the construction area and go to the “Design” tab. Let's change the style. Delete the legend (select – Delete). Let's add the name and labels of the vertical axes.

For the main and auxiliary axis, select the location option (separately for each) and enter the signature. Press Enter.

In this example, we used two ways to create combination charts: we changed the type for the series and added an auxiliary axis.

If data sets differ significantly in scale and method of expression, then you cannot do without an auxiliary axis to create a mixed chart. When using only one scale, one row becomes almost invisible. The solution to the problem is to use an additional axis for the second set of data.

Change for one chart type data series

Let's create a mixed chart by changing one of the series types.

Table with initial data:

Let's build a regular histogram based on two data series:

Let's highlight the histogram bars that display the planned indicators. On the Design tab, in the Type group, click the Change Chart Type button. Let’s choose “With areas” from the proposed options.

We can leave the planned indicators in the form of histogram bars, and display the actual indicators in the form of a graph with markers.

Attention! Not all chart types can be combined. You cannot combine some volumetric types, bubbles, and other charts. Excel generates an error when combinations are impossible.

Thus, a mixed chart is built on the basis of two or more data series. It uses different types of charts. Or one type (for example, a histogram), but contains a second axis of values.