Excel Report – A Pivot Table Review
Has the world become Pivot table crazy?
The fact is that every user who has learned to use PTs have experienced a tremendous rise in their productivity. Not only due to the speed of creating reports but by the quick insights you can get from large data sets.
If you present a report of Sales by Product and by Region and someone ask you, what if… You would say let me open the file and drag-and-drop it right now.
Here are the pros of why you should use Pivot Tables
- Summarizes thousands of rows in seconds.
- Changes the layout of report by simply dragging and dropping.
- Formats your report quickly.
- Slices and dices your flat table by any category.
- Allows you to add calculated columns.
- No formulas needed (at least you use calculated fields).
- Does not consume enough memory resources.
- Formats data ready to be analyzed.
- Allows you to connect to external databases: OLAP, SQL server, Access, etc.
Here are the cons of why you should not use Pivot Tables
- Does not allow you to see the source of values reported as in formulas (by pressing F2). Anyone wants to see where the data came from so they can easily check the formula.
- Does not offer flexibility to arrange customized reports in cells.
- Does not refresh the outputs immediately when the backend list changes.
- Clutters the workbook if there are dozens of little Pivot Tables everywhere.
- Occupies space in the worksheet.
- Does not offer easiness of moving the results across the worksheet.
- Does not allow to edit calculated fields directly in the cells.
- Offers limited supported functions in calculated fields.
Conclusion.
I am a fan of Pivot Tables but I am a fan of Lookup formulas too.
If you are looking for what-ifs calculations by changing the inputs, data Lookup formulas would be a good choice. On the other hand, If your input data is relatively static but you want to do what-if analysis with drill-down, then Pivot Tables are a good choice.
Unfortunately, the limited aggregate functions available in a Pivot Table, the impossibility to embed outputs in other formulas and the limited functions supported in a calculated field make the use of formulas unavoidable.
