Let's take a look at our data manipulation and preparation challenge.
You received the below information for sales agents and you need to do the following.
Number one, create a log in user I.D. that consists of the last name followed by the first character
of the first name.
So here is a list of our sales agents.
This is where we want to have the system log in.
Let's just take a look at how much data we have.
So control and the down arrow key
to jump to the bottom of the data set.
It's until line 40.
Okay.
So right here we want to get something that is like "BridgeS" and the next one should be "ThompsonE".
What feature or tool can we use here?
Flash Fill, right?
Just gonna press enter, go to the next cell, and start typing and Flash Fill already picks it up.
All I have to do is press enter and I have my list, so let's jump to the bottom.
We have "HoferL" everything looks nice.
That was it.
Number one is done. Number two. Add the start
year to column D.
So the start date is here.
We just want to get the year. What can we use here?
Also Flash Fill. Start typing 2013 the next one is 2012.
Now Flash Fill came and then disappeared because I continued typing. what I'm going to do is to
use AutoFill and then to like Flash Fill from the AutoFill options.
You can also highlight the area and use the shortcut key Control+E.
So I'm just going to send this down.
Now, that's AutoFill.
Click on the AutoFill options here and select Flash Fill and we have our years.
This is 2013, 13.
This one is 12, 12.
That was number two, number two is done. Number three. Add a note, legacy comment to A9.
It says please input first name followed by last name.
Actually I'm just going to copy this.
So highlight this,
press Control+c to copy, go to A9, right mouse click, and add the comment.
So in my case I have Office 365, I have to select new note because I want to add a legacy comment here.
If you have an older version of Excel you need to select new comment because your new comment is my
new note. So I'll go with new note and since I copied it already I'm just gonna do Control+V and that's
my comment right there.
That was number 3 so every time we hover over this cell we see the comment. Number three is done.
Number four. Add a screen tip for C9 that describes the logic behind creating this system login.
So basically our secret, so a screen tip this time.
Can I just right mouse click and select screen tip?
No, I can't.
It's not an option there.
Screen tip is a part of the data validation tool.
Where do we find data validation?
It's in the data tab and it's right under the Data Tools Group here.
Just click on this and we get to our data validation settings.
Now I don't need to change anything in the settings I just want to add a screen tip.
So all I have to do is add an input message here. That's it and then click on OK. And now I have a screen
tip.
So every time someone goes to this cell, the screen tip shows up.
That was number four.
Number four is done. Number five. Add data validation for start date to ensure the data is input correctly.
Let's apply this to B10 to B50 and then ensure it works correctly if new dates are added. I'm going
to use Control Shift down to jump to the bottom and then just hold on the shift key and expand this until
we get to 50.
Now I want to add a validation for this.
So I'm going to go back to data.
Data Validation, this time I want to go to settings because I don't want to accept any value. I want to
make sure this is a date, so select date from this dropdown. And from data here
it really depends on what we feel comfortable with using.
I'm just gonna go with greater than. I could give between two specific dates but I'll just use greater than
and put a date that I don't expect anyone to have the start date on. So I'll put 1999
and I can add an error alert as well and then click on Ok.
So now let's test this, Let's jump down and input a date that's valid so I'll do 3/1/2020.
Press enter, that was accepted.
All good.
Let's change this to 1998.
Press enter, "please make sure the data is input correctly"
So I can retry,
Cancel, and try to put a date that's valid.
That is done.
That was number five.
Number five is done.
Challenge completed.