So you have thousands of rows of data in an Excel sheet, and you want to scan through the rows to find a pattern in the columns of the data.

You know you need an Excel filter to help you with that, but you don't know how to go about it. Here, we'll discuss all the best ways to filter your Excel sheet and show the data you need.

How to Filter in Excel

Assume you have an Excel sheet with three columns: age group, region, and the number of sales. In that case, an Excel filter can come in handy when you want to see your sales performance in a selected region, based on a particular age group.

Excel filters can also be useful when you want to create a smaller group before plotting your Excel data on a chart.

Let's take a look at the following methods for filtering data in Excel.

How to Filter Based on Categories

Once you open your datasheet, on the Excel ribbon, click Data.

Next, locate the Sort & Filter group and click Filter. This puts a drop-down button on each column of your datasheet.

Click on filter to put a drop-down on each column

For instance, if your data has a gender column, which is a categorical variable, you can decide to filter your data to see only the female entries.

To do that, click the dropdown button on your categorical column of choice (gender in this case). In the menu that appears, untick everything by clicking the Select All box. Then select the values you want to filter your data by and click OK.

Select the value you want to filter

That operation filters the data based on the female listings, or whatever your categorical data of choice was. You can then check the lower-left corner of the Excel file to see the number of records on that particular filter.

While this option works for numbers as well, it's usually inefficient and daunting. That's because numbers can have a wider range of values. As such, ticking through the long list takes time.

How to Use Number Filters in Excel

Sometimes you might want to filter based on a particular range of numbers.

To use the number filter, click on Filter in the ribbon. Then click the dropdown button at the top of the column that contains the numbers you want to filter.

In the next menu, go to Number Filters and select your preferred choice from the options that pop up.

For instance, if you want to filter the column based on all numbers that are less than or equal to a certain number, from the Number Filters options, select Less than.

Go to number filter and select an option

You can also use the And/Or section to add a second filter for your numbers.

Click OK when you're done.

Setting less than or equal to in number filter

Lots of number-based filter options are available. You can play around with them to see how they work.

How to Use Advanced Filters in Excel

Excel's advanced filter method offers a more flexible way of filtering data. With the advanced filter, you can separate your filtered result from the source data by pasting within the same sheet or in a new Excel sheet.

To use the advanced filter, create a new column on the right edge of your sheet. This new column is where you set your filtering criteria. You can leave a space between this criteria column and the last column of your source data to separate them if you want.

In the criteria column, type the exact name of the source data column you want to filter; a better alternative is to copy and paste the column heading to avoid mistakes.

In the next row, type the value you want to filter your data by for that column. For instance, if you want to see the data for all males in the gender column of the source data, type "male" beneath "gender" in your criteria section.

You can create multiple criteria columns to filter by if you like. To do that, copy the column names you want from the main data and paste them after your first criteria column.

Below each column heading, type in the data value you want to filter for.

Set the criteria columns that you want to filter

You can use Excel Boolean functions like less than (<) or greater than (>) as criteria for filtering numbers.

Once you've followed the process above, select Data in the Excel ribbon. Within the Sort & Filter group, click Advanced.

In the menu that opens, click the up arrow next to the List range field and choose which portion of your data you want to filter. Or ignore the List range field if you want to apply the filter to all your data.

Click on advanced to bring up the advanced filter option box

Next, place your cursor within the Criteria range field and highlight the criteria columns you created earlier: headings and values.

Place your cursor within the criteria range and highlight the entire criteria data

Click OK to apply the filter.

How to Paste the Filtered Results With the Advanced Filter Method

As we mentioned earlier, you can also use the advanced filter to separate your result and paste it somewhere else in the same sheet or in a new Excel sheet.

To do that, repeat the process above for filtering data with the advanced method. However, this time, in the Advanced Filter menu, select Copy to another location.

Tick copy to another location

Next, place your cursor within the Copy to field and select any cell within your Excel sheet, then click OK.

Place your cursor in the copy to field and select a cell within your excel sheet

This operation copies the filtered result and pastes it at the selected location in your Excel sheet.

How to View the Filtered Result With Selected Columns in Excel

If you only want to view selected columns in the filtered data, highlight those column headings and copy them.

Highlight and copy the choice columns

Next, paste the headings anywhere within the same sheet, where you want to see your filtered result.

Paste the copied columns anywhere within the same sheet

Repeat the process above for separating filtered results with the advanced filter method. But this time, once you get to the Copy to field, highlight the headings you pasted.

Highlight to columns you pasted to set the copy to field

Then click OK to view the filtered result beneath these headings.

Filtered result copied against the selected columns

How to Use Advanced Filters to Paste the Filtered Result Into a New Excel Sheet

If you want to paste the resulting data into a new sheet, click on the Add (+) button at the lower-left corner of the Excel sheet to open a new sheet.

Click on any cell within the new sheet to activate it.

Select Data from the ribbon, then click on Advanced to make the Advanced Filter menu pop up. Select the Copy to another location option.

Select the advanced filter on the new sheet

Place your cursor in the List range field and jump into the sheet that contains the source data. Then highlight the entire source data.

Next, place your cursor in the Criteria range field, highlight the criteria you created earlier in the source data sheet.

Highlight the entire criteria data for the criteria range field

Select the Copy to field. Then select any cell on the new sheet again. Click OK to paste the filtered result to the new sheet.

Note: For this to work, ensure you activate the target sheet by clicking a cell within it first.

Related: How to Merge Excel Files and Sheets

Get the Most Out of Excel Filters

While there are other options for filtering data, Excel is one of the most versatile productivity tools around. Although Excel filters can get a bit confusing, it's easy once you master these tricks.

We've discussed the most common ways to filter data in Excel here, but there are even more advanced options available to if you play around with the advanced filter. When it comes to analyzing your filtered data, you might need the use of an Excel formula as well.