pivot table

Question

how can set text values in pivot table value area in Microsoft excel

like we have data name , date and shift

i like to arrange data name in row label and date in column label and shift in later like A B C

as per date it will display in value area

Answer 1

In Microsoft Excel, the Values Area of a Pivot Table typically aggregates numeric values (e.g., sum, count, average). However, since you want to display text values (Shift: A, B, C) instead of numbers, Excel does not natively support direct text values in the Values Area. But you can work around this limitation using the TEXTJOIN function, a Pivot Table with a helper column, or Power Query.

Solution 1: Use a Helper Column Since Pivot Tables require numeric aggregation in the Values Area, you can create a helper column that concatenates text shifts for each person and date.

Steps:

1. Add a Helper Column to the data:
   • Suppose your data columns are Name, Date, and Shift.
   • Insert a new column in your dataset.
   • Use a formula to combine multiple shifts per name & date:

          =TEXTJOIN(", ", TRUE, IF(($A$2:$A$100=A2)*($B$2:$B$100=B2), $C$2:$C$100, ""))
     

   • Adjust ranges accordingly. This formula joins all shifts for a given name and date.
2. Create a Pivot Table:
   • Go to Insert > Pivot Table.
   • Place Name in Rows.
   • Place Date in Columns.
   • Use the Helper Column in Values.
   • Set Values to "Max" or "Min" to show text (since there’s only one shift per name/date in the helper column).

Solution 2: Use Power Query (Recommended for Large Data) If your dataset is large, Power Query can group data and display shifts per name and date.

Steps:

1. Select your dataset and go to Data > Get & Transform Data > From Table/Range.
2. In Power Query:
   • Select the Name and Date columns.
   • Click Group By (under the Transform tab).
   • In the "Group By" window:
     • Group by: Name, Date
     • New column name: Shift List
     • Operation: All Rows
   • Click the small expand icon next to the Shift column and use TEXTJOIN() to combine shifts.
3. Click Close & Load, then insert a Pivot Table from the transformed data.

Solution 3: Use Power Pivot (Data Model) Power Pivot can handle non-numeric values in the Values Area.

Steps:

1. Select your data and Load to Data Model.
2. Create a DAX measure using:

      ShiftText = CONCATENATEX( TableName, TableName[Shift], ", ")
   

3. Use this measure in the Pivot Table Values Area.

---

Final Output Example | Name | 01-Mar | 02-Mar | 03-Mar | |--------|--------|--------|--------| | John | A, B | B | A | | Alice | C | A, C | B |

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin