Show Text in a Pivot Table Values Area

A common question is “How can I show text in a pivot table’s values area, instead of numbers?”

For example, if you add the Region field to the Values area, you’d like to see the region’s name, instead of a Count Of Region number.

If you use Crosstab queries in Microsoft Access, you might have achieved this by using the First or Last summary function, instead of Sum or Count.

Unfortunately, the First and Last functions aren’t available in Excel pivot tables, so there’s no easy way to show text in the Values area.

Workaround #1 – Use the Row Fields

You could add the Region field to the Row Labels area, with the City field. Then add another field in the Values area to show a count of the regions. The layout won’t be exactly what you wanted, but it will show the region name.

Workaround #2 – Create a Custom Number Format

In this example, there are only two region names, so you could use a custom number format to show the region names. You’ll assign a number to each region, then use that number in the pivot table. Note: This technique is limited to 2 items.

Create the Region Number field:

1. In the source data, add a new column with the heading RegNum. In this column, type a 1 for East region orders and 2 for North region orders.
2. Refresh the pivot table, so the RegNum appears in the Field List.
3. Add the RegNum field to the Values area, and right-click on one of the numbers.
4. In the popup menu, click summarize by Max.

Apply a Custom Number Format:

1. Right-click a Region value in the Values area in the pivot table.
2. In the popup menu, click Number Format.
3. In the Category list, click Custom.
4. In the Type box, enter [=1]”East”;[=2]”North”;General
5. Click OK, to close the dialog box.

The pivot table will show the Region names, instead of the Region numbers.

____________