No Links were listed yet. Go ahead and share!
Hey, welcome back to the Mr. Excel net cast I'm Bill Jelen. Basically, you start out with massive amounts of data, say how you're going to analyze this, well let's fire up the pivot table and see if we can solve this problem.
Hey, welcome back to the Mr. Excel net cast I'm Bill Jelen. Boy a pivot table question today send in by John, John has the live lessons DVD and he has a range of 10 years worth of stock data here and he wants one chart that shows the months going across the bottom and each year as a different line. I think we can do this with the pivot table, insert pivot table click okay. And here’s what I'm going to do, I'm going to put initially the dates going down the left hand side. And then we’ll choose something—let's go with the high as the values. Now, I'm going to change that from sum to a max that would receive the highest for each month.
Were going to get the months in here in the second, so coming here, with my date field, I'm going to choose group field and said I want to group this up to months and years. And so I will get exactly one point now for each month and year. Now what's really cool when we group a date up to two different fields is Excel makes it two virtual fields in the pivot table field list, that allows us to take those fields and for example take years and move it to column labels which gets us our years going across, our months going down. Now let's get rid of the grand total, so we come here and uncheck both grand totals for rows, grand totals for columns, click okay. And then finally were going to create a chart from this, I'm going to go back to insert chart. Choose a line chart like that and there we have it.
So we have the various years from 1999 to 2008 as separate lines and then the various months. So, it started back here in 1999, went up and then in 2000 big crash and it’s just been kind of cruising along here the whole time. So there is one way to take that data at a daily level or a weekly level, roll it up to months and then years and then create a chart from that one.
I thank John for sending that question in and I want to thank you for stopping by, see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services