How to create chart across/from multiple worksheets in Excel?
In many practical scenarios, you may have data spread across several worksheets in an Excel workbook, for example, monthly or yearly reports, departmental statistics, or comparative summaries maintained in separate tabs. Creating a useful chart that summarizes or visualizes data from these multiple sheets can provide key insights at a glance, especially when the data structures across worksheets are consistent.
For instance, imagine you have four tables with the same format in four different worksheets, as illustrated in the following screenshot. You may need to generate a chart by compiling either a series from each worksheet (for trend or comparative views), or select key data points from each sheet (for quick snapshot comparisons). This article will guide you through several practical solutions, explaining where each approach is best used, along with detailed, step-by-step instructions, supplemental tips, and troubleshooting guidance.
- Create chart with extracting many data series from multiple worksheets
- Create chart with extracting many data points from multiple worksheets
- VBA code to combine data from multiple worksheets and generate a chart
Create chart with extracting many data series from multiple worksheets
When your goal is to compare series across several worksheets with the same structured data, such as sales per region in four different months located in separate tabs, creating a consolidated column chart with multiple data series provides an effective visual comparison.
This method requires manual selection but is straightforward and doesn't need complex formulas or macros. It works best when your worksheets have an identical layout and you need to compose a chart directly from the source sheets.
Follow these steps to set up your chart:
1. Click Insert > Insert Column Chart (or Column) > Clustered Column. This opens a blank chart in the worksheet.
2. Right-click the newly inserted blank chart and select Select Data from the context menu.
3. In the Select Data Source dialog box, click the Add button to start adding a new data series.
4. In the Edit Series dialog box, enter the series name and specify the series values by navigating to the relevant worksheet and selecting the required data range. Double-check that the references are accurate, as referencing errors can cause the chart to display incorrect data or errors like #REF. Click OK to confirm.
Tip: To reference data from another worksheet in the series values box, switch to the target sheet, then select the desired range. Excel will automatically include the worksheet name in the reference.
5. Repeat steps 3 and 4 for each worksheet you want included in the chart. After adding all series, you'll see them listed under Legend Entries (Series) in the dialog.
Note: Ensure that you always pick the same cell range structure on each sheet; inconsistencies can make the chart misleading or cause alignment problems.
6. To fine-tune your chart, click Edit under the Horizontal (Category) Axis Labels in the Select Data Source window. In the Axis Labels dialog, select the relevant labels to correctly align with your data. Click OK when done.
7. Close the Select Data Source dialog by clicking OK. Your chart now combines data series from multiple worksheets.
8. (Optional) To improve visual clarity, select the chart, then go to Design > Add Chart Element > Legend, and pick an option (like Legend > Bottom) to display legends that identify each series.
This approach is ideal for static datasets. However, if your worksheet layouts deviate, or the data range varies between sheets, you’ll need to address these differences first. If your data updates frequently or needs automated consolidation, consider the other solutions below for greater efficiency.
Here is the resulting clustered column chart, which visually compares data series sourced from four different worksheets:
Create chart with extracting many data points from multiple worksheets
In cases where you wish to construct a chart by picking individual data points from several worksheets, instead of whole series, you can first collect the target cells into a summary sheet and then chart them together. This is common when you want to compare a single metric, such as the 'Total' value, from multiple departmental sheets.
Kutools for Excel’s Dynamically Refer to Worksheets provides an efficient way to extract specific values from multiple sheets into a single summary sheet, and is especially useful for dynamic data where source values may change over time.
Here's how to gather data points and create the chart:
1. On the Sheet Tab bar, click the New button or
to create a fresh worksheet for consolidation.
2. In this new worksheet, select the cell where you want to extract data from other sheets. Then go to Kutools > More (under the Formula group) > Dynamically Refer to Worksheets.
3. In the Fill Worksheets References dialog box, do the following:
- Select Fill vertically cell after cell from the Fill order dropdown. This will organize returned values in a vertical list.
- Check the worksheets containing the cells you wish to reference, ensuring you only select the relevant source tabs.
- Click Fill Range to pull the values, then Close once complete.
Tip: You can perform this step repeatedly if you need to collect different types of data, arranging them as distinct columns or rows in your summary sheet as needed.
After these steps, you'll see the data you selected from each worksheet neatly organized in your new sheet.
4. Highlight the consolidated data points, then proceed to create the chart as usual: Insert > Insert Column Chart (or Column) > Clustered Column.
Now, you've created a clustered column chart that visually compares selected data points, each one coming from a different worksheet.
Tips:
- This method works best for dynamically updating charts, as the links can refresh automatically when the source data changes (provided you use direct references or formulas).
- Check source worksheet names if you encounter #REF! errors, as renamed/deleted sheets will break the references.
Demo: create chart across/from multiple worksheets in Excel
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
VBA code to combine data from multiple worksheets and generate a chart
For users seeking a more automated and scalable solution—especially when dealing with a large number of worksheets or needing routine updates—a VBA macro can efficiently gather data from multiple sheets and instantly generate a chart. VBA allows you to customize the data consolidation process, making it well-suited for advanced users or anyone who frequently updates their reports.
Advantages: Automation, high flexibility for custom needs, works well with numerous worksheets.
Potential drawbacks: Requires permission to run macros, and some users may not be familiar with VBA syntax or troubleshooting.
You can use the following example to combine a specific cell(for instance, B2) from all worksheets into a new summary sheet, and then create a clustered column chart from the combined data:
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. Click Insert > Module, then paste the code below into the module:
Sub CombineDataAndChart()
Dim ws As Worksheet
Dim summarySheet As Worksheet
Dim lastRow As Long
Dim destRow As Long
Dim wsCount As Integer
Dim i As Integer
Dim rng As Range
On Error Resume Next
' Create summary sheet or clear previous one
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name = "SummaryChartData" Then
ws.Delete
Exit For
End If
Next
Application.DisplayAlerts = True
Set summarySheet = Worksheets.Add
summarySheet.Name = "SummaryChartData"
destRow = 1
' Set header
summarySheet.Cells(destRow, 1).Value = "Sheet"
summarySheet.Cells(destRow, 2).Value = "Value"
destRow = destRow + 1
' Collect data from all sheets (change range as needed)
For Each ws In Worksheets
If ws.Name <> "SummaryChartData" Then
summarySheet.Cells(destRow, 1).Value = ws.Name
summarySheet.Cells(destRow, 2).Value = ws.Range("B2").Value ' Modify "B2" as needed
destRow = destRow + 1
End If
Next
' Create chart
Dim chartObj As ChartObject
Set chartObj = summarySheet.ChartObjects.Add(Left:=250, Width:=350, Top:=20, Height:=250)
chartObj.Chart.ChartType = xlColumnClustered
chartObj.Chart.SetSourceData Source:=summarySheet.Range("A1:B" & destRow - 1)
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Combined Data from All Sheets"
xTitleId = "KutoolsforExcel"
End Sub
2. Click the Run button in the VBA editor to execute the code. The macro will automatically create a summary sheet ("SummaryChartData"), collect data (in this example, the value in cell B2) from all worksheets except the summary one, and build a chart based on the compiled data.
Note:
- If you want to extract a different cell from each worksheet, adjust the
ws.Range("B2")
reference accordingly. - To include more columns or flexible ranges, you can expand the code logic or loop through column indices.
- If any worksheet name conflicts occur, the macro will automatically overwrite or recreate the summary sheet as needed.
- Before running macros, ensure that your Excel settings allow macro execution.
If you encounter errors, double-check worksheet names, the specified cell references, and verify that no worksheet is protected or hidden.
Related articles:
Mirror/link cells across worksheets in Excel
Define named range across worksheets in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!