Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
Hello kind & helpful people,
I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
I'm no expert but did you check the path you noted as the source?
(\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263) Pivot data can be in external files and that's what this sounds like to me. "InsomniacFolder" wrote: Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
Hello, thank you for the response.
I did try, but the file and reference that is referred to does not relate to anything on my computer or my network. It is in fact an old employees records from another European office - who ulitmately originated the data. There is no way to identify where that computer is now. As Excel still stores all of the data for the Pivot Table somewhere in the file I have - as it is able to generate the data totals click through subsets correctly. I wondered where in the file that I have those values are stored, and how do i get at all of them to use them? "Steve" wrote: I'm no expert but did you check the path you noted as the source? (\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263) Pivot data can be in external files and that's what this sounds like to me. "InsomniacFolder" wrote: Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
"InsomniacFolder" wrote: Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee HTH Either set up the pivot table with row totals or column totals, this is done when you right click on a cell in a pivot table and you get "possibilities for the PT. (sorry I use qa foreign language version). The other possibilityis to choose PT wizard while in the pivot table and go back a step and choose the option for " get external data" and see where that leads you. If the main interest is to see what is the data in a set of cells theen I would use the first option, adding totals to columns and rows. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
Thanks for the response, both of those suggestions allow me to see that the
original path I posted is what Excel thinks is the data source, but that data no longer exists anywhere. I think the problem is that in the PivotTable Options menu - Data Tab, the "Save source data with file" checkbox is ticked (as is default I think) The accompanying data table (that was on a separate sheet originally) has then been deleted. That only leaves the data surviving via the "link" provided by that checkbox. So my question is: Where in the file does Excel save the data when the "Save source data with file" checkbox is ticked? How do I get access to it all in tabular form? "Stan" wrote: "InsomniacFolder" wrote: Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee HTH Either set up the pivot table with row totals or column totals, this is done when you right click on a cell in a pivot table and you get "possibilities for the PT. (sorry I use qa foreign language version). The other possibilityis to choose PT wizard while in the pivot table and go back a step and choose the option for " get external data" and see where that leads you. If the main interest is to see what is the data in a set of cells theen I would use the first option, adding totals to columns and rows. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
Hi
The data will be in the Pivot Cache. If you double click on the cell which is the the intersect of Grand total by Rows and Grand Total by columns, a new sheet will open which shows all of the source data. Save this. Create a Table from the data with Insert tabTablemy table has Headers. In the Options tab, give the Table an appropriate name On your PT, place cursor with the PT and from the Options TabChange Data sourcepoint it at your new table name -- Regards Roger Govier InsomniacFolder wrote: Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
View Pivot Table Source data as a Data Table
I see, thank you.
So I'll be effectively creating a new data table from the data generated from the totals cell. That certainly works. Many Thanks, KeLee "Roger Govier" wrote: Hi The data will be in the Pivot Cache. If you double click on the cell which is the the intersect of Grand total by Rows and Grand Total by columns, a new sheet will open which shows all of the source data. Save this. Create a Table from the data with Insert tabTablemy table has Headers. In the Options tab, give the Table an appropriate name On your PT, place cursor with the PT and from the Options TabChange Data sourcepoint it at your new table name -- Regards Roger Govier InsomniacFolder wrote: Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to there (xgraham) is not me, has never worked on this machine, and no longer works for the company. I believe the data table is saved with the PivotTable as a default - if I double click the values fields I do get the sheets created with subsets for that value - where is it stored in the file? There are no hidden (or very hidden) sheets or columns/rows in the file I need to add some columns to the original data table and recreate the PivotTable, but I can't get to the original data set though it must be somewhere because the Pivot Table functions normally without there being any live connections. Any help is greatfully appreciated, Regards, KeLee . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I check/verify data in my Data Source for Pivot table? | Excel Worksheet Functions | |||
View pivot table external data source in Excel 2007 | Excel Discussion (Misc queries) | |||
View Pivot Table Source Data | Excel Discussion (Misc queries) | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions | |||
How do I view Pivot Table source data file and field names? | Excel Discussion (Misc queries) |