(Using Excel 2007.)
I'm looking for advice about the performance (i.e., speed) of using pivot tables vs. data tables (Insert > Table) when you need to pull data from a very large worksheet of 10 columns and 100,000+ records. Is one typically faster than the other?
My worksheet looks like this:
[pre]
[/pre]
I need to return lists of rows that match selected criteria (e.g., a list of schools whose Type is "Private", a list of students whose grade is "1st", etc.). To do this I created a separate pivot table for each construct (one for schools, one for students, etc.). The dashboard cells refer to the pivot tables. For example, the area where the school list is shown has rows like this:
IF(LEN(PivotA!A10)=0,"",PivotA!A10))
IF(LEN(PivotSheet!A11)=0,"",PivotSheet!A11))
etc.
When the user wants to see a list of "Public" schools, he selects "Public" from a combo box and a macro updates the report filter of the School pivot table to show only Public schools. When he clicks on a school in the returned list, a macro updates the filter of the Student pivot table to show a list of students in that school. Etc.
I have three pivot tables and each gets re-filtered based on the user's selection(s). Sometimes multiple criteria are selected, so multiple report filters get updated. This is producing a performance problem - it sometimes takes about 10 seconds to filter each table and return the results.
Would it be faster if I convert the worksheet to a data table and base the dashboard directly off that? I'd use INDEX and MATCH statements to return the schools, students, etc. that meet the criteria. This would also avoid the need to manage multiple pivot tables. But performance may be worse with a data table of 100,000 records.
Any suggestions?
I'm looking for advice about the performance (i.e., speed) of using pivot tables vs. data tables (Insert > Table) when you need to pull data from a very large worksheet of 10 columns and 100,000+ records. Is one typically faster than the other?
My worksheet looks like this:
[pre]
Code:
Student Grade School Type GradRate
Bobby 1st John Middle Private 75%
Mary 2nd John Middle Private 75%
Jane Kind Smith Elementary Public 50%
etc.I need to return lists of rows that match selected criteria (e.g., a list of schools whose Type is "Private", a list of students whose grade is "1st", etc.). To do this I created a separate pivot table for each construct (one for schools, one for students, etc.). The dashboard cells refer to the pivot tables. For example, the area where the school list is shown has rows like this:
IF(LEN(PivotA!A10)=0,"",PivotA!A10))
IF(LEN(PivotSheet!A11)=0,"",PivotSheet!A11))
etc.
When the user wants to see a list of "Public" schools, he selects "Public" from a combo box and a macro updates the report filter of the School pivot table to show only Public schools. When he clicks on a school in the returned list, a macro updates the filter of the Student pivot table to show a list of students in that school. Etc.
I have three pivot tables and each gets re-filtered based on the user's selection(s). Sometimes multiple criteria are selected, so multiple report filters get updated. This is producing a performance problem - it sometimes takes about 10 seconds to filter each table and return the results.
Would it be faster if I convert the worksheet to a data table and base the dashboard directly off that? I'd use INDEX and MATCH statements to return the schools, students, etc. that meet the criteria. This would also avoid the need to manage multiple pivot tables. But performance may be worse with a data table of 100,000 records.
Any suggestions?
