none
PowerPivot - How save pivot table without data model RRS feed

  • Question

  • Hi All,

    Is there anyway we can save a Power Pivot table without the underlying data model. Say for example I have model and Power Pivot workbook. Now I have created a pivot table with right filters and data. I want to save the table figures and formats only as a new worksheet and then email it to a  co-worker. I don't expect the co-worker to work with the pivot table or model. He/she only need to receive a static formatted report. Is it possible?

    In this scenario we don't have sharepoint or office 365

    thanks

    Sonny 

    Tuesday, January 28, 2014 7:56 AM

Answers

  • Hello Sonny,

    You can copy the pivot table as a "static content" to a new Excel sheet.

    Mark the pivot table and copy it, open a new sheet, do a right mouse click on it => "Paste special" and select "MS Excel 8.0 Format", then the pivot table content will be copied as plain cell values.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, January 30, 2014 2:06 PM
    Moderator
  • thanks but we also want to formats in the table.

    Then copy the pivot table with "Paste Special" with option "Keep Source Formatting" or "Other Paste Options" => "Formatting"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by sonnyA Thursday, February 6, 2014 4:23 AM
    Friday, January 31, 2014 7:30 AM
    Moderator

All replies

  • Hi Sonny,

    If you create a PivotTable based on your PowerPivot data model, we must save all of PowerPivot data within the model. So, the answer is NO.

    In addition, we can use VBA email pivot table via Outlook 2010. Please refer to the following article:
    http://www.rondebruin.nl/win/s1/outlook/mail.htm

    If you have any more question regarding VBA email PivotTable report, I would suggest you post the question in the forum below:
    Excel IT Pro Discussions: http://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, January 30, 2014 9:49 AM
    Moderator
  • interesting question, i thought just copy the sheet to a new/empy workbook would do the trick...but unfortunately this copies also the PP model to new workbook. Also copy/paste with Option "insert values only" has the same effect..... so making a screenshoot or VBA seem to be really the only Option (like Elvis suggested)
    Thursday, January 30, 2014 1:51 PM
  • Hello Sonny,

    You can copy the pivot table as a "static content" to a new Excel sheet.

    Mark the pivot table and copy it, open a new sheet, do a right mouse click on it => "Paste special" and select "MS Excel 8.0 Format", then the pivot table content will be copied as plain cell values.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, January 30, 2014 2:06 PM
    Moderator
  • thanks but we also want to formats in the table...idea  is for use to send the data table for some else to see with since formats
    Thursday, January 30, 2014 9:42 PM
  • thanks but we also want to formats in the table.

    Then copy the pivot table with "Paste Special" with option "Keep Source Formatting" or "Other Paste Options" => "Formatting"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by sonnyA Thursday, February 6, 2014 4:23 AM
    Friday, January 31, 2014 7:30 AM
    Moderator
  • Your question is essentially how to "Remove" the (probably painfully large) data cache from a Power Pivot table effortlessly and seamlessly. Such removal is possible for native Excel PivotTable cache or an (MDX-driven) Excel PivotTable connection to Microsoft SQL Server Analysis services. Either of those two forms of cache or connection can be immediately removed by opening the "Connections" dialog, then selecting the offending cache or connection and clicking the "Remove" button.

    The answer for Power Pivot tables is ALMOST as effortless and seamless (at least in recent - perhaps too recent - versions of Excel). Click your "Manage Data Model" button. In the "Power Pivot for Excel" window, click on the tab(s) in the lower left corner that is labeled with your dataset name, right-click, then click "Delete". You will get a Yes/No message box asking "Are you sure you want to permanently delete this table, including any associated measures? Click Yes. Close the (now empty) "Power Pivot for Excel" window.

    At that point the cache is cleared out of memory, but all data and formatting remains intact in the client workbook. You can now save the (probably comfortably small) file.

    Tuesday, July 16, 2019 8:40 PM