Many financial professionals spend a long time putting together tables that summarize and aggregate data. Typically, these tables draw information from different columns and rows within the spreadsheet. However, as the underlying information changes on a daily, weekly, or monthly basis many hours can be spent updating the spreadsheet.
Pivot tables can help minimize the effort of updating the spreadsheets by automatically collating and aggregating data. They make data aggregation much faster and much more flexible. Pivot tables allow users to easily change the way the data is summarized depending on their needs.
Let us assume we’ve been asked to put together an analysis on the fees charged for each service (product) by our consultants for the first quarter of the year.
The data on the fees has been exported from the sales system into an Excel spreadsheet, but we need to present the information in a clear and user-friendly format. It has been suggested that pivot tables should be used to organize the data so that the various stakeholders can manipulate the data afterward to their liking.
In the video below, we construct a pivot table in Excel, showing which services brought in the most revenue, which consultants billed the most work, and the total values for each.
Note that the video makes use of keyboard navigation and shortcuts, but a computer mouse can also be used as an alternative for much of this.
The key is to do any housekeeping on the (imported) data before setting up the pivot table. In our example, the imported data has been cleaned in that every row and column is completed, that is, all data is consistent and has been spell checked.
There are many functions within Excel that are useful to identify duplicate information or incorrect data. It is best practice to use these tools to check the data thoroughly before setting up a pivot table.
For example, Excel includes functions such as conditional formatting which has a setting that can quickly format duplicated information. By formatting these data automatically, users can scan the information and check if everything seems correct before setting up the table.
