Excel: Pivot tables with Line Graph with outside data
October 10, 2012 11:33 PM Subscribe
Excel Pivot Table + Line Chart Help
I have a worksheet of raw data that I'm pivoting into a new worksheet. For the column row I have DATE, for the value date I have REVENUE. And under filter I have TYPE.
Then after the pivot table, i'm copy/pasting two more columns of data from another source to create a calculated field.
I want to create a line graph that makes a line for each TYPE. currently, I can only do it by what I select in the filter, but I want a line for each TYPE.
I could do this with a lot of copy/pasting out of the pivot table, but is there a simpler way? And I can't use a Pivot Chart, as I have to utilize copy/pasted fields outside the pivot table.
I have a worksheet of raw data that I'm pivoting into a new worksheet. For the column row I have DATE, for the value date I have REVENUE. And under filter I have TYPE.
Then after the pivot table, i'm copy/pasting two more columns of data from another source to create a calculated field.
I want to create a line graph that makes a line for each TYPE. currently, I can only do it by what I select in the filter, but I want a line for each TYPE.
I could do this with a lot of copy/pasting out of the pivot table, but is there a simpler way? And I can't use a Pivot Chart, as I have to utilize copy/pasted fields outside the pivot table.
You could make a separate pivot table on a separate worksheet that has Type on rows, Date on columns, and Revenue on values. Create a pivot chart based on this table, but place the pivot chart on the original worksheet that contains the table with the copy/pasted data. Hide the sheet with the pivot table driving the chart.
posted by crazycanuck at 5:01 AM on October 11, 2012
posted by crazycanuck at 5:01 AM on October 11, 2012
Here's what I do for something similar.
I do a pivot table using the item ID in the rows, then I have two dates in the columns.
But I do a combination of Pivot Table and VLOOKUP to get all of my data in columns across the top.
So, do your pivot table, and then a VLOOKUP to add TYPE as a second column. Save AS, so it's not a pivot table anymore.
Then you can pivot again, on TYPE to get sum of revenue by type, and you can do your charts really easily from there.
I am the QUEEN of jankity work-around.
Ask anyone. They'll tell you.
posted by Ruthless Bunny at 7:31 AM on October 11, 2012
I do a pivot table using the item ID in the rows, then I have two dates in the columns.
But I do a combination of Pivot Table and VLOOKUP to get all of my data in columns across the top.
So, do your pivot table, and then a VLOOKUP to add TYPE as a second column. Save AS, so it's not a pivot table anymore.
Then you can pivot again, on TYPE to get sum of revenue by type, and you can do your charts really easily from there.
I am the QUEEN of jankity work-around.
Ask anyone. They'll tell you.
posted by Ruthless Bunny at 7:31 AM on October 11, 2012
This thread is closed to new comments.
I may be way off here, but would moving TYPE from a filter to a column label not give you two columns of data, one for each TYPE, from where you should still be able to do the extra calculations for your Line Chart?
posted by kreestar at 3:07 AM on October 11, 2012