3

I want to add a column of values to a pivot table as is. It would not be used in any other calculations.

I.E. add something like a satisfaction rating for a company in an area to a table that sums up all the interactions with companies by location and price.

While I suppose it it would be possible to go back into the source data to add this data as a new field, I do not know how to do that.

With regard to how this column would behave when filtered, I would like to be tied to a binary primary key (pretty sure that is not the right way to phrase it, but I mean that it should be tied to the company and location), and insert the correct value when those conditions are matched.

If there is an easier/more sensible way to do what I am asking (and I am sure that is the case) I would happily take that instead.

1 Answer 1

2

You cannot add external data to a PivotTable other than by including it in your source data or adding some manual formulas next to your PivotTables (Method 1).

Method 1

If your satisfaction ratings reside in some other separate table, you can use LOOKUP formulas to tie them to your pivot tables. Say your current PivotTable is Columns A and B below, and want to show company ratings in C:

A              B           C
Location1      Some data   rating
 -- Company1   1000$       ****
 -- Company2   500$        ***
Location2      
 -- Company1   1000$       ****
 -- Company2   2500$       ***

If you have your rating data in another table somewhere else (say sheet2)

 A            B
 Company1     ****
 Company2     ***

You can use the VLOOKUP formula in column C to fetch it:

Location1      Some data   rating
 -- Company1   1000$       =VLOOKUP(A1, Sheet2!A:B, 2, FALSE)

The downside is that any change in your PivotTable will likely break things and you'll need to readjust your formulas.

Method 2

To make your PivotTable handle the ratings automatically, you should really add a new column to your source data called "rating" which can be a column contains a VLOOKUP formula that fetches ratings automatically from another table (so you maintain your ratings in just one place that will be reflected when refreshing your PivotTable.

That's assuming your PivotTable gets data from Excel. If your data does come from SQL or Access, you would need to either have access to that source, or download that data as a regular table (instead of straight-to-pivot), add that formula column, and make a local PivotTable out of that intermediary table. Either way, your final pivot table would have a usable "Rating" field you can put in Row Labels.

2
  • The data is not one to one, it is one to many. I.e. there are many times when the same company is in the same area, with many different bits of data in each row recording the differences, but all of those rows will have the same satisfaction rating
    – soandos
    Jan 25, 2012 at 4:31
  • 2
    Right, you perform the VLOOKUP on the one element that has a determined rating, eg the company name alone. With method 2, you'll end up with a lot of duplication (each row for that company will have the same value) but that's great - that's what allows Excel to show the appropriate row label in the resulting PivotTable. Maybe I'm not understanding right. Examples help ;)
    – mtone
    Jan 25, 2012 at 4:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .