How to Concatenate Values of Pivot Table

How to Concatenate Values of Pivot Table

Concatenate is an excel function that allows you to join two or more strings together. When creating Pivot table you may want to combine data from two or more columns to form a single column. An example, you may wish to combine names field with address field to form a single column or combine some texts with a formula driven value.

Pivot Table screenshot

In this table, you can create a PivotTable with combined Ship_city and Ship_state text values. To do that, you need to use CONCATENATE function which will allow you to join the columns together.

Syntax

=CONCATENATE (Text1, [text2, … text_n])

Pivot Table screenshot

NOTE: If you’re using Excel 2016, use CONCAT function.

The address column will be used to create the PivotTable, a Pivot chart or Pivot View report just like you would add any other column.

Concatenate with Space characters

To add spacing between the join text type this command

=CONCATENATE (C2, ” “, D2) on cell G2.

You can also add a comma to make the joined text more readable

=CONCATENATE (C2, “, “, D2)

Pivot Table screenshot

Add a PivotTable with the combined address column

Pivot Table screenshot

Format the PivotTable to display the data in columns.

Go to Pivot tools and click design menu. On layout group, choose report layout and select show in tabular form. The data will be displayed as shown below.

Pivot Table screenshot

Concatenate strings with a line break

In windows, use CHAR (10) where 10 is the ASCII code for line feed.

In Mac computers use CHAR (13) where 13 is the ASCII code for carriage return

In the above example, to get the combined mailing address of a customer use the following formula;

=B2& ” ” & CHAR (10) &C2 & “,” &D2

Pivot Table screenshot

For the text to display correctly with line breaks, you need to ensure the Wrap text option is enabled. Click on the cell then go to cells group in Home menu. Choose format cells. On the opened window, click alignment table then select Wrap text check box and click ok.

Once you concatenate the text, you can go ahead to create a PivotTable with the details in the joined column.

Calculated Columns in a Pivot Table

A Calculated column is often used when you want to add calculated results in an area in your PivotTable. This can be an added row or column in your PivotTable.

In the table below, we want to add a calculated column to display the total of sold Items.

Pivot Table screenshot

Click insert Pivot table, on the open window select the fields you want for your Pivot table.

Once you select the desired fields, go to Analyze Menu. Under calculations, choose fields, Items & Sets tab then click on calculated fields.

Pivot Table screenshot

Enter the values and click ok.

Pivot Table screenshot

Your PivotTable will display the total of combined units and price.