TEXT IN A PIVOT TABLE's VALUE FIELD

[Shafique]

I created a table as belw. and want to get its text data(CODE) in a pivot table's value field.

Table1........

NAME CODE
1 ABC AD-01
2 DEF AD-02
3 GHI AD-03

 

[sandy666]

You can't because in Values Area your code will be counted
Try to put NAME and CODE into Rows Area, choose Report Layout in Tabular Form

 

[DanteAmor]

You can add an auxiliary column to get the value of column B.
Use the following formula for that.
In the pivot table, add the auxiliary column value area.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:106.46px;" /><col style="width:94.1px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">NAME</td><td style="background-color:#ffff00; ">CODE</td><td style="background-color:#ffff00; ">Value</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ABC</td><td >AD-01</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >DEF</td><td >AD-02</td><td style="text-align:right; ">2</td><td > </td><td style="background-color:#538ed5; ">Title</td><td style="background-color:#538ed5; ">Sum</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >GHI</td><td >AD-03</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:left; ">ABC</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:left; ">DEF</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:left; ">GHI</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#538ed5; text-align:left; ">Total general</td><td style="background-color:#538ed5; text-align:right; ">6</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=--MID(B2,FIND("-",B2)+1,LEN(B2))</td></tr></table></td></tr></table>

 

[Fazza]

Instead of a pivot table (that can't return the text in a data field), a cross-tab query can.

One example of many https://www.mrexcel.com/forum/excel-questions/384664-can-i-return-text-pivot-table.html

 

[Shafique]

You can add an auxiliary column to get the value of column B.
Use the following formula for that.
In the pivot table, add the auxiliary column value area


.
Thanks for your reply.
I am.very sorry sir. I can't understand it which place I paste the formula that you suggest me.

 

[RoryA]

Which version of Excel do you have? It is possible to return text in a value field if you have Power Pivot.

 

[Shafique]

Which version of Excel do you have? It is possible to return text in a value field if you have Power Pivot.

Office 2013
And also have power pivot.
But how to use.

 

[RoryA]

See Bill's post here: https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/

 

[SpillerBD]

Why?
Seems like having that lable also in the ROWS area with the correct Pivot Table layout may suffice.
Design/Report Layout = Show in Tabular.
Design/Report Layout = Repeat Al Item Labels. (It is possible to only repeat specific labels.)
Grand Totals = None
SubTotals = None

 

[DanteAmor]

Thanks for your reply.
I am.very sorry sir. I can't understand it which place I paste the formula that you suggest me.

​You could show an example of what you need to show in the pivot table.