Excel Data Analysis – Filter in Excel

Filter your Excel data if you only want to display records that meet certain criteria. Filtering is a powerful tool in Excel that allows you to quickly and easily view only the data that you need. You can filter data by text, number, date, or other criteria.

Excel will display only the data that meets the criteria that you selected.

Examples of Filter

Here are some examples of how to use filters:

• To filter by text, select the Text Filters option and then select the criteria that you want to use. For example, you could select Starts With and then type a letter or word to filter for data that starts with that letter or word.
• To filter by number, select the Number Filters option and then select the criteria that you want to use. For example, you could select Greater Than and then type a number to filter for data that is greater than that number.
• To filter by date, select the Date Filters option and then select the criteria that you want to use. For example, you could select Between and then type two dates to filter for data that is between those dates.

1. Click any single cell inside a data set.

2. On the Data tab, in the Sort & Filter group, click Filter.

Arrows in the column headers appear.

3. Click the arrow next to Country.

4. Click on Select All to clear all the check boxes, and click the check box next to USA.

5. Click OK.

Result. Excel only displays the sales in the USA.

6. Click the arrow next to Quarter.

7. Click on Select All to clear all the check boxes, and click the check box next to Qtr 4.

8. Click OK.

Result. Excel only displays the sales in the USA in Qtr 4.

9. To remove the filter, on the Data tab, in the Sort & Filter group, click Clear. To remove the filter and the arrows, click Filter.

There’s a quicker way to filter Excel data.

10. Select a cell.

11. Right click, and then click Filter, Filter by Selected Cell’s Value.

Result. Excel only displays the sales in the USA.

Note: simply select another cell in another column to further filter this data set.

You can also use multiple criteria to filter data. For example, you could filter for data that starts with the letter “A” and is greater than 100. To do this, select the Text Filters option and then select Starts With. Type “A” in the box and then click OK. Next, select the Number Filters option and then select Greater Than. Type 100 in the box and then click OK. Excel will display only the data that starts with the letter “A” and is greater than 100.

Additional Information

• You can also use the FILTER function to filter data. The FILTER function takes two arguments: the range of data that you want to filter, and the criteria that you want to use. For example, the following formula would filter the data in the range A1:D10 for values that are greater than 50:

=FILTER(A1:D10,A1:D10>50)

• You can also use the Advanced Filter dialog box to filter data. The Advanced Filter dialog box allows you to use more complex criteria to filter data. For example, you could use the Advanced Filter dialog box to filter for data that is in a specific range, or to filter for data that contains a specific value.