How To APPLY FILTER In EXCEL- Essential Skill

Filtering in Excel is a powerful tool that can help you manage and analyse your data more effectively. Imagine having a magic wand to sift through piles of information effortlessly. That’s what filtering in Excel feels like! Knowing how to apply filters in Excel can make your data management tasks much simpler and more efficient.

Are you currently lost in a sea of numbers? Excel’s filters help you cut through the noise. Think of it as a smart assistant that instantly finds specific dates, customers, or details. This guide covers basics to more complex techniques, helping you tackle data challenges confidently and avoiding the hassle of trying to work out why your Excel filter isn’t working.

Whether you manage a small business or handle large datasets, this guide will show you how to master Excel’s filtering features, transform your data skills, and save valuable time!

Excel Practice Files

Filtering Basics in Excel

Using Excel’s filters is like having a handy tool to quickly sort through your data, whether it’s dates, text or numbers. These filters make it easy to find specific information, such as financial figures, certain months, or customer names and order statuses, without getting lost in endless rows of data.

How to Turn On Filter in Excel

1. Begin by selecting your data set, including the column headings. If there are empty rows in the dataset, either select over them and include them in the selection or delete them before selecting your data, as these can disrupt the filtering process.
2. Apply the filter by going to the Data tab [1] and clicking the Filter button [2].
3. Dropdown arrows will be displayed on the top row of your dataset [3]. Click the dropdown arrow in the column header you wish to filter. This action will display a list of filter options specific to your data type, such as dates, text, or numbers.

Tip: use CTRL + SHIFT + L to turn Filter on and off

Filter by Date in Excel

Excel automatically recognises date columns and provides filtering options by year, month, and specific dates. Follow these steps to filter by date:

Steps for Filtering by Date

Step 1: Click the dropdown arrow on the column containing date data [1].

Step 2: You’ll see a range of filter options, allowing you to filter by specific periods such as years, quarters, months, weeks, or even individual days. For example, if you want to see data specifically from January 2025, remove the check from the Select All option [2] to deselect all other options, then expand the 2025 list [3] and select ‘January’ [4].

Step 3: Once you have selected the desired dates, click OK to apply the filter [5].

Step 4: Clear the filters by selecting Clear Filter From option on the filter dropdown.

Filter Text in Excel

For columns with text, you can filter by specific entries or use the search box to quickly find what you need.

Steps for Filtering Text

Step 1: Click the dropdown arrow on the column containing the text data.

Step 2: You’ll see a list of filter options specific to the text data, including a search box at the top.

Step 3: To filter by specific text entries, remove the check from the Select All option, then scroll through the list and check the boxes next to the entries you wish to include. For example, if you want to filter by customer names or statuses, find and check the boxes next to the specific names or statuses you need.

Step 5: Use the Search box at the top of the filter options to quickly find specific text entries [4]. Type in the text you are looking for, and the list will update to show matching entries.

Step 6: Once you have selected the desired text entries, click OK [5] to apply the filter.

Step 7: Clear the filters by selecting Clear Filter from the filter dropdown.

Filter by Numbers in Excel

Use number filters for columns with numerical data. These filters allow you to select specific values or apply custom filters to find numbers greater than, less than, or between specific amounts, making it easy to narrow down your data.

Steps for Filtering Numbers:

Step 1: Click the dropdown arrow on the column containing the numerical data.

Step 2: You’ll see a list of filter options specific to the numerical data.

Step 3: To filter by specific values, remove the check from the Select All option, then scroll through the list and check the boxes next to the values you wish to include.

Step 4: Use the Search box at the top of the filter options to quickly find specific number entries. Type in the number you are looking for, and the list will update to show matching entries. This option is great when searching for part numbers, purchase order numbers or invoice numbers. However, when searching for amounts, you may enjoy using the Number Filters option.

Step 5: Use the ‘Number Filters’ option to create custom filters. For example, to filter on values greater than $500, click on Number Filters [1], then select Greater Than [2]. Enter 500 in the box next to “is greater than” [3] and click OK [4].

Step 6: Once you have selected the desired numerical entries, click OK [5] to apply the filter.

Step 7: Clear the filters by selecting Clear Filter from the filter dropdown

More Advanced Filtering in Excel

Excel’s filters are incredibly helpful for managing large amounts of information, but basic filtering isn’t the only feature on offer.

Beyond simple number filters, Excel offers a range of advanced filtering options using Tables, Slicers, and the dynamic FILTER function. These advanced tools can take your data management to the next level.

By leveraging these advanced filtering techniques, you can gain deeper insights and make more informed decisions based on your data.

Filter Tables in Excel

Tables provide a structured way to manage data and include built-in filtering options, making sorting and organising information easier.

Steps for Filtering Tables:

Step 1: First, select the range of data you wish to convert into a table.

Step 2: From the Insert tab on the Excel ribbon, click the Table button.

Step 3: Ensure that the “My table has headers” option is checked if your data range includes headers [1], click OK [2].

Step 4: This will format your data range as a Table with built-in filtering options.

Once your data is formatted as a Table, you can refine your data view by using the built-in filtering options to filter by text, number, and dates.

Tip: to hide the filter buttons on a Table from the Table Design tab, remove the check from the Filter Button option.

Using Excel Slicers To Filter Data

Slicers are visual tools that make filtering data in Tables more intuitive by allowing you to filter by specific criteria like ‘Payment Status’ or ‘Customer Name’. Once inserted, slicers appear as buttons on your worksheet, each representing a filter option. You can instantly narrow down your data by clicking these buttons to show only the rows matching your selection. This makes it easy to see and analyse specific data subsets without using traditional filter dropdowns.

