How to Filter and Sort Data in Google Sheets for Better Organisation

When working with large datasets, Google Sheets offers powerful filtering and sorting tools to help you organise your data effectively. Whether you need to find specific information, organise data in ascending or descending order, or simply make your spreadsheet easier to navigate, using filters and sorting can make a big difference. This guide will walk you through how to filter and sort data in Google Sheets, ensuring that your information is well-organised and easy to manage.

Step-by-Step Guide to Filtering Data in Google Sheets

1. Select Your Data Range Before applying any filters, you need to select the data range that you want to filter:Click and drag to highlight the range of cells you want to work with.Make sure that your range includes headers, such as “Date,” “Name,” or “Sales,” as filters are applied based on column headers.
If you’re working with an entire dataset, you can quickly select the full range by clicking the top-left corner of your sheet (where the rows and columns meet).
2. Enable Filtering Once your data is selected, the next step is to enable filtering. This will allow you to apply filters to each column: Go to the Data menu at the top of the screen.
3. Select Create a filter. Small filter icons will now appear in the header row of each column.
4. Apply Filters to Your Data Filters allow you to narrow down your data based on specific criteria. Here’s how to apply them:Click the filter icon in the column header that you want to filter by (for example, “Sales”).A dropdown menu will appear, allowing you to select filter criteria:Filter by values: You can select or deselect specific values that should be shown in the filtered data.Filter by condition: You can apply conditions, such as “Greater than” or “Text contains,” to only show rows that meet these conditions. For example, you could filter the “Sales” column to show only values greater than 1000.
After selecting your criteria, the sheet will automatically update to show only the rows that match the filter.
5. Clear or Adjust Filters To reset or modify your filters: Click the filter icon again in the filtered column and select Clear filter to remove the filter.
6. Alternatively, adjust the criteria to apply a different filter, such as changing the condition or selecting different values.
7. Filter Views for Collaboration Google Sheets also allows you to create filter views, which are useful when collaborating with others. Filter views enable you to filter data in your sheet without affecting how other collaborators see it:Go to the Data menu and select Filter views > Create new filter view.Apply your filters as usual, and they will only be visible to you.To exit the filter view, click Close in the filter view bar at the top of your sheet.
Filter views are great for team collaboration, as each person can analyse data differently without changing the main sheet for everyone.

Sorting Data in Google Sheets

1. Select the Data to Sort Sorting helps you organise data in ascending or descending order, making it easier to analyse trends or locate specific information. To start, select the range of cells you want to sort: Highlight the data range, including headers if you want them to be part of the sorting criteria.
2. Sort Data by Column Once your data is selected, here’s how to apply sorting:Go to the Data menu.Select Sort range if you want to sort only the selected range of cells, or select Sort sheet if you want to sort the entire sheet based on one column.Choose either Sort range by column A, A-Z (ascending) or Sort range by column A, Z-A (descending). Replace “column A” with the column you wish to sort by.
Sorting allows you to organise data such as names alphabetically, numbers from smallest to largest, or dates chronologically.
3. Custom Sort Options If you need more control over how your data is sorted, you can use the Advanced sorting options: After selecting your data, go to Data > Sort range > Advanced range sorting options.
4. Here, you can sort by multiple columns at once. For example, you could first sort by “Region” and then by “Sales,” ensuring that within each region, sales are organised in ascending or descending order.
5. You can also specify whether to sort by values, numbers, or dates depending on the data type in your columns.
6. Sorting Multiple Columns In some cases, you may need to sort your data based on multiple columns to reveal deeper insights. Here’s how to do it:Select your data range.Go to Data > Sort range > Advanced range sorting options.In the sorting dialog box, select your primary sorting column (e.g., “Sales”).Click Add another sort column, and choose your secondary column (e.g., “Region”).This will sort the data by the primary column first, and within each grouping, it will sort by the secondary column.
Sorting by multiple columns allows you to analyse complex datasets more effectively, ensuring that your data is organised logically.

How to Make Google Sheets Sort Itself Automatically

