PivotTable vs. Formulas

[Stoner7Matt]

I'm building a template to do a basic margin calculation on 20,000 rows of data. What advantages would there being either a Pivot Table or Formula in your experience. What is the most efficient?

I personally like Pivot Tables but I need to talk my supervisor into using them over sumifs.

 

[Fazza]

hi,

Suggest you describe the specific situation. Without knowing what is being done, my guess is that a pivot table is more efficient and offers simpler & better functionality.

Is the data in the same file as the template doing the calculation? Cause if the data is in a database, text file, csv file, etc then obviously the pivot table would be preferred. Avoids filling Excel with 20,000 records.

FWIW, I never use SUMIFS and work most days with datasets exceeding 50,000 records: queries and database type approaches, like pivot tables, are great for this. Formulas are not. If using formulas, how do you create the entries to sum against? Via formulas over huge ranges is slow. Best to work entirely without formulas (so no formulas in workbook) in my experience.

If an option for you, what about using Access?

cheers