How do I create an Excel chart that pulls data from multiple sheets

Question

I have monthly sales figures stored in separate sheets   I would like to create a plot of sales for multiple products per month   Each product would be represented in a different colored line on the same chart with each month running along the x axis   What is the best way to create a single line chart that pulls from the same relative cells on multiple sheets

User · Accepted Answer

Use the Chart Wizard   On Step 2 of 4  there is a tab labeled  Series   There are 3 fields and a list box on this tab  The list box shows the different series you are already including on the chart  Each series has both a  Name  field and a  Values  field that is specific to that series  The final field is the  Category  X  axis labels  field  which is common to all series   Click on the  Add  button below the list box  This will add a blank series to your list box  Notice that the values for  Name  and for  Values  change when you highlight a series in the list box   Select your new series   There is an icon in each field on the right side  This icon allows you to select cells in the workbook to pull the data from  When you click it  the Wizard temporarily hides itself  except for the field you are working in  allowing you to interact with the workbook    Select the appropriate sheet in the workbook and then select the fields with the data you want to show in the chart  The button on the right of the field can be clicked to unhide the wizard   Hope that helps   EDIT  The above applies to 2003 and before  For 2007  when the chart is selected  you should be able to do a similar action using the  Select Data  option on the  Design  tab of the ribbon  This opens up a dialog box listing the Series for the chart  You can select the series just as you could in Excel 2003  but you must use the  Add  and  Edit  buttons to define custom series

User · Answer

Here s some code from Excel 2010 that may work   It has a couple specifics  like filtering bad-encode characters from titles  but it was designed to create multiple multi-series graphs from 4-dimensional data having both absolute and percentage-based data   Modify it how you like   Sub createAllGraphs    Const chartWidth As Integer   260 Const chartHeight As Integer   200     If Sheets Count   1 Then     Sheets Add   Sheets 1      Sheets 2  Name    AllCharts  ElseIf Sheets  AllCharts   ChartObjects Count  gt  0 Then     Sheets  AllCharts   ChartObjects Delete End If Dim c As Variant Dim c2 As Variant Dim cs As Object Set cs   Sheets  AllCharts   Dim s As Object Set s   Sheets 1   Dim i As Integer   Dim chartX As Integer Dim chartY As Integer  Dim r As Integer r   2  Dim curA As String curA   s Range  A   amp  r  Dim curB As String Dim curC As String Dim startR As Integer startR   2  Dim lastTime As Boolean lastTime   False  Do While s Range  A   amp  r   lt  gt          If curC  lt  gt  s Range  C   amp  r  Then          If r  lt  gt  2 Then seriesAdd              c SeriesCollection Add s Range  D   amp  startR  amp    E   amp   r - 1      False  True             c SeriesCollection c SeriesCollection Count  Name   Replace s Range  C   amp  startR                         c SeriesCollection c SeriesCollection Count  XValues         amp  s Name  amp      D    amp  startR  amp     D    amp   r - 1              c SeriesCollection c SeriesCollection Count  Values         amp  s Name  amp      E    amp  startR  amp     E    amp   r - 1              c SeriesCollection c SeriesCollection Count  HasErrorBars   True             c SeriesCollection c SeriesCollection Count  ErrorBars Select             c SeriesCollection c SeriesCollection Count  ErrorBar Direction  xlY  Include  xlBoth  Type  xlCustom  Amount        amp  s Name  amp      F    amp  startR  amp     F    amp   r - 1   minusvalues        amp  s Name  amp      F    amp  startR  amp     F    amp   r - 1              c SeriesCollection c SeriesCollection Count  ErrorBar Direction  xlX  Include  xlBoth  Type  xlFixedValue  Amount  0              c2 SeriesCollection Add s Range  D   amp  startR  amp    D   amp   r - 1   amp    G   amp  startR  amp    G   amp   r - 1      False  True             c2 SeriesCollection c2 SeriesCollection Count  Name   Replace s Range  C   amp  startR                         c2 SeriesCollection c2 SeriesCollection Count  XValues         amp  s Name  amp      D    amp  startR  amp     D    amp   r - 1              c2 SeriesCollection c2 SeriesCollection Count  Values         amp  s Name  amp      G    amp  startR  amp     G    amp   r - 1              c2 SeriesCollection c2 SeriesCollection Count  HasErrorBars   True             c2 SeriesCollection c2 SeriesCollection Count  ErrorBars Select             c2 SeriesCollection c2 SeriesCollection Count  ErrorBar Direction  xlY  Include  xlBoth  Type  xlCustom  Amount        amp  s Name  amp      H    amp  startR  amp     H    amp   r - 1   minusvalues        amp  s Name  amp      H    amp  startR  amp     H    amp   r - 1              c2 SeriesCollection c2 SeriesCollection Count  ErrorBar Direction  xlX  Include  xlBoth  Type  xlFixedValue  Amount  0             If lastTime   True Then GoTo postLoop         End If          If curB  lt  gt  s Range  B   amp  r  Value Then              If curA  lt  gt  s Range  A   amp  r  Value Then                 chartX   chartX   chartWidth   2                 chartY   0                 curA   s Range  A   amp  r              End If              Set c   cs ChartObjects Add chartX  chartY  chartWidth  chartHeight              Set c   c Chart             c ChartWizard   xlXYScatterSmooth          True  Replace s Range  B   amp  r              amp       amp  s Range  A   amp  r   s Range  D1    s Range  E1                Set c2   cs ChartObjects Add chartX   chartWidth  chartY  chartWidth  chartHeight              Set c2   c2 Chart             c2 ChartWizard   xlXYScatterSmooth          True  Replace s Range  B   amp  r              amp       amp  s Range  A   amp  r   amp          s Range  D1    s Range  G1                chartY   chartY   chartHeight             curB   s Range  B   amp  r              curC   s Range  C   amp  r          End If          curC   s Range  C   amp  r          startR   r     End If      If s Range  A   amp  r   lt  gt     Then oneMoreTime   False   end the loop for real this time     r   r   1 Loop  lastTime   True GoTo seriesAdd postLoop  cs Activate  End Sub

User · Answer

2007 is more powerful with ribbon      To add new series in chart do  Select Chart  then click Design in Chart Tools on the ribbon  On the Design ribbon  select  Select Data  in Data Group  Then you will see the button for Add to add new series   Hope that will help

[excel] How do I create an Excel chart that pulls data from multiple sheets?

Examples related to excel

Examples related to charts

Examples related to excel-2007