I build an Excel 2007 spreadsheet which contains a larger table with source data (about 500,000 rows and 10 columns). I need to extract data from this large table for my analysis. To extract and aggregate data I usually use sumif, vlookup/hlookup and index+match functions.
I recently learned about the existence of the function getpivotdata, which makes it possible to extract data from a pivot table. To be able to use it, I first need to convert my large source table to a pivot table and after that I can extract data using the function getpivotdata.
Would you expect a performance improvement if I would use getpivotdata to extract and aggregate data instead? I would expect that within the underlying Pivot object aggregated values are pre-calculated and therefore performance would be better.
If performance would be better, are there any reasons not to follow this approach? To be clear, there is no need to refresh the pivot table because it contains source data (which is located in the beginning of the calculation chain).