• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Does excel calculate correctly - always?

Caravishah

Caravishah

New Member
Hi chandoo,

Firstly you're doing an amazing job. Thanks.

My issue:

I'm working on an excel that is about 70 MB (in xlsb) with more than 80 sheets. I hope you recognise what kind of dinosaur I'm dealing with. :)
The file has all sort of functions that can make excel unstable like offset, etc. Obviously the file is not on automatic calculation mode.

I applied a simple sum formula to about 15,000 rows today and Pressed F9. The cells were not calculating the formula. Pressed Ctrl Alt F9 - didn't work. Selected the range and pressed Shift F9 - didn't work. I had to manually go to each cell and press F2 and enter for formula to calculate. Obviously I didn't do that for 15k rows.

Which brought me to a question - was excel calculating everything in my workbook all this while? Have had similar experiences before where despite doing all the F9s and saving the file excel didn't calculate the cells.

Please share your views. Thanks.
 
Just how long do you think such a large file with countless formulas and functions is going to take to run through all the sheets.
Set it running get a good book and a large amount of coffee and wait.
 
Hi ,

There are a few things you can try :

1. Press CTRL END and see where the cursor is placed ; pressing CTRL END takes the cursor to the last cell of the worksheet which has been used for any purpose. See if this cell is really the last cell which has been used for any purpose.

Sometimes , even formatting a cell creates a UsedRange ( as it is called ) which extends over hundreds of columns and probably even a million rows. This can add to the recalculation time.

To then make this UsedRange represent accurately the last used cell , you will need to delete the unwanted columns and rows , and save the workbook ; not only will this make a difference to the recalculation time , it can also reduce the file size.

You can check this out in every worksheet of the file.

2. Using VBA , you can recalculate ranges. Open the Visual Basic Editor , and in the Immediate window , type in the command :

Activesheet.Range("A1:A30").Calculate

Change the range reference to what ever range you wish to recalculate.

3. There are Excel macros available on the Internet to measure the recalculation times for individual worksheets in a workbook ; running this macro on your workbook will tell you where the maximum time is taken.

You can go through the following link for more details :

http://www.decisionmodels.com/calcsecrets.htm

Narayan
 
Thanks bobhc & Narayan for replying back.

@Narayan-
I have done all I could have to reduce the file size, i.e removed unwanted rows and column using Ctrl End, checking for unwanted objects, unwanted defined names, using xlsb rather than xlsx/xlxm (well I know theoretically it doesn't change anything in the underlying excel except for binary mode rather than normal), etc.

Reducing the size is not the concern here. The focus of the question is (sorry if I was not clear in my post earlier) - Does excel always calculate every damn thing on that kind of massive spreadsheets? The sense I get is - it is unable to calculate everything all the time. I know it sounds impossible but that's what I'm seeing on my workbook.

The VBA you have provided works only for a range. I need a solution for entire workbook which surely works, unlike Ctrl Alt F9. Thanks for the resource on identifying the sheets taking lot of time.
 
Hi ,

If you go through the posted link , you will understand how Excel recalculates , based on dependency trees. Excel will never recalculate every cell on every worksheet , unless the formulae have been created to make this happen. In well designed workbooks , recalculation time is purely a function of the intensive calculations which are needed for the application , and not a function of workbook design.

In badly designed workbooks , unfortunately , the design can impact recalculation time.

Narayan
 
Thanks Narayan once again. I tried googling for the macro that shows me which sheet within a workbook is taking a lot of time to calculate/recalculate but couldn't find one. Could I ask you to post the link please? I found one but it isn't working. Thanks very much.
 
You must log in or register to reply here.
Back
Top