How to Filter Data in Excel

Filters are an important part of the data processing. Excel has a state-of-the-art filtering functionality. It is used to categorize a dataset according to requirements, narrow down a large dataset or select a particular portion of the data. For example, if a dataset contains a survey of a thousand people, we can use a filter to extract the response of elderly women who are between the ages of 70 and 90. This article will show you how to use filters and get the relevant information you need from your spreadsheet.

Lesser Clutter

In a large dataset, filters gain a vital role. To work efficiently, it is important that filters be applied correctly. The data can be filtered using dates, a particular number of a certain phrase of text. Excel does not limit the filtering action to only one time, it can be used multiple times to get the desired data.

How to Access Filters

Filters can be selected from the Editing submenu of the home tab. It can also be accessed through shortcut ctrl+shift+L. In the editing menu, the sort and filter options are available along with the find and select option. Both of these can be used to narrow down the amount of data. It should be noticed that the sort and filter options can only be applied when one or more cells are selected in a spreadsheet.

Sorting

Sorting provides basic functionality such as sorting various names according to alphabets, e.g., sort by a to z or z to a. it can also sort by largest to smallest and vice versa. Excel also provides custom sorting options. In this option, data can be sorted according to the day of the week, month, and year. Using this option data can also be sorted according to the cell color of the spreadsheet.

Filtering

Filtering is a much-advanced feature than sorting. Filtering is used to single out data according to multiple criteria. Filtering by color is also provided by the filter menu. When a cell is selected, Excel will analyze the entire column underneath it and offers a quick suggestion for filtering based on the nature of the data. If there is a numeric data, the basic filters that can be used are: equal to, greater than, less than, not equal to, between two numbers, top ten, average, above average, and below average. And if the data is text-based than the common text filters are: equal, does not equal, begin with, end with, contain, and does not contain.

Equal to and Not equal to

This filter can be used when a particular number of values are needed. For example, filtering the patients in the hospital who were admitted on 15th September. Similarly, in a tax dataset, the filter can be used to single out people who have not paid their tax by checking status as “not equal to paid”.

Greater or Less than

This filter is used to find all data that is either larger or smaller than a certain number. For example, finding all the countries with an average life expectancy above 80 years.

Begin with and End With

This text-based filter can be used to obtain data that begins or ends with a certain alphabet or phrase. For example, filtering everyone whose name starts with “John” or extracting countries whose name ends with “land”.

Contain and Does not Contain

Examples of this filter include: selecting all football clubs whose name contained FC or filtering all the data that contain objectionable words.

Other useful articles: