Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by SCarroll9025
at 2024-07-22 09:09:26
Point:500 Replies:3 POST_ID:829073USER_ID:10
Topic:
Spreadsheet Software;;Microsoft Excel Spreadsheet Software
I have 1 excel document that is very slow to open. It's not a very large file but there are lots of formulas. Any ideas on how to speed this up?
Expert: Rob Henson replied at 2024-07-23 08:48:33
To you as the user, it might not appear to be a large file but what physical size does it take up on the disk?
Does the file use the Shared Workbook feature? This is renowned for being unreliable and one of the corruptions it can cause is file size bloat. So a file that doesn't visibly contain a lot is bloated and takes up masses of space.
Another option would be the Used Range on each sheet is out of kilter. On each worksheet/tab in the file press End then Home and check where the cursor ends up. If it goes to the bottom of right of known data, wher you would expect the bottom right to be, then the Used Range for that sheet is OK. If it goes way beyond, lots of columns to the right or lots of rows below then you will need to reset the Used Range for that sheet. Easiset way to do this is to manually find the bottom right of the data and then select all columns to the right and delete them; actually remove the columns so they are replaced with blank standard width columns. Repeat for surplus rows below the known data area. Return the cursor to the top left and Save. Pressing End + Home should now only go to bottom right of expected range.
You say you have masses of formulas. What sort of formulas, most formulas don't use much resource unless they are particularly complex with multiple nested functions or large ranges used within the parameters, also array formulas are quite resource hungry. Likewise excel prefers to calculate formulas in a particular order working from left to right, top to bottom. So if you have lots of formulas that rely on results of formulas elsewhere, if excel has to go back round re-calculating what it has already calculated it will take longer.
Where are you opening it from, Local or network?
Do the formulas link to other workbooks? If so are these workbooks located in same location?
Does it take a long time on all machines? Can you boost the spec of the particular machine?
Thanks
Rob H
Does the file use the Shared Workbook feature? This is renowned for being unreliable and one of the corruptions it can cause is file size bloat. So a file that doesn't visibly contain a lot is bloated and takes up masses of space.
Another option would be the Used Range on each sheet is out of kilter. On each worksheet/tab in the file press End then Home and check where the cursor ends up. If it goes to the bottom of right of known data, wher you would expect the bottom right to be, then the Used Range for that sheet is OK. If it goes way beyond, lots of columns to the right or lots of rows below then you will need to reset the Used Range for that sheet. Easiset way to do this is to manually find the bottom right of the data and then select all columns to the right and delete them; actually remove the columns so they are replaced with blank standard width columns. Repeat for surplus rows below the known data area. Return the cursor to the top left and Save. Pressing End + Home should now only go to bottom right of expected range.
You say you have masses of formulas. What sort of formulas, most formulas don't use much resource unless they are particularly complex with multiple nested functions or large ranges used within the parameters, also array formulas are quite resource hungry. Likewise excel prefers to calculate formulas in a particular order working from left to right, top to bottom. So if you have lots of formulas that rely on results of formulas elsewhere, if excel has to go back round re-calculating what it has already calculated it will take longer.
Where are you opening it from, Local or network?
Do the formulas link to other workbooks? If so are these workbooks located in same location?
Does it take a long time on all machines? Can you boost the spec of the particular machine?
Thanks
Rob H
Expert: duncanb7 replied at 2024-07-22 09:19:19
Last post is for Excel 2003
For Excel 2007, click office logo->Excel options->Formulas->uncheck
auto and check manual,
Excel 2010: File > Options > Formulas > Workbook Calculation > Automatic
save and re-open the file
Duncan
For Excel 2007, click office logo->Excel options->Formulas->uncheck
auto and check manual,
Excel 2010: File > Options > Formulas > Workbook Calculation > Automatic
save and re-open the file
Duncan
Expert: duncanb7 replied at 2024-07-22 09:13:46
it might be the time to do re-calcuation for all formula
You can turn it off by un-check automatic and click manual at
click tools->options->calculation
save and re-opened the file
Duncan
You can turn it off by un-check automatic and click manual at
click tools->options->calculation
save and re-opened the file
Duncan
