Creating an Excel chart that uses data from multiple sheets

Question

Consider the following case:

I have a single Excel workbook with 4 sheets in it.
Each sheet represents one quarter of a year.
Each sheet contains 2 columns:
- Date
- Revenue

I now want to create a line graph for daily revenue over the whole year.
The X-axis should start at January 1st (first row of the first column of the first sheet) and end at December 31st (last row of the first column of the fourth sheet).
There should be a single line plotting the daily sales over that year on the Y-axis.

Matt S · Accepted Answer · 2011-02-16 14:10:58Z

1

I think the most straight forward thing to do is to add a fifth sheet to your workbook that references the cells from the other 4 sheets. Then create your graph from the data on the fifth sheet.

Also, I think you will get better results with an XY Scatter plot, if you use a line chart, the X axis is pre-defined but in your case, you want your X axis to be the date.

answered Feb 16, 2011 at 14:10

Matt S

1266 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Oliver Salzburg Over a year ago

Ok, how would I set up that fifth sheet?

Matt S Over a year ago

from the Menu, Insert -> WorkSheet. Then on the new worksheet in the A1 cell type '=' then switch to your Q1 sheet and select the first cell there, then hit enter. Drag the cell on your combined sheet to right one column and down until you get all the values from the Q1 sheet. For next unused row, do the same for the Q2 sheet.

Oliver Salzburg Over a year ago

Ok, thanks, I was hoping there was a more automated approach ;)

Matt S Over a year ago

You could copy and paste if you aren't planning on changing the original data.

Oliver Salzburg Over a year ago

Using an XY scatter plot pretty much achieves a graph like I had in mind. Fits right for my case.

chris neilsen · Accepted Answer · 2011-02-17 06:37:43Z

Here's a subroutine to get you started automating adding a chart

Sub zx()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim Chrt As Chart
    Dim Srs As Series

    Set wb = ActiveWorkbook
    Set Chrt = wb.Charts.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    Chrt.ChartType = xlXYScatterLines
    Chrt.SeriesCollection(1).Delete
    Chrt.Name = "Annual Trend"

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q1"
    Set sh = wb.Sheets("Quarter1")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q2"
    Set sh = wb.Sheets("Quarter2")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q3"
    Set sh = wb.Sheets("Quarter3")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q4"
    Set sh = wb.Sheets("Quarter4")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address
End Sub

Collectives™ on Stack Overflow

Creating an Excel chart that uses data from multiple sheets

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related