Categories
Excel

Filter (AutoFilter) Data

When you want to view only those records that have specific data in certain cells, you should filter the data range.

Filter a Data Range

Using filters you can easily analyze and view specific subsets of your data based on different conditions or criteria. To apply filter, follow these steps:

  1. Select the range of cells that you want to filter. This can be a single column or multiple columns with headers.
  2. Go to the Data tab, in the Sort & Filter group, click on the Filter button or press Ctrl+Shift+L to toggle the filter on/off.
  3. Excel will add filter dropdown arrows to the headers of the selected columns. See following figure:
  1. Click on the filter arrow in the column header you want to filter. A dropdown list will appear with unique data entries in the column and additional searching, sorting, and filtering options (such as text filters, number filters, date filters, etc. depending on the column data).
  2. Choose the filter criteria that you want to apply. For example, if you’re going to filter a column for values greater than a certain number, you can select Number Filters > Greater Than and enter the desired value in the Custom AutoFilter dialog box.

Excel will filter the data based on your selection, hiding the rows that do not meet the specified criteria as shown in the following figure:

You can apply filters to multiple columns by selecting the desired filter criteria for each column separately.

To remove the filters and show all the data again, go back to the Data tab and click on the Filter button again or use the keyboard shortcut Ctrl+Shift+L.

Filter Data For The Top Items

Suppose you want to see the top ten items based on the Sales. You can use the (Top 10…) AutoFilter option to filter a range by the largest number or smallest number. To filter and display only the top 10 items follow this step-by-step guide:

  1. Click on the filter drop-down arrow in the Sales column header. This will open a list of options for that column.
  2. In the filter options, select Number Filters >Top 10 option.
  3. The Top 10 AutoFilter box will appear, allowing you to choose how many items you want to display. By default, it’s set to show the top 10 items. If that’s what you want, simply click OK.

Excel will now show only the top 10 items in the filtered data. The rest of the data will be hidden from view.

Clear Filter

If you make changes to the data or want to remove the filter later, you can simply click on the filter drop-down arrow again and choose Clear Filter from [Column Name].

Sort and Filter Data

  1. Quickly Sort Data by Single or Multiple Columns
  2. Sort Data with Conditional Formatting Criteria
  3. Sort Weekdays and Months
  4. Sort and Fill Data with Custom Lists
  5. Random Sort using RAND() Function
  6. Filter (AutoFilter) Data
  7. Excel Advanced Filter