Articles

Learn Excel – Text Instead of Numbers in Pivot Table – Podcast 2223


Learn Excel from MrExcel Podcast, Episode
2223: Show Text in a Pivot Table in the Values Area. Hey, welcome back to the MrExcel netcast,
I’m Bill Jelen. Today’s question from Susan, in Melbourne,
Florida. Susan wants to create a pivot table with text
in the values area. She wants to report this code to have original
and revised. She wants to compare those and a pivot table. I know pivot tables don’t do text; pivot tables
are for numbers, you know. And, at first, when she said this, I said,
“What, are you just trying to show yes or no?” Because I have this, you know, weird trick
when you have zeros and ones. We can create a custom number format and change
the zeros and ones. The ones will be yes, negative won’t happen,
Zeros will be no, click OK. You can change the number format in the pivot
table to change the zeros and ones to yes or no. No, Susan actually wants how to report these
values. And, yeah, the answer is just No, you can’t
do it. But, wait. But wait, you can do it if you convert this
to a real table: Ctrl+T, and then Insert, PivotTable, Add this data to the Data Model. Because that enables a brand-new formula language
called DAX– Data Analysis eXpressions. So, we’ll put Market down the left hand side,
and Rep, Version across the top. And instead of Code, I’m going to calculate
a new measure. Alright, so we will, in fact, right-click
and say Add Measure, and it’s going to be called a ListOfCodes. ListOfCodes, all right. And our formula’s going to use the=CONCATENATEX
function. So,=CONCATENATEX now wants to know– the
first thing it wants to know is– the table, and, of course, I just press Ctrl+T, which
means that that’s going to be called Table1. So I start to type Table1 here, and we choose
from the list, and My expression is going to be the Code. So I start to type Code, Table1[Code] and
then the delimiter, what we want between each one, I’m going to put in-quotes, comma, space–
“, ” — like that, close– )– Check DAX Formula– Formula has no errors. Leave the category as General; we don’t want
to report this as Number. Click Ok, and then what we get is this ListOfCodes
is now here, and I can take that and drag it to the Values area. Now, check this out– this is actually, actually
working. So, for each item, we’re seeing what it was. So, in Atlanta, Gary had Fig. Mike had Cherry, and then in the Revised it’s
Fig and Orange, and then the Atlanta Total is reporting all of the values, separated
by a comma– so Fig and Cherry. And, if we take Market out and put Region
in– let’s take Rep out– so, now I’m seeing all of the items that were reported in that
region. Alright, and then down here in the Grand Total,
all of the items again. This is pretty darn cool using the Grand Total. Now, one of my pivot table defaults is, Subtotals,
Include Filtered Items in Totals, and it looks like that is not having an impact. Over here on the far right-hand side we have
a Grand Total there, so down at the bottom, we’re essentially getting a list of everything. This might be a pivot table where you really
don’t need these grand totals. Remove Grand Total, and maybe even you don’t
need these grand totals– right-click and Remove Grand Total. Very cool way using the CONCATENATEX function–
the CONCATENATEX funtion. Let’s take a look at that again: So,=CONCATENATEX,
the name of the table, the column, and then the delimiter is in “, ” It’s always awesome
to see what new things we can get just from choosing that box. Add this data to the data model. That box is discussed in my book, “MrExcel
LIVe, The 54 Greatest Tips of All Time,” along with a lot of other tips. Click that “I” on the top right-hand corner
to check it out. All right, wrap up for today. Susan in Melbourne wants to create a pivot
table that shows text in the values area. Typically, yeah, no. No deal. But it is possible with the DAX formula language. In order to use DAX, you have to format your
data as a table using Ctrl+T, Insert, PivotTable, and then check the box for Add this data to
the Data Model. If you don’t do this, it’s not going to work. Build the row and column areas of the pivot
table, right-click the table name in the fields list and choose Add Measure, and then the
formula is,=CONCATENATEX. Very much like the new TEXTJOIN function. I specify the table, which field, and then
the delimiter, and it works. To download the workbook from today’s video,
visit the URL in the YouTube description. Thanks to Susan for coming to my seminar and
thanks to you for stopping by. We’ll see you next time for another netcast
from MrExcel.

Leave a Reply

Your email address will not be published. Required fields are marked *