Show Text in Pivot Table Values Area

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
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
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.

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.

_____________

5 thoughts on “Show Text in Pivot Table Values Area”

  1. 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?

  2. Hi,

    Adding Text in place of Number works for 1st 3 values, kindly share some working examples for n number of values and individual text reflecting in the value area against it.

Leave a Reply to GJ Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.