Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Finding the Size of Individual Worksheets.

Finding the Size of Individual Worksheets

by Allen Wyatt
(last updated June 23, 2018)

1

Maarten wonders if there is a way to find out the size of each worksheet in a workbook. He has a workbook with almost 100 worksheets and he wants to reduce the size of the workbook file. However, he doesn't know which worksheets are the biggest ones in size.

Figuring out the "size" of individual worksheets depends, in large part, on what is meant by "size." Does it mean the number of cells used? The columns and rows used? How much text is stored in the worksheet? The list of metrics could go on and on.

The problem is that questions such as these miss the mark; a worksheet can have many, many items stored on it. For instance, it could contain comments, formulas, text, charts, sound files, and any number of other items. One chart may be larger than another in terms of numbers of cells, but the other could be larger in terms of objects (such as charts or PivotTables).

The only real way to compare relative sizes of worksheets is to save each worksheet out into its own workbook and then examine the size of each resulting workbook. This obviously doesn't answer precisely how large each individual worksheet is because the act of saving a workbook introduces additional overhead into the saved file. However, if each worksheet is saved in the same way, each one will have comparable overhead and thus can be compared to each other to see which is larger.

The following macro adds a worksheet to the current workbook in order to record the sizes of each workbook created. It then steps through each worksheet and saves it into an individual workbook. The size of the workbook is then determined, recorded, and the new workbook deleted.

Sub WorksheetSizes()
    Dim wks As Worksheet
    Dim c As Range
    Dim sFullFile As String
    Dim sReport As String
    Dim sWBName As String

    sReport = "Size Report"
    sWBName = "Erase Me.xls"
    sFullFile = ThisWorkbook.Path & _
      Application.PathSeparator & sWBName

    ' Add new worksheet to record sizes
    On Error Resume Next
    Set wks = Worksheets(sReport)
    If wks Is Nothing Then
        With ThisWorkbook.Worksheets.Add(Before:=Worksheets(1))
            .Name = sReport
            .Range("A1").Value = "Worksheet Name"
            .Range("B1").Value = "Approximate Size"
        End With
    End If
    On Error GoTo 0
    With ThisWorkbook.Worksheets(sReport)
        .Select
        .Range("A1").CurrentRegion.Offset(1, 0).ClearContents
        Set c = .Range("A2")
    End With

    Application.ScreenUpdating = False
    ' Loop through worksheets
    For Each wks In ActiveWorkbook.Worksheets
        If wks.Name <> sReport Then
            wks.Copy
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs sFullFile
            ActiveWorkbook.Close SaveChanges:=False
            Application.DisplayAlerts = True
            c.Offset(0, 0).Value = wks.Name
            c.Offset(0, 1).Value = FileLen(sFullFile)
            Set c = c.Offset(1, 0)
            Kill sFullFile
        End If
    Next wks
    Application.ScreenUpdating = True
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11112) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Finding the Size of Individual Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Footnotes within Footnotes

Need to add footnotes to your footnotes? It's actually allowed by some style guides, but Word doesn't make it so easy.

Discover More

Finding the Number of Significant Digits

When looking at a number, you may wonder how many significant digits it contains. The answer is not always an easy one, ...

Discover More

Printing a List of Building Blocks

Building Blocks are a great way to semi-automate the creation of common documents. At some point you may want to get a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Relative Worksheet References

Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it ...

Discover More

Determining a Worksheet's Number

When you add a new worksheet to a workbook, it receives a meaningful name such as "Sheet4" or "Sheet17." If you want to ...

Discover More

Forcing a Worksheet to be Protected Again

Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 2?

2011-12-31 02:47:48

sourav gangawat

I tried the code, just wanted to share few observations

I ran this code on a file (file size 100kb), when the individual sheets size were added it came to be 250 kb approx.

Did not understand what went wrong.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.