Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Another great database type feature that Excel has is the ability to sort data. Now sorting could be a matter of just one or two clicks or you could make it a little more interactive if you need. You can filter your data as you are sorting as well, which is pretty cool. Now if you aren't unfamiliar with database technology, Excel regards each row as like a record in a database and each column is like a field. So we have a Last Name field, First Name field, and so on. So let's say we want to sort by Department.
Click anywhere in the Department column. Now do not select the whole column and you don't have to select the whole area. Just clicking anywhere in the Department column is okay. Now, we want to be on the Home tab and over here on the right click Sort & Filter, and just choose Sort A to Z and boom, it's done. Let's say we want to sort by State. Click anywhere in the State column, click Sort & Filter, and maybe let's sort from Z to A. And now we have Vermont at the top and California at the bottom.
We can do this by number also. Maybe you want to sort by many hours people put in. So click in the Hours, Sort & Filter, and Smallest to Largest or Largest to Smallest. Now, that's all great but what if you want to sort by multiple keys? So, for example, what if we want to sort first by state? Then once we have all the states grouped, we want to sort by Department, and then within the group departments, we want to sort by Last Name. So that's when we use what's called a custom sort.
So clicking anywhere in this data area is okay. Go back to Sort & Filter and then over here choose Custom Sort. So over here under Column where it says Sort By, click that down arrow and choose State. And we are going to sort on values from, let's make that from A to Z. Now we want to add a key. So click Add Level and where it says Then by we'll sort by Department, also A to Z.
Add another level and Then by Last Name. Unlike earlier versions of Excel, you aren't limited to only three keys. You can have as many levels as you want. And also up here we see that our data have headers and that's kind of important to leave that selected. Click OK and now it's sorted. Let's take a look at what's happening here. Let me select California. So C is the earliest one in the alphabet in our range here. So California is on top. Now, within California, we have Executive and Finance and so on and Sales comes latest in the alphabet.
Now, within Sales, let me select these two. We have Coules coming before Richardson. So that's how we have first the State, then the Department, then the Last Name. Now we can filter also. So click anywhere in the data area, go back to Sort & Filter, and from the menu here,choose Filter. Now when you do that, you get these little drop-downs coming up here and if you want to adjust the columns, you can do that. So let's say we wanted to see only those people who are in the Sales department.
So over here, next to Department, click that down arrow and you might need to scroll down and you can see all of the departments here. First thing is let's hide all of them. So where it says Select All, just click that so they are all deselected, scroll down, click in Sales, click OK and now we are filtered. So we see just the Sales department regardless of what state and regardless of what last name they got. Now, we could filter this even further. Maybe we want to see only those people who are in Sales who get a window seat.
So let's go over here to Seat Preference and let's open that up. Click the down arrow and I am just going to deselect Window. So Aisle is selected, click OK, and now it's sorted even further. You notice that next to Seat Preference, there is a little filter icon and next to Department, there is a little filter icon. Now while we are filtered, we can still sort by Last Name. So click somewhere in the Last Name field, go up to Sort & Filter, and let's Sort A to Z. So now we are sorted from Fitzpatrick down to Zarish.
Well, we can clear some of these filters, but we don't have to clear in the same order. Remember we first filter by Department, then by Seat Preference. We don't have to do it in reverse order. Let's un-filter this and two ways to do it. You can simply click Select All or you could click Clear Filter from Department. So now we see every state, every department, as long as the Seat Preference is an Aisle seat. Well, what if we want to see only those people who have a particular wage? Maybe everybody who has a wage greater than or equal to 1000.
Well, first, let's clear this filter over here, so I am going to clear that filter. So here is the Wage column, click that down arrow and let's choose number of filters. And let's choose Greater Than Or Equal To and where it says Greater than or equal to, type in 1000, click OK, and now regardless of what seat they have or state or anything else, we see only those people where the wage is greater than 1000. We can continue filtering this, we can continue sorting this. Maybe everybody who has wage greater than a thousand and is in New Jersey. We can click that down arrow. Deselect Select All.
By the way, you can stretch this out if you want. If that's easier. Click New Jersey and OK and there you go. So you could keep doing this over and over. But just one more thing. Let's say this is what we want. We have everybody in New Jersey whose wage is greater than 1000 and we want to take this data and put it on another worksheet. Well, click in here and we want to select all and the easiest way is press Ctrl+A, because all begins with the letter A. Now if you don't want that Payroll in there, you can simply just select like this.
It's your choice. So copy to the Clipboard. I'll just press Ctrl+C. And you see the marching ants have some multiple borders? That's because it's picking up just what you want and you can see over here the row headers are skipping numbers. Now let's go to Sheet 2, first cell is fine, press Ctrl+V to paste, and there you go. Well, the columns are a little too narrow. So if you click this little box in the upper-left corner and then just stretch out the columns. Now everything is out and this is not dynamically linked. This is simply copied and pasted and is exactly what you are looking for.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.