Skip to main content

How to create chart across/from multiple worksheets in Excel?

Author: Kelly Last Modified: 2025-07-22

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

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.
click Clustered Column from Insert tab

2. Right-click the newly inserted blank chart and select Select Data from the context menu.
select Select Data from the right-clicking menu

3. In the Select Data Source dialog box, click the Add button to start adding a new data series.
click the Add button in the Select Data Source dialog box

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.

specify the series name and series values in the Edit Series dialog box

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.
repeat steps to add data series from other worksheets

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.
select a legend option from the Legend submenu

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:
a chart across multiple worksheets is created


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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

Here's how to gather data points and create the chart:

1. On the Sheet Tab bar, click the New button New button or new button 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.
click Dynamically Refer to Worksheets feature of kutools

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.
    set options in the dialog box

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.
data points are extracted from different worksheets

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.
a chart across multiple worksheets is created

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 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:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!