Large File and Need to Have Formulas

[jonathanchi]

Hi Everyone -

I am working on a large data file and was looking for a schematic (or list) of how exactly excel formulas work

For example:

=if(B2=1,1,0) - would just look at one column (B) and one row (2) and compares that to a value (1) so the processing time for when I try to copy it down my workbook the processing time is minimized

=vlookup(1,D:G,3) - looks through entire column D for matching values to 1 then it searches for three columns over; I would imagine this would take excel longer to complete

My excel file is 1,000,000 rows by 500 columns, so I would imagine that the file takes a lot longer than I'd hoped for because it has multiple vlookups. I could be completely wrong.

If someone can point me in the direction of a schematic that has the order of formulas working, that would be amazing.

Thanks all.

[#2]

@jonathanchi 

Can you expand further on what you mean by "a schematic that has the order of formulas working". All aggregation, logical and lookup/reference formulas will work, it depends on what you are trying to calculate.

One warning is that an excel should not be treated as a database, as it is very limited and the runtime to do any operation is longer and more prone to crashes. If you are interested in different schematics to work with your data, I would recommend indexing your data to make it more manageable. For example, separating the large file into different tables/spreadsheets that can then be joined and queried through PowerQuery or VBA.

[#3]

@jonathanchi 

Of course VLOOKUP() doesn't check cells sequentially one by one, that's more complex mechanism. And it depends on which version of Excel you are, couple of years ago performance of such functions was dramatically improved. Now, in addition to VLOOKUP() and INDEX/MATCH we have XLOOKUP().

More about that is here

Excel performance - Tips for optimizing performance obstructions | Microsoft Docs

Excel performance - Performance and limit improvements | Microsoft Docs

Excel performance - Improving calculation performance | Microsoft Docs