Break link from source data to pivot table
At present you cannot send the output of a pivot table without the link to the underlying data - but often I want to do exactly that - share the results without sharing the base data.
Cutting and pasting the pivot table works but loses all the careful formatting (actually worked much better up to Excel 2010, now entirely broken).
So please can we have a method of preserving the output from pivot tables but delinking them from the source?
Anonymous
shared this idea
If we break the link to the data, the PivotTable will no longer be interactive and won’t support expand/collapse, filtering, or sorting. Copy/Paste into Paint or any other graphics program will preserve an exact picture of the PivotTable with all formatting. In any event, this item isn’t likely to be addressed soon.
23 comments
-
Malcolm
commented
The best work around at present is to use Power Query rather than a Pivot Table, then you can delete the query and the table stays with all the formatting you have used.
If there are specific features of a pivot table you need which are not available easily in Power Query then create a Power Query that dumps an intermediate summary of anonymised data into another table (which you are prepared to give to the third party) and use that as the basis of the power pivot.
Neither are ideal or as elegant as simply de-linking a pivot table though. Hopefully Microsoft will reconsider this thought. It would be so much more useful (to me at least) than some of the features which they have added and which must surely involve a lot more coding effort for them than this.
-
Anonymous
commented
Thanks for the response, however I think it misses the point (may be a difference in perspectives of a developer vs. an end user of the product).
I'll try to explain. I find pivot tables incredibly useful and powerful in making sense of large quantities of data.
One key use I have is where I have a series of pivot tables on a sheet showing various summary information (approx 20). I don't want the person I share this with to have access to the base data as it would cause privacy issues. They do however want to make further use of the values in the cells (so pictures wouldn't help). So currently to create the flat tables I have to copy/paste values and then do a lot of manual formatting to make the data presentable again.
As you can appreciate from the Excel users who have expressed this as an issue, along with the many a varied attempts to find workarounds, many users are frustrated by not being able to simply copy/paste a pivot table as a standard 'flat' table along with the formatting.
-
Malcolm
commented
Not really an answer. One would not expect it to be interactive. That is the point of breaking the link. If the breaking of the pivot table turned it into a table it would still support expand/collapse, filtering and sorting.
We don;t want a picture as that will definitely not allow expand/collapse. filtering and sorting and will break any links out from the pivot table (should there be any) not only those in. Using a picture also involves a fundamental change to the spreadsheet rather than a simple breaking of a link.
It's rather like requiring people to take pictures of worksheets in a workbook in order to send the workbook to somebody else rather than merely breaking the links in the workbook. That would clearly not be sensible and nor is the work around proposed.
Please reconsider.
-
Space
commented
You certainly have another solution without having to copy and paste. You can right-click Tab from your original data file, and select Make a Copy onto an Empty excel Workbook. That copy you made would still contain all the collapse property and filters of your original pivot table. While your recipient can have functional flexibility but they will not access to the Original Data file. Whatever they do on their end, will not affect your data file. If you are concerned about the Pivot Table List and filters, you can hide them all in the Background.
This should help someone.
-
clint
commented
Yes, of course you can copy/paste to break. But what we need is to maintain the *********** EXPAND / COLLAPSE ************ (ie, folding) functionality.
-
Muneer Valappil
commented
Try copying a portion of the pivot table using Cntrl C and then pasting it in another location using Cntrl V. You can delink the entire table by leaving the first row of the table and copy/paste the rest.
-
Stacy Davis
commented
copy and pasting, then reformatting..really? This is very time consuming and so 2010. There must be a better way. Next excel version.. please give us an option to unlink the data source and covert to standard table. Cant be that hard. Thank you!
-
Heba Aboulnaga commented
Simple trick: Copy and paste the table starting by choosing the table with extra cell/row from above or below the table....voila.
-
Jen
commented
I wish for the ability to copy and paste values and formats without the underlying data too. I have found if I copy and paste the pivot table into Word or Outlook and then copy from there and paste into Excel, it accomplishes this goal.
-
Mark
commented
I have found copying as a picture is a good workaround if you don't want to format the chart further
-
Akos Groller
commented
There is an ugly but pretty concise workaround for that.
The key is that if you copy and paste only part of a Pivot table, Excel will implicitly dispose of the data linkage, furthermore it won't assume that the conditional formatting is for the Pivot report (thus not copying it when you request to paste values and formats).
In most cases, you're well off copying first the page fields and headers, then in a separate step all the rest (row labels, data and column totals).
You might also do the trick by adding a needless page field at the top (if you do have some unused field), then copying all except that top-row entry. Again, the Pivot link will break up and Excel will deem the formatting as applicable to the content, not the structure - as intended.
Finally, if you need to detach in-place (e.g. your fancy conditional formatting links the surrounding areas), copy the entire sheet out to a new workbook, then copy back the pivot as values to break data link and make the range editable, finally copy back the pivot report in parts as above. There still can be cases when this messes up something (e.g needless localization of global named ranges), but often it'll do the job for you. -
Kevin
commented
This work around doesn't really meet my company's business needs. We have a massive data file across 24 clients, each with 10-250 user IDs registered to them and the output is every screen visited in a third party mainframe and each time they visited it last month for each user id. All told I have ~140k records with 6 fields that need to be sorted first into client, then into user ID, then display the screens for each of them. This report gets viewed by all the team leads at our company (because we get charged for each screen load in this mainframe) and in order to let them sort through the table effectively I need to maintain the collapse/expand features of the pivot tables. The clipboard workaround doesn't achieve that.
Please change this feature in future updates of excel! I'm sending out an 8 MB report every month which is being downloaded onto roughly 100 different computers on our network. That's 9,600 MBs of space being taken up on our network every year just from this one report!
-
Rene Noel Cruz commented
Why can't Excel do this from within Excel if:
In SSRS, i can connect to a SSAS Tabular Model, create a pivot table and save the report to Excel with the link disconnected to the data source (report now has static data), the table format intact and the row drill down still working. -
Adarsh Madrecha
commented
This is a very much required feature.
Somewhat hacky solutions exist (link below), but excel supporting it by would be great to have. -
Anonymous
commented
I agree on the importance of being able to delink the data source from the results. I want to copy the pivot table and manipulate it, but can't due to the linking of the data source.
-
Dural Lash
commented
Thanks for raising this issue,
I have found I can select the pivot table copy to a new sheet, maintaining the original pivot.
I then paste special as values in the new sheet, then go back to the original copy table again and then paste special formats.Hope this helps
You used to be able to copy the P table, then paste special values and then paste special formats which was quick and easy :)
regards
Epi -
Tina W
commented
Thank you Thomas K. I have unselected the "save source data with file", and it did break the link. The problem is anyone open the excel can select the "save source data with file" and see all the detail data. Any suggestion to solve this?
-
Thomas K
commented
In the pivot table options, there is a fan called 'data'.
In that fan there is a paramter called 'save source data with file'.
Unticking that, seems to me, must be your solution. At least short term. -
Tim Green
commented
Thomas K, you are right, you can paste values, then formats. Unfortunately, most of the formatting features of a pivot table are completely lost when you do this. Borders, shading and bolding is lost. All that is pasted correctly is row heading indentation and number formatting.
-
Malcolm
commented
Please, please sort this. Every week I need to send the results of a number of pivot tables to a third party without any references to the source data. At present I have to copy the values to a new file, which breaks all the formatting and then go through and recreate all the formatting manually. Even though I am using conditional formatting and vba to help in this task, what should be any easy thing to do takes ages.