Can an Excel 2007 Pivot Table merge data from different tables like a database?

Question

I want to analyze data with a pivot table in Excel 2007.

I tried to construct an easy example to explain my question below:

• I have a table (Table2) with data sorted by Person.
• I want to analyze those data with a Pivot Table.
• I want to add additional data about each person to the analysis, which are shown in Table 1.

I could firstly merge those 2 tables to something like Table 3 which could be analyzed with the Pivot Table, however this would lead to a lot of duplicate information (Company name and age are repeated in each row).

So my question is:
Could a Pivot Table in Excel 2007 do that for me and use Table 1 and Table 2 as data and "aggregate" them itself based on the "primary key" Name (given the fact that the name is unique, for sure)?

Solution 1

Unfortunately, the short answer is no. However, there are some possible workarounds.

1. If you want to try Excel 2013, the new Data Model is designed to do just what you're asking-tie multiple data sources/tables into a single Pivot for analysis.

2. If you upgrade to Excel 2010, you can use the free PowerPivot add-in from Microsoft. Again, this will allow you to use multiple data sources/tables in a single Pivot.

3. If your data is being served from a SQL compliant database, you can write a SQL view/stored procedure to do all of your linking for you, then use that modified data source for your Pivot (I use this all the time and it works great).

4. Last possiblity you've already identified, merge your 2 tables into a third and use that one for your Pivot analysis. Although, you could probably streamline your data a bit with the creative use of some functions to pull only the info you want to analyze, thereby reducing some of your redundancy.

Solution 2

You can consolidate using the Pivot Wizard (alt d, p). But you can't change the columns specifically like you can in 2013.

Solution 3

Another alternative would be to use your Table 2 for the pivot table analysis and just lookup the values from Table 1 where required.

Comments

[MostlyHarmless]

that's right and that's the way I'd probably do it - I was just hoping I could integrate this step of combining 2 or more datasets directly in the Pivot Table definition without any lookup functions or VBA magic :-)

[mehov]

I'm in the same situation. Tried option 3 and the XML file I export to Excel literally grew from 9 MB to 90 MB.