Steps for Filtering Using Slicers:

Step 1: Make room for the Slicers: To insert a slicer, first, make room for it on the worksheet. This may require you to insert empty rows above the Table [1].

Step 2: Insert the required Slicers: Click anywhere within your Table [2], go to the Table Design tab [3], and select Insert Slicer [4]. Choose the columns you want slicers for, such as ‘Payment Status’ or ‘Customer name’ [5], then click OK [6].

Step 3: Format & position the Slicer: The Slicers will appear on your worksheet. Resize, reposition and format each Slicer.

Step 4: Filter using the Slicer options: Click the options to filter data based on your selection

Tip: Be sure to click away from the Slicer so the handles aren’t selected. This ensures that it can’t easily be deleted.

SUBTOTAL Function on Filtered Data

For even more dynamic data management, the Subtotal function allows you to create dynamic totals that update with your filters, ensuring that your calculations always reflect the current data view.

Steps to Sum Filtered Data in Excel

Step 1: Select the Data Table: Select the data you want to filter or use the shortcut Ctrl + A to select the entire data table. Ensure that you have included all relevant data, including any empty rows if they exist.

Step 2: Apply Filters: Go to the Data tab. Click on the Filter option to insert filters over the data. Apply the required filters to the data [1].

Step 3: Select the cell to hold the total: Scroll to the bottom of your data and select the cell where you want the total to appear.

Step 4: Insert the SUM function: From the Home tab, click SUM to insert the SUM function or use the shortcut Alt + = (equals) to insert the function [2].

Step 5: Check the Function: Ensure that the function inserted is the SUBTOTAL function instead of SUM. After filtering, the SUBTOTAL function automatically updates the total based on the visible rows.

Step 6: Verify the Total: Apply a filter to any column (e.g., filter by a specific value in the “Oper” column). Check that the total now reflects the total for only the visible rows.

Step 7: To see the total for all data again, clear the filters by selecting Clear from the filter dropdown.

By following these steps, you can ensure that your totals dynamically update based on the filtered data, providing accurate sums for the visible rows only.

Excel FILTER Function To Create Dynamic Filters

Additionally, the Filter function provides a powerful way to create custom filters that adjust dynamically as your data changes. This method overcomes the common problem of copying filtered data to another sheet, which results in out-of-date information if the source data updates. With the Filter function, the filtered data remains connected to the source data, ensuring that any updates to the original data are automatically reflected in the filtered results.

Note: Enter the FILTER function in a single cell, such as A2, from which the filtered results will spill. To delete or edit the formula, go back to cell A2.

Steps to Create Dynamic Filters

Step 1: Navigate to where the Filtered data will be held: Open a new worksheet or navigate to an empty cell where you want to display the filtered data.

Step 2: Select the Starting Cell: Click on the cell that will be the top left cell once the filtered data is pulled onto this sheet, e.g., cell A2 (or any cell where you want the filtered data to appear).

Step 3: Enter the Filter Function: Type =FILTER( to start the filter function. You will see the function arguments appear on the screen.

Step 4: Select the Data Array: Switch to your source data worksheet. Select the range of data you want to filter. Ensure the selected range is correct, and then type a comma.  

Step 5: Specify the Filter Criteria: Determine the column you want to filter by. For example, if you want to filter by the “Status” column, select the appropriate range (e.g., D2:D2428). Ensure the starting and ending rows of your array and filter criteria match. Type the condition for filtering. For example, to filter for “outstanding” records, type =”outstanding”. Add a comma after the condition.

Step 6: Handle Errors (Optional): Optionally, specify what should be displayed if no match is found. For example, to leave the cell empty, if no match is found, type “”.

Step 7: Complete the Function: Close the function with a closing parenthesis ). Your complete function should look something like this: 
=FILTER(‘Source Data’!A2:K2428,’Source Data’!D2:D2428=”outstanding”,””)

Step 8: Press Enter: Press Enter to apply the filter function. The filtered data will now appear in your selected worksheet, dynamically updating as changes occur in the source data.

Following these steps, you can use Excel’s FILTER function to create dynamic, connected filtered data sets.

Combining the FILTER and CHOOSECOLS Functions

Enhance your filtering by combining the Filter function with functions like SORT or CHOOSECOLS. For example, use the CHOOSECOLS function to return specific columns from your filtered data.

Steps to Insert the FILTER Function with CHOOSECOLS in Excel

Step 1: Complete the Filter Function: Create the Filter function following the steps above. For example,  =FILTER(‘Source Data’!A2:K2428,’Source Data’!D2:D2428=”outstanding”,””)

Step 2: Enter the Choose Columns Function: To select specific columns from the filtered data, nest the filter function within the CHOOSECOLS function. For example, to select columns 1, 5, and 10, use the formula below.
=CHOOSECOLS(FILTER(‘Source Data’!A2:K2428,’Source Data’!D2:D2428=”outstanding”,””),1,5,10).

Step 3: Press Enter: Press Enter to apply the combined filter and the CHOOSECOLS function. The filtered data with only the specified columns will now appear in your selected worksheet, dynamically updating as changes occur in the source data.

By following these steps, you can effectively use the filter function in combination with the CHOOSECOLS function in Excel to create dynamic, connected filtered data sets with only the columns you need.

Conclusion

Filtering in Excel is an essential skill for data management and analysis. You can streamline your workflow and make more informed decisions by mastering basic and advanced filtering techniques. Try out these tips and let me know which ones save you the most time and improve your efficiency in Excel!

Excel Practice File Download