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

Question

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.

two sets of data:

Moving dates into one column, still one line:

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

@SeanC - Removed the 2016 total, same issue

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):

Answer 1

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 :)

Answer 2

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.

Answer 3

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

The blue line is accurate but not the red.