Excel Data Analysis – Filter in Excel

Date:

Share post:

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 in Excel

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.

Filter Example

Arrows in the column headers appear.

Filter Arrows

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.

Click USA

5. Click OK.

See also  Excel Data Analysis - Conditional Formatting in Excel

Result. Excel only displays the sales in the USA.

Filter in Excel

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.

Click Qtr 4

8. Click OK.

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

Filter in Excel

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.

Filter by Selection

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

Filter by Selected Cell's Value

Result. Excel only displays the sales in the USA.

Filtered Data

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.
See also  Excel Example - Flash Fill in Excel
Next Chapter: Conditional Formatting

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Related articles

Excel Example – Subtract in Excel

Subtract There's no SUBTRACT function in Excel. However, there are several ways to subtract numbers in Excel. Are you...

Excel Example – Most Used Functions in Excel

Most Used Functions Let's check out the 10 most used Excel functions. Visit our section about functions for detailed...

Excel Example – ROW function in Excel

The ROW function in Excel returns the row number of a reference. For example, =ROW(C4) returns 4. The...

Excel Example – Move Columns in Excel

To move columns in Excel, use the shift key or use Insert Cut Cells. You can also change...