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

Finding the Size of Individual Worksheets

Written by Allen Wyatt (last updated December 1, 2022)
This tip applies to Excel 2007 and 2010


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)
        .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
            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


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 (11113) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel 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. ...


Understanding Picas

Word can understand many different measurement units. One common unit understood by Word is the pica, described in this tip.

Discover More

Deleting Multiple AutoText Entries

AutoText is a great feature in Word; it helps you put together documents quickly. If you need to delete multiple AutoText ...

Discover More

Hiding Fonts

Windows makes it easy to manage the fonts installed on your system. One of the lesser-known options is one that allows ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Shifting Objects Off a Sheet

One day you are just editing your worksheet like you normally do, then you see an error that says "Cannot shift object ...

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

Picking Worksheets Quickly

If your workbook contains a multitude of worksheets, the worksheet tabs at the bottom of the program window start to lose ...

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 + 4?

2018-07-12 13:22:47

Michael Davydov

This worked great, thanks! The only thing I would add is "wks.visible=true" right before wks.Copy; otherwise, it will crash if there are hidden tabs, like my file had.

2016-09-04 06:01:29

Willy Vanhaelen


I mentioned this already in my comment of 14 Aug 2014.

The sheet files are called "sheetxx.xml" except when saved as an Excel binary workbook (*.xmlb) then they are called "sheetxx.bin". They always reside in the folder xlworksheets.

If you didn't delete any sheets the name of the files do correspond to the name you see in the VB Editor.

2016-09-02 12:29:08


You can also rename the workbook with a .zip extension and open it up, then look through the files called "sheetXX.xml" and find big ones. Keep in mind though that the name of this file doesn't correspond to the sheet name you see in the VB Editor. For that, you have to open up the XML file in a text editor and find the "codename=xxx" attribute.

2016-07-18 06:07:52



Our project team have been struggling with a shared 40MB workbook for months now. 5 minutes with this macro and we identified one sheet tab being 22MB. Very impressed.

2016-06-06 10:07:32

Robbie Jacobsz

Thank you thank you thank you!

2016-05-16 12:37:08


Very nice!

Worked like a charm.

I'm going to see if I can add hyperlinks within the size sheet for each of the worksheet names.

Still new at VBA so wish me luck.

Thanks again!

2016-05-04 10:18:14

Henk Beersma

Brilliant. Thank you. Just what I needed

2016-04-23 08:50:06

Johann van der Merwe

I have a similar problem and tried this solution but receive run time 1004 error. Application defined or object defined error.

2015-11-30 18:44:36

Ken S

@Willy Vanhaelen,

To identify which worksheet in My Computer is which, I had to open the .xml file for the worksheet and find this line near the top of the xml code:

<sheetPr codeName = "Sheet7">

In the file I'm working on, my largest worksheet in My Computer is called sheet61.xml, but when I open it, it's VBE code name "Sheet7" as listed above. I checked this against the VBE Project Explorer and some of the data in the .xml and confirmed it's indeed Sheet7, not Sheet61.

2015-11-28 12:44:17

Willy Vanhaelen


1. The zip worksheet files are named as in the Visual Basic Editor mostly Sheet1, Sheet2 ... This is not necessarly the order as displayed L-R in the viewed workbook.
2. The zip worksheet Sheet1 does not contain general data. Those are stored elswere i.e. in the _rels folder.

2015-11-27 23:54:37


@ Willy Vanhaelen
I have followed your .zip method and now seek confirmation of a couple of my assumptions.
1. The zip worksheet file numbers are numbered in the order as displayed L - R in the viewed workbook. i.e. Not the sheet numbers noted in the VBE
2. The system stores a lot of general data in the zip sheet No. 1

2015-11-26 04:23:31

Des Lavender

Most excellent Willy. At least the original tip brought me here!

2015-11-25 10:59:36


@Paul Whitaker - in Computer (Explorer), find the folder and select the file name, click again on the name to edit it. Highlight the .xlsx and change to .zip then press Enter or click away from the file name.

2015-11-25 09:40:43

Paul Whitaker

