Locked lesson.
About this lesson
Organize your data using sorts and filters.
Quick reference
Sorting & Filtering
Quickly organize data by grouping them with sorting and filtering techniques
When to use
Learn how to group data or place them in a particular order. Learn how to temporarily remove data from a display using Excel filters
Instructions
Sorting
- Highlight the dataset that we want sorted, go to the ‘Data’ tab on the Ribbon
- Click ‘Sort’ in the Sort & Filter command group
- In the dialog box, select whether or not your selected data has headers
- Select the column you want your data to be sorted by, and choose the sort order
- Select additional columns if needed by clicking ‘Add Level’ in the within the dialog box
Filtering
- Select the data you want to create a filter for
- Go to the Data tab and click the Filter icon in the Sort & Filter command group
- Use the dropdown arrow to uncheck any data that you would like to temporarily remove from your display
- 00:06 If you have data that you want to quickly organize, then Sorting and
- 00:10 Filtering are your best friends.
- 00:12 You can sort data by text, numbers, dates and times, and even by multiple criteria.
- 00:20 Let's look at this list of office expenses, alphabetically, by Expense Name.
- 00:24 We'll just highlight the dataset that we want sorted.
- 00:29 Go to the Data tab on our ribbon and
- 00:32 click the A to Z icon on the Sort and Filter command group.
- 00:37 It's that simple.
- 00:38 Ours was easy, because we wanted to sort by the first column, and
- 00:43 Excel recognized our headers.
- 00:45 So, everything turned out as expected.
- 00:47 But what if we're sorting requirements we're a bit more detailed or complex?
- 00:53 Maybe we wanted to sort by the category, and then by the cost within each category.
- 00:59 In that case, we'd have to go to the sort dialogue box and
- 01:03 tell Excel how we want our data sorted.
- 01:05 Let's do that.
- 01:06 We highlight our dataset and go to the Sort icon.
- 01:11 So, we want our expenses sorted by category, first.
- 01:17 And we notice that Excel recognizes that our data has headers.
- 01:20 So, it excluded it from the highlighted section that it's about to sort.
- 01:25 If we unchecked that box, then Excel would treat row 4,
- 01:29 just like all the other rows, and sort it with the values below.
- 01:34 We don't want that, so, let's recheck the box.
- 01:37 My data has headers.
- 01:39 And we start by sorting the category column and
- 01:43 we are sorting based on cell values in an A to Z order, that's alphabetically.
- 01:49 Additionally, we want the expenses within each category to be sorted by their cost.
- 01:53 So, let's add a level, and
- 01:56 click the Total Cost column in the order of Largest to Smallest.
- 02:02 We click OK, and there we have it.
- 02:06 All the accommodation, employee, transportation, and
- 02:10 utility expenses are together, and each one is sorted by their cost.
- 02:14 What if we temporarily only wanted to see a certain set of data without
- 02:19 deleting the other data?
- 02:21 We can filter out what we don't want, and
- 02:24 display only the data we're interested in seeing for now, by using a filter.
- 02:29 We can start by clicking anywhere within that dataset that we want to create
- 02:33 a filter for.
- 02:36 If we click on the Filter icon in the Sort and Filter command group,
- 02:40 Excel will then create a filter arrow in the first row of the dataset.
- 02:44 If this isn't where you wanted your filter to be, just highlight the row that
- 02:48 you want to treat as your header row, and click on the filter icon, like this.
- 02:54 Highlight, and click Filter.
- 02:57 Generally speaking, if you have blank rows within your dataset,
- 03:01 Excel will assume that your dataset ends just before the blank row, and
- 03:06 a new dataset starts after the blank row.
- 03:09 Let's do that now.
- 03:11 Let's remove this filter, and create a blank row here.
- 03:17 If we click below that blank row and click on the Filter icon,
- 03:22 Excel will assume that this is a new dataset.
- 03:25 So let's Undo that filter.
- 03:28 So, if you want your filter to include all the data that follows your blank rows,
- 03:33 then manually highlight the entire dataset, blanks and all,
- 03:37 when creating your filter, click on your Filter icon,
- 03:41 and it will include the data below your blank rows too.
- 03:45 Each header row has a drop down menu, which includes a list of unique values
- 03:49 that you can choose to filter your data by.
- 03:52 Any box that's unchecked will be filtered out of your display.
- 03:57 So, if I only want to see the utilities category of expenses,
- 04:01 then I can Deselect All, then click Utilities.
- 04:05 I hit OK.
- 04:07 And I'm only seeing the utilities expenses for now.
- 04:11 As you can see from the blue row numbers, the other data is still there.
- 04:15 They're just hidden for the time being.
- 04:18 You'll also see that there's an option to view blank rows,
- 04:22 if there are blank rows within your dataset.
- 04:25 And then if you want to see everything again, then you can just look for
- 04:28 which column had filters applied.
- 04:30 That's this one.
- 04:31 Click on the down arrow, Select All, click OK, and your data is showing up again.
- 04:38 And if you want to remove the filter arrows altogether,
- 04:41 just click on the filter icon, and the arrows are gone.
Lesson notes are only available for subscribers.