Question Regarding File Size

zakkair

New Member
Joined
Jan 29, 2013
Messages
39
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!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top