Sort and filter your data

               

Want advanced Google Workspace features for your business?

Try Google Workspace today.

 

 

You can sort data in alphabetical and numerical order, or use filters to hide data that you don't want to find.

Sort and Filter Your Data

To get an example spreadsheet and follow along with the video, click 'Make a copy' below.

Make a copy

Sort data into alphabetical or numerical order

1. On your computer, open a spreadsheet in Google Sheets.
2. Highlight the group of cells that you'd like to sort.
3. If your sheet includes a header row, freeze the first row.
4. Click Data Sort range Advanced range sorting options.
5. If your columns have titles, click Data has header row.
6. Select the column that you'd like to be sorted first and choose a sorting order.
   • To add another sorting rule, click Add another sort column.
7. Click Sort.

Sort an entire sheet

1. On your computer, open a spreadsheet in Google Sheets.
2. At the top, right-click the letter of the column that you want to sort by.
3. Click Sort sheet A to Z or Sort sheet Z to A.

Sort by colour

1. On your computer, open a spreadsheet in Google Sheets.
2. Select a range of cells.
3. Click Data Create a filter.
4. To find the filter options, go to the top of the range and click Filter .
   • Sort by colour: Choose which text or fill colour to filter or sort by. Cells with the colour that you choose to sort by will move to the top of the range. You can sort by conditional formatting colours but not alternating colours.
5. To turn the filter off, click Data Remove filter.

Filter your data

Important:

• When you add a filter, anyone with access to your spreadsheet can find the filter too. Anyone with permission to edit your spreadsheet will be able to change the filter.
• The 'Create a filter' option is unavailable under certain conditions. Learn more about these conditions.

1. On your computer, open a spreadsheet in Google Sheets.
2. To create a filter, select an option:
   • Select a range of cells, then click Data Create a filter.
   • Right-click on a cell or a range of cells, then click Create a filter.

   Learn why 'Create a filter' is unavailable

   The option to 'Create a filter' can be unavailable due to:

   • Formatting
     • Check for any merged cells, hidden rows or hidden columns in the sheet. Try to unmerge the cells and unhide the rows or columns.
     • Check for any conditional formatting applied to the sheet.
       1. Go to Format.
       2. Select Conditional formatting.
          • Make sure that there are no rules applied.
     • Data validation
       • Check for any data validation rules applied to the sheet.
         1. Go to 'Data'.
         2. Select Data validation.
            • Make sure that there are no rules applied.
     • If none of the above solutions work, you might not have access. To get access:
       1. Create a copy of the sheet.
       2. Check if the filter option is enabled in the copy.
          • If it is, you can use the copy of the sheet instead.
3. To find the filter options, go to the top of the range and click Filter .
   • Filter by condition: Choose conditions or write your own custom formulas.

     Custom formula examples

     Find unique values within a data range

     • Select Custom formula is
     • Type =COUNTIF(data_range, data_range)=1

     Find text matching 'Good' or 'Great' within a data range

     • Select Custom formula is
     • Type =OR(REGEXMATCH(data_range, "Good"), REGEXMATCH(data_range, "Great"))
     • You can filter cells that have valid or invalid data validation rules.
   • Filter by values: To hide data points, untick the box next to the data point and click OK.
     • To create a filter and filter by cell value, right-click on a cell, then click Filter by cell value.
   • Search: Search for data points by typing in the search box.
   • Filter by colour: Choose which text or fill colour to filter by. You can filter by conditional formatting colours but not alternating colours.
4. To remove the filter, select an option:
   • Click Data Remove filter.
   • Right-click on any cell, then click Remove filter.

Once filtered, at the bottom right, users can find the number of rows displayed out of the total rows in the table.

Tip: Filters also apply to merged cells. Learn how to merge rows or columns.

Create, save, delete or share a filter view

Important: If you only have permission to view a spreadsheet, you can create a temporary filter view that only you can use. Your filter view won't be saved.

On a computer, you can filter data so the filtered data only applies to your view of the spreadsheet. Your filter view changes are automatically saved.

1. On your computer, open a spreadsheet in Google Sheets.
2. Click Data Create filter view.
3. Sort and filter the data.
4. To save your filter view, at the top right, click Save view.
5. Click Save.

To delete or duplicate a filter view, at the top right, click Options Delete view or Duplicate view.

To remove all filters, go to each filter view and click Options Delete view.

Tip: You can’t change the order of filter views.

Find an existing filter view

Important: You can only apply one filter view at a time.

1. On your computer, open a spreadsheet in Google Sheets.
2. Click Data Change view.
3. Select a filter view.
4. Your filter will be applied to the spreadsheet.
5. To close your filter view, in the top right, click Close .

Save a filter as a filter view

1. On your computer, open a spreadsheet in Google Sheets.
2. Apply a filter.
3. Click Data Save as filter view.

Rename a filter view

1. On your computer, open a spreadsheet in Google Sheets.
2. Click Data Change view.
3. Select a filter view.
4. At the top left of the sheet, next to 'Name', click the filter view name and type the new name.
5. Press Enter.

Share or send a filter view link

1. On your computer, open a spreadsheet in Google Sheets.
2. Apply the filter view .
3. Copy the URL.
4. Share the filter view link.

Learn more about filters and filter views

Filters and filter views help you to analyse a set of data in a spreadsheet.

You can use filters to:

• Show a specific filter when people open your spreadsheet.
• Sort your data after using a filter.

You can use filter views to:

• Save multiple filters.
• Name your filter.
• Let multiple people find different filter views at the same time.
• Share different filters with people.
• Make a copy or create another view with similar rules.
• Filter or sort a spreadsheet you don't have edit access to. In this case, a temporary filter view will be created.

Tip: When you have a formula that points to a cell inside a filtered range, note that sorting the filtered range won't change the formula. For example, '=B2' will continue to be '=B2' when B2 is within the filtered range.

 

 

Related resources

• Use tables in Google Sheets
• Name a range of cells