Excel 2003 File Size

[ukdane]

How can I find out what is making a workbook file size so big?

What should I look for as reasons?

Is there a program that can tell me what part of a workbook is taking up so much room (amount of sheets/rows/columns in use, changes in layout/font colour/size etc etc)?

[Bob Phillips]

The usual things are

- colour
- custom formats
- conditional formatting

[ukdane]

WHen you say cusotm formats, would changing the way in which a date is presented cause major problems?

What about how variables are declared?

If I standardise the font for each worksheet, would that change the file size?

From what I can see, when the file was saved on friday last week, it jumped from about 6Mb to 106Mb!

It is a shared document (I know, I know), but I have removed the history, and also removed the autosave function. There are currently 10 worksheets, with the columns not going further that BM on any sheets, and no more than 130 rows (on one sheet) being in use.

There are a few hyperlinks, but I have had other worksheets, with more pages, rows and hyperlinks in use, and that hasn't filled more than 20 Mb max (normally 5Mb when I kick out users- reset the shared doc).

[Bob Phillips]

Standardisng the fonts would help, but you would need to delete all the unused formats.

I can't see variable declaration matter, that is more a runtime matter, not a file size.

I can't advise much about shared workbooks as I never use them, they are just not worth the problems that they cause.

6Mb to 106Mb is ridiculous though. Have you tried a 'Detect and Repair' on it?

[Marcster]

The following Add-In from Microsoft removes access formatting from Excel files:

http://www.microsoft.com/downloads/details.aspx?familyid=ecfd076c-b873-48cc-b842-da999c848c82&displaylang=en

Marcster

[ZamBeato]

That doesn't sound like the normal 'spreadsheet bloat' that can occur over time. Are there any pivot tables in the file? I have seen file sizes increase like you described when users expand pivot table ranges. For example, when the source data for a pivot table increases often, a user (trying to help) may set the pivot table range to include entire columns to allow for future additional rows. That will greatly increase file size and performance.
Also, similar scenario with VLOOKUPS if the user references entire columns, it will increase size and reduce performance.

[ukdane]

Nope, no pivot tabels.
I tried removing all the data from the worksheets, but the file size remained. I also tried repairing the workbook. But the file size remained 106Mb.

I've now copied the data into a new (backup copy) of the program, and the filesize is down to under 2Mb. Although the new version doesn't include any of the hyperlinks that were in the initial program. (I should point out that I don't think these were the cause of the file size bloat).

[Bob Phillips]

The thing to do now is to add the other functionality one at a time, save the file each time, and see which if any bloat it.

[geekgirlau]

Also check that the used range doesn't extend below the data. You may have 130 rows containing data, but when you press [Ctrl-End] you'll see what Excel is treating as the last row.

[Aflatoon]

Just to check, have you read this MSKB article (http://support.microsoft.com/kb/930253)?

[ukdane]

Geekgirlau: Yup did the CTRL-END thing on all the worksheets (including the hidden ones) as one of the first things, as that has previously been a problem in a different workbook. Some of the sheets extended to 650 lines, and I reduced them, but even 650 shouldn't cause this bloat.

Aflatoon: No, I hadn't read the article. However, I have now, and there is nothing in the article that I a) didn't know already b) hadn't tried already. Purging ghost users (and zeroing the history) is normally enought to reduce the file size. And what we usually do on a daily basis, as file sizes can quikly bloat to 20Mb But in this instance it didn't work, and the bloat is exceptionally large.
(Thanks for the tips though :-) )

[Paul_Hossler]

I've had 2003 files get huge, and had to copy/paste data into new workbook, run the modules though CodeCleaner, etc.

Fixed the problem.

My best guess was that somehow the file got corrupted behind the scenes where none of the usually tricks (suggested by others in this thread) could get to it.

Once I moved the WS data etc. over to a new WB, the problem stayed behind and everything was ok


Paul

[jolivanes]

I found this in my "saved examples".
Make sure to try it on a copy first.


Sub ExcelDiet()
'Run this code if macros or workbook seem to be running slowly -
this worked brilliantly in October 2005, 'reducing file size from 4850kb to 3230kb!!!
Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In Worksheets
With ws
'Find the last used cell with a formula and value
'Search by Columns and Rows
On Error Resume Next
Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
On Error GoTo 0
'Determine the last column
If ColFormula Is Nothing Then
LastCol = 0
Else
LastCol = ColFormula.Column
End If
If Not ColValue Is Nothing Then
LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
End If
'Determine the last row
If RowFormula Is Nothing Then
LastRow = 0
Else
LastRow = RowFormula.Row
End If
If Not RowValue Is Nothing Then
LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
End If
'Determine if any shapes are beyond the last row and last column
For Each Shp In .Shapes
j = 0
k = 0
On Error Resume Next
j = Shp.TopLeftCell.Row
k = Shp.TopLeftCell.Column
On Error GoTo 0
If j > 0 And k > 0 Then
Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
j = j + 1
Loop
If j > LastRow Then
LastRow = j
End If
Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
k = k + 1
Loop
If k > LastCol Then
LastCol = k
End If
End If
Next
.Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete
.Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete
End With
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub



If this doesn't work, I have some more you could try (on a copy)

HTH

John


To Bob (xld)

Bob,
I think you corrected me once on a similar question. However, this is quite a while
ago and so far I have not been able to find this thread. Maybe your memory is
better than mine!!!!!