How to Make a Chart From Two Different Sheets in Excel
By Micah McDunnigan
When Microsoft removed the chart creation wizard from Excel, they were ignoring a business reality: the data series you need for your chart are not always on the same Excel worksheet. Fortunately, you still can draw on data from two different worksheets to make a chart in Excel. If you have a workbook with two worksheets that contain data that are compatible for a single chart, you can easily create one chart containing all of the data without having to combine the data at the outset. For example, you probably wouldn't want to include data for apples from 1974-89 with data for oranges from 1997-99 in the same chart, but you might want to include revenue data for 2005-12 in the same chart as manufacturing costs for the same time period.
Open the Excel worksheet containing the data for your first chart.
Select the data series, click the "Insert" tab, and then choose the type of chart you want to use from the Charts group. For example, click the down arrow under "Bar" and click to select a specific type of bar chart.
Click on the resulting chart to select it.
Click the "Design" tab and then click the "Select Data" button.
In the Select Data Source window, click the "Add" button.
Click the button to the right of the "Series name" field. The window will shrink to a narrow Edit Series window.
Click on the tab of the worksheet that contains the other chart data you want to use in the chart. The Edit Series window will remain on-screen as you change worksheets. Use your mouse to select the column or row of data you want to add to the chart and click "OK."
Repeat steps 5 through 7 until you have added all of the data from the second data series that you want to combine with the first data series.
Click the "OK" button and save your spreadsheet.
Micah McDunnigan has been writing on politics and technology since 2007. He has written technology pieces and political op-eds for a variety of student organizations and blogs. McDunnigan earned a Bachelor of Arts in international relations from the University of California, Davis.