Although you probably never taught about it, there is an option to show the text in the Pivot Values area.

Read on to find out how this can be done.

Tutorial Content

## Show Text in Pivot Values Area With Data Model

For our example, we will use the table with NBA players, their clubs, the conference that their clubs are in, and statistics from one or several matches.

Now, we will create our **Pivot Table** to derive the data that we need.

To get what we want, we have to make sure to add our data to the data model. We do this by selecting our table, then going to** Insert** >> **Tables **>> **Pivot Table**. When we click on it, we will have a pop-up window from which we have to choose to **Add this data to the Data Model**.

We will create our **Pivot Table** on a different sheet. In the field list of our **Pivot Table**, we will right-click on the Table name and select **Add Measure**:

Once we click on it, we will be presented with the following pop-up window:

Our table name is Table1 (we did not change this). Measure name will be Players, as we will input this column into our formula. Finally, our formula is:

1 |
=CONCATENATEX(Values(Table1[Player]), Table1[Player], ", ") |

We will leave the **General **category. This formula will list all of our players one after the other, allowing us to use them in the values field as well.

We should click on **Check DAX Formula** when done just to check if there are any errors in the formula.

We will then include **Players** into the **Value field**. In **Rows field**, we will place **Club** and **Conference**.

Our table now looks like this:

To make the table neater, we will remove **Grand Total**. To do so, we have to click on our table, go to **Pivot Table Tools** >> **Design** >> **Grand Totals** >> **Off for Columns and Rows:**

Now we have our players ordered by club and conference:

Remember, if we have not added our table to the data model, we could have not had our players, that are obviously in text format, in the **Pivot Values Area**.

## Show Text in Pivot Values Area With Conditional Formatting

There is another way in which you could insert a text in the **Pivot Values Area**. This method should be used if you have only a few variances since it has to be done manually, for the most part.

To show this in an example, we have added the **Conference ID** into our table, and we marked Western Conference with number 1 and Eastern Conference with number 2.

Next, we create the **Pivot Table**. This time, we create it without adding it to the data model.

In the row area of the **Pivot Table**, we will put Players, while we will put Conference ID into the Values Area. Then we will right-click on Conference ID and go to **Value Field Settings**:

We will change the value to show the **Max** of our conferences.

Our Conference ID column now has two values: 1 and 2.

To change these values into the text, we have to select our range (**B4:B12**), then go to the **Home tab** >> Conditional Formatting and select **New Rule**.

In a pop-up window that appears, we have to define a couple of things:

- We have to choose the rule to be applied to: “
**All cells showing “Max of conference ID” for players**” - In a
**Select a Rule Type**section, we need to choose the last option:**Use a formula to determine which cells to format.** - Our formula begins to apply to the first cell in our range. The first cell is cell
**B4**. Our formula is:

=B4=1. This means that all of the cells that have this value will be subjected to our new format.

Once we have this set, we click **Format…** in the bottom right corner. In the **Format Cells** window, we select the **Number tab**, and then we go to **Custom**. Beneath the **Type** we type in the following format:

1 |
[=1]"Western” |

This format tells Excel to show **“Western”** for any positive number that is equal to 1.

When we click **OK**, we will see that these changes have successfully been implemented to our table:

To define the same thing for the Eastern conference, we have to do the same steps again. The difference will be that we will change the rule description in **Formatting rules** to be:

1 |
=B4=2 |

And to define the type in** Format Cells **part:

Once we are done with this, now table looks like this:

Note: we have excluded **Grand totals** in this example as well.