Dueling Excel with Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen looks at 'Employee Review'. Its that time again and We want to show each employee their performance. While rated against everyone else, we only show the name of the employee. In Episode #1662, we will learn two ways to create this Chart. Also watch as Bill struggles to remember how to make a Chart Title all caps and how he whacks out the Recommended Charts feature in Excel 2013.
Dueling Excel Podcasts...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. Slaying Excel Dragons [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"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"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcasts...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. Slaying Excel Dragons [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"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"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back.
We're live here in the wonderful Highline Community College.
Now, there dueling Excel podcast, guess this is our episode 115.
I'm Bill Jelen from MrExcel, joined here by Mike:Excel is fun.
Bill: Mike Girvin. All right, so here.
This was a strange one sent by YouTube.
They want to do like employer review but they don't want any of the employees to see any of the other employees names.
But they want to see their values.
So, the first thing they want to do is kind of at the top of the screen.
And I'm gonna hide this stuff later.
Here's we gonna do some Data Validation, to allow us to choose a social security number or code, whatever this is.
This code over here is looks like social security numbers Bill: But maybe it's not.
Mike: Or it's like employee ID.
Bill: Employee ID, so they're gonna be able to choose that and then we're gonna want to chart the data down here.
But only show the label for the employee that we've chosen.
All right, So let's see I just need to count how many we have here and I guess I can subtract 12 minus 1 to tell that there are 11.
So, we'll copy that down 11 times...
Mike: You did that without Excel?
Bill: without Excel. Yeah, I was gonna do an Excel. I said no, no way.
I really think I could do 12 minus 1.
You know if It started in row 7 or something like that, I might have had to do the one of my tricks for counting data.
All right, so over here.
This is where the labels gonna be.
This is our value. This is our label.
So, I'm gonna say equal, IF, equal, IF.
This ID number is equal to the ID number they chose.
F4.
Then I want the corresponding name from column B.
But I'm not gonna F4 the A2 or the B2.
Those are ought to be relatives that way they could move down and then otherwise, I want quote, quote nothing.
All right, so now, I have that in.
The fill handle will look to the right if there's nothing to the left.
So, there we go that Jesus fill!
And then we'll choose someone else Gigi... Rock and roll.
I'm gonna call this Score something like that.
I don't know why I'm going to do that.
Alright, did they say what kind of a chart they want?
Plot all numbers on a chart.
Mike: Like a column.
Bill: You know what, I don't know what kind of chart to use.
I'm going to use insert recommended charts.
Mike: Oh! That is awesome.
Bill: All right, that will give me a pie chart.
No, it will give me a bar chart.
Now, I'm gonna go with the column chart.
All right, there we go. I like it.
And let's do a little Magic here with the cell style.
See if we can find it interesting one.
I love this in Excel 2013.
Mike : Look at that.
Bill: Look at that SCORE 41, all that.
Is that title, what's going on with that title.
That is really, really evil.
The recommended charts, click delete.
You know the hack, create of those recommended charts, we're obviously confusing Excel.
Because the blank cell...
It should love the blank cell.
That's so funny.
I'm gonna have to go back and watch that video.
They can't even recommend...
All right, all right, column chart.
There we go. Score, that's that's funny all right.
That's a really cool feature, if I could just remember what it was.
All right, so we'll go with that one.
I love the fact that we're getting all caps here because this isn't at all caps.
They're automatically capitalizing that.
Mike: Why are they doing that?
Bill: Yeah, it's good. It's a good feature, but it's really hard to find.
Hey, it's Bill Jelen.
I'm gonna interrupt the the Dueling podcast here.
It was so hard to find us but the next three minutes there and I couldn't couldn't ever find it .
So, here's how you make that be all caps, we choose the the title.
I'm gonna right-click on the border and go to Font.
Dot, dot, dot.
For some reason this box offers things that all of the other boxes don't offer, including all caps and smaller caps.
All right, so those are...
Choose one or the other.
All right, I don't know why it's not anywhere in the Format Chart Title.
And go through all these icons, all these other things.
I cannot get it.
But in the font box, we can change that to all caps.
There you go alright back to the dual.
Alright, so there we go.We have our chart.
They don't want to see all this data up here.
So, I'm just gonna use...
Select everything.[ ALT+O R H ] Format Row Hide.
Now, when we choose a new ID, name will reflect only the person we've chosen.
They get to see how they compare to everyone else.
They don't get to see everyone else's name.
Bill: All right, Mike.
Mike: Cool! I love that.
That formula, I think is a little bit easier than the one was thinking about.
Let's see. I'm gonna copy this data, paste it right here.
[ CTRL+C ] I'm gonna do data validation [ ALT+D L+Tab ] and then something like that.
So, I'll click Ok.
So, we can select this.
Now, I'm actually gonna move this over here.
I'm gonna do a LOOKUP.
Just a straight VLOOKUP.
And look the name up in a separate cell right here.
So, if you look up, I'm gonna look at that.
Comma, within that.
F4, I actually didn't need to lock it.
In the second column, comma, 0.
Exact match.
All right, so I'm gonna have that there.
Now I'm gonna...
Let's see highlight this. I think MrExcel said 11 right?
Bill: Evelen...
No, I'm gonna use the screen tip because I'm not as smart as doing that math in my head, and I'm gonna say equals this right here and then [ CTRL+ENTER ] So, we got that and now right here, I'm gonna do something slightly different.
I'm gonna do a silly array for it I'm gonna say if anything in this range...
Oh! You know I didn't hate to do that name right there, but anyway if anything in that range right there, you know what, I don't need to lock it.
When you're doing a array format, I'm gonna do an array formula that delivers multiple values, simultaneously to multiple cells.
You don't have to lock the cells because the same formula gets put into every cell when you do [ CTRL+SHIFT+ENTER ] but I'm gonna say, hey, anything in there equal to this name right here.
If that's true, then I want this name, otherwise, I want a double quote.
Alright, and you're ready.
[ CTRL+SHIFT+ENTER ].
So now I have this right here.
[ ALT+F1 ], that's the default chart and I'm gonna put, let's see, Data labels, and then get rid of these.
Delete, get rid of this, delete.
Even get rid of that sales, nice at sales, and so now I have this little chart right here.
I think I can select a different number and there it is.
Tina got 33, everyone else got different numbers right.
Who's this last one right here. There's min 10.
Alright min happens to have some min value.
Bill: That's so funny.
All right, well, there you go.
Creating a chart, that shows only the selected person's name.
I wanna thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel.
We're live here in the wonderful Highline Community College.
Now, there dueling Excel podcast, guess this is our episode 115.
I'm Bill Jelen from MrExcel, joined here by Mike:Excel is fun.
Bill: Mike Girvin. All right, so here.
This was a strange one sent by YouTube.
They want to do like employer review but they don't want any of the employees to see any of the other employees names.
But they want to see their values.
So, the first thing they want to do is kind of at the top of the screen.
And I'm gonna hide this stuff later.
Here's we gonna do some Data Validation, to allow us to choose a social security number or code, whatever this is.
This code over here is looks like social security numbers Bill: But maybe it's not.
Mike: Or it's like employee ID.
Bill: Employee ID, so they're gonna be able to choose that and then we're gonna want to chart the data down here.
But only show the label for the employee that we've chosen.
All right, So let's see I just need to count how many we have here and I guess I can subtract 12 minus 1 to tell that there are 11.
So, we'll copy that down 11 times...
Mike: You did that without Excel?
Bill: without Excel. Yeah, I was gonna do an Excel. I said no, no way.
I really think I could do 12 minus 1.
You know if It started in row 7 or something like that, I might have had to do the one of my tricks for counting data.
All right, so over here.
This is where the labels gonna be.
This is our value. This is our label.
So, I'm gonna say equal, IF, equal, IF.
This ID number is equal to the ID number they chose.
F4.
Then I want the corresponding name from column B.
But I'm not gonna F4 the A2 or the B2.
Those are ought to be relatives that way they could move down and then otherwise, I want quote, quote nothing.
All right, so now, I have that in.
The fill handle will look to the right if there's nothing to the left.
So, there we go that Jesus fill!
And then we'll choose someone else Gigi... Rock and roll.
I'm gonna call this Score something like that.
I don't know why I'm going to do that.
Alright, did they say what kind of a chart they want?
Plot all numbers on a chart.
Mike: Like a column.
Bill: You know what, I don't know what kind of chart to use.
I'm going to use insert recommended charts.
Mike: Oh! That is awesome.
Bill: All right, that will give me a pie chart.
No, it will give me a bar chart.
Now, I'm gonna go with the column chart.
All right, there we go. I like it.
And let's do a little Magic here with the cell style.
See if we can find it interesting one.
I love this in Excel 2013.
Mike : Look at that.
Bill: Look at that SCORE 41, all that.
Is that title, what's going on with that title.
That is really, really evil.
The recommended charts, click delete.
You know the hack, create of those recommended charts, we're obviously confusing Excel.
Because the blank cell...
It should love the blank cell.
That's so funny.
I'm gonna have to go back and watch that video.
They can't even recommend...
All right, all right, column chart.
There we go. Score, that's that's funny all right.
That's a really cool feature, if I could just remember what it was.
All right, so we'll go with that one.
I love the fact that we're getting all caps here because this isn't at all caps.
They're automatically capitalizing that.
Mike: Why are they doing that?
Bill: Yeah, it's good. It's a good feature, but it's really hard to find.
Hey, it's Bill Jelen.
I'm gonna interrupt the the Dueling podcast here.
It was so hard to find us but the next three minutes there and I couldn't couldn't ever find it .
So, here's how you make that be all caps, we choose the the title.
I'm gonna right-click on the border and go to Font.
Dot, dot, dot.
For some reason this box offers things that all of the other boxes don't offer, including all caps and smaller caps.
All right, so those are...
Choose one or the other.
All right, I don't know why it's not anywhere in the Format Chart Title.
And go through all these icons, all these other things.
I cannot get it.
But in the font box, we can change that to all caps.
There you go alright back to the dual.
Alright, so there we go.We have our chart.
They don't want to see all this data up here.
So, I'm just gonna use...
Select everything.[ ALT+O R H ] Format Row Hide.
Now, when we choose a new ID, name will reflect only the person we've chosen.
They get to see how they compare to everyone else.
They don't get to see everyone else's name.
Bill: All right, Mike.
Mike: Cool! I love that.
That formula, I think is a little bit easier than the one was thinking about.
Let's see. I'm gonna copy this data, paste it right here.
[ CTRL+C ] I'm gonna do data validation [ ALT+D L+Tab ] and then something like that.
So, I'll click Ok.
So, we can select this.
Now, I'm actually gonna move this over here.
I'm gonna do a LOOKUP.
Just a straight VLOOKUP.
And look the name up in a separate cell right here.
So, if you look up, I'm gonna look at that.
Comma, within that.
F4, I actually didn't need to lock it.
In the second column, comma, 0.
Exact match.
All right, so I'm gonna have that there.
Now I'm gonna...
Let's see highlight this. I think MrExcel said 11 right?
Bill: Evelen...
No, I'm gonna use the screen tip because I'm not as smart as doing that math in my head, and I'm gonna say equals this right here and then [ CTRL+ENTER ] So, we got that and now right here, I'm gonna do something slightly different.
I'm gonna do a silly array for it I'm gonna say if anything in this range...
Oh! You know I didn't hate to do that name right there, but anyway if anything in that range right there, you know what, I don't need to lock it.
When you're doing a array format, I'm gonna do an array formula that delivers multiple values, simultaneously to multiple cells.
You don't have to lock the cells because the same formula gets put into every cell when you do [ CTRL+SHIFT+ENTER ] but I'm gonna say, hey, anything in there equal to this name right here.
If that's true, then I want this name, otherwise, I want a double quote.
Alright, and you're ready.
[ CTRL+SHIFT+ENTER ].
So now I have this right here.
[ ALT+F1 ], that's the default chart and I'm gonna put, let's see, Data labels, and then get rid of these.
Delete, get rid of this, delete.
Even get rid of that sales, nice at sales, and so now I have this little chart right here.
I think I can select a different number and there it is.
Tina got 33, everyone else got different numbers right.
Who's this last one right here. There's min 10.
Alright min happens to have some min value.
Bill: That's so funny.
All right, well, there you go.
Creating a chart, that shows only the selected person's name.
I wanna thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel.
