Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)?

enter image description here

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
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 :-) –  Martin Oct 9 '12 at 15:34
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.