Question Regarding File Size

[zakkair]

Hello all,

I have a question regarding whether or not using PowerPivot will reduce the size of this file I have.

Currently, I have a file with about 20 pivot tables, all of them are from the same 60,000 rows of data. The problem with this is that right now, the file is over 100mb and it's starting to get extremely slow. If I were to build all the pivots through powerpivot, would it reduce the size of my file? And if it doesn't, is there another way to make the file smaller?

Thanks so much for any help given!

 

[RoryA]

Do they all use the same pivot cache? That seems like a very big file for that amount of data, so I'd suspect they use different caches, which is inefficient.

 

[tweedle]

I'm not sure about PowerPivot, however...

Inside each Pivot Table are options to Save the Data and Allow Drill-Down (among others)
You can navigate to the settings via: Right Click on the Pivot Table, Pivot Table Options, Data Tab

Un-Checking those will provide file-size savings to some degree.

Below are utilities to cycle through Pivot Tables: One Reporter and one to update settings.
Note: Please run on workbook copies only, until such time that you've determined that changing the settings is right for your circumstance.

Sub ReportActiveWorkbookPivotTableDataOptions()
    'Outputs a txt file report on Pivot Table elements
    Dim wb As Workbook, ws As Worksheet, pts As PivotTables, pt As PivotTable
    
    Set wb = ActiveWorkbook
    fname = wb.FullName & "_PivotTable Report.txt"
    
    fn = FreeFile
    Open fname For Output As #fn
    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            With pt
                Print #fn, "======="
                Print #fn, pt.Name
                Print #fn, Tab(5); "Save source data with file: "; .SaveData
                Print #fn, Tab(5); "Enable Show Details: "; .EnableDrilldown
                Print #fn, Tab(5); "Refresh data when opening the file: "; .PivotCache.RefreshOnFileOpen
                Print #fn, Tab(5); "Source Data: "; pt.SourceData
                Print #fn, "Pivot Cache:"
                Print #fn, Tab(5); "Record Count: "; .PivotCache.RecordCount
                Print #fn, Tab(5); "Memory Used: "; .PivotCache.MemoryUsed
                Print #fn, Tab(5); "Source Data (Cache): "; .PivotCache.SourceData
            End With
        Next
    Next
    Close #fn
retval = InputBox("Report File Created Here", Default:=fname)
End Sub


Sub TrimDownActiveWorkbookPivotTableData()
'Updates Pivot Tables within a workbook to un-check data settings
    Dim wb As Workbook, ws As Worksheet, pts As PivotTables, pt As PivotTable
    
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            With pt
                .EnableDrilldown = False
                .SaveData = False
                .PivotCache.RefreshOnFileOpen = False
            End With
        Next
    Next
End Sub