How to create a chart in Excel from multiple sheets

This tutorial shows how to create and modify Excel charts from data across multiple worksheets. Previously, we covered basic charting; this expands on that by addressing the common question of combining data from different sheets.

Creating Charts from Multiple Sheets

Let's say you have revenue data for several years on separate sheets and want a single chart showing the overall trend.

Step 1: Chart from the First Sheet

Select the data on your first sheet, go to the Insert tab, choose Charts, and select your desired chart type (e.g., Stacked Column).

Step 2: Adding Data from Another Sheet

Click the chart to activate the Chart Tools. Go to the Design tab (Chart Design in Excel 365) and click Select Data. Alternatively, use the Chart Filters button (on the right of the chart) and select Select Data….

Click Add. In the Edit Series window, click the Collapse Dialog button next to Series values. Navigate to the sheet containing your next data set, select the data, and click Expand Dialog.

Repeat the collapse/expand process for the Series name, selecting a cell with the series name or typing a custom name (e.g., "Second data series"). Click OK.

Your chart now includes data from the second sheet.

Step 3: Adding More Data Series (Optional)

Repeat Step 2 for each additional data set.

Step 4: Chart Customization (Optional)

Use the Chart Elements button (green plus sign) to add or modify chart elements like titles and legends. For advanced customization, refer to our tutorial on customizing Excel charts.

Using a Summary Table

This method requires data to be in the same order across sheets. For inconsistent ordering, create a summary table using functions like VLOOKUP to consolidate data before charting.

Modifying Existing Charts

To change data series in an existing chart:

• Select Data Source Dialog: Use the Select Data dialog to edit, reorder, hide, or delete series.

• Chart Filters Button: Use the Chart Filters button to quickly hide or show series and edit individual series.

• Data Series Formulas: Directly edit the series formula in the formula bar (use caution!). The formula follows this structure: =SERIES([Series Name], [X Values], [Y Values], [Plot Order]).

This tutorial provides a comprehensive guide to creating and modifying multi-sheet Excel charts. Remember to use the method best suited to your data organization and comfort level with Excel functions.

The above is the detailed content of How to create a chart in Excel from multiple sheets. For more information, please follow other related articles on the PHP Chinese website!