Sort and filter spreadsheets

You can organize your data for easier viewing and deeper analysis using sorting and filtering.

Sort data in a sheet

To sort a range of data:

1

Select the range you want to sort.

2

From the Data toolbar, click Sort.

3

If the selection has a header row, check Selection includes header row.

4

Select the column you want to sort by from the dropdown menu and choose alphabetical (A → Z) or reverse alphabetical (Z → A).

5

Click Sort.

To learn more about working with data in Spreadsheets, see Viewing Data in Spreadsheets.

Filter a sheet

You can display or hide table rows based on your own filters.

1

Select an entire table, columns, or a range of cells.

2

In the Data toolbar, click Filter, and then Apply Filter.

3

To see filter options, click the filter icon at the top of the range that you would like to filter by.

- Selected values: Use the checkboxes to filter out values, you can also search to find certain values.

- Conditions: Choose a condition to filter by in the dropdown. You can also use formulas to create custom filter conditions.

4

Click Apply to filter the spreadsheet.

You can change the filter parameter by clicking the filter icon at the top of the column and adjusting the filter settings. 

Filter by formula condition

You can write custom formulas to filter the contents of your table.

There are two formula filter conditions available:

• Formula is true - rows will be displayed if the formula condition evaluates as true for the row. Rows in which the formula condition evaluates as false will be filtered from view.
• Formula is false - rows will be displayed if the formula condition evaluates as false for the row. Rows in which the formula condition evaluates as false will be filtered from view.

Formula filter conditions are evaluated on a per-row basis, beginning with the row beneath the row in which the filter is applied. For example,

To hide rows, excluding negative numbers:

1

Select Formula is false  from the conditions menu.

2

Enter the formula expression, =AND(ISNUMBER(B2),ISNUMBER(D2),B2=0,D2=0)


In this example,

• The filter is applied to A1:D9.
• The formula filter condition references only the row beneath the first row of the filter.
  • AND is used as the primary function in order to specify the requirements for a row to be filtered from view. Each condition of the AND expression must be met for the row to be filtered with Formula is false.
  • ISNUMBER is used to verify there is a number value present in the cells of the row containing data (B2, D2).
  • B2=0, D2=0 expressions are used to require that the number present in cells B2 and D2 equal zero.

To hide rows with zero balances, excluding negative numbers:

• Formula is false: =SUM(2:2)=0

To hide blank rows:

• Formula is false: =COUNTA(2:2)=0
• Formula is true: =COUNTA(2:2)>0

You can also filter values within a column. Below are examples of filtering in column A.

To hide rows with an empty value:

• Formula is false: =ISBLANK(A2)

To show rows with a value greater than the value of a specific cell (e.g., J10):

• Formula is true: =A2>$J$10

To show rows with a value greater than the corresponding value in another column (e.g., column B):

• Formula is true: =A2>B2

Reapply filters

If your source data changed, you can reapply your filters to reflect those changes in data.

To reapply filters:

1

In the Data toolbar, click Filter.

2

Select Reapply Filter.

Reapply all filters

File owners and editors can reapply all filters to a spreadsheet or table.

To reapply all filters:

1

In the Data toolbar, click Filter.

2

Select Reapply, then All filters.

3

To confirm, click Reapply.

Reapply all filters won't work on sections that you don't have editing permissions or locked sections.

Remove a filter

To remove a filter:

1

In the Data toolbar, click Filter.

2

Select Remove Filter.

Create a filtered view

You can use filtered views to hide data from a spreadsheet without editing the view for all collaborators.

To create a new filter:

1

From the Data toolbar, click Filter and select Create View from the dropdown.

2

Select the range you want to include in the filtered view.

To edit the filter:

1

Select the filter  icon at the top of the column you want to filter.

2

Select the items you would like to see and hide from the list.

3

Click Apply to apply the filter.

To save your filtered view:

1

Double-click the Filter View text box to name your filter.

2

Click Save in the Filter View toolbar.

You can share your filtered view by clicking the arrow next to the Save button and selecting Copy Link. You can send this link to collaborators, which sends them a copy of your spreadsheet with the filter automatically applied. 

Use an existing filtered view

To open an existing filtered view:

1

From the Data toolbar, click Filter.

2

Select Open from the dropdown.

3

Click the filtered view from the list that you want to apply.