Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Large File and Need to Have Formulas

Copper Contributor

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 Replies


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.


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