Sorting and Filtering

When you sort, you have two choices about the amount of data you want to work with: you can sort all rows in your sheet or only a selection of them.

Here are some things to keep in mind as you sort:

• Applying a sort will impact the existing rows in the sheet. When new rows are added, you'll need to sort again to incorporate the newly added rows.
• You cannot undo a sort after the sheet has been saved. Make sure the rows in your sheet are ordered as desired before you save it.
• Although Editors can sort a sheet, they can't sort locked rows. If the Sort Rows option appears unavailable, and you're shared to the sheet with Editor permissions (learn more about sharing permission levels), deselect any locked rows before attempting to sort.
• The Sort Rows command is unavailable if a filter is applied to your sheet. Before you sort, you'll first need to ensure that filters are not applied. To clear a filter (stop applying it to the sheet), select Filter Off from the Filter menu. Learn more about using filters to show or hide sheet data.

Sort all rows

1. Right-click on a column header and select Sort Rows. The Sort form appears.
   Brandfolder Image
2. Enter sort criteria and then click OK.

Sort a selection of rows

1. Highlight the rows by holding down the Shift key and clicking on the row numbers you want to sort.
2. Right-click on the selected area and select Sort Rows.
3. In the Sort form, enter your sort criteria and click OK.

How different data types are sorted

The type of data you sort will influence the result. Here are a few examples:

• Hierarchy: If you’ve created sub-items, the sub-items will be sorted but kept beneath their parent row. The parent rows will also be sorted but their sub-items will move with them.
• Data created with a Dropdown list: If you sort on a column that is a Dropdown List type, items will be sorted using the order of the drop-down list values. Double-click on a column header and select Edit Column Properties to view/edit these values or to change their order.
• Harvey Ball values: When sorted in ascending order, the values will be ordered as Empty > Quarter > Half >Three-Quarters > Full > Blank.
• Red-Yellow-Green values: When sorted in ascending order, the values will be ordered as Red > Yellow > Green > Blank.

• Mixed letters and numbers: Values that start with a letter and include a number will be sorted first in alphabetical order then in numerical order starting from the leftmost digit. For example, the values Q1, Q2, Q3, Q12, will be sorted as Q1, Q12, Q2, Q3.

  When sorting in ascending or descending order, blank rows will be sorted below rows with values.

Sort behavior in reports

When you sort a report, items will be sorted in ascending or descending order. If you sort on a column that is a Dropdown List type in the source sheet, items won’t sort based on the predetermined dropdown values in the included source sheets. 

A report can pull from multiple sheets and will consolidate columns with the same name and column type. If the dropdown list columns have different values across sheets (for example: a,b,c,d in one sheet and 1,2,3,4 in another), the report will be unable to determine which sort to honor in the consolidated column. 

You can filter on sheet rows with columns matching specific conditions that interest you. Here are some of the conditions you can check for a column:

• Matches a specific value or a value contained in a list.
• Matches none of the values contained in a list.
• Greater than, less than, or equal to a value.
• Blank or empty
• Not a number
• Not a date

If you set multiple conditions, you can select whether to show rows that match

• all conditions
• at least one condition

With the appropriate permissions, you can create filters for yourself or share them with others. 

The Shared Filters functionality covered in this article is included with Business and Enterprise plans. For more information about plans and pricing, see our Pricing page.

Required permissions

What you can do with filters in a sheet depends on the sharing permissions you have for that sheet.

A sheet's Unnamed Filter is unique to you. You can configure it however you like.

Create a filter

1. On the toolbar, go to Filter > New Filter. The filter editor appears. 

   

2. To create a named filter, fill in the Name field. (requires Editor permissions or higher, to the sheet)

   Or,

   To create an unnamed filter, leave the Name field blank.

3. Under Show rows that match, set one or more conditions for filtering the data you want.

   A column's data type determines the conditional operators available for it. For example, contains can only be used with text strings and contacts, and is between can only be used with dates and numbers.

4. Toggle the link next to Show rows that match to switch between matching all conditions or at least one condition.
5. To share the filter with other sheet collaborators (requires sheet Admin permissions or higher), select the Share filter checkbox.
6. If your sheet uses hierarchy, you can filter on parent rows by selecting the Include parent rows checkbox.
7. Select Apply to see the filtering results.

The rows matching your filter appear while the other rows are hidden.

In the Filter menu, your filter's name appears--it shows as Unamed Filter if the filter is unnamed. If you shared the filter, the share icon    appears next to the filter name.

Modify a filter

You can always go back and change the conditions of an existing filter:

1. From the Filter menu, hover over the name of the filter and select the Edit icon .
    

   The title of the form displays the name of the filter that you’re modifying. Verify the title to ensure that you’re modifying the correct filter.

2. In the Edit Filter form, update the conditions as desired
3. Select Apply.

The updated filter is applied to the sheet.

If you don’t have Editor permissions or higher, you can only modify Unnamed Filters.

Turn off a filter

To stop applying a sheet's current filter, select the filter name in the top bar. The menu item name changes to Filter Off.

Delete a filter

Here is how to remove a filter from the sheet:

1. From the filter menu, hover over the name of the filter and select the delete icon  Brandfolder Image .
2. Select Delete.

Tips for working with conditions

As you specify conditions for your filters, you might get a feeling for how to set up the criteria that you find the most useful. Here are a few ideas to get you started:

• To specify that a filter uses all the specified criteria or just one of the criteria, select the link next to Show rows that match to switch between all conditions and at least one condition.
• Find rows where data might be missing with the is blank condition.
• If your sheet uses hierarchy, you can choose to have your filter include parent rows by selecting the Include parent rows checkbox.
• Focus collaborators on assigned tasks by creating a Current User filter. For details, see View your assigned tasks.  
• Use the Row filter criteria to narrow in on critical path data, attachments, comments, and locked rows. 

• To find the rows containing a pure number value using a filter, you need to convert the number to text. You can do that by creating a new column and the formula: 

  =[NumberColumn]1 + ""

where [NumberColumn] is the name of the column and row number is 1. Copy that formula to the rest of the new column.

Filters don’t find pure numeric values. 

Filter on critical path data, attachments, comments, and locked rows

Use the Row filter criteria to narrow down critical path tasks, attachments and comments that you need to action on, and rows that are locked to prevent editing to certain people.

Select Row in the first dropdown field to see these options.

Isolate your critical path data

If you’ve set up your sheet to highlight critical path rows, you can apply a filter with is on critical path or is not on critical path as the criteria to display or hide critical path rows. Steps on how to enable critical path in your sheet are available in Tracking a project’s critical path.

Quickly find rows with attachments and comments

You can create a filter with the Row criteria that shows or hides rows that contain attachments or comments. Use has attachments or has comments to isolate only the rows to download attachments or reply to comment threads quickly. The does not have attachments or does not have comments criteria hide rows with attachments or comments. 

Find locked and unlocked Rows

Use Row is locked or Row is not locked to show or hide rows that can only be edited by people with Admin sharing permissions on a sheet. This can help people with Editor permissions narrow down on the rows that they’re allowed to edit.