Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
Hello,
I have some data based on companies. The data is the following.... Each day, (using date), I have a new Profit/Loss number based on a stock. I have a list of companies about 100 long, and so far 6 dates worth of P&L's. I am trying to set up a pivot chart to automatically create line graphs for each company which I will filter, to see the trend in P&L's. The problem is I am unable to draw the lines so the X variables are displayed as dates, the Y as the Value of P&L, and have it individually make a simple line graph for each company. Any suggestions? I really don't want to have to write a line graph each time for each company 100+ times, A filter would be 1000x easier. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
Assuming your data has headers of Date, Company Name, and P/L:
Select your data, go to Data, PivotTable. Select a PivotChart, then hit 'Finish'. Place Date into the Category Field area. Place Company Name into the series field area. Place P/L into the Data Items area. Finally, right-click on chart, change type to line. You can now use the series filter to choose which companies you want to look at. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trevor Greene" wrote: Hello, I have some data based on companies. The data is the following.... Each day, (using date), I have a new Profit/Loss number based on a stock. I have a list of companies about 100 long, and so far 6 dates worth of P&L's. I am trying to set up a pivot chart to automatically create line graphs for each company which I will filter, to see the trend in P&L's. The problem is I am unable to draw the lines so the X variables are displayed as dates, the Y as the Value of P&L, and have it individually make a simple line graph for each company. Any suggestions? I really don't want to have to write a line graph each time for each company 100+ times, A filter would be 1000x easier. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
On Jul 20, 2:25*pm, Luke M wrote:
Assuming your data has headers of Date, Company Name, and P/L: Select your data, go to Data, PivotTable. Select a PivotChart, then hit 'Finish'. Place Date into the Category Field area. Place Company Name into the series field area. Place P/L into the Data Items area. Finally, right-click on chart, change type to line. You can now use the series filter to choose which companies you want to look at. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trevor Greene" wrote: Hello, I have some data based on companies. The data is the following.... *Each day, (using date), I have a new Profit/Loss number based on a stock. I have a list of companies about 100 long, and so far 6 dates worth of P&L's. *I am trying to set up a pivot chart to automatically create line graphs for each company which I will filter, to see the trend in P&L's. *The problem is I am unable to draw the lines so the X variables are displayed as dates, the Y as the Value of P&L, and have it individually make a simple line graph for each company. Any suggestions? *I really don't want to have to write a line graph each time for each company 100+ times, A filter would be 1000x easier. Thanks! I wouldn't exactly Say I have headers-- I have a header for symbol. To lay it out, it looks like this... Symbol 7/9/2009 7/10/2009 AAA 25 15 BBB 50 22 CCC 30 13 So i have a header for Symbol, But my date is technically my header for P&L. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
Hi,
If that is your data layout you need to change the way the raw data is set up the pivot table will not handle the dates across the top correctly. 1. Select the raw data and press Alt+D, P 2. Choose Multiple Consolidation Ranges, Next, Next, 3. Highlight all your data and click Add, Finish 4. At the bottom right corner of the pivot table double-click the Grand Total 5. On the new sheet delete the Page1 column and change the heading titles to Symbol, Date, and Amount (or whatever) 6. Using your standard method create a pivot chart from this data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Trevor Greene" wrote: On Jul 20, 2:25 pm, Luke M wrote: Assuming your data has headers of Date, Company Name, and P/L: Select your data, go to Data, PivotTable. Select a PivotChart, then hit 'Finish'. Place Date into the Category Field area. Place Company Name into the series field area. Place P/L into the Data Items area. Finally, right-click on chart, change type to line. You can now use the series filter to choose which companies you want to look at. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trevor Greene" wrote: Hello, I have some data based on companies. The data is the following.... Each day, (using date), I have a new Profit/Loss number based on a stock. I have a list of companies about 100 long, and so far 6 dates worth of P&L's. I am trying to set up a pivot chart to automatically create line graphs for each company which I will filter, to see the trend in P&L's. The problem is I am unable to draw the lines so the X variables are displayed as dates, the Y as the Value of P&L, and have it individually make a simple line graph for each company. Any suggestions? I really don't want to have to write a line graph each time for each company 100+ times, A filter would be 1000x easier. Thanks! I wouldn't exactly Say I have headers-- I have a header for symbol. To lay it out, it looks like this... Symbol 7/9/2009 7/10/2009 AAA 25 15 BBB 50 22 CCC 30 13 So i have a header for Symbol, But my date is technically my header for P&L. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
On Jul 20, 2:57*pm, Shane Devenshire
wrote: Hi, If that is your data layout you need to change the way the raw data is set up the pivot table will not handle the dates across the top correctly. 1. Select the raw data and press Alt+D, P 2. Choose Multiple Consolidation Ranges, Next, Next, 3. Highlight all your data and click Add, Finish 4. At the bottom right corner of the pivot table double-click the Grand Total 5. On the new sheet delete the Page1 column and change the heading titles to Symbol, Date, and Amount (or whatever) 6. Using your standard method create a pivot chart from this data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Trevor Greene" wrote: On Jul 20, 2:25 pm, Luke M wrote: Assuming your data has headers of Date, Company Name, and P/L: Select your data, go to Data, PivotTable. Select a PivotChart, then hit 'Finish'. Place Date into the Category Field area. Place Company Name into the series field area. Place P/L into the Data Items area. Finally, right-click on chart, change type to line. You can now use the series filter to choose which companies you want to look at. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trevor Greene" wrote: Hello, I have some data based on companies. The data is the following.... *Each day, (using date), I have a new Profit/Loss number based on a stock. I have a list of companies about 100 long, and so far 6 dates worth of P&L's. *I am trying to set up a pivot chart to automatically create line graphs for each company which I will filter, to see the trend in P&L's. *The problem is I am unable to draw the lines so the X variables are displayed as dates, the Y as the Value of P&L, and have it individually make a simple line graph for each company. Any suggestions? *I really don't want to have to write a line graph each time for each company 100+ times, A filter would be 1000x easier. Thanks! I wouldn't exactly Say I have headers-- I have a header for symbol. *To lay it out, it looks like this... Symbol * * *7/9/2009 * * * *7/10/2009 AAA * * * * * *25 * * * * * * * * * * *15 BBB * * * * * *50 * * * * * * * * * * * 22 CCC * * * * * *30 * * * * * * * * * * *13 So i have a header for Symbol, But my date is technically my header for P&L. That last suggestion definitely helped, But i am still struggling on getting the chart to design a line for each company individually over the 5-6 dates vs. the line of ALL companies over the course of one day. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
Excel 2007 PivotTable
All done according to Shane. http://www.mediafire.com/file/mjug3dlktzt/07_20_09.xlsm Will not convert to 2003. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Chart question
On Jul 20, 4:13*pm, Herbert Seidenberg
wrote: Excel 2007 PivotTable All done according to Shane.http://www.mediafire.com/file/mjug3dlktzt/07_20_09.xlsm Will not convert to 2003. That helped very much. Thank you everybody. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Chart Question | Excel Discussion (Misc queries) | |||
Pivot chart question | Charts and Charting in Excel | |||
pivot chart question | Charts and Charting in Excel | |||
Pivot chart question | Excel Discussion (Misc queries) | |||
Pivot Chart Question | Charts and Charting in Excel |