by A while ago we published the first part of our Excel charts tutorial that provides the detailed guidance for beginners. 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!
Indeed, when creating charts in Excel, the source data does not always reside on the same sheet. Fortunately, Microsoft Excel provides a way to plot data from two or more different worksheets in a single graph. The detailed steps follow below.
How to create a chart from multiple sheets in Excel
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.
1. Create a chart based on your first sheet
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. In this example, we will be creating the Stack Column chart:
2. Add a second data series from another sheet
Click 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.
Or, click the Chart Filters button 
on the right of the graph, and then click the Select Data… link at the bottom.
In the Select Data Source window, click the Add button.
Now we are going to add the second data series based on the data located on a different worksheet. This is the key point, so please be sure to follow the instructions closely.
Clicking the Add button opens the Edit Series dialog window where you click the Collapse Dialog button next to the Series values field.
The Edit Series dialog will shrink to a narrow range selection window. 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).
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.
And 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 the Expand Dialog to return to the initial Edit Series window.
Make sure the references in Series name and Series value boxes are correct and click the OK button.
As you see in the screenshot above, we've linked the series name to cell B1, which is a column name. Instead of the column name, you can type your own series name in double quotes, e.g. ="Second data series".
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.
At this point, the result should look similar to this:
3. Add more data series (optional)
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. When done, click the OK button on the Select Data Source dialog window.
In this example, I've added the 3rd data series, here's how my Excel chart looks now:
4. Customize and improve the chart (optional)
When creating charts in Excel 2013 and 2016, usually the chart elements such as chart title and legend are added by Excel automatically. For our chart plotted from several worksheets, the title and legend were not added by default, but we can quickly remedy this.
Select your graph, click the Chart Elements button (green cross) in the top right corner, and select the options you want:
For 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.
Making a chart from the summary table
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. Otherwise, your graph is going not be messed up.
In this example, the order of entries (Oranges, Apples, Lemons, Grapes) is identical in all 3 sheets. If you are making a chart from large worksheets and you are not sure about the order of all items, it makes sense to create a summary table first, and then make a chart from that table. To pull the matching data to a summary table, you can use the VLOOKUP function or the Merge Tables Wizard.
For instance, if the worksheets discussed in this example had a different order of items, we could make a summary table using the following formula:
=VLOOKUP(A3,'2014'!$A$2:$B$5, 2,FALSE)
And got the following result:
And then, simply select the summary table, go to the Insert tab > Charts group and choose the chart type you want.
Modify an Excel chart built from multiple sheets
After making a chart based on the data from two or more sheets, you might realize that you want it to be plotted differently. And because creating such charts is not an instant process like making a graph from one sheet in Excel, you may want to edit the existing chart rather than create a new one from scratch.
In general, the customization options for Excel charts based on multiple sheets are the same as for usual Excel graphs. You can use the Charts Tools tabs on the ribbon, or right-click menu, or chart customization buttons in the top right corner of your graph to change the basic chart elements such as chart title, axis titles, chart legend, chart styles, and more. The detailed step-by-step instructions are provided in Customizing Excel charts.
And if you want to change the data series plotted in the chart, there are three ways to do this:
Edit data series using Select Data Source dialog
Open the Select Data Source dialog window (Design tab > Select Data).
To change a data series, click on it, then click the Edit button and modify the Series Name or Series Values like we did when adding a data series to the chart.
To change the order of series in the chart, select a series and use the Up and Down arrows to move that series up or down.
To hide a data series, simply uncheck it in the Legend Entries (Series) list in the left-hand side of the Select Data Source dialog.
To delete a certain data series from the chart permanently, select that series and click the Remove bottom.
Hide or show series using the Charts Filter button
Another way to manage the data series displayed in your Excel chart is using the Chart Filters button . This button appears on the right of your chart as soon as you click on it.
To hide certain data, click on the Chart Filters button, and uncheck the corresponding data series or categories.
To edit a data series, click the Edit Series button to the right of the series name. The good old Select Data Source dialog window will come up, and you can make the necessary changes there. For the Edit Series button to appear, you just need to hover over a series name with the mouse. As soon as you do this, the corresponding series will get highlighted on the chart, so you will clearly see exactly what element you are 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 graph we created a moment ago, the series formula will look as follows:
=SERIES('2013'!$B$1,'2013'!$A$2:$A$5,'2013'!$B$2:$B$5,1)
Each data series formula can be broken up into four basic elements:
=SERIES([Series Name], [X Values], [Y Values], [Plot Order])
So, our formula can be interpreted in the following way:
- Series name ('2013'!$B$1) is taken from cell B1 on sheet "2013".
- Horizontal axis values ('2013'!$A$2:$A$5) are taken from cells A2:A5 on sheet "2013".
- Vertical axis values ('2013'!$B$2:$B$5) are taken from cells B2:B5 on sheet "2013".
- Plot Order (1) indicates that this data series comes first in the chart.
To modify a certain data series, select it on 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 this might be an error-prone way, especially if the source data is located on a different worksheet and you cannot see it when editing the formula. And still, if you feel more comfortable with Excel formulas than with user interfaces, you may like this way to quickly make small edits in Excel charts.
That's all for today. I thank you for your time and hope to see you on our blog next week!
25 comments
Can you make a tutorial on how to make a line graph taking data from multiple tabs into one line graph? For example I have a spreadsheet with tabs of sales for apples, bananas, and coconut by month. I would like to graph total sales of apples for the year. Can I pull the totals from each tab to create one graph?
This may be confusing. I hope it made sense.
I have a similar question as regarding graph's.
I Keep a daily log of my investment portfolios for 5 trading markets and wish to see the history plotted on a single chart to display the performanc.
How do you have the same chart in different tabs so that I only have to update 1 chart.
how to move chart
hi
I have an excel with over 500 sheets. each sheet has a variable number of rows with historical descriptions of art objects (I know...).
I was wondering if it would be possible to make a chart gathering the information from ALL the sheets and ALL the rows.
I can prepare a column identifying each row by a specific definition or number, like, silver or 1, furniture or 2, ceramic or 3 etc.
would it be possible for excel to make a chart with this...?
I hope I'm explaining this well enough because I really need help because I have around 7.000 different pieces and it would be awful to count them one by one...!
thanks!
Helpful tip to add: before the design menu will appear for use in step 1 (at least in some versions of excel), it's necessary to create a table (by selecting cells and insert tab > table).
Hi!
The Design tab in Excel is context-sensitive. For a table, it shows table-specific features, and for a chart - chart-specific. To activate the tab, you just need to select a corresponding object, chart in our case.
To avoid confusion, in the current version of Excel 365, the tab is named "Chart Design".
Thanks for the information. It was useful.
Hi,
I have four Excel tabs that I am working on and they are all full with numbers. I have (Tab: L1, L2, M1 & M2) I have to plot L1&L2 together in one sheet and M1 with M2 as well. Everytime I plot each tab separately all the graphs look good. I tried to plot L1 with L2 but it doesnt seem to be giving me the graph that I am expecting.
Can someone please help me with this matter step by step please? I am not sure what am I doing wrong.
Regards,
AL
Thanks a lot for this. Utterly valuable.
I wanna create progressive R&E after each month, each month's R&E will be in diff worksheet, i want amount to change in progressive R&e as i in put amount in monthly R&E. please guide me
bopsman:
You can create a running total by entering a range formula which is part absolute reference and part relative reference. The range formula looks like this: $A$1:A1. Absolute with the "$", relative without. Where A1 is the first cell in the range.
So if the cell on the far right of your Table that will hold the formula is in column "D" and the calculated cell is in column "E" the formula will look like this:
=SUM($D$6:D6) formula in E6
=SUM($D$6:D7) formula in E7
=SUM($D$6:D8) formula in E8
after you've copied it down to row 6, 7 & 8. This will calculate the running total to row 8.
OK, first I would create a monthly sheet for each of the the month's data they will contain. Like January 2018, February 2018, etc.
Then I would create another sheet and name it Progressive Total. This sheet will be updated by a link from each of the monthly sheet's Total cells.
So, in each of these sheets I would create a Table. Tables have several advantages over a simple data table. Svetlana has a very good article here in AbleBits that explains tables. The link can be found by entering " How to make and use a table in Excel" in the search box.
When your table is ready enter the range as I have it above; absolute and relative references. Enter it in an empty cell on the far right cell in your table where you want to display the running total. Copy it down the column and each cell will display the running total.
In the last cell of your table should display the Total. Again this can be done very simply with the Table. This is the cell you will link to the Progressive Total sheet.
On the Progressive Total sheet create a table where each row corresponds to a total from a month. In the cell for each month you want to link the total from each month's total.
Do this by entering "=" in the cell and then open up the appropriate monthly sheet and click the Total cell on that sheet. Then open the Progressive Total sheet and click Esc and Save. The link in the cell should show something like "January 2018!E35". Where the first part is the name of your monthly sheet and the second part is the address for the Total cell. You'll want to click "Esc" after this so that Excel will know you're finished with the linking procedure.
Then, enter the range formula in the first cell that will hold the running total and copy it down the column. Because this Table is just like the others you can create a running total at the bottom which will be updated each time you update a monthly cell.
So, create monthly sheets which contain Tables at the bottom of which are Total cells. Then, create a Progressive Total sheet on which is a Table that contains cells that are linked to the Total cells in your monthly sheets. At the bottom of this Table is a cell which displays the calculated running total.
I have a similar request. I can email a sample of my data.
Have not been able to get this to work yet!
How can I select data in different rows, the rows are not below each other, there are other data in between, to show on the same line in the graph
Is it possible for some data only will be select for column and row will take a chart in excel
Is it possible to create a chart from multiple sheets that will automatically update when a new sheet of data is added?
yes
I am trying to great a comparisons graph. However, I have slightly different axis labels for each comparison. How do I go about putting this into one comparison graph?
I am following the instructions but the add button is grayed out when trying to select another data source. Is there something I need to update to be able to add?
chart made in exel how to get details to click one particular name?
Is it possible to have in the command
=SERIES([Series Name], [X Values], [Y Values], [Plot Order])
the x/y-value ranges combined from different sheets, i.e. without collecting them in one summery-sheet?
I tried this but it did not work:
=SERIES('2013a'!$B$1,'2013a'!$A$2:$A$5+'2013b'!$A$2:$A$5,'2013a'!$B$2:$B$5+'2013b'!$B$2:$B$5,1)
It also did not work when replacing the "+" with "&" and ";". Is it just not possible or did I miss something?
if i have to select data from a database in other sheet what to do
in sheet 1
power man
10 20 30 40
10 100 200 300 400
20 440 540 660 789
30 500 600 700 800
now in another sheet i have input power 20 and man 30 then corresponding 660 should come in that sheet for that what to do please suggest me.......
my mail id is yagnesh.2228@gmail.com
40
What about this:
Up top where you have 2014, 2015 and 2016 charts, what if you have 10 charts like that, but they're all spread across horizontally in one worksheet? Would there be a way to automate getting the information from each year into a summary?
Sir, I want to make a students performance evaluation sheet along with their performance graph on each subject. I have taken a filed as
name rollno month subject skill grading remarks
abc 11 april English reading 3
ddd 14 april English reading 2
abc 11 april pubjabi writing 3
abc 11 may English reading 2
ddd 14 may English reading 2
this way I have n number of data stored in sheet. I want to extract the performance evaluation bar graph on individual name or roll no wise and also on individual subject wise. how I will do it just by selecting name/roll no and subject and it will give the output in bar graph
This is the example I googled and used to do the charts. See what you can do with this information.