Some time ago we published the first part of our tutorial on creating charts in Excel for beginners, in which it was given detailed instructions, how to build 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.

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 worker Excel sheet, 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 volumetric 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 referring to 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:

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)

And get this result:

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 will probably 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 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. Step by step instructions How to configure these parameters can be found in 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:

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) like us. 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 do required settings. To button Change row(Edit Series) has appeared, just hover your mouse over the name of the series. In this case, the data series 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:

ROW([ row_name];[category_name];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 2013 .
• The category names ('2013'!$A$2:$A$5) are taken from the cells A2:A5 on a sheet 2013 .
• Data ('2013'!$B$2:$B$5) taken from cells B2:B5 on a sheet 2013 .
• The series number (1) indicates that this series 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!

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 planar 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 of 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.

Two in one - how to do it?

Probably many of you make all kinds of reports in Excel and build graphs and charts based on them. Or they want to learn how to do it. And of course, everyone wants this graph to look beautiful and impressive, but at the same time reflect the whole picture. You may have already seen graphs in which data is displayed both in the form of a curve and in the form of columns or a pie chart at the same time. Such diagrams are also called mixed. Today I will briefly tell you how to combine several different types in one diagram and get something like this:

To build such a diagram, we need a table with source data and at least two data rows. For training, you can download an example file at the bottom of the article - all the source data is there. First we need to determine the type of our diagram.

Important: if you plan to use the type in your diagram Histogram (Columns), then when creating a chart you must select this particular chart type. And then add other types of diagrams, because... attempts to assign a type Histogram (Columns) after all the others will result in all rows becoming like Histogram (Columns). Moreover, without any warnings.
You should also remember that you cannot combine three-dimensional and flat types of diagrams. But if you try to combine flat and volumetric types, Excel itself will warn you about this, so it’s not so scary.

After selecting the initial chart type, click once on one of the data series in the chart (all columns of this row will be highlighted). Right mouse button:

Select the type and see how the diagram has changed.
Small nuance: if your data has a big difference between each other in one table (for example, sales amounts in rubles and the quantity of goods sold) and you want to compare this data by combining it in one diagram - then initially nothing will work out for you, because the difference in numerical data between the series will differ tens or even hundreds of times. Which will make the quantity data practically invisible. To avoid this you can use interesting opportunity- plot this data along another axis. Right mouse button on row - group Series Options-Secondary Axis:

Also, if you right-click on a row of data and select already Format Data Series, then you can wander through other parameters and change the fill color for a separate row, border, add shadows, etc. This gives quite a lot of scope for creativity and there is nothing more to say - you have to try and create :-)

Download example:

(47.0 KiB, 14,126 downloads)

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; text-align:left;","textbgcss":"display:absolute; left:0px; ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

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 several data series and uses various types 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 the placement of charts as graphic objects: one on top of the other. A chart on top 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.

Consider the construction inMSEXCEL2010 charts with multiple data series, as well as the use of auxiliary axes and the combination of different types of charts on one chart.

The construction of charts with one data series is discussed in the article. It is also advisable for novice users to read the article before reading.

HISTOGRAM

Let's build Histogram with grouping based on a table with two numeric columns that are close in value.

Select any table cell (see example file) on the tab Insert, in the group Diagrams click the button Histogram, select from the drop-down menu Histogram with grouping .

MS EXCEL will plot both series using only the main axes (to ensure this, double-click first on the column of one series, then on one of the columns on the other. In the window Data series format on the tab Series parameters it will be indicated on which axis the values ​​of the series are plotted). Because the values ​​of both series are close, then this solution suits us.

For series whose values ​​differ significantly (by an order of magnitude or more), one of the series must be constructed using .

To do this, just select the construction on the auxiliary axis for one of the rows (by double-clicking on one of the columns), and then adjust the width of the columns (side gap) so that both rows are displayed.

If you do not adjust the width of the columns, this chart can be confused with a chart Histogram with replenishment(columns belonging to the same category are “stacked” on top of each other).

Another option for displaying two series with significantly different values ​​on a chart is to scale the values ​​themselves in the source table.

The horizontal auxiliary axis can even be positioned at the top. In this case, the columns different rows will intersect in an original way.

Now let's change the labels along the horizontal axis (category).

In the window Selecting a data source It can be seen that for both rows the labels of the horizontal axis are the same, because the categories are the same for both rows (Month column).

