Excel taking hours to calculate
I have a spreadsheet with 4 tabs and about 60000 rows in each tab, in one of the tabs i have all the formulas like index match, sumif and countif, the issue is for every calculation it is taking hours and excel get stopped how do I do this calculation faster?
John Tsioumpris
How about moving it to Access....just a suggestion...
Benchmark your calcs on 64-bit Excel 2016/Office 365. You'll be pleasantly surprised.
Microsoft made a thousand-fold improvement in the speed of lookups on large lookup tables in the subscription version of Excel about a year ago. Basically, they are indexing the lookup column (just what a database like Access does) so the lookup formula goes straight to the answer. Furthermore the 64-bit version of Office is able to use every bit of memory that you have installed on your computer, whereas the standard 32-bit Excel is limited to 4 GB.
Microsoft made a thousand-fold improvement in the speed of lookups on large lookup tables in the subscription version of Excel about a year ago. Basically, they are indexing the lookup column (just what a database like Access does) so the lookup formula goes straight to the answer. Furthermore the 64-bit version of Office is able to use every bit of memory that you have installed on your computer, whereas the standard 32-bit Excel is limited to 4 GB.
Those are regular formulas or array formulas?
Do the formulas perform any comparison on column values and then return the output? If yes, do the formula use whole column or row references?
Do the formulas perform any comparison on column values and then return the output? If yes, do the formula use whole column or row references?
ASKER
HI John Tsioumpris,
Great Suggestion, never got an used it, not sure how flexible it is as excel. I am currently using power query in excel to import data, delete few columns, add headers, concatenate few columns and finally do index match(not in power query) but outside table. can you please let me know how can i achieve this in access. sorry for too many questions
Great Suggestion, never got an used it, not sure how flexible it is as excel. I am currently using power query in excel to import data, delete few columns, add headers, concatenate few columns and finally do index match(not in power query) but outside table. can you please let me know how can i achieve this in access. sorry for too many questions
ASKER
@Subodh Tiwari (Neeraj),
sir these are most of the index match from two tables (table 1 i sheet1, table 2 in sheet 2) these formulas run across 100 thousand records
sir these are most of the index match from two tables (table 1 i sheet1, table 2 in sheet 2) these formulas run across 100 thousand records
Can you please share those formulas?
Another option would be to place those formulas through VBA and convert them into values. You may have a button called "Place Formulas" and the code underneath that button would place the formulas on the tab and convert them back to values only.
Btw what's the size of your file?
Another option would be to place those formulas through VBA and convert them into values. You may have a button called "Place Formulas" and the code underneath that button would place the formulas on the tab and convert them back to values only.
Btw what's the size of your file?
If you are importing the data with Power Query, is it not possible to merge the tables involved in index/match formulas in the query editor itself?
i.e. import all your lookup tables as connection only and then use merge queries to get the values from the lookup tables?
i.e. import all your lookup tables as connection only and then use merge queries to get the values from the lookup tables?
ASKER
@Subodh Tiwari,
Sir, I am importing two files with power query each one is about 100 MB csv and the other is about 45 MB CSV, I have sto start to use power query lately and I am struggling to add formulas in power query, maily there are three formulas in countif in the same table, index match from different table could you please help me how do i have countif and index match in power query?
I have grouped in power query however it is giving me the results like in excel formula example, if i have a row which is repeated 5 times by excel formula i would get it a s 5,5,5,5,5 and in power query the result is 1,2,3,4,5
Sir, I am importing two files with power query each one is about 100 MB csv and the other is about 45 MB CSV, I have sto start to use power query lately and I am struggling to add formulas in power query, maily there are three formulas in countif in the same table, index match from different table could you please help me how do i have countif and index match in power query?
I have grouped in power query however it is giving me the results like in excel formula example, if i have a row which is repeated 5 times by excel formula i would get it a s 5,5,5,5,5 and in power query the result is 1,2,3,4,5
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Access is a whole different concept compared to excel so its not that easy to give you "a point to start ".
Given the fact that you have too many records it would great to have a small sample to see what kind of workflow you have...Excel its extremely "loose" when it comes to data input ... (hey i got a new value...np just put it in the next available cell) ...compared to Access which demands just about everything to be "in place" before any input.
Given the fact that you have too many records it would great to have a small sample to see what kind of workflow you have...Excel its extremely "loose" when it comes to data input ... (hey i got a new value...np just put it in the next available cell) ...compared to Access which demands just about everything to be "in place" before any input.
Do you need every calculation in the entire workbook to be updated every time you open the workbook?
If not, Excel has an option to calculate values automatically, ...or manually
Excel Options-->Formulas-->Calcu
If you turn off "Automatic", ....you can recalculate the entire workbook at any time by pressing Ctrl+Alt+Shift+F9
These options are also on the Ribbon:
Formulas-->Calculation
If not, Excel has an option to calculate values automatically, ...or manually
Excel Options-->Formulas-->Calcu
If you turn off "Automatic", ....you can recalculate the entire workbook at any time by pressing Ctrl+Alt+Shift+F9
These options are also on the Ribbon:
Formulas-->Calculation
ASKER
@byundt its a 64 bit excel
ASKER
Thanks a lot, sir will go thru what you have provide will work on it further. As usual you are one of the best.
You're welcome Nirvana! Appreciate your feedback!