As a CPA and a power Excel user, my goal in Excel has always been to create useful reports and analysis for audit documentation as well as to meet my client’s need for information and reports. I quickly discovered that the best tool in Excel to meet my goals was to learn and master the capabilities of pivot tables. Learning pivot tables has increased my efficiency in almost every project that I have worked on and has been the tool I most often turn to in order to complete projects in a timely manner.
As great as pivot tables are they do have limitations, for example pivot tables do not work with data sets larger than one million rows. Pivot tables also can only work on one data set at a time. If I want to create a single report from multiple data sets, I must use V-Lookup formulas. If I want to perform calculations in my pivot table, I must use Calculated Items and Calculated Fields, which are cumbersome to work with and are not well supported by the Microsoft team.