Yesterday, I talked about Jason's request to format a grouped pivot field in the row area of a pivot table. There is a solution, but it requires a bit of work in the original data set. Episode 913 will show you about the TEXT function and the other type of VLOOKUP.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, a couple of days ago, we talked about Jason's problem, where he wanted to show groups of percentages in a Pivot Table, and there was just no way to format that correctly.
One solution is, we could come out here to the right of the Pivot Table and build a little table with the group.
So I'm entering 5% and 5.5%, and I'm going to grab the Fill handle and drag that down far enough below our data, and then here, I'm going to use the TEXT function to format that exactly as I want it.
So =TEXT of the 5%, and in quotes I'm going to put 00.0% ampersand, and then I'll put a dash between the two percentages, ampersand, and then again the text of the next value, still with the 00.0%.
(=TEXT(K3,"00.0%")&"-"&Text(K4,"00.0%").
And you see that we get an answer there of 05.0%-05.5% percent, and when I copy this down we're going to get all of the values.
And the reason I used 00 in the format is because I want it to sort correctly-- that the things with 10 should come after 09, and if we just use 9, the 9s would come to the end.
So I'm going to add a new column here; I'll call this "Group".
And we'll use the =VLOOKUP.
It's not like the normal VLOOKUP though.
Usually though, were you doing a VLOOKUP we'll put ,FALSE at the end; this is a range VLOOKUP and this is the one case where we don't put anything at the end-- don't put ,FALSE, basically defaulting to a TRUE.
And it's going to give us a range.
So I'll copy that down, alright, good, we're there.
And now when we create the Pivot Table-- Insert, PivotTable, OK-- I can take that field-- the Group field-- to the row labels, and then use Customer to get a count of the Customer and see how many Customers show up in each individual group.
So this does solve Jason's problem, although it really is sort of cheating because we're not solving it with a Pivot Table-- we're saying, "Okay, this is a weekness, this is where Pivot Tables can't do what we want, so therefore, we're going to go outside the Pivot Table and basically, back in the original data set, create a nice view of how we want that formatted." So a couple of tricks here: First of all using the TEXT function in order to format a number as we want it formatted; and then using a VLOOKUP without ,FALSE at the end, which basically says that anything between 6 up to 6.499999 is going to show up with 6 to 6.5%.
So, interesting use of VLOOKUP.
I almost always use the ,FALSE, which is the exact match.
Here's one instance where we found something where we could use the TRUE.
So, interesting way to go.
Alright.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Well, a couple of days ago, we talked about Jason's problem, where he wanted to show groups of percentages in a Pivot Table, and there was just no way to format that correctly.
One solution is, we could come out here to the right of the Pivot Table and build a little table with the group.
So I'm entering 5% and 5.5%, and I'm going to grab the Fill handle and drag that down far enough below our data, and then here, I'm going to use the TEXT function to format that exactly as I want it.
So =TEXT of the 5%, and in quotes I'm going to put 00.0% ampersand, and then I'll put a dash between the two percentages, ampersand, and then again the text of the next value, still with the 00.0%.
(=TEXT(K3,"00.0%")&"-"&Text(K4,"00.0%").
And you see that we get an answer there of 05.0%-05.5% percent, and when I copy this down we're going to get all of the values.
And the reason I used 00 in the format is because I want it to sort correctly-- that the things with 10 should come after 09, and if we just use 9, the 9s would come to the end.
So I'm going to add a new column here; I'll call this "Group".
And we'll use the =VLOOKUP.
It's not like the normal VLOOKUP though.
Usually though, were you doing a VLOOKUP we'll put ,FALSE at the end; this is a range VLOOKUP and this is the one case where we don't put anything at the end-- don't put ,FALSE, basically defaulting to a TRUE.
And it's going to give us a range.
So I'll copy that down, alright, good, we're there.
And now when we create the Pivot Table-- Insert, PivotTable, OK-- I can take that field-- the Group field-- to the row labels, and then use Customer to get a count of the Customer and see how many Customers show up in each individual group.
So this does solve Jason's problem, although it really is sort of cheating because we're not solving it with a Pivot Table-- we're saying, "Okay, this is a weekness, this is where Pivot Tables can't do what we want, so therefore, we're going to go outside the Pivot Table and basically, back in the original data set, create a nice view of how we want that formatted." So a couple of tricks here: First of all using the TEXT function in order to format a number as we want it formatted; and then using a VLOOKUP without ,FALSE at the end, which basically says that anything between 6 up to 6.499999 is going to show up with 6 to 6.5%.
So, interesting use of VLOOKUP.
I almost always use the ,FALSE, which is the exact match.
Here's one instance where we found something where we could use the TRUE.
So, interesting way to go.
Alright.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
