Analyzing Data: Functions or PivotTables

Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, discusses two different approaches to analyzing data in Excel.

Edit: I've attached a file at the bottom of this blog that contains spreadsheets of the examples discussed in this post.

My earlier blog on the new Excel 2007 function SUMIFS spawned a very interesting discussion (thanks to everyone who posted comments there): when trying to analyze/aggregate data in a table, how do we decide whether to use functions versus PivotTables?

This blog outlines reasons to use one option or another. To better illustrate the two alternatives I’ll consider a real estate inspired example: let’s say I have list of homes for sale and their corresponding characteristics:

and I’m trying to find the average price for those homes which have at least 3 bedrooms, a garage and are between 5 and 10 years old. Just by looking at the table, we see that only house3 and house4 satisfy all conditions and the average of their prices is 312,500.

Here is a functions based solution:

=AVERAGEIFS(G2:G6,C2:C6,">2",E2:E6,"yes",F2:F6,">1999",F2:F6,"<2004")

which returns $ 312,500.

To build a corresponding PivotTable, one can use several filters:

Advantages of PivotTables:

1. While AVERAGEIFS is limited to using at most 127 conditions, PivotTables can handle more than 127 conditions.


2. The elements of the set that fulfill all conditions are listed in the resulting PivotTable.


3. PivotTables have a lot of flexibility: the ease of use offered by the new UI allows for a very quick detailed analysis of different available options. Nested layers in a PivotTable offer added results visualization.


4. Several different approaches are possible: one can construct different PivotTables that answer the same question above.


5. Particularly useful for large data sources: when relying on an external data source, you don’t need to bring all the data in Excel and one could, for example, use OLAP databases.

Advantages of using functions:

1. It’s easier to see in one glance all the conditions being used either by looking in the formula bar, or by listing all criteria in separate ranges.


2. The result updates immediately when adding rows to the source table, while PivotTables need to be refreshed.


3. The criteria, when referenced in a cell, could be as well the result of another formula, while value filters in PivotTables can only use constants.
   For example, in the formula above, one could replace
   =AVERAGEIFS(G2:G6,C2:C6,">2",…
   with
   =AVERAGEIFS(G2:G6,C2:C6,I4,…
   where I4 contains another calculation ( e.g. =">"&FIXED(SUM(1,1),0)).
   The image below shows a corresponding PivotTable filter:
   
   The last field will not accept formulas, only numbers.


4. Formulas take little space and are easy to move around in a sheet.

Note that in both cases you can use the wildcard characters to define criteria. Also both solutions deal in a similar manner with missing data or errors in the range.

I don't know if I can be fully objective on this question because I'm more of a formula person myself. If I've missed a reason you should pick one approach over another, feel free to let me know the reason you use formulas or PivotTables for summarizing data by leaving a comment.