Show text in the Pivot Table Values area, by using conditional formatting and custom number formats. Set this up manually or with a macro.
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.
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, 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.
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
This is the full transcript for the video, Show Text in Excel Pivot Table Values Area, shown above.
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.
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.
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.
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.
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.
I'm going to turn off the grand totals because we don't need those.
Now we just have our region numbers.
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'm going to use a formula to decide which cells should be formatted.
Anywhere there's a cell that has a 1, we're going to format that.
Now we can create our own format. We want any cell that equals one.
And the cells that had a 1 in them now change to East.
We're going to repeat that for numbers 2 and 3.
And the third number here is for the West region. So I'll do that step.
And there, we have all of them formatted.
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.
Last updated: October 26, 2023 3:46 PM