Show Text in Excel Pivot Table Values Area


Usually, you can only show numbers in
the values area of a pivot table. But in this example we’re going to see
how you can use number formatting to show text. We’ve got cities and store numbers,
and with some formatting, we can see the region name, instead of a
number or account here. Here’s the data that I used. It’s just sales records,
and we see the date. We know when something was sold,
and we’ve got region, city and store, so we know where it was. We
can see what was sold and how much. I’ve created a blank pivot table from
that data, and we’re going to set it up, and see how we can use
that formatting feature. I’ll use the field list, and I’m going
to put city in the row labels area. Then I’ll put the store
numbers across the top, so that goes into the columns. In here is where I would
like to see the region name. In the data, we have two fields. We have a region name
and a region number. It’s important that we have a number
field that we can use, because the values area doesn’t recognize
text at all. Back at the pivot table, it would be ideal if I could just drag
region into values and a region name, but that doesn’t happen. It defaults
to showing a count of the region. I’m going to remove that. We’re going to use this region
ID, and the regions are numbered. So East is 1. We also have Central, which is 2, and West is 3. In the sales pivot, I’m going to add
the region ID field, and because it’s numeric it goes right
into the values area. But it’s showing us SUM,
and that’s not what I want. I want to be able to see
the numbers 1, 2 and 3 in here, and then
I’m going to work with those. okay, Summarize Values By, and I could use Max or Min and that
will give me the unique number for each region. So I’ll use Max, and now we
can see those region ids. So we’re halfway there. I’m going to turn off the grand totals,
because we don’t need those. On the Design tab. Grand Totals,
Off for Rows and Columns. Now we just have our region numbers.
To change these numbers into text, we’re going to use number formatting,
and I’ll use conditional formatting to find what number is in the South, and
then use the number format based on the cell value. I’ll select all the cells that have
the values, and on the Home tab, click Conditional Formatting, New Rule. Apply Rule To is currently showing the
address of the range I have selected, which is B5 to H10. We can see
here that B5 is the active cell. We don’t want to confine
this to a specific address,
because perhaps later we’ll add new cities or stores, so
we want this to be expandable. I’m going to click the
third choice, All Cells Showing Max of RegID
Values for City and Store. I’m going to use a formula to decide
which cells should be formatted. In the first case, we’re going to be
formatting any cells that have a 1, and that’s the East region. Here I’m going to say equals B5,
and that’s the active cell. That’s what we use in
our formula, equals one. Anywhere there’s a cell that has
a 1, we’re going to format that. We’ll click the Format button.
In here, under the Category, click Custom. Now we can
create our own format. We want any cell that equals one. In square brackets, type=1
close the square bracket. And for that we want it to say East, Inside double quotes, type East. That will format any positive numbers. Then we’ll type two semi-colons, because
we don’t care about the formatting for any negative numbers or Zeros. We’ll just leave those
blank, and click OK, and OK. And the cells that had a one
in them now change to East. We’re going to repeat that
for numbers 2 and 3. So going back into Conditional
Formatting, New Rule, All cells showing Max RegID
for city and store. Use a formula, and in this case, we say
=B5=2. Again, click Format, Custom. This time, we’re going to do the 2. So in square brackets=2. This time it is the Central. In double quotes, type Central
two semi-colons, click OK. And OK. They’re just
a little too narrow, so we’ll make those columns wider. And the third number here
is for the West region. ‘ll do that step, make sure
I have all the cells selected. Conditional formatting, New Rule, All cells, Formula=B5=3. Format. Custom, square bracket=3, West, two semi-colons. OK, OK And there, we have
all of them formatted. It looks like we’ve typed text or made
text show in here, and that’s all done with number formatting, through
conditional formatting. This would be rather tedious
if you have lots of values, but if you only have a few, it’s a
way to show text in your pivot table. For more Excel tips and tutorials, and to download the sample
file for this video, please visit my Contextures website
at www.contextures.com

50 thoughts on “Show Text in Excel Pivot Table Values Area

  1. Awesome tip!  That conditional formatting can be reused to by simply copying a cell in the values area of the pivot table and pasting it anywhere in the workbook.
    Thanks for sharing!

  2. Thank you for this. I have a MAC and use Excel 2011. Unfortunately "Conditionally formatting" does not give the options you describe. Is there another solution to this problem?

  3. @Contextures Inc. When creating the rules I'm finding that only one value will convert to the new format, but "All cells showing.." has to be selected. It actually will not work when I select "All cells showing "Max…" Why would this happen? 

  4. Great, Great Tip !!,

    Is there a reason to use: [=1]"East" ?
     I think that using "East";; it's enough because I am telling the conditional in the New formatting rule dialog box

    Thanks for great trick !!!

  5. How would you get a exact text in the pivot table. As my text is not consistent. I am planning to use customer name (rows) and lot number( Columns) they received. as production date change lot date change, hence, I can not define or use custom formula 1= central,etc. Please advise.

  6. I did not see a response to the post when you have inconsistent text that you want displayed for multiple line items.  I have a comments column so each comment is different but want it displayed to the right of the values field.  is that possible?

  7. I love this. I love doing things like in Excel and this is absolutely brilliant because I need to do this to tally up my students results for marking. thank you so much

  8. This is excellent. Thank you. However, despite not "fixing" the cell range to conditionally format, I find that the conditional formation does not remain relative (move as rows and columns are added). I also see that the B5 adds the $ signs even after you select the 3rd radio/option button down. Am I missing something? Thank you.

  9. Hello , I was trying with your instruction but i can only one formula when i using =$G$3=1, i can only use text for 1 , no effect other formula using 2, text no change for 2 in my excel sheet. Please advise me. I can only used one formula .

  10. My conditional formatting doesnt give me the same options as you.
    I'm using Excel version 15.22, can you give me any directions for this version?

  11. Thank you soooo much for posting this!! I've been searching for hours how to do this! Briliant idea.!!! 🙂

  12. Hello! The trick for assigning three text words to numbers so you can then use conditional formatting to show the text in the pivot table isn't working for me. When I select to summarize the value by "min" or "max" it turns everything to "0" or "1" even though I assigned 1 – 3 as values. Any recommendations?

  13. Wow… I really liked it as this solved my issue 🙂 God bless you and keep sharing more wonderful stuff please. Thanks!

  14. In this case we have limited values (East, West etc) under values so easy to use under conditional formatting. However, how do we approach the same solution when we have hundreds of values to update?

  15. This is a great explanation. I had spent an hour trying to figure out how to get the Text data to my Pivot table and there were plenty of pages and videos that made it so complicated. I found this video and in 15 minutes I was able to get the data I needed. Thank you so much for the help. I almost gave up when I found this.

  16. how about when the region ID's include blank cells, '0', and '1'. How to apply rule for '0' while the blank cells are not affected?

  17. In my case there're >3 text values which are constantly changing. Moreover, a cell can contain multiple text values which I need to show via delimiter. I've nailed it via MS Power BI: I created a calculated measure for the case when 1 cell contains multiple text values: Measure Name=IF(HASONEVALUE('Table'[Column]);VALUES('Table'[Column]);CONCATENATEX('Table';'Table'[Column];" | ")) and than built Matrix table with values = measure. If additionally you'd like to export the table to Excel, use Power BI export to csv and then use Excel Power Query function called "Pivot Column" to build a table with NO aggregation of values in Advanced options.

Leave a Reply

Your email address will not be published. Required fields are marked *