Today's Tip comes from Tony - who was watching Bill Jelen's "LiveLessons Power Excel 2010 DVD". [ http://www.mrexcel.com/livelesson10.html ] Tony wrote in to tell us that there is an easier way to mark items with a 'Gold Star', as exemplified in the DVD. Today, in Episode #1566, Bill 'MrExcel' Jelen shows us the method that he originally used and then the alternate method that also allows more versatility including the ability to show nothing at all if items fall outside of your set parameter. Nice!
...This a the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This a the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast: Only Gold Stars.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen from MrExcel.
That was, ahh – got a great email from Tony.
Tony was watching the live lessons Power Excel 2010 DVD and he says, “Hey, you know, there’s an easier way to do what you are trying to do.” I was using icon sets, and so, this is Home, Conditional Formatting, Icon Sets and then, just outside to your field of view, I’d use the 3 stars.
The 3 stars.
So, depending on the shape of your data or the range of your data, Excel automatically decides which numbers get gold stars, which numbers get the half-star and which numbers get no stars.
And I went through this convoluted set of steps to get only the gold stars and Tony says, “Wait, starting in Excel 2010, they made this easier.” So, we went to Manage Rules and say that we want to Edit the Rule, alright?
And I had done this, I said, we want to go to an absolute number, everything >=90 gets the gold star.
But then I’d use the second rule and stop if true to make sure that we never got the icon set for 89 and less.
But look at this.
It’s really cool.
They added a drop-down here where we can say, No Cell Icon.
We could also mix and match.
So if, you know, for some reason I don’t know why you would want to do it, you’d want to do, you know, the green traffic light this way but mix it up with the yellow flag and the red X.
You can do that now starting in Excel 2010.
But this, it’s just a cool way to make sure that we have no cell icon for the things that don’t qualify for the gold star.
Click OK, click OK.
And now, you know, one of the things that frustrates me here is the icon.
It’s always along the left and I always right-justify my numbers.
I’m going to go into Alignment, and change this to Right with an Indent, then click in that up to 4.
That will kind of right-justify the numbers in so far as that they always line up, but they’ll be closer to the icons set there.
And here, let’s just take a look.
We’ll do some random numbers, RANDBETWEEN(60,99).
And so every time I press F9, the gold stars only appear on the scores above 90.
Cool trick here starting in Excel 2010.
I want to thank Tony for sending that in.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast: Only Gold Stars.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen from MrExcel.
That was, ahh – got a great email from Tony.
Tony was watching the live lessons Power Excel 2010 DVD and he says, “Hey, you know, there’s an easier way to do what you are trying to do.” I was using icon sets, and so, this is Home, Conditional Formatting, Icon Sets and then, just outside to your field of view, I’d use the 3 stars.
The 3 stars.
So, depending on the shape of your data or the range of your data, Excel automatically decides which numbers get gold stars, which numbers get the half-star and which numbers get no stars.
And I went through this convoluted set of steps to get only the gold stars and Tony says, “Wait, starting in Excel 2010, they made this easier.” So, we went to Manage Rules and say that we want to Edit the Rule, alright?
And I had done this, I said, we want to go to an absolute number, everything >=90 gets the gold star.
But then I’d use the second rule and stop if true to make sure that we never got the icon set for 89 and less.
But look at this.
It’s really cool.
They added a drop-down here where we can say, No Cell Icon.
We could also mix and match.
So if, you know, for some reason I don’t know why you would want to do it, you’d want to do, you know, the green traffic light this way but mix it up with the yellow flag and the red X.
You can do that now starting in Excel 2010.
But this, it’s just a cool way to make sure that we have no cell icon for the things that don’t qualify for the gold star.
Click OK, click OK.
And now, you know, one of the things that frustrates me here is the icon.
It’s always along the left and I always right-justify my numbers.
I’m going to go into Alignment, and change this to Right with an Indent, then click in that up to 4.
That will kind of right-justify the numbers in so far as that they always line up, but they’ll be closer to the icons set there.
And here, let’s just take a look.
We’ll do some random numbers, RANDBETWEEN(60,99).
And so every time I press F9, the gold stars only appear on the scores above 90.
Cool trick here starting in Excel 2010.
I want to thank Tony for sending that in.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
