See all How-To Articles

How to Filter Rows in Excel & Google Sheets

This tutorial will demonstrate how to filter rows in Excel and Google Sheets.

Excel enables us to store data in a table format made up of rows and columns. These tables are often in database format with the columns as the headers (database fields), and the rows as the database entries. Excel then has some fabulous database features, one of which is the ability to filter lists of data. Filtering data lists enables you to locate and report on a subset of the data.

Formatting Data as a Table

Once you have a list of data stored in Excel, you can choose to format the data as a table. Doing this will automatically add filters to the columns in the table.

Click in the data, and then in the Ribbon, select Home >Styles > Format as Table.

 

CreateDatabase formatAsTable

 

Select the type of format you wish to apply, and then, in the Format As Table dialog box, make sure the ‘My table has headers‘ is checked.

 

CreateDatabase HeaderRows

 

The format will be applied to the data, and a new tab called Table Design will appear in your Ribbon.

 

CreateDatabase TableDesign

 

Adding a Filter to Excel Data

If you do not format your data as a table, you can still add filters to the data manually.

Click in your data, and then, in the Ribbon, select Home > Editing > Filter.

 

FilterData Ribbon

 

Small drop down arrows will be applied to the header row of your data.

 

FilterData Filters

 

Filtering by Data in the Drop Down List

Click on the drop down arrow to the right of the heading you wish to filter on, and then remove the check mark from Select all.

 

FilterData clearselectall

 

In the list of entries that is shown, click in the checkbox of the ones that you require to be shown in your filtered data.

 

FilterData selectdata

 

Click OK to show the data.

 

FilterData filtereddata

 

You will notice that the data that does not match what you have selected is hidden – the row headers turn blue as only the rows for the filtered data is shown. You will also notice that the header that you have filtered on has a small icon of a filter indicating that that column of data has the filter applied to it.

Click on the small filter to activate the drop down list. Select “Clear Filter” to remove the filter from the data.

 

FilterData clearfilter

 

OR

select (Select All) and then click OK to remove the filter from the data.

OR

In the Ribbon, select Home > Editing > Filter > Clear.

 

FilterData clearfilter menu

 

This will clear the filter from the data, but the filter headers will still remain at the top of each column of data.

Filtering by Text

Click on the drop-down arrow next to the header column of the data you wish to filter, and then select Text Filters.

 

FilterData textfilters

 

Select Contains… from the list shown and then type a word that is contained within the data e.g., “Science”

FilterData sciencefilter

 

Click OK to filter the data.

 

FilterData science

 

Sorting a Filter

The filter drop down list has the ability to sort the filtered data.

In the filter drop down list, select Sort A to Z or Sort Z to A, depending on how you wish to sort the filtered data.

 

FilterData sort

 

The data that is filtered will be sorted – you will notice a small arrow appearing next to the filter, indicating that the filtered data is sorted.

 

FilterData sorted

 

If we remove the filter, you will notice that the rest of the data in the list has not been sorted.

 

FilterData notsorted

 

To sort the entire list of data, once the filter is cleared, you can select Sort A to Z or Sort Z to A in the filter drop down list

OR

In the Ribbon, select Home > Editing > Sort & Filter and then select Sort A to Z or Sort Z to A.

 

FilterData filtersort

 

Removing Filters

To remove the filters from the data list, in the Ribbon, select Home > Editing > Sort & Filter and then click on Filter once again. This is a toggle button so will either apply filters to the list, or remove filters from the list.

 

FilterData removefilter

 

Filtering by Color

If you have formatted your data by color, Excel has the ability to filter the data based on the colors applied.

In the filter drop down, select filter by Color.

 

FilterData filterbycolor

 

Select “More Cell Colors” to see all the cell colors applied to the data.

 

FilterData applycolorfilter

 

Select the color you wish to filter on, and then click OK.

 

FilterData english

 

How to Filter Rows in Google Sheets

Filtering data in Google sheets is much the same as filtering data in Excel.

In the Menu, select Data > Create a Filter.

 

FilterData gs create a filter

 

In the drop down list, (1) click Clear to clear all the check marks from the data, and then (2) select the data items from the list.

 

FilterData gs select data

 

Click OK to filter the data.

 

FilterData gs DEMSEng

 

To remove the filter, in the drop down list, click (1) Select All and then click (2) OK.

 

FilterData gs selectall

 

The filter will be cleared but the filter drop down will still be showing on the column headers.

To filter the data by specific text, select Filter by condition and then in the drop down below, select Text contains. There are multiple conditions that you are able to filter on.

 

FilterData gs text condition

 

(1)Type in the text that you wish to filter on, and then (2) click OK.

 

FilterData gs science

 

Only the rows that contain the required data will be shown.

 

FilterData gs showscience

 

Once we have filtered our data, we can also sort the data as we can in Excel.

In the filter drop down list, select Sort A to Z or Sort Z to A, depending on how you wish to sort the filtered data.

 

FilterData gs sort

 

Note that as with Excel, this will only sort the filtered data – not the entire data list.

To remove the filter, select Filter by condition > None in filter the drop down list, and then click OK.

 

FilterData gs none

 

We can also filter by color in Google Sheets.

In the drop down filter list, select Filter by color > Fill color and then select one of the fill colors shown.

 

FilterData gs filterbycolor

 

The data will be filtered by the color selected.

 

FilterData gs yellowfilter

 

To remove the filter, select Filter by color > None in the drop down filter list.

 

FilterData gs filterbycolor-none