TEXT IN A PIVOT TABLE's VALUE FIELD

Shafique

Board Regular
Joined
Nov 23, 2018
Messages
117
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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

screenshot-78.png
 
Last edited:
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
Which version of Excel do you have? It is possible to return text in a value field if you have Power Pivot.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top