For decades, Excel users have prepared reports using formulas and PivotTables. Typically, we export data from an accounting system, copy and paste it into an Excel workbook, clean and prepare it for use, and then use PivotTables and/or functions such as VLOOKUP and SUMIFS. That workflow, however, is manual and slow. The good news is that Excel 2016+ for Windows Offers Improved PivotTable capabilities.
Beginning with Excel 2016 for Windows, Microsoft has included some built-in power tools we can now use to streamline our reporting process. In previous versions of Excel, these capabilities were available by downloading and installing the Power Query and Power Pivot add-ins. In Excel 2016 for Windows, however, these tools are integrated and easy to use.
How to Build a PivotTable with the Data Model
Traditional PivotTables are a very useful feature of Excel, but they are not without their limitations. Many of these typical restrictions, however, are removed when you use the data model rather than a single Excel table. If you’d like to learn how to build a PivotTable using the data model, check out my presentation at Accountex 2018 — or check out the following article on my Excel University blog.
What Is the Data Model?
The data model provides a way to organize tables and formulas that can be used in a PivotTable. The data model comes with Excel 2016+ for Windows, and was formerly available as the Power Pivot add-in.
Building a PivotTable from the data model rather than a single Excel table offers numerous advantages. Here are just a few to get us started:
- We can create a PivotTable that uses various fields from multiple tables.
- The formulas we can write far surpass those available in a traditional PivotTable. A language called DAX is used to write the formulas, and it provides many powerful functions.
- We can pick and choose rows and columns using named sets.
- We can directly connect to the data source (instead of having to copy/paste data into a worksheet), use a Get & Transform query (to clean the data before it arrives), and connect to multiple data sources (e.g., a csv file, a database table, and an Excel workbook) in a single model.
- Once built, we can just Refresh the report in subsequent periods (rather than having to go through the whole export, clean, import, and merge into a single data table process).
And, these are just a few of the highlights.
In my Accountex presentation (and the article mentioned above), I will lead you through building a PivotTable from two tables. I’ve created a video and a full narrative with all of the step-by-step details.
PivotTable from Many CSV Files
In my Accountex presentation I will also show you how to summarize data from multiple CSV files with a PivotTable. Specifically, we’ll use a Get & Transform query (Power Query) to retrieve and prepare data from numerous CSV files. Then, we’ll send the query results into the data model (Power Pivot). Then, we’ll build our summary report using a PivotTable based on the data model. There is also an article on this topic on my Excel University blog.
We’ll prepare our report using these steps:
- Retrieve with a Get & Transform Query
- Relate Tables in the Data Model
- Summarize with a PivotTable
We’ll walk through each step, one by one. Note that the steps I will take you through are performed with Excel 2016 for Windows — and use features that are not available in all versions of Excel, namely, Get & Transform queries (Power Query) and the data model (Power Pivot). In addition, the navigation and dialog boxes may differ from the version of Excel you may be using right now.
You can visit the Microsoft website for more information about these features, how to enable them, and which versions include them.