Sorting and Filtering in Datasheet View

Locked lesson.

Upgrade

About this lesson

Organize and refine records in a table using sorting and filtering in datasheet view. 

Quick reference

Sorting and Filtering in Datasheet View

As our dataset grows, it's important to be able to use sorting and filtering in Datasheet view to organize and refine our data. 

When to use

We filter our data when we want to refine our results and we sort data to organize our records alphabetically and make them easier to understand. 

Instructions

Filtering data in Datasheet view

Applying a filter to a column or columns allows us to refine our dataset and only see records that match the criteria we have specified. This is particularly useful if we have a large dataset with many records. 

For example, maybe I want to filter to all trips that have an activity level of moderate. 

  • Select the 'Priority' column. 
  • From the Home tab, in the Sort & Filter group, click Filter. 
  • De-select all items in the list except High. 
  • Click OK to see the filtered results. 

Columns that have a filter applied will display a funnel icon in the column header. 

Clearing the Filter
  • Select the filtered column. 
  • From the Home tab, in the Sort & Filter group, click Filter. 
  • Click Clear filter from Priority. 
  • Alternatively, we can click Toggle Filter to turn the filter off. 
Using the Filter Drop-down 

We can also apply filters using the in-column filter drop-down arrows. 

  • Click the arrow in the 'Priority' column heading. 
  • De-select all items in the list except Low. 
  • Click OK to see the filtered results. 
  • Click Toggle Filter. 

Filters can be applied to multiple columns to further refine search results. 

Applying Text Filters

Filters can also be applied according to the type of data contained within the column. 

  • Select the CustomerName column. 
  • Click the arrow in the column heading. 
  • Hover the mouse over the Text Filters menu item. 

We can choose any option from this list to apply a text filter. For example, we might want to apply a filter to only show records that contain the word 'Harry'.

  • Choose Contains from the list. 
  • Type 'Harry' and click OK. 
  • Clear the filter. 
Applying Number Filters

This works for numeric fields also except we have a list of number filters instead. 

  • Select the 'Charge' column. 
  • Click the arrow in the column heading. 
  • Hover the mouse over the Number Filters menu item. 

This time we might want to filter for all trips that have a charge greater than $300. 

  • Choose Greater Than from the list. 
  • Type '300' and click OK. 
  • Clear the filter. 

Sorting in Datasheet View

Sorting data helps us make sense of records by organizing them alphabetically in ascending or descending order. 

By default, records are sorted by the Primary Key field which in our case is an autonumbered field. 

However, we can sort the records in a different order, by 'CustomerName' instead. 

  • Select the 'CustomerName' column. 
  • From the Home tab, in the Sort & Filter group, click Ascending to sort A-Z or Descending to sort Z-A. 
  • Click Remove Sort to return the records to their original sort order. 

Hints & tips

  • If we have values in a column that begin with a numeric value, when we apply an ascending sort, these records will appear at the top of the list. 
Login to download
  • 00:04 In this lesson, we're going to take a look at sorting and
  • 00:07 filtering records in datasheet view.
  • 00:10 Now most of us who have used other applications, maybe applications like
  • 00:15 excel, have probably used sorting and filtering previously.
  • 00:19 But it does work slightly differently in access, but
  • 00:21 I will say that if you do have experience using these tools then
  • 00:24 you probably are not going to find it too different.
  • 00:27 So let's start out with filtering.
  • 00:30 Now what filtering enables us to do is basically filter our records and
  • 00:35 really zone in on the information that's of interest to us.
  • 00:39 For example, it might be that I only want to see the records that
  • 00:44 have a priority of high, I could use a filter to sort that out for me.
  • 00:50 Now when it comes to applying filters in access,
  • 00:52 there are two methods that you can use.
  • 00:54 I'm going to make sure that I either select the priority column, or
  • 00:58 my mouse is clicked somewhere within it.
  • 01:00 And then on the Home tab, if I click the Filter button, I can then filter for
  • 01:05 exactly what I'm interested in.
  • 01:07 So I'm going to deselect everything.
  • 01:09 Just select High, click on OK,
  • 01:11 and now I'm only seeing the tickets that have a priority of High.
  • 01:15 Notice that once I've applied a filter,
  • 01:18 not only does it save filtered at the bottom down towards that status bar,
  • 01:23 but I also have this Toggle Filter button selected.
  • 01:27 If I click Toggle Filter again,
  • 01:29 it's basically going to toggle back to how my list was before I applied that filter.
  • 01:34 So that is one method you can use.
  • 01:37 The other method is to use the drop-down arrow in the column heading.
  • 01:41 So again if we use the Priority column as our example, I get the same filter.
  • 01:46 I can deselect and this time maybe I want to see everything with a status,
  • 01:51 or sorry, a priority of low.
  • 01:53 So very nice and straightforward.
  • 01:56 If I want to clear the filter from this column,
  • 01:59 I can click the drop-down again and I have a Clear filter from Priority option.
  • 02:04 And we can apply filters to multiple columns as well.
  • 02:08 So maybe I want to see everything that has a high priority,
  • 02:13 but I also want to see everything that has an SLA of 7 days.
  • 02:18 And there we go.
  • 02:19 So in just a couple of clicks,
  • 02:21 I've really managed to refine the records that I'm seeing.
  • 02:25 I'm going to click Toggle Filter,
  • 02:27 which will remove both of the filters from the columns at the same time.
  • 02:31 There's something else that you can do here, is depending on the data type in
  • 02:36 the column, you can use text and number filters.
  • 02:39 So if we select the CustomerName drop down,
  • 02:42 notice that we have a text filters option.
  • 02:46 So what I could do here is filter for
  • 02:49 all customers whose name contains the word, Harry.
  • 02:55 Click on OK.
  • 02:56 I have a result of two records.
  • 02:59 Toggle Filter to remove it.
  • 03:01 If I have a field that contains numeric values,
  • 03:04 when I click the drop-down instead of text filters, I have number filters.
  • 03:09 So I could choose to filter for all charges that are greater than,
  • 03:16 and let's say greater than $300.
  • 03:20 And click on OK, and my list is filtered.
  • 03:23 Toggle Filter to remove it again.
  • 03:26 So a few different options you have there when it comes to filtering.
  • 03:29 Now when it comes to sorting, again, we have fairly limited options here.
  • 03:34 Up in the Sort and Filter group, we can sort in ascending A to Z or descending.
  • 03:39 So if I choose the CustomerName column and
  • 03:43 sort in ascending, I'm going to get those sorted A to Z.
  • 03:47 Click Descending to sort the other way.
  • 03:50 If I choose Remove Sort, it's going to put the list back to how it was originally.
  • 03:56 And of course, if I click the drop-down arrow,
  • 03:59 I have both of those options in here as well.
  • 04:01 One final point to note here about sorting and filtering.
  • 04:04 If you have a column that maybe contains numerical values at the start and
  • 04:08 then some text,
  • 04:09 it's always going to position the numerical values at the top of the list.
  • 04:13 So those will come before A, if you're sorting A to Z.
  • 04:18 So just be wary of that, it does catch some people out in some circumstances.

Lesson notes are only available for subscribers.

Gift this course