Line Chart in Excel 2007

Blair Benjamin
Blair Benjamin used Ask the Experts™
on
Hello.  I'm trying to make a line chart using Excel data, but I can't seem to figure out how to get the results I want.  I'm thinking that it can't be this complicated.  I have a spreadsheet with dates of sales - one column has dates for Jan-May of 2011 and another has dates for Jan-May of 2012.   I want two lines on the chart representing the two years, with the y-axis having the # of sales and the x-axis having date ranges (say, every X days or so).  My data might look like this, with a date representing each sale (so there would be multiple entries with the same date).
Column 1
1/1/2011
1/2/2011
1/2/2011
1/2/2011
1/4/2011
1/6/2011
1/6/2011

Column 2
1/1/2012
1/1/2012
1/2/2012
1/3/2012
1/3/2012
1/4/2012
1/8/2012
1/8/2012
1/8/2012

How might this be accomplished?  Thanks to anyone who can help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
Creating a Pivot Chart, so you can sum sales on the same day might be the most expedient.

Please upload a sample sanitized worksheet so we can demonstrate.

Dave
Blair BenjaminSr. Systems Administrator

Author

Commented:
OK, thanks.  Here is a sample file.  Again, each date indicates a sale transaction, so some dates have multiple, some have none.
EE-Sales-Data.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
Where are the sales figures?  All you uploaded were dates.  Should I make them up?
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Top Expert 2010
Commented:
Assuming you want to plot the number of sales events for each, and further that you are trying to compare Jan 1 from 2011 against Jan 1 from 2012...

1) I recast the data to put the sale dates/times in a single column, with a new column to indicate the year, and a phony date column to cast everything to a date in year 2000 (to force the chart plotting to do what I want)

2) I then built a PivotTable and PivotChart against that data, with Year as the column field, the phony date as the row field, and count of the real date as the data field

3) I added in a grouping level of Month on the PT

I attached a sample document as a demo.  You might also want to check out:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
Q-27732729.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
Patrick - I certainly didn't catch that the OP was trying to plot the actual number of sales events, I was looking for actual unit sales, lol.

Thanks for stepping in.

Dave
Top Expert 2010

Commented:
That's what I'm assuming, Dave, but I've been wrong before :)
Blair BenjaminSr. Systems Administrator

Author

Commented:
Yes, each date represents one transaction.  That's why for some dates there are more than one transaction and other dates have none.   Hope this clarifies.  I did not have a chance to check out the links or attachments yet, but I definitely will when I get back into the office!  Thanks!
Blair BenjaminSr. Systems Administrator
Commented:
Thanks everyone for your contributions.  What I was actually attempting to accomplish was a line chart showing sales trends.  I was able to figure this out by having columns representing each transaction in each year.  Then I converted the dates to text so that I could exclude the year.  (That way I could have each year overlap in the chart rather than be one after the other.   Then I tallied the total year-to-date sales for each date by doing a "COUNTIF".  If you're curios, see attached.  Check out the formulas in Column G, etc.  The chart showing the trends is on a separate tab.
EE-Sales-Data.xlsx
Top Expert 2010

Commented:
benjaminfam,

Clever, but I think you'll find that the method I used in http:#a38013680 yields the same result in fewer steps.

:)

Patrick
Blair BenjaminSr. Systems Administrator

Author

Commented:
Looks like there are multiple ways to accomplish this. I appreciate the assistance!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial