Pivot Table Text Values

Show text in the Pivot Table Values area, by using conditional formatting and custom number formats. Set this up manually or with a macro.

Introduction

In Microsoft Excel, usually you can only show numbers in a pivot table values area, even if you add a text field there. By default, Excel shows a count for text data, and a sum for numerical data.

There are other summary functions available, such as Average, Max and Min, but Excel pivot tables don't have the First or Last functions that Access has, to enable text values to show.

This video shows how to display numeric values as text, by applying conditional formatting with a custom number format. The written instructions are below the video.

The full written transcript is at the bottom of this page.

Region Data

In this example, there is food sales data from 3 regions. Each region has a number and a name:

• 1 - East
• 2 - Central
• 3 - West

A pivot table was created from that data, with City in the Row area, Store in the Column area and Region ID in the Values area.

NOTE: Grand Totals have been turned off.

Show Region Numbers

When the Region ID field was added to the Values area, from the pivot table fields list, Excel automatically set its summary function to Sum. Instead of a sum of the ID numbers, we want to see the actual ID numbers.

To show that, change the summary function to Max.

• Right-click on one of the value cells
• In the popup menu, click Summarize Values By, and then click Max

The pivot table values changes, to show the region numbers.

Change Region Numbers to Names

The pivot table values now show the correct region number for each value, but instead of the numbers 1, 2 or 3, we’d like to see the name of the region – East, Central or West.

To show text, you can combine conditional formatting with custom number formats.

• If your pivot table only has a few numbers, you can apply the conditional formatting manually. In this example, there are only 3 regions — East, Central and West.
• If your pivot table has many numbers that you want to show a text, it will be easier to use a macro to apply the conditional formatting.

Manually Add Conditional Formatting

There are special settings to use when you apply conditional formatting in a pivot table.

To change the region numbers to text, follow these steps to manually add conditional formatting:

• Select all the Value cells in the pivot table (B5:F8). NOTE: B5 is the active cell, and you can see its address in the NameBox
• On the Excel Ribbon's Home tab, click Conditional Formatting
• Then click New Rule, to open the New Formatting Rule dialog box
• In the Apply Rule to section, select the 3rd option - All cells showing 'Max of RegID' values for 'City' and 'Store'. This option creates flexible conditional formatting that will adjust if the pivot table layout changes.
• Next, in the Select a Rule Type section, choose "Use a formula to determine which cells to format"

• In the formula box, type the formula for Region ID 1: =B5=1
• Click the Format button, then click the Number tab
• In the Category list, click Custom
• In the Type box, enter this custom number format: [=1]"East";;

  

  • The first part of the format tells Excel to show "East", for any postive numbers equal to 1.
  • The 2 semi-colons are separators, and there is nothing in the 2nd section (negative numbers) or 3rd section (zeros) of the custom format.
• Click OK, twice, to close the windows.

The conditional formatting rule for RegID 1 has been applied, and those cells show the text, "East", instead of the number 1. With cell B6 selected, you can see a 1 in the Formula Bar, even though "East" is displayed on the worksheet.

Make 2 More Rules

Next, select cells B5:F8 again, and follow the previous steps, to create 2 more conditional formatting rules.

Region ID 2

• Formula: =B5=2
• Custom Number Format: [=2]"Central";;

Region ID 3

• Formula: =B5=3
• Custom Number Format: [=3]"West";;

After all 3 condtional formatting rules have been applied, the pivot table will show all text in the Values area, instead of numbers.

If necessary, widen the columns, to show the text.

Values to Text Macro

If your pivot table has several numbers that you want to show as text, it will be easier to use a macro to apply the conditional formatting.

Thanks to Prof. Lee Townsend, who contributed this macro. The code has an array of 3 numbers to change to region names. You can change those numbers and names, or add more, if necessary, to match your pivot table items.

Option Explicit
Option Base 1

Sub ApplyCFArrays()
'by Prof. Lee Townsend
'downloaded from contextures.com
'select a pivot table cell
'then run this macro

Dim CFRange As String
Dim pvt As PivotTable
Dim CellOne As String
Dim colonLocation As Integer
Dim iLoop As Long
Dim regionNames As Variant
Dim regionNumbers As Variant
Dim currentName As String
Dim quote As String
regionNames = _
  Array("East", "Central", "West")
regionNumbers = Array(1, 2, 3)
  
quote = Chr(34)

On Error Resume Next
Set pvt = ActiveCell.PivotTable
On Error GoTo 0
If pvt Is Nothing Then
  MsgBox "Please select a pivot table" _
     & vbCrLf _
     & "cell and try again"
  Exit Sub
End If

'find the location of the top left of
'the pivot table's DataBodyRange
CFRange = pvt.DataBodyRange.Address
colonLocation = InStr(CFRange, ":")
CellOne = Left(CFRange, colonLocation - 1)
CellOne = Replace(CellOne, "$", "")
  
'Start the conditional format rule
CellOne = "=" & CellOne & "="

'Set up the arrays
' https://stackoverflow.com/questions
'   /39509796/is-there-a-way-to
'   -assign-values-to-an-array
'   -in-vba-using-a-single-line
  ReDim Preserve regionNames(1 _
    To UBound(regionNames) + 1)
  ReDim Preserve regionNumbers(1 _
    To UBound(regionNumbers) + 1)
  
'Set up the loop
For iLoop = 1 To 3
  currentName = "[=" _
    & regionNumbers(iLoop) & "]" _
        & quote & regionNames(iLoop) _
        & quote & ";;"
  
  With Range(CFRange).FormatConditions _
    .Add(Type:=xlExpression, _
      Formula1:=CellOne _
      & regionNumbers(iLoop))
    .NumberFormat = currentName
  End With
Next iLoop

End Sub

Transcript: Show Text in Values Area

This is the full transcript for the video, Show Text in Excel Pivot Table Values Area, shown above.

'---------------------------

Introduction

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 formattin,g we can see the region name instead of a number or account here.

Pivot Table Data

Here's the data that I used. It's just sales records, and we see the date so we know when something was sold.

And we've got region, city and store so we know where it was sold.

We can see what was sold and how much.

Set Up Pivot Table

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.

And 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. And it's important that we have a number field that we can use. because the Values area doesn't recognize text at all.

Add Region Field

Back at the Pivot Table, it would be ideal if I could just drag region into values and see a region name. But that doesn't happen. It just 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. 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.

Change Summary Function

I'll right click, Summarize Values By, and I could use Max or Min, and that will give me the unique number for each region.

I'll use Max.

Now we can see those Region IDs. We're halfway there.

Grand Totals Off

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.

Change Region to Text

To change these numbers into text, we're going to use number formatting.

I'll use conditional formatting to find what number is in the cell, 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.

Conditional Formatting Formula

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 =B5 and that's the active cell.
• That's what we use in our formula, =1.

Anywhere there's a cell that has a 1, we're going to format that.

Custom Number Format

• 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 1 in them now change to East.

More Conditional Formatting

We're going to repeat that for numbers 2 and 3.

• Going back into Conditional Formatting, New Rule,
• All cells showing Max of 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.
• In square brackets, =2.
• And 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. Make those columns wider.

Third Conditional Formatting Rule

And the third number here is for the West region. So I'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.

Text Shows in Values Area

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.

Get the Sample File

1. No Macros: Download the Pivot Table Values Text workbook with the NO macro. The zipped file is in xlsx format, and does not contain any macros. You can manually apply the conditional formatting.
2. Macro: Download the Pivot Table Values Text workbook with a macro to apply conditional formatting. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when opening the file, if you want to test the macros.

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Table Protection

Clear Old Items in Pivot Table