Terry called in with a question where she wanted to use a pivot table to show text fields from a database. While this question initially threw me for a loop, we did end up with a workable pivot table that showed off text. Episode 748 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
All right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, you know I love pivot tables They're the most powerful feature in Excel.
I've written a couple of books about Pivot tables all of my books mentioned pivot tables, but I got a call this week from someone who wanted to create a Pivot table and initially I just thought there's no way.
That will every old create a Pivot table to solve this, they had text.
It was just a huge database of text and they wanted to be able to look for certain criteria and figure out which text entries match the criteria and I said, you know let's use the AutoFilter and our manager said no, no, I don't want to use an AutoFilter.
I wanna use a Pivot table, and so, this is what we did we started out with this data set, Data, Pivot table, Finish.
And I took the title the text that they wanted to return instead of putting it in the data area, I put it over here in the drop row fields here.
So, there's a list of all of the items.
Now, in this case it happens to be all of the podcasts.
I didn't want to use their data.
And then we said we're going to take all of the other fields and add them up into the page area.
So, I'll take the keywords up and then all of these little check boxes, they basically had checkbox, this is for you know in for example, Charts, VBA, Pivot table, Data Analysis, Formulas, Formatting, Printing.
Whether it's about Excel 2007 or not which book it was from.
Now the air date. I'm gonna the air date is the interesting one because it would be too specific.
I'm going to take the air date here to the left column temporarily.
Choose one of these air dates right click group and show detail group and group that up to months and years, click OK.
Now, that those are grouped.
I'm going to take those up to the page field as well because we have air date and years.
And basically now, what we have is we have a ad hoc reporting tool.
I want to see a list of just the podcast episodes that for example, are about Pivot tables I would go here choose just the X and now my list of 600 and some podcast episodes is reduced just down to the ones about Pivot tables.
Maybe, I want to see just the Pivot tables that are about Excel 2007.
So, I go open that and I get a nice short list of just the items from that specific area.
Now, the one thing that was really strange if we scroll to the right here, you'll see that there's a huge empty data area It looked really strange, and it just took up a lot of space and we said well, okay how can we make that data area not appear the way that it's appearing.
So, we took the title which is a text field and put it in the data area, which forces Excel to count.
All right and we kind of try to sell this to the manager saying hey, In addition to giving you a list right down here now in the grand total you'll see a count of how many items matched. So, for Pivot tables Excel 2007 there were 11 but if we go back to all Excel 2007, and then choose just charts Click X. You'll see that there were only three episodes that are about charting in Excel 2007.
Now, I'm so kind of a very unusual Pivot table and that most of the time what we care about in the Pivot table is here in the data area, but in this case because we're interested in text the real use of this Pivot table is what's in the row area, those lists of titles that match certain criteria that are up here in the page fields.
So, that was a very unique use of pivot tables and glad was able to help Terry out with that question and passing it on to you in case you ever have a situation where you basically have a lot of text want to show the results in a pivot table and make use of the Pivot table drop down features up in the page field area.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Well, you know I love pivot tables They're the most powerful feature in Excel.
I've written a couple of books about Pivot tables all of my books mentioned pivot tables, but I got a call this week from someone who wanted to create a Pivot table and initially I just thought there's no way.
That will every old create a Pivot table to solve this, they had text.
It was just a huge database of text and they wanted to be able to look for certain criteria and figure out which text entries match the criteria and I said, you know let's use the AutoFilter and our manager said no, no, I don't want to use an AutoFilter.
I wanna use a Pivot table, and so, this is what we did we started out with this data set, Data, Pivot table, Finish.
And I took the title the text that they wanted to return instead of putting it in the data area, I put it over here in the drop row fields here.
So, there's a list of all of the items.
Now, in this case it happens to be all of the podcasts.
I didn't want to use their data.
And then we said we're going to take all of the other fields and add them up into the page area.
So, I'll take the keywords up and then all of these little check boxes, they basically had checkbox, this is for you know in for example, Charts, VBA, Pivot table, Data Analysis, Formulas, Formatting, Printing.
Whether it's about Excel 2007 or not which book it was from.
Now the air date. I'm gonna the air date is the interesting one because it would be too specific.
I'm going to take the air date here to the left column temporarily.
Choose one of these air dates right click group and show detail group and group that up to months and years, click OK.
Now, that those are grouped.
I'm going to take those up to the page field as well because we have air date and years.
And basically now, what we have is we have a ad hoc reporting tool.
I want to see a list of just the podcast episodes that for example, are about Pivot tables I would go here choose just the X and now my list of 600 and some podcast episodes is reduced just down to the ones about Pivot tables.
Maybe, I want to see just the Pivot tables that are about Excel 2007.
So, I go open that and I get a nice short list of just the items from that specific area.
Now, the one thing that was really strange if we scroll to the right here, you'll see that there's a huge empty data area It looked really strange, and it just took up a lot of space and we said well, okay how can we make that data area not appear the way that it's appearing.
So, we took the title which is a text field and put it in the data area, which forces Excel to count.
All right and we kind of try to sell this to the manager saying hey, In addition to giving you a list right down here now in the grand total you'll see a count of how many items matched. So, for Pivot tables Excel 2007 there were 11 but if we go back to all Excel 2007, and then choose just charts Click X. You'll see that there were only three episodes that are about charting in Excel 2007.
Now, I'm so kind of a very unusual Pivot table and that most of the time what we care about in the Pivot table is here in the data area, but in this case because we're interested in text the real use of this Pivot table is what's in the row area, those lists of titles that match certain criteria that are up here in the page fields.
So, that was a very unique use of pivot tables and glad was able to help Terry out with that question and passing it on to you in case you ever have a situation where you basically have a lot of text want to show the results in a pivot table and make use of the Pivot table drop down features up in the page field area.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
