Excel Gone Very Slow

Hintza

I have a large spreadsheet running and have just gone over 2,600 lines ever since then it takes an age to perform any task (cut and paste etc) other sheets in the same file with lower numbers are still functioning fine.

Is there a limit on the number of lines on Excel for safe running? and have I reached it?

johnmc

Probably down to the size of the file and what's on those 2,600 lines.

Take a look at whether you've got "auto recalculate" turned on. If so it will check the whole sheet everytime you update a cell. While your at it look at the Autosave options.

Are you using Conditional formatting? Any unnecessary formatting will also slow it down, as will complex linked formulae.

With 2,600 lines are you using the right product for the job? Would Access do it better?

Hintza

Thanks john, yes I know I am using the wrong product but its yeas since I did anything in Access!! I tried once to set it up in access but gave up when I couldn't get any formulaes to work.

Would you like the job? Or would anyone like the job? I'll pay!

MercilessKiller

Just to let you know the new Office 2007 supports up to 1 million lines with no problems May have better support for large systems?

johnmc

1 million lines? And how are you supposed to 1) read it 2) follow it!

If it's 1 million records then you're using the wrong product. That's what databases do.

Hintza........ for advise on database creation try here. If you get stuck pm me. No promises.

Hintza

Hmm just spent a few hours upgrading to Office 2007. And no difference..damn.

Think I will split it...should have been split anyway into years.

Will have a look at access again but...yikes scary.

Hintza

Now excel is just hanging in compatability mode, whatever that is and I have to force closure. Had to do all my entries onto paper in the meantime...arghhh.

MercilessKiller

Have you thought about using something like Oracle and ASP to sort it all out?

Its a performance setting it up and learning SQL (though you can use MySQL which is a web based version and add the data that way so no need to use code).

Oracle would run on a server and you add tables and then data obviously. You can take the information and manipulate it very easily and also use .ASP web code to create a proper web based system to access your database. You can also use .ASP with Microsoft Access.

It may sound a bit hard if you've never touched it before, but it's one of those things, once you've done it once it's easy!!!! Installing Oracle is the hardest part but many tutorials around...

Sorry Office 2007 didn't help.

And to the other person, ye I agree... 1 million lines is simple allowing for bad systems! I mean, my matched betting system has one sheet with data entry and a macro to add it to the second sheet for a record... I could use access for this but its easier in Excel. If I did 1 million matched bets... I'd have a problem :P

Hintza

Thanks for all the replies.

I have managed to fix it by cut and pasting the basics into a new work book without all the calculations and the redoing all the calculations via cut and paste.

It was a somewhat laborious project and initially I couldn't even select the whole document to cut and paste. But job is done and I now have a a very slick worksheet again. Obviously some error had crept in from all my entries (many are cut and pasted from external sources which was where I think an error crept in).

I also latterly started to get a error message saying there was a conflict with Adobe Acrobat.