Excel Memory Use & File Size

Hi people.. I'm using Excel 2010, I have a workbook with 5 sheets. They have some formatting. The problem sheet is the helper sheet. It has about 30 columns with formulas. I was going to have some of the formulas go to the end of the sheet, but abandoned that idea when it started crashing. That one sheet alone is over 20mb, it has minor cell background colour formatting in the columns with formulas to make it easier to read. Is it normal for excel to consume so much space?
When trying to do anything with it now, Excel complains there's not enough memory. I've got 4gb. Is that normal?
It's driving me nuts. It's the sort of thing you expect with freebie software, not from the biggest software company. Especially as they made the operating system too.
Any Ideas what's causing it?
Thanks

Hi teylyn.. I found that by open & repairing the file, copying sheets to another book & opening them in their own books was the way to find which sheet was causing the grief. With the helper sheet I found by deleting all the cells from 50000 to the bottom of the sheet, it brought the file size down to 8 mb. It still takes a little while to open. Not the make a cup of coffee time the whole book takes. Basically I think the workbook's stuffed now. I've tried opening it via open & repair, extract the data & recover the formulas, deleting the cells, rows & columns in the helper sheet. It bleats about a lack of memory & wont save without Excel crashing. I think I'm going to have to do & lot of copying & pasting to make another workbook. What makes it worse, I had done a backup this afternoon while the file was behaving. I tried opening that & it spat the dummy again.
What's the point in a million rows if you can't use them. You cant use keyboard shortcuts to fill the formula to the bottom without it crashing. Yep MS get the blame.
I was/am a user of OpenOffice for years. I gave Excel a bash because the help here & the internet in general is really good. I've learned heaps.Especially macros. OO macros are a nightmare to try & learn. Next to no information.

Well now the situation has been forced on me lol. I've got no choice but to find a better way. The reason I was trying to use all of the column, was to make sure the friend of mine & my wife who are going to be using it, will never run out of space. He's the reason I tried excel too, office came with his laptop.
I will explore the tables feature, you never know, I might learn some more I haven't got a clue how to use them

Originally Posted by martindwilson

if you could type into that much space it would take a long time, say you typed martin into a cell that would take say 2 seconds thats 24 days of continuous entry!

Going on that theory, if I typed Phil in, it should take about 16. But with my keyboard skills more like 32.

I've had a search & it looks like the memory thing is a pretty common problem. Maybe they're all trying to do what I did.

I've looked at tables. I couldn't see a way of making that work for my purpose. I think I'd better not drag the formulas too far down.
I've just worked out that with the average amount of data that is likely to be entered, it would take 19 years to use 10000 rows. That's 2 entries per day, 5 days a week. I reckon I could halve the amount of rows with no problems.
Thanks for the replies

I'll do that if I can. I'm trying to resurrect the file. There's one column that keeps making excel crash. I'll persevere

Have you had a look at using a database? They tend to be geared to more data, you can then pull queries into excel for summary reports etc.

Originally Posted by Kyle123

Have you had a look at using a database? They tend to be geared to more data, you can then pull queries into excel for summary reports etc.

It has crossed my mind. But you can fit what I know about databases on a pin head lol

I think maybe it's time to learn...

I've stolen this from DaveGugg's sig, but it's a great starting point http://www.deeptraining.com/litwin/d...aseDesign.aspx

Originally Posted by TMShucks

@Philb1: the other option, if you're OK with a VBA solution is a Worksheet Change event monitoring the input columns and populating the formulae on a row whenever any data is entered in the row.

That's something I've thought about too. I don't know how to do it though. There are a few macros in the workbook, I tried removing them to see if they were the cause. They're not. I managed to clear that problem column, I had to do it a couple of hundred cells at a time. 50000 in all lol. There's still something corrupting it because when I try to save it, it either takes an eternity or it crashes.
This all appears to have come from saving 10 columns to the bottom of the sheet. Just as well it wasn't more. Does this qualify as a valuable lesson ?
As soon as I've got it in a stable readable condition I'll post it.. At the moment it's a mess.

I've managed to get the file to open & stay open. I'm copying the formulas from it to a new workbook, basically starting again. If it plays up when it's set up, I'll be back. Otherwise I'll start a new thread regarding event handling macros. I'll do that when I've got more time.
But it does look like excel has problems with memory usage. That's what I've summised from my googling

Thanks TMS I will.
The very first time I had a play with a spreadsheet was when MS works came with one of my first (386/486) computers. I could have sworn it came with some sort of auto formula advancing feature similar to the event handling we're talking about. I'm probably wrong lol. That was the only contact I've had with spreadsheets until about 4 months ago.
When I've got it sorted & working, I'll post it here for you all to see & hopefully suggest some improvements. I have 3 dynamic charts, so I'm sure there's a way of making that work more efficiently
Cheers