Excel Graph - 2 Line chart / Each line representing it's own data set

0
votes

I have tried multiple ways to create this graph but I can not. If I place the data into a pivot table, it will not allow me to create the graph (error message is that I can not create a graph from a scatter chart). I am placing it in a pivot table to group the data for both columns to help organize the chart (the dates are formatted 1/1/2015 so I am trying to group all dates into months).

Here is what I am trying to do:

I have two columns. Column A and Column B.

Column A / 319 rows - Data consists of dates ranging from every month in the year 2015

Column B / 109 rows - Data consists of dates ranging from every month in the year 2016

I am trying to create a line graph that shows the following:

Two lines, one representing 2015 and one 2016.

The x-axis should display the months throughout a year. The y-axis should display numbers (representing # of times data shows up in that month).

Am I using a wrong chart? Am I not setting up my data correctly?

Currently, in row A1 and B1 are the years, so 2015 in A1 and 2016 in B1. Every row below that has random dates.

Basically each of these dates represents a open ticket so I am trying to find out how many tickets were opened in each month.

Photos due to request:

I can get a single line, but not with two sets of data.

enter image description here

two sets of data:

enter image description here

Moving dates into one column, still one line:

enter image description here

@SeanC - The data is not accurate. Also, there are no dates for 2016 from July-Dec. Also, it shows 2 dates for June 2016 but there are more. 2015 looks more accurate but not 2016.

enter image description here

@SeanC - Removed the 2016 total, same issue

enter image description here

Adding more evidence here (Lastest Date in Column B is June 28 and it also has 13 dates June - If you notice, the pivot table shows 2 dates for June and also dates for months that don't even exist in Column B):

enter image description here

3
excelchartsexcel-2010pivot-tablevba
can you upload a screenshot with examples from both columns ? just a sample of the data you have - Shai Rado
You can use PIVOT and create a chart, but you will need to modify your data structure. You need all of your data, all the dates in column A and B to be in Column A (put the values from Column B under the ones in Column A, and Cell A1 should be Date. Then you can create a PIVOT, and group the results by Year and Month. I hope I am being clear... - Shai Rado
@ShaiRado I added a screenshot. - LOZ
@ShaiRado I followed your suggestion but there is still only one line of data. I will post picture in the original post. - LOZ

3 Answers

1
votes

You are close. What you may not realize is that Excel will accept the same field in value and in a row/column/filter

Put column A in Row Labels, then put columns A and B in Values.

Group column A by months (leave out the years) and you will end up with the counts you require.

0
votes

Looks like I almost found my answer by placing the year value to the legend.

enter image description here

The blue line is accurate but not the red.

0
votes

I Think in your latest screen-shot you plotted the Grand Total series instead of 2016 (or there is some mix-up in the Data).

Please see attached screen-shot of the table and chart I got when putting all the dates in a single column (I only typed in a part of your data, just to check it's running OK)

Excuse my chart, going right-to-left, some Hebrew settings my wife did on the PC at home :)

enter image description here