Using External Data with Excel Workbooks

Most pivot tables in Excel are built using a simple Excel list as the data source. This is the default in the pivot table wizard. We can also base pivot tables on external data. Typically the external data is taken from a database or text file, however there are also advantages to working with external data when the original data is kept in Excel worksheets. This example uses Excel 2003, you can do the same thing in Excel 2007, but the dialogs are slightly different.

Let's say we have a range containing hours worked on a project by each department. We create a named range called 'HoursWorked' to hold this data. We could use OFFSET and COUNTA to create a dynamic range if this was a real application, but for this sample we only need to create a simple range.

Assume that each department charges a different hourly rate for their work. This is kept in a separate worksheet range called 'DeptRetes' as shown below. 

If we want to show Total Cost = Hours * StdCost, the usual method in Excel is to use a VLOOKUP formula to put StdCost for each department in a column to the right of the 'HoursWorked' range. We could then use a pivot table to summarise our results.

Another way of doing this is to save the data in a workbook and use that workbook as an external data source for a pivot table. To do this, first close the data workbook. We cannot use an open workbook as an external data source. Then open a new workbook and create a pivot table. In step 1 of the wizard, check the 'External data source' option. 

Click next, then click the 'Get Data' button. Choose 'Excel files' as the data provider and make sure that the checkbox 'Use the Query Wizard to create/edit queries' is not checked. Then click 'OK' and locate the Excel file containing the data. Each named range in the data workbook is available as a table in MS Query. You can add tables to the query designer, and click-and-drag between table columns to create relationships. In fact, this is very similar to the Access query designer.

Here we build a relationship between the two tables using [Department] as the key.

When you have finished building a suitable query, click the fourth button from the left on the MS Query toolbar (it looks like an arrow pointing out the door) to return the data to Excel. This returns to the pivot table wizard where we proceed to define the pivot table.

In this example we can create a calculated field to define [Billable] as the product of [Hours] and [StdCost] as shown.

The finished pivot table can look like this.

This method of relating data ranges can be used with several data sets that are kept in separate sheets in one workbook. It allows for reasonably complex database-style queries to be used entirely within Excel.

I really like your presentation. I have struggled with getting this to work in the past.

Questions:

1. What would be the value of saving the query? How would that be re-used?

2. Will this work with dynamic ranges as source tables?

I normally do not save the query. However it can be useful as a backup, particularly if you build complex queries and need to change the data source at a later stage. Saved queries are plain text files (with a .dqy extension). If you move the datasource, you can edit the file DSN with notepad easily enough.

The technique shown will certainly work with dynamic ranges. I did not mention dynamic ranges only because I wanted to keep things simple.

I tried to  set up the same example using dynamic source ranges, however the MS Query does not recognixe the data tables any longer. Are there some tricks involved?

Whoops, my mistake. You cannot use dynamic ranges with MS Query. The usual workaround is to place each data range on a separate sheet and define range names like this;

HoursWorked =Sheet1!$A:$E

This assumes that column headings are in the first row, and there is nothing below the data. The range includes all rows in columns A to E, but MS Query ignores empty rows.

Thanks for the clarifiaction, ed. In my situation, I will probably use an event to copy the dynamic ranges to static "export" ranges, since I am dealing with data modified real-timeby users, not just accumulated in a big table. But I like the empty rows part.