Show Text in Pivot Table Values Area

Usually you can only show numbers in a pivot table values area, even if you add a text field there. In the screen shot below, the Max of Region ID is in the Values area. Instead of the numbers 1, 2 or 3, we’d like to see the name of the region – East, Central or West.

pivot table values show numbers only


Show Text With Custom Number Formats

To show text, you can combine conditional formatting with custom number formats. This works well, in Excel 2007 and later, if you don’t have too many text options. In this example, there are 3 regions — East, Central and West.

In the screen shot below, the formula bar shows a 2, which is the ID number for the Central region. The selected cell – D6 – shows the text “Central” instead, because of the custom number formatting that has been applied.

pivot table values show text with custom number format

Video: Show Text in Values Area

Watch this video to see how to see how to set up the formatting so text shows in the values area.

Or watch on YouTube: http://youtu.be/wslp2BqHuz8

Download the Sample File

To download the sample file, please visit my Contextures website. On the Sample Files page, go to the Pivot Tables section, and look for PT0036 – Show Text in Pivot Table Values Area. The zipped file is in xlsx format, and does not contain macros.

_____________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Data Fields. Bookmark the permalink.

3 Responses to Show Text in Pivot Table Values Area

  1. GJ says:

    Great tip. I think its works fine without the square bracket arguments either.

  2. SP says:

    What if the Text in Pivot Table Values Area is not something that can grouped at all? For instance the pivot table values are employee’s name, and each of the name is unique?

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>