I have a workbook, with three sheets and only a couple rows in one sheet and around 100 rows in another sheet (with only three columns) that is about 50MB.

I used Ctrl+Shift+Down to enter formulae across all the rows, is that the culprit?

  • Some info would help: how big is the file now, how big do you expect it to be,etc. That said, if you are entering formulae in ALL the cells of a column, the file wont be very small for sure – Akash May 31 '12 at 18:22
  • @Akash About 50 MB, is this acceptable? What other way I have other than enter formulas in all cells of a column if I don't have how many rows user would input? – TPR May 31 '12 at 19:34
  • 50MB is huge for what you say you have. At work we have spreadsheets with 10 or 20 or so tabs, one of which has sometimes 10s of thousands of rows of data over a couple hundred columns. This is probably around 50 MB as well. What you have should be a few hundred kilobytes. Do not copy your formulas down an entire column. Akash's solution will work for you. Or, if you'll only ever have 100 rows in there, maybe copy your formula down 200 rows. That's not going to make a big file. – GeoffDS Jun 1 '12 at 17:42

Ok, so you have an unknown number of rows into which data will be entered and a formula will be applied row wise right?

Create titles for each column and enter the formulae in ONE row: the row below the titles

Then, use the format as table tool to create a table out of those 2 rows

Now,as users will add data to the table row by row, the table will get extended and the formula will automatically be copied to the next row


You could try opening a new (empty) workbook, and copying/pasting all cells from each existing worksheet into corresponding sheets of the new workbook. The ranges sound small enough, it should be a few minutes work.

Save the new workbook and see the difference in file size. If useless bloat has crept in, this may fix it.


Expanding on the broken links comment;

In your check for links to other worksheets, also include Named Ranges. I had a worksheet that I had attempted to copy off a single tab using Copy to New Workbook. I dutifully broke the link to the original worksheet using Edit Links ... Break Link, and thought that would do the trick. The file was still way too big.

I then went back in and looked at the Formulas ... Name Manager and found a bunch of broken references to ranges that were in the prior sheet. These references were originally used in VLOOKUP's, but not in the sheet that I had copied out. I deleted all these bad references and saved and the size dropped to where I expected it to be (< 50K).

My suspicion is that Named Ranges cause Excel to retain the data behind them, even if they are not being used in the worksheet.


Copied from SO:

Extraordinarily large (bytes) workbooks with very little apparent content are all too often created by data dumps into Excel.

One reason can be that the extraction code has been included with the data (check Developer, Visual Basic).

More likely to have a significant size impact is formatting, that can be detected by Editing, Find & Select, Go To Special, Last cell and observing the highlighted cell is a long way from the last ‘occupied’ cell. This can be fixed as below:

  1. Select row immediately below the last ‘occupied’ row.
  2. Ctrl+Shift+Down.
  3. Del.
  4. Save.

Size may also be increased by embedded objects (check Editing, Find & Select, Selection Pane) and also the content of Comments even where these are not displayed. [The specific answer to the SO question.]

Pivot tables with broken links may also be a cause.

Other causes can include the likes of the apparently blank ‘last cell’ indeed being occupied (say with a font colour to match the background) or other hidden objects.

Note that in all cases the file size may not reduce in response to ‘deletions’ until it is saved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.