Show text in the Pivot Table Values area, by using conditional formatting and custom number formats. Set this up manually or with a macro.
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.
In this example, there is food sales data from 3 regions. Each region has a number and a name:
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.
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.
The pivot table values changes, to show the region numbers.
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.
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:
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.
Next, select cells B5:F8 again, and follow the previous steps, to create 2 more conditional formatting rules.
Region ID 2
Region ID 3
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.
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
Last updated: December 13, 2020 10:36 AM