Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Graphth Jun 1 '12 at 17:42

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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