Now I say the arrow keys because it really depends on which way you're going. Left, right, up, or down. And we can select the cells to the next empty cell using this keyboard shortcut. So, for instance, if I want to select all of the salary numbers, I can click the first iteration of it, which is D3, and I can then use CTRL, SHIFT, down arrow. And it's going to select all of the data to the next empty cell. Now if you did have data, you had maybe an empty cell here, and then you had data continuing, you could then use ctrl shift down arrow to go to the next as well. So no worries if you do have blanks within your data, you can.
Still use this keyboard shortcut. And the reason why I say arrow keys is because let's say that I want to only select the first row here. I can do ctrl shift, right arrow, and it brings me there, or ctrl shift, down arrow, to select everything. This works really well, especially if you're using conditional formatting, because, well, conditional formatting does not like when you select headers. Typically it doesn't work well. So, if you don't want to use ctrl A, because ctrl A will select all the data, you just want to select the data, you can use that keyboard shortcut. Ctrl shift, down arrow, ctrl shift, right arrow, And we've selected all of the data..
I hope this really helps you, but yeah, try it out on your own. The next thing we're going to talk about is Flash Fill. And once again, this is one of my favorite features. It allows you to manipulate data really quickly and with ease. The way that we can do this is, let's say that we have First name and the last name here, and we decide that we want to concatenate it or combine it to a full name. Now, some of you have probably seen this before using concat, and we have a.
Function equals concat that will combine those two strings of text together. But what happens if I told you it'd be easier to actually use Flash Fill? Well, watch this. Let me give you an example. The first thing you have to do is give Excel an example of what you want. So, for instance, I want the full name. I'll just type John. I'll then press enter. Now there's a couple ways to do this. You can either start typing out the next example and it will autofill.
And use ctrl e and And that's it. It takes the first example. It says, okay, Joe, we see what you're doing. You're taking the first name, putting a space and the last name. We'll do it for you for the rest of them. Now, the only way this works is if the data is next to each other. For instance, I couldn't just come over here and do a first name column and hope for it to work. It would say it doesn't know what I'm talking about. So just keep that in mind. Let me show you more powerful things that I can do. I'm going to delete out all the data, and let's say that I want to.
Do LastName, comma, FirstInitial. Just give the example. LastName, comma, space, FirstInitial is J. I press enter, and then I use Ctrl E. And there we go. It follows the pattern all the way down. And I can continue to do things like this. Maybe I want to do just the initials, J. Press enter, control E, and boom, once again, it works beautifully. But it's not only just about combining things together like a full name. It's also about being able to data mine, pull data from data that already exists..
For instance, we have here this data prior. And maybe for some reason we need the year of hire, maybe we're ordering shirts for the company's anniversary, and we need to put the back of the date of hire on the back of the shirt, something like that. Well, what we can do is we can click into our first cell here. Type out what we want. What do we want? Just simply the year. Press enter, ctrl e, and boom. You might notice something though. It didn't work the way we wanted it to. Well, that's because if we look at this column, and we take a.
Look at the home tab, we can see that it's automatically the date. So if this happens to you, no worries. Just put no specific format, and it's gonna work beautifully. You're gonna select all of them. Make sure you choose general for all of them, and then you're going to try that again. So, I'll do that one more time here, I'm just going to select everything, clear this out, we'll put in our first date, that's what we want, 2020, hit enter, and then use control E, and it worked beautifully..
So if that does happen to you, no worries, don't be nervous. A lot of times what happens is you'll probably auto fill over, and that's why it's going to carry over the formatting. Just remember to change it back to general, and it'll work beautifully for you. Or you can also use the year function. But once again, with the year function, you're putting in a function with the result. Here, you're actually getting data as the actual data. Now, besides being able to just pull out a year from a date, we can also split things apart like location..
Posts Related:
- Design A Responsive Homepage With Full Screen Video Background - Part 2 BlogSpot
- PowerPoint Glamour - Channel Trailer
- Excel VBA Introduction Part 57.8 - Printing in Google Chrome using Selenium
Now, you'll see here that we have the location all together. Maybe we want to split it through all these different columns. - ID Card Make
Now, I know what you're saying, Joe, I've seen this before. You go to data. And we can use the text to columns in the data tool. And yes, that does work well. The only thing that I say there is, I don't like the wizard. And no, I'm not talking about Harry Potter this time. I'm talking about when you click on text to columns, it's going to open up a wizard that looks something like this. And you'll see step 1 to step 3..I don't want to go through all that. You got to pick a delimiter. All you simply have to do is use FlashVille. What's the city? Well, New York. Press Enter, Ctrl E, and I'm done. What's the state? New York, press enter, control E, you're done. What's the zip code? 10001, press enter, control E, you're done. Now you might have noticed something though..
First off, our states didn't come over perfectly. And neither did our zip code. We're missing some of those leading zeros. You will run into this once in a while. And that's okay, I'll show you how to fix it. First off, when we do New York, it thinks automatically that it's following the pattern of stealing the N from here and the Y from here. That's why it says LA, it's stealing the L and the A. And then it only sees Chicago, so it's like, oh, the C, the H. It thinks it's taking it from these columns here. That's not what we want. So what we can do instead is we can give it more examples..
It's okay to give FlashFill more examples. The first thing I'm going to do is delete this all out. We're going to click into state, and we're going to do N, Y. We're going to press enter, and we're going to do C, A. That way now we're telling auto, uh, FlashFill that we want to follow this pattern, New York and California. We can then press enter again, use control E, and you'll see it fixes the issue. For the issue for the leading zeros, this tends to be a common problem. Excel doesn't like having leading zeros unless it's formatted that way..
So all you need to do here is select your zip code. I'll use my keyboard shortcut that we learned the first time. Control shift down arrow. I'm going to right click and I'll select format cells. Because format means change the appearance of the cell. Right now, if we look at the formatting for these, by clicking Format Cells, it's going to tell us it's in a general format. And that's not what we want. We want the zip code format. And yes, there's actually a zip code format. There's also a social security format and a telephone format..
You can find them under the special category and you'll see zip code, zip code, plus the four digits phone number, social security number, pick whichever one makes sense and also pick your location. I'll pick the zip code, it already says United States, and click OK. And take a look at this one right here, where it's the 2101. Once I click OK, you'll have the leading zero, and there it is. So if you ever have issues with zip codes, always remember there's a format for zip codes. Now we have this looking great, and as you can see, this was pretty quick to get done..
If you ever run into those little issues there, now you know how to troubleshoot them as well. I'll see you in our next example. Thanks, everyone. Welcome back, everyone. The next thing we're talking about today is data validation. This is, and you've probably heard me say it, another great tool that I love in Excel. It allows us to force data to be selected, and the reason why it forces it is so that we can validate that the data going into our datasets is accurate..
There's nothing worse than somebody filling out a form and putting in accurate data that we later need to fix. So by forcing them to put in the correct data, that will allow us to essentially have better looking data. Let's take this service form, for example, and let's showcase what could happen if we just allowed them to fill out anything. Michael Lee could say, okay, I'm going to put in today's date. And they might do something like this. And they don't mean to, they just accidentally slipped on the keyboard..
We then have the service received. And they're like, well, what service did I receive? Um, it was a repair. I think it was something repairs. Service rep? Oh, I know who I had. I had Rob. And, uh, best contact number to get me by. So, let's go with... My best number here. Okay, we're good to go. The problem is a couple of things here. Because we're not validating the data, they put in the incorrect year..
They just put in a random service received, which this might not match our data that we're trying to gather.
You put in Rob instead of Robert or even Rob. And also added an extra digit for the best contact number. Now this happens sometimes, but to actually reduce the margin of error, we're going to use data validation. I'm just going to clear these out, and we're going to go through all the different types of data validation that will help us force the correct data. So first off, where is data validation located? Well, since we're talking about data, it's going to be in the data tab..In that data tab, we're going to go over to the data tools over here. And that's where you'll find data validation. That little icon there kind of looks like a green check mark and a red deny sign, and we're going to give it a click. It's going to populate this dialog box. And right now it's saying that whatever cell we have selected, any values allowed there. So I'm going to cancel this out because it's important to know that you want to select all of the cells that you want to affect, select to affect..
So I'm going to select from B5 to B10. And then I'm going to click on data validation. That way it's affecting all of these cells at one, and I don't have to do it individually. So right now, anything's allowed in there a number, a date, a function, a string of text, whatever I want. But we don't want that. We wanna actually force something. What do we wanna force? A date. So you'll see here we can force a whole number, a decimal number, a list of data, a date, a time, a text length, or even customize our own..
So I'm going to say here that I want to do, let's do a date, and then we can even choose data between. Now if you want to do between, let's say, one date to another, you'll use between. If you want the date to be greater than or equal to, or greater than, or equal to, or greater than, or not equal to, or equal to whatever the comparison operator you want, you can use. Now, I'm gonna just say that I know for a fact that this service form.
Was given from January to February. So I'm gonna say between, and I'm gonna choose the two dates. I'll say 1, 1 20 23. And then the end date we can say is, let's do, let's say on the 25th, we ended at 2 25 2023. Now that we have this, we can click OK. And we have to enter in dates between those two times..
If I enter in a date that's not there, like let's say 5 31. 2023. It's going to say that it doesn't match my validation. Now, I don't like this message. We can actually create our own customized messages. So just re select B5 to B10. We're going to go back to data validation. And after you choose the actual rule that you're creating or the validation criteria, you can then also do an input message to let people know exactly what you want and an error message just in case they don't listen to your validation..
Let's start with the input message. Let's tell people that they need to pick a date between January 1st to February 25th. So I'm going to say, please select date between, and you can put this any way that you want. I'll say between January 1st, 2023 to February 25th, 2023. So now we're giving them some direction, and it's always good to do an input message. Next, we'll do an error message. And it's important to note that these error messages will appear and stop them.
From actually continuing if it says stop. But if you select warning Or if you select information, it will just give them a warning sign or an information sign with whatever text you have in there, but it will allow them to continue to make the error. Only the stop one will make them stop and re input the information until it's valid. So keep that in mind that we want to use stop here. We'll then put in a message and just say, not a valid date selection. And you can put whatever you want here. Now I'm going to click OK. And once they click in there, you'll see please select the date between.
January 1st, 2023 to February 25th, 2023. You can quickly put in a false one to see if this is working okay, press enter. And now it gives our custom message saying not a valid date selection. So I'll retry and let's do 2 15 2023. And it accepts that. So right now we are forcing a date, which is beautiful. It's working nice. We have our error code. We have information so that they know what to actually do..
And we have our actual criteria. Next, we want to go to the service received. And what we're going to do here is we're just going to simply make a list, and this list is going to be custom. It doesn't matter what we put in here, it's going to be a list of items that we create. So the first thing you always want to do is select to effect. I'm going to select from C5 to C10. I'll then go over to my data validation, and I'll see that right now, if we go to settings here, Anything's allowed in there. But what I want to put in there is an actual list of data..
So I'm going to click the dropdown and choose list. Now for this list, all you need to do is put the item and separate them by a comma. So I'm just going to say something like this new hardware, new software repair, and that's fine. I think only those three are good. We'll put in the input message. We'll just say select option from list, and then we can even put in an error alert saying not a valid selection. We'll click OK, and let's see if it works..
We get a little drop down, select option from list, and yeah, it works beautifully. New hardware, software repair. We can choose which one makes sense to us. And if somebody tries to enter in like, custom, and says hello or something, it's going to say not a valid selection, and it'll make you retry. So they have to select one of these items from the list. Now we're going to do the same thing for our service route. Except, instead of creating the list ourselves, We already have a list of service rep over here on the side..
What I want to do is instead of having to put all the work into type out their names, what I can do is I could just select the list as a reference. That way it creates a list of data based off of this list here. So to do that, same thing, we're going to select from D5 to D10. We'll go over to data validation. We'll then put in our settings, which we want to create a list again. This time our source, though, is not going to be something we create. It's going to be from G5 through G8..
We'll put in our input message, which will be the same. Just select from list of reps. Error alert, not a valid selection. And we'll click OK. Now we'll see select from list of reps. We'll select anyone here. Rachel, make that a little bigger. And there we go. And the best part about it is if something changes, let's say Rachel gets married, maybe Rachel gets married to me. Now in the list, it's updated..
So we can reselect the new name. So that's one of my favorite things about creating these lists is we can create them from scratch. Or, you're more than welcome to actually select them from a list that already exists. The next data validation that I'm going to show you is going to be a customized one. Let's say that we want to force a phone number. Well, the first thing we need to do is let Excel know that this is going to be a phone number. So, we're going to select from E5 to E10, and we're going to do the same.
Thing we did at the last exercise. We're going to right click, we're going to go to Format Cells. And if you remember, last exercise, we decided that we needed a special category, and we used zip code. Well, in this case, it's a phone number. So I'm going to choose phone number, and I'm going to click OK. So now Excel knows that any numbers that go in here will look like a phone number. Even if I just put a bunch of numbers here, it automatically gives it that look of a phone number, which I love. So that's the first step. I'll just delete that out..
Now, when it comes to custom data validation, it's important to note that you cannot select all of them like we've been doing. It's only for if you use data validation that's predefined, like whole number, decimal, list, date, time, x length. If you're doing a custom, most likely that means that you're going to write a formula. So you're going to have to reference the formula. So for instance, I'm going to select E5 only in this case. Once you select E5, we'll go about the same way. We're going to click Data Validation, and this time we're.
Going to force a custom criteria. Now, formula I'm using, I'm actually going to nest two functions inside of an and function. And if you want, you're more than welcome to use this in any of your personal work files. But for me, I'm going to just showcase what it does. I'm going to use two functions. called the len function and is number function. This is going to force it to look. Is this a number? Because we don't want someone just writing any text in there.
Because it's a phone number. So it's going to check if it's a number and then it's also going to check the length of a number. Now, the American length of a number is going to be 10 digits. For every other location, it's always going to be different. So, you'll just really tweak this formula to be yours. So, for me, it's going to be 10 digits. For you, it might be 11 or 12. Now, the first thing we have to do is ask two questions, which means, if you want to ask two questions, we have to use the and. Because we're asking, Is it a number, and is the length equal to 10?.
So I'm going to start off my formula using an and, and I'm going to say, Is Number, and I'm going to put E5, and all that's doing is checking if E5 is a number. I'll then put my comma, and I'm going to start my next function, which is my len. I'm going to say, is the len of E5 equal to, so I'm asking these two questions now. I'm asking, is whatever's in E5 a number? And is the length of that number 10 if it's not a number, and if the length isn't.
10, well, then it's going to give them an error if it is a number, and it is a length of 10, and it's going to be valid. And now that I have this, this looks great. I'm going to put in an input message and just type, please enter valid phone number, and then the error alert will just be not a valid number. I'll then click OK, and let's see if it works. I'll put in an extra zero, it's going to say not a valid phone number, that worked. Now we're going to do a normal number, and it worked beautifully..
How awesome is that? With that said, that is our data validation. We've now seen how we can force a date, how we can create a list from scratch, or a list from a list that already existed, or FlashFill. Now, I'm going to showcase a couple more techniques to grab data that may not be present at the moment, but still lies in our data. It's known as metadata information about information that already exists. So you can see here that I have this payroll information and maybe what I want to do is I want to grab a couple more things that can be very useful in. other types of visualizations, or even being able to create graphs,.
Or just analyze the data in general. So to do this, I'm going to decide what I want. And the first thing I probably want from this data set is the gross pay. I have the hours, That are worked per week, and I also have what the hourly rates are for each employee. So it would be a good idea to see how much gross pay they make weekly. The first thing I'm going to do is I'm going to create another column here, and I'm just going to autofill this header right over. I'll adjust it, and I'm just going to rename it gross pay. Next up, I'm going to write a function and I'm just going.
To do a simple formula here. I'm going to do equals and I'm going to select from H4 and multiply that using my asterisk by I4 and press enter. Of course, I don't want to have to do that for all this data because there's a lot of data here. So I will be utilizing my autofill and that autofill handle is going to be in the bottom right hand corner there. And once you hover over that, you'll see that your white cross haired cursor will turn into a black cross haired cursor. Once there, you can either click and drag, or, if there is data living to.
The left of this dataset, or to the right, You can then just do a quick double click, so I'm going to do that because I have data to the left there. I'll double click and there we go. We have our gross pay now. I'm not because I also want more information. Maybe I want the year of fire next because who knows? I could use that for statistics, right? I could say how many. People have we hired in 2004 versus 2010 and we can see that trend..
So I'm going to create another column here. Let's call this one year of hire and to get the year of hire. We've already seen that flash field can do this or you can do equals and you can use the year function. It's really up to you on how you want to grab this. If you use the year function, once again, what's really living in this cell is a function. So if you're trying to do like calculated calculations later on, it might not come over so authentically. So what I want to do is I want to just actually pull this using flash.
Fill so that it's an actual value. Otherwise you would have to copy it, right click and just paste it as a value. And I don't want to have to go through that since I could just use class fill regardless. Let's do that. I'm going to grab 2006, press enter, and then use control D. And there you go, I get all of the years. Next thing I want to do is grab the month of hire. For the same reason I have the year of hire. I want to see how many people I hire in, let's say, December versus June. So, I'm going to grab the month of hire. And once again, you're more than welcome to either autofill, Like this,.
Or if you wanted to, what you can also do is just type out what you want. I'm going to do a month of hire, and it will automatically copy that format since it's right next to the data. So now that I have the month of hire, we could use what's known as the month function, right? And once again, that's going to give me something that looks like this. It's going to give me that 12. We can also just pull that 12 if we wanted to, using our flash fill, we can put a 12 press enter and use control E. But what happens if we don't want to look at the data like this?.
Maybe we don't want numeric data, we want a string of data. Which means that you want, instead of a 12 here, you want it to say December, whether it's abbreviated or non abbreviated. In that case, we can't use flashValue, and we can't use month. The month function will always return a numeric value. So instead, we're going to use another type of function. The function I'm going to showcase is known as the text function. Now, the way the text function works is it takes a numeric value and it turns it to a string of text. But it really depends on the built in arguments that you want..
