I'm starting to learn more about Excel and it's charting capabilities and I have a question to which I can't seem to find the answer. I have three worksheets, each with a single column that contains one date in each row from 1/1/2018 to present.
My goal is to end up with a chart that has dates grouped by week along the X-axis, and a Y-axis representing a count of the number of cells containing dates during that week.
I've started with a single worksheet by creating a pivot table/chart, grouped by week. That gets me a good start.
My questions, based on where I'm at now are:
1) How do I add two new series representing the data from the other two worksheets (again, each is just a single column of dates). I have pivot tables created for those as well already. If I "Select Data" on the chart, the "Add" button for series is greyed out.
2) How do I modify the values in the X-axis to just be the FIRST date of the date range? So if the pivot table groups the days into "1/1/2018 - 1/7/2018", I'd like the X-axis label to just be "1/1/2018" for readability.
Maybe I'm going about this the wrong way, not sure. The goal is to have a chart with an X-axis grouped by week, with three series on a line chart, one series for each of my worksheets.
Thanks for any help you might be able to offer!
Here are two screenshots of the raw data/pivot table and the resulting chart from ONE of the worksheets.
