It seems almost trivial to say that Excel is a spreadsheet application. Yet, many organizations use Excel workbooks as a repository for data, in other words, as a database system. I myself have seen United States military operations executed from worksheets, pretty much because the people involved weren’t quite sure how to use Access. For better or for worse, a great deal of important and useful information is stored in Excel workbooks, and we must learn to deal with it. Nowhere is this more true than in the world of economics.
Many countries and the most important banks maintain databases of economic data and make subsets of this data freely available as pdf files and Excel workbooks. Adobe pdf files are fine for perusing the data, but if you’re an analyst, then only the workbook format will do. How then best to use this data?
In this instalment we will see how to use Power Pivot linked tables to make pivot tables more flexible and informative. For our example data, we will use statistics on external debt from the World Bank. If you want to work this example, you can download the sample data for Excel 2010 or Excel 2013. If you wish to see the completed examples only: Excel 2010 Finished or Excel 2013 Finished.
These data are downloaded from the World Bank as worksheet rows. This is fine, but it hampers our ability to examine this data from different perspectives. What if we wanted to compare data from the ASEAN nations with that from G7 countries? In a relational database this would be easy, but worksheets do not provide a means for easily relating our worksheet of data to a range that defines the groups of nations we would like to compare. Fortunately, Power Pivot does provide a mechanism for accomplishing this important task.
In the example workbooks, the data have been placed into a Power Pivot table. As usual, clicking on the Power Pivot Window button will allow us to examine the data. Right now, the most important thing to note is that in addition to the country name, there is a three-letter ISO standard symbol for each country. This will serve as our key value.
We must then manually construct a range in Excel that defines the groups of nations we would like to compare in a pivot table. This is very easy to do: we only need two columns. In each row, one column contains the name of the group, the other contains the three-letter key for one of the nations belonging to that group. In this example, we use the ASEAN nations and G7; clearly defining your own groups is very easy.
From the Power Pivot tab, choose Create Linked Table. In the dialog box, double check to make sure the range is correct and check the “My table has headers” check box.
Excel will convert your range to a table and immediately create a matching linked table in Power Pivot. It’s always a good idea, Power Pivot or no, to immediately go to the Table Design tab after creating a table and giving the new table an appropriate name. “Table1” and “Table2” just won’t cut it in a professional quality workbook. Unfortunately, Excel will not automatically rename the linked table in Power Pivot. You will have to do that manually.
In the Power Pivot window, you will get an error if you try to update the new Power Pivot table. This is because Excel creates the Power Pivot table before we have a chance to rename it. Clicking the Options button on the error window will give you a chance to correct the problem by pointing Power Pivot to the new Excel table name.
The last thing that remains is to explain to Power Pivot the relationship between the raw data and the newly created linked table of groups. This is done by clicking on the “Diagram View” button on the Power Pivot window ribbon. You should see something like this:
Drag the “Country Code” column from the raw data tables to the group table, and you’re all set.
Now let’s create a pivot table by clicking on the Pivot Table button on the ribbon. You should see the Power Pivot Field List.
Choose Group Name from the Group section and the four quarters for 2014 from the Raw Data section.
The resulting pivot table includes the data we want, but note that all the countries without a group in our linked table are aggregated together in the “blank” group. This is easy to fix by simply filtering out the blank data.
After formatting, our pivot table looks like this:
One limitation of this technique is that we cannot have overlapping groups. For example, we could not compare G7 with the European Union, since they have members in common. Power Pivot will refuse to update the related linked tables if it sees duplicate country codes in the linked Group table.
The addition of linked tables to a Power Pivot database provide an easy and flexible means for imposing organization and grouping on data obtained from research groups in Excel workbook format. Stay tune for my next post where we will learn how to use the standard SQL query language to access data contained in Excel worksheets
For more, have a look at Learning Tree’s 2-day course – Power Pivot for Excel: Mining Data for Business Intelligence.