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)?
Best Answer
Unfortunately, the short answer is no. However, there are some possible workarounds.
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.
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.
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).
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.