Skip to main content

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

Author: Kelly Last Modified: 2020-05-09

For example you have four tables with same layout in four worksheets as below screenshot shown. And now you need to create a chart with extracting a data series from each worksheet, and create a chart with extracting data points from each worksheet, how could you solve them in Excel? This article recommends two workarounds for you:


Create chart with extracting many data series from multiple worksheets

This section is talking about creating a column chart with extracting many data series from multiple worksheets in Excel. You can follow below steps to archive it.

1. Click Insert > Insert Column Chart (or Column)> Clustered Column. See screenshot:

2. Now we insert a blank chart. Right click the blank chart, and select Select Data from the right-clicking menu. See screenshot:
doc chart across multiple worksheets 3

3. In the opening Select Data Source dialog box, click the Add button.
doc chart across multiple worksheets 4

4. And in the Edit Series dialog box, specify the series name and series values from a worksheet, and then click the OK button.
doc chart across multiple worksheets 5

5. When it returns to the Select Data Source dialog box, repeat step 3 and step 4 to add data series from other worksheets. Finally, all added data series from worksheets are listing in the Legend Entries (Series) box as below screenshot shown.
doc chart across multiple worksheets 6

6. In the Select Data Source dialog box, click the Edit button in the Horizontal (Category) Axis Labels section to open the Axis Labels dialog box, and then specify the axis labels as you need, and click the OK button. See above screenshot:

7. Click the OK button to close the Select Data Source dialog box.

8. It’s optional. Keep selecting the chart, click Design > Add Chart Element > Legend, and then select a legend option from the submenu. See screenshot:
In our case, we select the Legend > Bottom.
doc chart across multiple worksheets 7

So far we have created a clustered column chart with four data series from four worksheets. See screenshot:
doc chart across multiple worksheets 8


Create chart with extracting many data points from multiple worksheets

Sometimes you may want to create a chart whose data points are from different worksheets. This section will introduce Kutools for Excel’s Dynamically Refer to Worksheets to extract data points from multiple worksheets into a new worksheet, and then create a chart with these data points.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

1.On the Sheet Tab bar click the New button or  to create a new worksheet.

2. In the new worksheet, select the cell whose cell contents you will extract from other worksheets, and click Kutools > More (in the Formula group) > Dynamically Refer to Worksheets. See screenshot:
doc dynamically refer to sheets 1

3. In the opening Fill Worksheets References dialog box, please do as follows:
(1) Select Fill vertically cell after cell from the Fill order drop down list;
(2) In the Worksheet list section, check the worksheets where you will extract data points from.
(3) Click the Fill Range button and the Close button successively.
doc chart across multiple worksheets 12
Note: If you have several data series whose data points are from different worksheets, you can repeat this step as you need.

And then you will see data points are extracted from different worksheets. See screenshot:

4. Select the extracted data points, and create a chart. In our case, we create a clustered column by clicking Insert > Insert Column Chart (or Column)> Clustered Column.

Till now we have created a clustered column chart whose data points are from different worksheets. See screenshot:
doc chart across multiple worksheets 14

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Demo: create chart across/from multiple worksheets in Excel


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

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

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

Description


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!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
SUper!!111!!!!!!!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations