How to Create a Chart from Multiple Sheets in Excel

Creating a chart from multiple sheets in Excel is a powerful way to consolidate data and visualize it in a meaningful way. Whether you’re working with different datasets on separate sheets or need to compare data across multiple tabs, knowing how to create a chart from multiple sheets in Excel can simplify the process. This guide will walk you through the steps of combining data from multiple sheets to create a cohesive chart, including tips on using Excel references and data consolidation. By the end of this article, you’ll have the skills to generate insightful charts that pull data from various sources within your workbook.

How to Create a Chart from Multiple Sheets in Excel

Assuming you have a couple of worksheets with income information for various years and you need to make an outline in light of that information to picture the general pattern.

Step 1: Select Data for the Graph

Open your first Excel worksheet and select the data you need to plot in the graph. Highlight the relevant information to ensure it is included in the chart.

Select Data

Step 2: Choose a Chart Type

Go to the Insert tab and, in the Charts group, select the type of graph you need to create. Choose the appropriate chart style based on your data and desired presentation.

Choose a Chart Type

Step 3: Select the Stacked Column Chart

In this model, we will create a Stacked Column chart. Select the Stacked Column option from the chart types available to visualize your data effectively.

Select the Stacked Column Chart

Step 4: View the Result

Below is the result of the stacked column chart created from your selected data, visually representing the values in a clear and organized manner.

View the Result

How to a Second Data Series from Another Sheet

Here are the steps to add a second data series from another sheet in Excel:

Step 1: Activate the Chart Tools

Click on the diagram you’ve recently created to activate the Chart Tools tabs on the Excel ribbon. This will allow you to make further customizations to your chart.

Activate the Chart Tools

Step 2: Add a New Data Series

In the Select Data Source window, click the Add button to add a new data series to your chart. This allows you to include additional data from another sheet or source.

Add a New Data Series

Presently we will add the second information series in light of the information situated on an alternate worksheet. This is the central issue, so kindly make certain to adhere to the guidelines intently.

Step 3: Open the Edit Series Window

Clicking the Add button opens the Edit Series window. In this window, click the Collapse Dialog button next to the Series values field to select the data range from another sheet.

Click on Collapse Dialog

Step 4: Select the Data from Another Sheet

The Edit Series window will shrink to allow access to a smaller selection window. Click on the tab of the sheet that contains the additional information you need for your Excel graph. The Edit Series window will remain open as you navigate between sheets.

Select the Data

Step 5: Select Data from the Other Worksheet

On the subsequent worksheet, select the range or line of data you need to add to your Excel chart. After selecting the data, click the Expand Dialog icon to return to the regular Edit Series window.

Step 6: Set the Series Name and Confirm the Data

Next, click the Collapse Dialog button to the left of the Series Name field. Select the cell containing the text you want to use as the series name. Then, click the Expand Dialog button to return to the original Edit Series window. Ensure the references in both the Series Name and Series Values boxes are correct, and then click the OK button to confirm the changes.

Set the Series Name >> Confirm the Data

Step 7: Add a Series Name

As shown in the screenshot above, the series name is linked to cell B1, which contains a section name. Alternatively, you can type your own series name in double quotes, e.g., =”Second Data Series”. These names will appear in the chart legend, so make sure to give meaningful and descriptive names to your data series. The outcome should now look similar to this:

Add a Series Name

Modify an Excel Chart Built from Multiple Sheets

Subsequent to making an outline in view of the information from at least two sheets, you could understand that you believe it should be plotted in an unexpected way. Furthermore, on the grounds that making such diagrams is certainly not a moment cycle like making a diagram from one sheet in Excel, you might need to alter the current graph as opposed to making another one without any preparation.

As a general rule, the customization choices for Excel outlines in view of numerous sheets are equivalent to regular Excel diagrams. You can utilize the Charts Tools tabs on the lace, or right-click menu, or outline customization buttons in the upper right corner of your diagram to change the fundamental graph components, for example, graph title, pivot titles, diagram legend, diagram styles, and that’s just the beginning.

Modify the Excel Chart

Also, to change the information series plotted in the graph, there are three methods for doing this:

• Select the Data Source dialog
• Chart Filters button
• Data series formulas

Conclusion

By learning how to create a chart from multiple sheets in Excel, you now have the ability to seamlessly combine and visualize data from various tabs. This method not only simplifies the process of comparing information but also enhances your ability to present clear and comprehensive data insights. Whether you’re working with complex datasets or simply need to consolidate information, mastering this technique will improve your efficiency and data management in Excel.