Question Why is my bloody Spreadhseet so large?

Discussion in 'computers, web and general tech' started by belboid, Nov 5, 2018.

  1. belboid

    belboid TUC Off Your Knees

    I have written a Timesheet for work, and it seems to work fine. Hours worked, fine. It keeps track of your holiday allowance as well, let#s you know just how much you have left etc etc. It can add up any time off sick and other such useful little additions too. It does everything we want it too, and is generally pretty straightforward.

    The only problem is that it is ridiculously big and can take yonks to open (and heaven forfend if you want to open two such timesheets at the same time!). Each sheet is 2.85Mb, and I need 14 such sheets per timesheet.

    My initial thought was the conditional formatting - it changes certain rows and cells' colour if you are on Annual Leave or whatever. First off, I made sure every row and column other than the ones containing the actual Timesheet were completely clear of data, and formatting. This made the sheet increase in size to 47.6Mb. Which seems odd. I then removed all conditional formatting from the original spreadsheet. This made absolutely no difference whatsoever. I can't see that it does anything that a bog standard, 100Kb, file wouldn't do. So what might have made the bugger so bloody big?
  2. Supine

    Supine Rough Like Badger

    Try copying the cells being used into a fresh spreadsheet. See if that reduces it.
    Pickman's model likes this.
  3. 8ball


    Try working fewer hours.
    Chilli.s, two sheds, Fez909 and 8 others like this.
  4. belboid

    belboid TUC Off Your Knees

    I copied one sheet from the 29.8Mb workbook and made a new file. That was the 2.85Mb version
  5. FridgeMagnet

    FridgeMagnet Administrator

    Excel sheets just get larger, that’s what they do. You could put it on google docs, or export it to another format then import it again.
    Badgers likes this.
  6. belboid

    belboid TUC Off Your Knees

    It hasn't grown over time though. It has started off like this, without inputting any actual data into it.

    Logically, I know there must be some other data hidden somewhere, but where could it be if I've already done the 'clear all data' thing?
  7. FridgeMagnet

    FridgeMagnet Administrator

    I mean that the file size gets larger without any actual data being added because Excel is shit and adds loads of inefficient guff that you can’t see or get rid of. Word does as well. That’s why I suggest exporting and importing again, which might cut some of it down.
    belboid and Badgers like this.
  8. Saul Goodman

    Saul Goodman It's all good, man

    What are you opening it on, a ZX81?
    2.85Mb is nothing. If your PC is struggling to open it, there's probably something wrong with your PC.
  9. Ted Striker

    Ted Striker Foot's on the other hand

    Step 1 - on each tab, press ctrl+end (it selects the last cell 'stored' in the memory)

    If the selected row is waaaaay past what your actual last row is, then the sheet/tab has had editing done to increase the memory require to store the sheet. Delete the rows, from the-row-beneath-the-last-actually-used-row, to the bottom row, 1048576 (you can aid the selection process by holding down shift and ctrl) I.e delete the rows you consider empty below your data. Then do the same for columns.

    Step 2...let me know how step 1 goes first - it's the usual issue if the file size has jumped by such magnitude.

    Also, save files as xlsb in future, it halves-ish the file size of the other formats.

    Also x2...have you got calculation set to manual?! This couldd also be a problem...

    (Posting on a bus so excuse brevity...)
    belboid likes this.
  10. two sheds

    two sheds Least noticed poster 2007

    And/or waiting for (for example) references to other documents?
  11. belboid

    belboid TUC Off Your Knees

    A: As mentioned, 2.85Mb is the sheet, not the file. It isn't a particularly large file, per se, just a very large file considering what it is being asked to do
    B - 30Mb shouldn't be a problem, and isn't for any other file of similar size, or even much larger. So it aint that.
  12. belboid

    belboid TUC Off Your Knees

    90.3Kb now! Which is somewhat better.
    And that got it down to 28Kb.

    Ta muchly the pair of you
    two sheds likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice