After summarizing data in a pivot table, you want to replace the numbers with a simple Yes/No text. Yes if they met the condition, No if they did not.
Episode 723 will show the rather convoluted process. In the process of creating the pivot table, you will see data grouping, calculated fields, table options, and then a bizarre conditional number format.
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!
Episode 723 will show the rather convoluted process. In the process of creating the pivot table, you will see data grouping, calculated fields, table options, and then a bizarre conditional number format.
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:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
What I'm doing my Excel seminars, you know we take questions from the audience every once in a while.
I get a question that just kind of makes my head Spin and today we had a question about a pivot table and basically we wanted to summarize data in a pivot table by customer in quarter.
But this was a report that was going to the higher-ups in the company, and we didn't want to show numbers.
We just want to show, did they meet the condition or not meet the condition?
And so, we basically had apply almost an if statement and put tax either yes, or no, and I have to admit that this one threw me for a loop and my ultimate solution is interesting.
I think it's cheating a little bit, but let me walk through how we did this?
We started with data pivot table, click [ finish ] and I'm going to put date along the left-hand side first, of course we have daily dates.
I need to roll that up to quarters, so I right click, group and show detail group and choose just quarters, click [ ok ].
Well now, that's short enough that we can have it going across the top of the report.
Down the side, I want customer and then in the heart of the pivot table, I want gross profit percent.
Now, that's not in my field list here.
I have profit and I have revenue.
So, I'm going to create a new field, formulas, calculated field and we'll call this GP, maybe the formula I have to get rid of the zero.
Click profit, divided by Revenue and we'll click [ add ].
Okay! So, you see that we have gross profit per cents throughout.
Now, a couple of things we want to get rid of these error values that means that there was no sales to that customer in that quarter.
So, we'll go to table options and say for empty sales, show blank, will also choose for error sales, show blank.
And I'm going to turn off the grand total for the columns and the grand total for the rows.
Click [ ok ].
Alright! So, we're getting closer now.
We have just a series of GP percents here, and this was where we said Okay! You know, let's pick some arbitrary number if it's greater than maybe .54.
We want to show yes, if it's less than .54, we wanna show no!
Of course, you have your own numbers.
And we talked about adding a field to the original data set, but that is at detail level, we need something at the final level here.
So, what I did was, I went to Sum of GP, and I double clicked on that first thing.
We're going to change the name.
That's a strange name Sum of GP.
We'll call it GP percent or something but that I'm going to go into the number tab and we're going to reach way back to the days of Lotus 123.
There's some bizarre bizarre, custom number formats and basically in square brackets, I'm going to put a condition.
The condition is greater than 0.54.
Put that in square brackets and then put a custom number format, that we should use.
Well, there's a bizarre thing you can do instead of displaying the number, we can actually display text.
So, in that case I want a 'yes' and then we'll put a semicolon.
Any other condition we want to use 'no'.
Click [ ok ] and sure enough, Excel will change all of those numbers which you know generally, you give it to CEO.
He's going to be confused with all those numbers, to just a very simple they made the condition or they didn't we have a series of yes, no's using a very...
I'm going to call it obscure custom number format that will allow you to create conditional formats and basically overriding the digit display completely with some text of either. Yes, or no.
Well, there you have it.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
What I'm doing my Excel seminars, you know we take questions from the audience every once in a while.
I get a question that just kind of makes my head Spin and today we had a question about a pivot table and basically we wanted to summarize data in a pivot table by customer in quarter.
But this was a report that was going to the higher-ups in the company, and we didn't want to show numbers.
We just want to show, did they meet the condition or not meet the condition?
And so, we basically had apply almost an if statement and put tax either yes, or no, and I have to admit that this one threw me for a loop and my ultimate solution is interesting.
I think it's cheating a little bit, but let me walk through how we did this?
We started with data pivot table, click [ finish ] and I'm going to put date along the left-hand side first, of course we have daily dates.
I need to roll that up to quarters, so I right click, group and show detail group and choose just quarters, click [ ok ].
Well now, that's short enough that we can have it going across the top of the report.
Down the side, I want customer and then in the heart of the pivot table, I want gross profit percent.
Now, that's not in my field list here.
I have profit and I have revenue.
So, I'm going to create a new field, formulas, calculated field and we'll call this GP, maybe the formula I have to get rid of the zero.
Click profit, divided by Revenue and we'll click [ add ].
Okay! So, you see that we have gross profit per cents throughout.
Now, a couple of things we want to get rid of these error values that means that there was no sales to that customer in that quarter.
So, we'll go to table options and say for empty sales, show blank, will also choose for error sales, show blank.
And I'm going to turn off the grand total for the columns and the grand total for the rows.
Click [ ok ].
Alright! So, we're getting closer now.
We have just a series of GP percents here, and this was where we said Okay! You know, let's pick some arbitrary number if it's greater than maybe .54.
We want to show yes, if it's less than .54, we wanna show no!
Of course, you have your own numbers.
And we talked about adding a field to the original data set, but that is at detail level, we need something at the final level here.
So, what I did was, I went to Sum of GP, and I double clicked on that first thing.
We're going to change the name.
That's a strange name Sum of GP.
We'll call it GP percent or something but that I'm going to go into the number tab and we're going to reach way back to the days of Lotus 123.
There's some bizarre bizarre, custom number formats and basically in square brackets, I'm going to put a condition.
The condition is greater than 0.54.
Put that in square brackets and then put a custom number format, that we should use.
Well, there's a bizarre thing you can do instead of displaying the number, we can actually display text.
So, in that case I want a 'yes' and then we'll put a semicolon.
Any other condition we want to use 'no'.
Click [ ok ] and sure enough, Excel will change all of those numbers which you know generally, you give it to CEO.
He's going to be confused with all those numbers, to just a very simple they made the condition or they didn't we have a series of yes, no's using a very...
I'm going to call it obscure custom number format that will allow you to create conditional formats and basically overriding the digit display completely with some text of either. Yes, or no.
Well, there you have it.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
