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

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.

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, 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
• 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

Download 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

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table