In this example, the order of entries ( Oranges, Apples, Lemons, Grapes) is identical in all 3 sheets. Otherwise, your graph is going not be messed up. The solution demonstrated above works only if your entries appear in the same order in all the worksheets you want to plot in the chart. Select your graph, click the Chart Elements button (green cross) in the top right corner, and select the options you want:įor more customization options, such as adding data labels or changing the way the axes are displayed in your chart, please check out the following tutorial: Customizing Excel charts. For our chart plotted from several worksheets, the title and legend were not added by default, but we can quickly remedy this. When creating charts in Excel 20, usually the chart elements such as chart title and legend are added by Excel automatically. Customize and improve the chart (optional) In this example, I've added the 3 rd data series, here's how my Excel chart looks now:Ĥ. When done, click the OK button on the Select Data Source dialog window.
HOW TO PLOT A GRAPH IN EXCEL 2016 SERIES
If you want to plot data from multiple worksheets in your graph, repeat the process described in step 2 for each data series you want to add. The series names will appear in chart legend of your chart, so you might want to invest a couple of minutes in giving some meaningful and descriptive names for your data series.Īt this point, the result should look similar to this: Instead of the column name, you can type your own series name in double quotes, e.g. Make sure the references in Series name and Series value boxes are correct and click the OK button.Īs you see in the screenshot above, we've linked the series name to cell B1, which is a column name. Click the Expand Dialog to return to the initial Edit Series window. On the second worksheet, select a column or a row of data you want to add to your Excel graph, and then click the Expand Dialog icon to get back to the full-sized Edit Series window.Īnd now, click the Collapse Dialog button to the right of the Series name field and select a cell containing the text you want to use for the series name. Click on the tab of the sheet that contains the other data you want to include in your Excel chart (the Edit Series window will remain on-screen as you navigate between sheets). The Edit Series dialog will shrink to a narrow range selection window. This is the key point, so please be sure to follow the instructions closely.Ĭlicking the Add button opens the Edit Series dialog window where you click the Collapse Dialog button next to the Series values field. Now we are going to add the second data series based on the data located on a different worksheet. In the Select Data Source window, click the Add button.
Or, click the Chart Filters button on the right of the graph, and then click the Select Data… link at the bottom.
Add a second data series from another sheetĬlick on the chart you've just created to activate the Chart Tools tabs on the Excel ribbon, go to the Design tab ( Chart Design in Excel 365), and click the Select Data button. In this example, we will be creating the Stack Column chart:Ģ. Open your first Excel worksheet, select the data you want to plot in the chart, go to the Insert tab > Charts group, and choose the chart type you want to make. Supposing you have a few worksheets with revenue data for different years and you want to make a chart based on those data to visualize the general trend.
HOW TO PLOT A GRAPH IN EXCEL 2016 HOW TO
How to create a chart from multiple sheets in Excel Customize a chart created from several sheets.Make a chart from multiple Excel sheets.Fortunately, Microsoft Excel provides a way to plot data from two or more different worksheets in a single graph. Indeed, when creating charts in Excel, the source data does not always reside on the same sheet.
And the very first question posted in comments was this: "And how do I created a chart from multiple tabs?" Thanks for this great question, Spencer! A while ago we published the first part of our Excel charts tutorial for beginners that provides the detailed guidance on how to make a graph in Excel.