How can I concatenate values of Pivot Table columns in Excel?
18,977
Not the prettiest solution but it helped me once:
- Create your new column next to the Pivot table.
- In the first row (below your new column label) insert your concatenation formula referring to the cells of the Pivot table.
- 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.
Related videos on Youtube
Author by
BreakPhreak
Updated on September 18, 2022Comments
-
BreakPhreak over 1 year
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.