How can I concatenate values of Pivot Table columns in Excel?

Question

Given the pivot table:

A  B
1  2
3  4

I'd like to have a third column:

A  B C
1  2 1_2
3  4 3_4

It's easy to do with the regular table, how can it be done with Pivot Table? The regular formula (if it's not a part of a pivot table) will not honour the number of rows in the pivot table which can be changed due to modifications in the filter.

Answer 1

Not the prettiest solution but it helped me once:

1. Create your new column next to the Pivot table.
2. In the first row (below your new column label) insert your concatenation formula referring to the cells of the Pivot table.
3. Drag down to fill the formula beyond the last row of the Pivot table. How far beyond depends on your data and how much you expect it to grow and gain more rows over time.

Below is an example I created to demonstrate this. The formula for cell C2 is:

=IF(AND(A2<>"", A2<>"Grand Total"), A2 & "_" & B2, "")

I filled this formula down way beyond the end of the Pivot table. When I later add more data and refresh the Pivot table, if the new data generates a new row in the Pivot table, I get the concatenated value for it as well.

Answer 2

How it works:

• Sort data by column A.

• Write formula in C13 to get comma-separated values & Copy formula down.

  =IF(A13<>"", A13 & "-" &B13)

Note, adjust cell references in formula as needed.