Graph monthly and daily data in bar chart and line chart in the same graph

0
votes

Would love to seek help from excel pro after trialling and googling for hours. Appreciate your help and thanks a lot in advance.

Data:

  • Series 1: 365 data points on each day of 2018
  • Series 2: 12 data points on each month of 2018

Graph:

  • Line graph of series 1
  • Bar chart of series 2 (the width of a month covering data points from series 1 of that month)
  • 2 Y-axis (i.e. secondary axis)
  • X-axis shows months

I am not sure how to organise the data so that Excel can read the 31 data points in January from series 1 are from the same month with the 1 data point in January from series 2.

The X-axis issue is also tricky.

Thanks for your time.

1
excelexcel-charts
@J Tg - does a combo chart not work? Data should not need to be re-organized if you just add a new data series to your chart. The "horizontal" axis will need some commonality with the Series 1 but since you are using dates, there should be no issue. – Sam

1 Answers

1
votes

When using line/column charts Excel only cares about the number of data points for the x-axis (not their x-values). The reason you're having difficulty is because your two series have different numbers of points (i.e. 365 vs 12)

To get around this you need to make them match by spanning Series 2 across 365 data points.

You can do this by creating a second column for Series 2 with the same date values as series 1. I'll call this "modified Series 2".

You can populate this column by doing a VLOOKUP of the month of the original Series 2 data.

I made a dummy example below. The dataset on the right (E:G) is the original Series 2 data. The formula is column F is =MONTH(F2) filled down.

Once this is done, the modified series 2 dataset can be populated. The formula in column C is =VLOOKUP(MONTH(A2),$F$2:$G$13,2,FALSE). This returns the corresponding Series 2 value for every date based on the month.

enter image description here

Once your modified Series 2 dataset is prepared:

  1. Insert a Line chart (or Column chart)
  2. In Select data:
    • Add series 1 (colB in my example) and modified series 2 (colC in my example).
    • Edit Horizontal Axis labels to be the date column (colA in my example)
  3. Change Chart Type:
    • Select Combo
    • Make Series 1 Line, Series 2 Column
    • Check off Secondary Axis for Series 2
  4. For Series 2, go to Format Data Series:
    • Under Series Options, change Gap Width to 0%

That will produce something like the following. Hope this helps! enter image description here