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?

You Can Work With External Data In Excel 2016 For Mac Mac

How to Use Linked Tables to Facilitate Analysis of Power Pivot Pivot Tables 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. M ramadan ismaili. If you want to work this example, you can download the sample data for.

If you wish to see the completed examples only:. 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 nations and; 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. Changing the table name from “Table1” to a more readable name.

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.

Pivot Table with “blank” group After formatting, our pivot table looks like this: Final Pivot Table Have you noticed the limitation? 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. Conclusion 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 contained in Excel worksheets For more, have a look at Learning Tree’s 2-day course –.

Share this article:.

By Excel 2016 allows you to consolidate data from different worksheets into a single worksheet. Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets. For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years. If you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets. However, even when the data entries are laid out differently in each spreadsheet, Excel can still consolidate them provided that you’ve used the same labels to describe the data entries in their respective worksheets. Most of the time, you want to total the data that you’re consolidating from the various worksheets. By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category).

You can, however, have Excel use any of other following statistical functions when doing a consolidation: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, or VARP. To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data.

(If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidating sheets in different workbooks, open a new workbook. If the sheets in the various workbooks are generated from a template, open the new workbook for the consolidated data from that template.

Before you begin the consolidation process on the new worksheet, you choose the cell or cell range in this worksheet where the consolidated data is to appear. (This range is called the destination area.) If you select a single cell, Excel expands the destination area to columns to the right and rows below as needed to accommodate the consolidated data.

If you select a single row, the program expands the destination area down subsequent rows of the worksheet, if required to accommodate the data. If you select a single column, Excel expands the destination area across columns to the right, if required to accommodate the data. If, however, you select a multi-cell range as the destination area, the program does not expand the destination area and restricts the consolidated data just to the cell selection. If you want Excel to use a particular range in the worksheet for all consolidations you perform in a worksheet, assign the range name ConsolidateArea to this cell range. Excel then consolidates data into this range whenever you use the Consolidate command. When consolidating data, you can select data in sheets in workbooks that you’ve opened in Excel or in sheets in unopened workbooks stored on disk. The cells that you specify for consolidation are referred to as the source area, and the worksheets that contain the source areas are known as the source worksheets.

If the source worksheets are open in Excel, you can specify the references of the source areas by pointing to the cell references (even when the Consolidate dialog box is open, Excel will allow you to activate different worksheets and scroll through them as you select the cell references for the source area). If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you don’t type =). For example, to specify the data in range B4:R21 on Sheet1 in a workbook named CG Music – 2014 Sales.xlsx as a source area, you enter the following external reference: 'CG Music – 2014 Sales.xlsxSheet1'!$b$4:$r$21 Note that if you want to consolidate the same data range in all the worksheets that use a similar filename (for example, CG Music – 2012 Sales, CG Music – 2013 Sales, CG Music – 2014 Sales, and so on), you can use the asterisk (.) or the question mark (?) as wildcard characters to stand for missing characters as in 'CG Music - 20??

Sales.xlsxSheet1'!$B$4:$R$21 In this example, Excel consolidates the range A2:R21 in Sheet1 of all versions of the workbooks that use “CG – Music – 20” in the main file when this name is followed by another two characters (be they 12, 13, 14, 15, and so on). When you consolidate data, Excel uses only the cells in the source areas that contain values. If the cells contain formulas, Excel uses their calculated values, but if the cells contain text, Excel ignores them and treats them as though they were blank (except in the case of category labels when you’re consolidating your data by category).