Google Sheets: Sorting and Filtering Data

Lesson 16: Sorting and Filtering Data

/en/googlespreadsheets/working-with-functions/content/

Introduction

Google Sheets allows you to analyze and work with a significant amount of data. As you add more content to your spreadsheet, organizing information in it becomes important. Google Sheets allows you reorganize your data by sorting and applying filters to it. You can sort your data by arranging it alphabetically or numerically, or you can apply a filter to narrow down the data and hide some of it from view.

In this lesson, you will learn how to sort data to better view and organize the contents of your spreadsheet. You will also learn how to filter data to display only the information you need.

Types of sorting

When sorting data, it's important to first decide if you want the sort to apply to the entire sheet or to a selection of cells.

  • Sort sheet organizes all of the data in your spreadsheet by one column. Related information across each row is kept together when the sort is applied. In the image below, the Name column has been sorted to display client names in alphabetical order. Each client's address information has been kept with each corresponding name.
    Sorting a column by alphabetical order
  • Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content on the worksheet.
    Sorting a range

To sort a sheet:

In our example, we'll sort a list of customers alphabetically by last name. In order for sorting to work correctly, your worksheet should include a header row, which is used to identify the name of each column. We will freeze the header row so the header labels will not be included in the sort.

  1. Click View and hover the mouse over Freeze. Select 1 row from the menu that appears.Freezing the header row
  2. The header row freezes. Decide which column will be sorted, then click a cell in the column.
    Selecting a cell in the desired column
  3. Click Data and select Sort Sheet by column, A-Z (ascending) or Sort Sheet by column, Z-A (descending). In our example, we'll select Sort Sheet by column, A-Z.
    Selecting Sort sheet by A to Z
  4. The sheet will be sorted according to your selection.
    The sorted data

To sort a range:

In our example, we'll select a secondary table in a T-shirt order form to sort the number of shirts that were ordered by class.

  1. Select the cell range you want to sort. In our example, we'll select cell range G3:H6.
    Selecting a range
  2. Click Data and select Sort range from the drop-down menu.
    Selecting Sort range
  3. The Sorting dialog box appears. Select the desired column you want to sort by.
    Setting the sort range options
  4. Select ascending or descending. In our example, we'll select descending (Z-A). Then click Sort.
    Selecting descending
  5. The range will be sorted according to your selections (in our example, the data has been sorted in descending order according to the Orders column).
    The sorted range of cells

To create a filter:

In our example, we'll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout. In order for sorting to work correctly, your worksheet should include a header row, which is used to identify the name of each column. We will freeze the header row so the header labels will not be included in the filter.

  1. Click View and hover the mouse over Freeze. Select 1 row from the menu that appears.
    Freezing the header row
  2. Click any cell that contains data.
    Selecting a cell
  3. Click the Filter button.
    The Filter button
  4. A drop-down arrow appears in each column header.
    Drop-down arrows in the column headers
  5. Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment.
    The checklist of column values
  6. Click Clear to remove all of the checks.
    Clicking Clear
  7. Select the data you want to filter, then click OK. In this example, we will check Laptop and Projector to view only these types of equipment.
    Setting the filter
  8. The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and projectors are visible.
    Viewing the filtered results

Applying multiple filters

Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it down further to only show laptops and projectors that were checked out in August.

  1. Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view information by date.
    Creating a new filter
  2. Check or uncheck the boxes depending on the data you want to filter, then click OK. In our example, we'll uncheck everything except for August.
    Selecting items
  3. The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August.
    Viewing multiple filters

If you're collaborating with others on a sheet, you can create a filter view. Creating a filter view allows you to filter data without affecting other people's view of the data; it only affects your own view. It also allows you to name views and save multiple views. You can create a filter view by clicking the drop-down arrow next to the Filter button.

Creating a filter view

To clear all filters:

  • Click the Filter button, and the spreadsheet will return to its original appearance.
    Clicking the filter button

Challenge!

  1. Open our example file. Make sure you're signed in to Google, then click File > Make a copy.
  2. Select the Equipment Log tab if it is not already open.
  3. Freeze row 1.
  4. Sort the spreadsheet by the Checked Out date from most recent to the oldest.
    Hint: Sort by column D from Z to A.
  5. Sort the range A2:F9 by column B from A to Z.
    Hint: Make sure the box next to data has header row is left unchecked.
  6. Filter the spreadsheet so it only shows equipment that has never been checked in.
    Hint: Filter column E to show cells that are empty.
  7. When you're finished, your spreadsheet should look like this:
    sorting filtering challenge

/en/googlespreadsheets/understanding-the-new-google-sheets/content/