Excel has a new feature available in the 2016 version (free add-in download is available for 2010 and 2013 versions of Excel). This feature is called Power Query and the purpose is to make it easier to create pivot tables. The feature takes advantage of the process that data analysts use to organize data: ETL – extract, transform, and load. This process helps to whittle down the extracted data (data dump from the accounting software) to obtain and retain the necessary data and format it before creating a pivot table. This reduces the extraneous and cumbersome data that can clutter a pivot table once it is in pivot table format.
The process includes approximately eight steps that organize the data before loading it to a pivot table. The details of the process and a sample Excel data set can be found in the December 2017 Journal of Accountancy using the following link: