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.
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). The measure name will be Players, as we will input this column into our formula. Finally, our formula is:
=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 in the Value field. In the 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, which 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 to 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 in 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 the 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:
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 in 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:
And to define the type in the 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.