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.
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.
=CONCATENATE (Text1, [text2, … text_n])
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)
Add a PivotTable with the combined address column
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.
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
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.
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.
Enter the values and click ok.
Your PivotTable will display the total of combined units and price.