Excel issues

Having issues with a very large spreadsheet. Need to know how to be able to edit the large spreadsheet without machine degradation. Machine freezes usually when editing. Machine is I7, 8gb, 500HD.
UnidineITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please define "large". If you're talking about disc size then look at each sheet and delete most if not all of the unused row,
0
arrorynCommented:
When you say "large" what's the complexity? I doubt with your spec if you just had Excel open the machine would have problems.

What version of Excel are you using, and is it fully patched?

Is the Excel sheet referencing external sources? I would say that's most likely the source of your issues. Alternatively, where is this Excel sheet stored? If you're accessing and editing it over the WAN, try it on your local HDD.
0
UnidineITAuthor Commented:
Office 2010
Document is locally stored on machine.
Has over 200,000 lines on multiple pages.
Document is over 100 mbs.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Martin LissOlder than dirtCommented:
Has over 200,000 lines on multiple pages.
Are all of them used, or are a significant number blank?
0
UnidineITAuthor Commented:
Majority are used, and they document will grow in size.
0
Martin LissOlder than dirtCommented:
Try saving it as an xlsb file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
Do you have very many formulas using volatile functions (OFFSET, INDIRECT, RAND, CELL, TODAY, NOW)? These need to update each time any value is entered or changed in any worksheet in any open workbook.

Do you have very many formulas using VLOOKUP or HLOOKUP? These need to update whenever you change data (even if unimportant) in one of the columns in the lookup table.

Do you have very many array formulas? These include both non-CSE formulas using SUMPRODUCT, AGGREGATE or LOOKUP and Control + Shift + Enter (CSE) formulas. Though array formulas can do wonderful things, they do tend to be somewhat expensive computationally speaking.

The end result of any of the above issues is the need to perform a CPU-intensive (and slow) recalc. You can't enter more data until the recalc is complete.

There are workarounds for each of the above issues. One of the easiest is to change the calculation mode from Automatic to Manual. You then F9 to force a recalc when desired.

If you just need to deal with large amounts of data, take a look at the PowerPivot add-in. I've watched it fly through calculations on 100 million rows of data.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help. Out of curiosity how much smaller did the workbook get?

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.