Choosing Between Functions And Pivot Tables In Excel
Excel can perform very complicated analyses on data, but often the trickiest part of the exercise is deciding what approach will get you the results most efficiently. Microsoft’s Excel blog offers a useful summary of the pros and cons of two common data analysis approaches: building formulas or using the pivot table function. The author admits his own bias towards formulas, but pivot tables also have some advantages (particularly if you’re working with external data). If you’ve got your own rule of thumb for making choices in these kinds of situations, share it in the comments.
- Next Post: Make Sure You’re Counting All Your Debts »
- « Previous Post: Last.fm No Longer Free For Aussies
Comments
To build these functions, it is easiest to build the formula using the mouse. Type an equals sign in a cell outside of the pivot table and then use the mouse to click on a cell inside the pivot table. Excel will handle the details of building the references. In the image above, months and years are grouped fields, created from the date field. Excel help doesn’t document that the month field should be specified as 1 for Jan, but you can learn this from observing the results from allowing Excel to build the GetPivotData. Of course…to use this feature, you have to re-enable the Generate GetPivotData feature that you might have previously disabled.