Hi Willy Vanhaelen.

Apologies for my naivity but how can you change the extension to .zip. I am currently using Office Excel 2007.

Thank you

2015-11-03 06:36:47

Willy Vanhaelen

@Andy Thompson

You don't need the macros in this tip. See my comment of 14 Aug 2014.

2015-11-02 17:05:34

Andy Thompson

Have tried the above macro using excel 2010 and each time falls over with run time error 1004. "You cannot save this workbook with the same name as another open workbook or add-in. choose a different name, or close the other workbook or add-in before saving."

If I could get it to work it would be a fantastic help to me.

Look forward to your feedback.

2014-12-02 13:19:54


Awesome! It truly helps. As anyone else commented I had a problem with hidden worksheets but, after show them, it worked like a charm!

2014-11-05 00:36:50

john j

dear Willy Vanhaelen 14 Aug 2014, 08:02 ---- this is easy the best paragraph of info I have ever read I have or should I say had, a 40 MB file that was killing me and the team, using your advice I found that the smallest tab on the workbook that nobody even needed was causing 35 m off the problem thanks you clever man!!

2014-09-09 07:42:23

Willy Vanhaelen

I am refering to the method explained in my comment of 14th August

2014-09-08 10:30:43


Thanks Willy but I dont understand how to get that to work. I've been using the macro and wondered what that unit was in?

2014-09-07 06:10:44

Willy Vanhaelen

In Explorer it's KB as indicated

2014-09-05 11:06:16


This is amazing thank you so much!!!

When it returns the file size what s the unit? like bytes etc?

2014-08-15 08:46:20

Glenn Case


Thanks for the tip! Amazing what even relatively experienced can learn from forums such as these.

2014-08-14 08:02:47

Willy Vanhaelen

There is no need for these complicated macros.

Since Excel 2007 the workbooks are saved with the extension .xlsx, .xlsm or .xlsb. In fact they are zip files. So change the extension to .zip and open the file. You'll see a number of folders. Open the 'xl' folder and then the 'worksheets' subfolder. There you see a list of all the sheets in the workbook. If you select View|Details you'll see the size of each worksheet.

There are even file managers such as Total Commander who can open your Excel file as a folder without changing the extension.

2014-08-13 17:21:02


Fantastic! Helped me find and fix a 10 MB tab full of (formated) blank cells.

2014-07-30 12:59:34

Juan patino

This can help if you want to paste it in your personal Macros:

Sub Sizereport(name1 As String)
'este sub pesa cada hoja para detectar cuál es la hoja que le da más tamaño al archivo
Dim wks As Worksheet
Dim c As Range
Dim mat()
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

fila = Workbooks(name).Worksheets.Count
Set c = Range(Cells(1, 1), Cells(fila + 1, 2))
mat = c
mat(1, 1) = "Nombre de la Hoja"
mat(1, 2) = "Tamaño Aproximado (MB)"
Application.ScreenUpdating = False
' Loop through worksheets
For i = 1 To fila
Set wks = ThisWorkbook.Worksheets(i)
If <> sReport Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sFullFile
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
mat(i + 1, 1) =
mat(i + 1, 2) = FileLen(sFullFile) / 1000000#
Kill sFullFile
End If
Next i
c = mat
Application.ScreenUpdating = True

End Sub

2014-03-03 19:28:07


Also worked for me.

2013-05-23 03:43:47

Mark Schwarz

Excel Seattle, I fixed this by making all all hidden sheets visible, first. Add this bit of code to the top of Allen's routine.

' First, make all hidden sheets visible, so they can be selected later
For Each wks In ThisWorkbook.Worksheets
wks.Visible = True
Next wks

2013-04-19 18:11:34

Mike R.

Thank you for the VBA code! It worked great

2013-04-08 11:55:40

Excel Seattle

I added this macro to my personal workbook and tried to run on a different file, and it error'ed out saying "Select method of Worksheet class failed."

I then added it to the workbook itself that I was testing, and the error this time said "Method 'Copy' of object_Worksheet' failed.

Any tips on implementing?

This Site

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

Newest Tips

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.