The quickest way to make Google Sheets sort itself is a =SORT() formula on a separate tab – it re-orders automatically every time a new row is added, which the one-off Data > Sort range menu cannot do. Use one of these:

• The SORT function. On a clean tab, enter =SORT(Data!A2:D, 2, TRUE) to show your data sorted by column 2, ascending. As new rows are added to the source, the sorted view updates on its own.
• Sort and filter together. Wrap a filter in a sort: =SORT(FILTER(Data!A2:D, Data!C2:C>0), 2, FALSE) shows only the rows you want, highest first, and refreshes automatically.
• QUERY for more control. =QUERY(Data!A2:D, "select * order by B desc", 1) sorts, and can also group or filter, using a simple SQL-like syntax.

Keep your raw data on one tab and the auto-sorting formula on another, so people can still enter data in any order while the report stays tidy. If you genuinely need the source rows themselves reordered as they are typed, that takes a short Apps Script onEdit trigger – useful, but the formula approach above is simpler and covers most needs. For more ways to make a sheet run itself, see our guide to automating Google Sheets with formulas.

The FILTER Formula and Slicers

Two features take filtering beyond the menu: one builds a filtered view with a formula, the other lets people filter a dashboard themselves.

The FILTER formula pulls only the rows that meet a condition into a new range, and updates live as your data changes. On a clean tab, enter:

• =FILTER(Data!A2:D, Data!C2:C>100) – show only rows where column C is over 100.
• =FILTER(Data!A2:D, Data!B2:B="Open", Data!D2:D>TODAY()) – combine conditions (status is Open and the date is in the future).

Unlike the Data > Create a filter menu, FILTER leaves your original data untouched and never disturbs anyone else on a shared sheet, which makes it ideal for a report or dashboard tab.

Slicers are on-sheet filter controls, perfect for dashboards other people use. Go to Data > Add a slicer, pick your data range, then choose a column such as Region or Status. A drop-down button appears on the sheet, and anyone can click it to filter the linked charts and tables with no menus or formulas needed. Add one slicer per column you want people to filter by.

Advanced Filtering and Sorting Techniques

1. Sort and Filter by Colour If you’ve used colour coding in your Google Sheet (for example, by using conditional formatting), you can also filter and sort based on cell colour or text colour: Click the filter icon in the relevant column header.
2. In the dropdown menu, select Filter by colour and choose either Fill colour or Text colour.
3. You can also sort by colour by going to Data > Sort range > Sort by colour. This is useful when you’ve used colour to highlight key data points or categories.
4. Use Slicers for Interactive Filtering Google Sheets offers a feature called Slicers, which allows you to create interactive filters that sit above your data and make it easy for users to filter by specific criteria:Go to Data > Slicer.Select the data range and the column you want to apply the slicer to.Once the slicer is added, you can easily toggle between different filters by clicking the slicer dropdown.
Slicers are particularly useful in dashboards or reports where you want users to interact with the data without manually adjusting the filters.
5. Filter and Sort with Formulas For advanced users, Google Sheets also offers formulas that allow you to filter and sort data dynamically:FILTER Formula: =FILTER(range, condition) allows you to filter data based on conditions, such as =FILTER(A2:B20, B2:B20 > 1000) to filter rows where column B has values greater than 1000.SORT Formula: =SORT(range, sort_column, is_ascending) allows you to sort data based on a specific column. For example, =SORT(A2:C10, 2, TRUE) will sort the range A2by the second column in ascending order.
These formulas are useful for creating automated filters and sorting rules that dynamically update as your data changes.

Improving Data Management with Filtering and Sorting

Filtering and sorting in Google Sheets are essential tools for organising your data efficiently and gaining deeper insights. Whether you’re working with a small dataset or a large one, these features help you quickly locate key information, analyse trends, and manage your data more effectively. By mastering advanced techniques like sorting by multiple columns, using slicers, and applying filters based on colour or formulas, you can take full control of your data and improve your ability to make informed decisions.

Mastering filtering and sorting will allow you to stay organised, reduce clutter, and enhance your data analysis capabilities, making your Google Sheets more powerful and efficient.