How to create a chart in Excel from multiple sheets
A while ago we published the first part of our Excel chart tutorial for beginners which provides a detailed guide on how to make a chart in Excel. And the first question posted in the comments was this: “And how do I create a chart from multiple tabs?” Thanks for this great question, Spencer!
In fact, when creating charts in Excel, the source data doesn’t always reside on the same sheet. Fortunately, Microsoft Excel provides a way to plot data from two or more different worksheets into a single chart. Detailed steps follow below.
- Make a chart from multiple excel sheets
- Customize a chart created from multiple sheets
How to create a chart from multiple sheets in Excel
Suppose you have some worksheets with revenue data for different years and you want to make a chart based on that data to visualize the general trend.
1. Create a chart based on your first sheet
Open your first Excel spreadsheet, select the data you want to plot on the chart, go to the Insert tab > Charts group, and choose the type of chart you want to create. In this example, we will create the Stack Column chart:
2. Add a second data series from another sheet
Click the chart you just created to activate the Chart Tools tabs on the Excel ribbon, go to the Layout tab (Chart Layout in Excel 365), and click the Select Data button.
Or, click the Chart Filters button to the right of the chart, and then click the Select Data… link at the bottom.
In the Select Data Source window, click the Add button.
Now let’s add the second data set based on the data located in a different worksheet. This is the key point, so be sure to follow the instructions to the letter.
Clicking the Add button opens the Edit Series dialog window, where you click the Collapse Dialog button next to the Serial values countryside.
The Edit Series dialog box will shrink to a narrow range selection window. Click the tab for the sheet that contains the other data you want to include in your Excel chart (the Edit Series window will remain on the screen while you navigate between sheets).
In the second worksheet, select a column or row of data that you want to add to your Excel chart, and then click the Expand Dialog icon to return to the full-size Edit Series window.
And now, click the Collapse Dialog button to the right of the series name and select a cell that contains the text you want to use for the series name. Click in the Expand dialog box to return to the Edit Initial Series window.
Make sure the references in the Series Name and Series Value boxes are correct and click the OK button.
As you can see in the screenshot above, we have bound the series name to cell B1, which is a column name. Instead of the column name, you can enclose your own string name in double quotes, for example =”Second data string”.
The series names will appear in the chart legend of your chart, so you may want to spend a couple of minutes coming up with some meaningful and descriptive names for your data series.
At this point, the output should look similar to this:
3. Add more data series (optional)
If you want to plot data from multiple worksheets on your chart, repeat the process described in step 2 for each data series you want to add. When you’re done, click the OK button on the Select Data Source dialog.
In this example I added the third data series, this is what my Excel chart now looks like:
4. Customize and enhance the chart (optional)
When you create charts in Excel 2013 and 2016, Excel usually automatically adds the chart elements, such as the chart title and legend. For our chart plotted from multiple worksheets, the title and legend weren’t added by default, but we can quickly remedy that.
Select your chart, click the Chart Elements button (green cross) in the upper right corner, and select the options you want:
For more customization options, like adding data labels or changing the way the axes are displayed on your chart, check out the following tutorial: Customizing Excel Charts.
Make a chart from the summary table
The solution demonstrated above works only if its inputs appear in the the same order on all worksheets you want to plot on the chart. Otherwise your chart won’t be messed up.
In this example, the order of the entries (oranges, apples, lemons, grapes) is identical on all 3 sheets. If you’re making a chart from large worksheets and you’re not sure of the order of all the elements, it makes sense to create a summary table first, and then make a graph from that table. To extract the matching data in a summary table, you can use the VLOOKUP function or the Join Table Wizard.
For example, if the worksheets discussed in this example had a different order of items, we could make a summary table using the following formula:
And I got the following result:
And then just select the summary table, go to the Insert tab > Charts group, and choose the type of chart you want.
Modify an Excel chart created from multiple sheets
After making a chart based on data from two or more sheets, you may find that you want it to be rendered differently. And because creating such charts is not an instant process like making a chart from a sheet in Excel, you may want to edit the existing chart instead of creating a new one from scratch.
In general, the customization options for multi-sheet based Excel charts are the same as for regular Excel charts. You can use the Chart Tools tabs on the ribbon, the context menu, or the chart customization buttons in the upper right corner of your chart to change basic chart elements such as the chart title, axis titles, chart legend, chart styles and more. Detailed step-by-step instructions are provided in Customizing Excel Charts.
And if you want change data series plotted on the graph, there are three ways to do this:
Edit data series using the Select Data Source dialog box
Open the Select Data Source dialog box (Design tab > Select Data).
A change a data seriesclick on it, then click on the Edit and modify the Series Name or Series Values as we did when adding a data series to the chart.
To change the serial order On the chart, select a series and use the up and down arrows to move that series up or down.
A hide a series of datajust uncheck it in the Legend Entries (Series) list on the left side of the Select Data Source dialog.
A Remove a certain data series on the chart permanently, select that series and click the Remove down.
Hide or show series with the Chart Filter button
Another way to manage the data series displayed in your Excel chart is to use the Chart Filters button . This button appears to the right of your chart as soon as you click on it.
A hide certain dataclick the Chart Filters button and uncheck the appropriate data series or categories.
A edit a data series, click the Edit Series button to the right of the series name. The old Select Data Source dialog will appear and you can make the necessary changes there. To bring up the Edit Series button, simply hover over a series name. As soon as you do this, the corresponding series will be highlighted on the chart, so you will clearly see which element exactly is going to change.
Edit a data series using a formula
As you probably know, each data series in an Excel chart is defined by the formula. For example, if you select one of the series in the chart we created a moment ago, the formula for the series will look like this:
Each data series formula can be broken down into four basic elements:
=SERIES(, , , )
So, our formula can be interpreted as follows:
- The name of the series (‘2013’!$B$1) is taken from cell B1 in sheet “2013”.
- The horizontal axis values (‘2013’!$A$2:$A$5) are taken from cells A2:A5 of sheet “2013”.
- The vertical axis values (‘2013’!$B$2:$B$5) are taken from cells B2:B5 of sheet “2013”.
- Plot Order (1) indicates that this data series comes first on the plot.
To modify a certain data series, select it in the chart, go to the formula bar and make the necessary changes there. Of course, you need to be very careful when editing a series formula because it could be an error-prone way, especially if the source data is in a different worksheet and you can’t see it when you edit the formula. And yet, if you’re more comfortable with Excel formulas than user interfaces, you might like this way of quickly making small changes to Excel charts.
That’s all for today. I appreciate his time and hope to see him on our blog next week!