Very large sheet not processing at once.

[comp]

I have a massive workbook that takes 5 minutes per page to process, once i drop in my data. To allow me to enter in the data faster i switched formula calculation to manual. That way i can drop all the data in, then calculate it all at once, thus saving effort.
However, when i hit F9 it doesnt calculate the whole workbook. I have to go in and do shift+F9 on every page manually. Any ideas how to calculate the whole file at once?

[TMS]

F9 Calculate all worksheets in all open workbooks.
Shift+F9 Calculate the active worksheet.
Ctrl+Alt+F9 Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
Ctrl+Alt+Shift+F9 Recheck dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

Courtesy of: http://www.shortcutworld.com/en/win/Excel_2007.html
http://www.shortcutworld.com/en/win/Excel_2010.html

Why is it so big? How many sheets? How many columns, rows, formulas, array formulas, formatting, Conditional Formatting, full column selection formulas/array formulas, etc on each sheet? Do you have formulas to "prepare" a row for data entry? For example: =IF(A2="","",... a formula dependent on A2 ...)

Seems like you might be addressing the symptom, not the root cause.

Regards, TMS

[comp]

There are 13 sheets, with 140,000 rows and 8 columns per sheet.
There are two columns on each that have =IF() and =SUMIF() thatspan the length of the page (all rows).
These cells are populated when i drop in the data in nearby cells. (copy/paste from CSV file). It takes 7-8 min per page, and it is loaded on an SSD with a fast computer.
I have also tried the Ctrl+Alt+F9 and Ctrl+Alt+Shift+F9 and they do not calculate the whole workbook. It seems the only command that works is shift+F9.

[shg]

Each of the 13 * 140000 SUMIFs references 140000 cells? That is a heap of calculations. Would a pivot table be an alternative?

[TMS]

If you press Ctrl-End on each sheet, does it go to the last cell (column/row) of the data, or does it go way beyond? If it goes way beyond, you need to delete the excess rows and columns (using the Delete icon, not the Delete key on the keyboard)

[xladept]

It would probably be worth your time to use VBA for the big file. Not to mention much neater!

[comp]

Each of the 13 * 140000 SUMIFs references 140000 cells? That is a heap of calculations. Would a pivot table be an alternative?

unfortunately that wouldnt be a good alternative

It would probably be worth your time to use VBA for the big file. Not to mention much neater!

Frankly, Im not sure how to do that.

If you press Ctrl-End on each sheet, does it go to the last cell (column/row) of the data, or does it go way beyond? If it goes way beyond, you need to delete the excess rows and columns (using the Delete icon, not the Delete key on the keyboard)

they carry on into infinity. I will fix this immediately and see how that affects the calculation time/etc.

[TMS]

To infinity and beyond ... Yep, that might make a difference.

[comp]

Shortening those really helped alot Thanks! It still takes quite a bit of time, but its to be expected with this much data i suppose.
I had them going off the sheet because i have variable lengths of data outputs. In the future i will delete the unused cells before i calculate.

[TMS]

You're welcome.

Be sure to delete excess columns too.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[xladept]

It would probably be worth your time to use VBA for the big file. Not to mention much neater!

Frankly, Im not sure how to do that.

You could task this forum for it with an appropriate sample file!

[comp]

Does linking back the results of the calculations also add to the processing time? I link from the =SUMIF() to a sheet that has all of the values in one place.

Of course, i would be interested in using VBA if it were faster...I will post an example file.

[xladept]

Hi comp,

I'll have a go at it and perhaps some other VBA programmers will as well

BTW - Your example has to be less than 1mb

[comp]

I posted a thread for those interested here for the VBA possibility.