Click first on the Sales row, then on Profit - the labels on the horizontal axis will be the same.

This can also be changed if desired. In the window Selecting a data source highlight the Profit row, click the button Change located on the right, remove the cell reference. Now, instead of category names, the Profit series will simply have serial numbers 1, 2, 3,... However, they will not be displayed on the chart, because currently only displayed Main horizontal axis .

Now in the Axes menu (tab Layout, group Axles) select and install it From left to right. In the format window Auxiliary vertical axis change the axis intersection point (set Auto select). We get this diagram.

Although the technical possibility of displaying 2 different categories exists, of course, such diagrams should be avoided, because they are difficult to perceive. The categories must be the same for all series in the chart. Naturally, this trick will only work for two groups of rows, because There are only 2 types of axis: main and auxiliary.

SCHEDULE

A Graph chart is similar in many ways to a Clustered Histogram: it uses the same ideas for displaying two series with significantly different values ​​as a Histogram.

After placing the rows on different axes, we get a diagram like this (the lines of the vertical axes are highlighted in colors corresponding to the colors of the rows).

Note. If you draw horizontal grid lines to the auxiliary axis, then they, naturally, may not coincide with the lines to the main axis, because the scales of the axes (the values ​​of the main divisions of the vertical axes) may not coincide. This may overload the diagram.

In general, the use of auxiliary vertical axes, and even more so auxiliary horizontal axes for the Histogram and Graph, must be approached carefully: after all, the diagram must be “readable” - understandable without additional comments.

SPOT

Visually, a Scatter Chart is similar to a Graph type chart (unless, of course, the points in a Scatter Chart are connected by lines).

Note. If a reference to X values ​​is not specified to construct a Scatter Plot (or the reference points to text values), then the same sequence 1, 2, 3, ... will be used as the X coordinates as for the Graph.

Based on the above, when constructing a Scatter chart, it is advisable to indicate numerical values ​​​​by X. Otherwise, you just need to use the Graph, because for it you can set any labels along X (including text), which cannot be done for a Scatter chart (only numbers).

Now about combining different types of charts with Scatter. If a Scatter Plot does not use an X coordinate, it appears as a Graph on the chart.

Labels along the horizontal axis are taken from the Graph. In the window Selecting a data source It can be seen that for the series displayed by a scatter plot, the horizontal axis labels cannot be changed/deleted.

In addition, the Graph can only be on the main axis and this cannot be changed.

If the X coordinate is used for a Scatter Plot and both series are plotted on the same (main) axis, then the result is not a very beautiful diagram.

This is due to the fact that when combined with a Scatter chart (on the same axis and when the Scatter has X values), the Graph type chart becomes, as it were, the main one:

• Labels only for the Graph are displayed on the horizontal axis;
• the vertical grid is not displayed for negative X values ​​(since the graph is built only for X=1, 2, 3, ...);
• It is impossible to change the Axis of the Graph from the Main to the Auxiliary (for the Spot Axis it is possible).

If a Spot is built on an auxiliary axis, the diagram will change.

Labels for Spot (X values) are now displayed at the top.

Advice. Scatter chart type is used for and others.

Now let's look at building 2 data series that use a Scatter chart.

First, let's build 2 ellipses with different center coordinates and semi-axes sizes without using auxiliary axes.

Note. There are actually 4 data series in the chart: the center point represents a separate series.

Double-click on the burgundy ellipse and select constructing a row on the auxiliary axis (we’ll do the same for the center of the ellipse).

Now the Y coordinates for the burgundy ellipse are plotted along the right vertical axis (you can also highlight it in burgundy color for clarity).

Let's add an Auxiliary horizontal axis (in the Axes menu (tab Layout, group Axles) select Secondary horizontal axis and install it Default).

Probably for a diagram like Spot the use of auxiliary axes is most justified - this allows you to display curves of different scales on one diagram.

CIRCULAR

They can also boast of the originality of the combination Histogram And Normalized stacked bar .

Probably the only types of diagrams that are worth combining are Schedule And Histogram(categories must be the same for both rows).

For such a diagram, first construct Histogram with grouping with two rows (see the Histogram section at the beginning of this article). Then select the desired row and press the button Change chart type(tab Constructor). By selecting Schedule or Graph with Markers click OK.

If the scales of the values ​​of the series differ significantly, then you can set up an auxiliary vertical axis (see the Histogram section).