How To Filter In Excel

What is Filter in Excel?

The filter in excel helps display relevant data by eliminating the irrelevant entries temporarily from the view. The data is filtered equally per the given criteria. The purpose of filtering is to focus on the crucial areas of a dataset. For example, the urban center-wise sales data of an organization tin can be filtered by the location. Hence, the user can view the sales of selected cities at a given time.

A filter is necessarily required when working with a huge database. Being a widely used tool, the filter converts a comprehensive view into an easy-to-understand one. To apply filters, the dataset must incorporate a header row which specifies the name of every column.

You are gratuitous to utilize this image on your website, templates etc, Please provide u.s. with an attribution link Commodity Link to be Hyperlinked
For eg:
Source: Filter in Excel (wallstreetmojo.com)

How to Filter in Excel?

You can download this Filter Column Excel Template here – Filter Column Excel Template

Information technology is proficient to work with filters because they fit our needs the way we want to. In social club to filter data, select the entries to exist visible and deselect the rest of the items.

The three methods to add together filters in excel are listed every bit follows:

With filter option nether the Domicile tab
With filter selection nether the Data tab
With the shortcut key

Allow us consider a dataset to become through the iii methods of adding filters.

The post-obit tabular array shows the invoices issued to the buyers of dissimilar cities. We want to filter the data using different methods.

Method 1: With Filter Option Under the Home tab

In the Dwelling house tab, there is a "filter" option under the "sort and filter" drop-down of the "editing" department, as shown in the following prototype.

Pace i: Select the data and click "filter" under the "sort and filter" drop-downward.

Stride 2:The filters are added to the selected data range. The drop-down arrows, shown within the ruby boxes in the following image, are filters.

Step 3: Click the drop-down arrow of the column "metropolis" to view the different names of the cities.

Step iv: To see the invoice values of "Delhi" but, select "Delhi" and uncheck all the remaining boxes.

Stride 5: The data for the urban center "Delhi" is filtered and displayed in the post-obit image.

Method 2: With Filter Option Under the Data tab

In the Information tab, in that location is a "filter" option nether the "sort and filter" section, equally shown in the following epitome.

Method 3: With the Shortcut key

The keyboard shortcuts An Excel shortcut is a technique of performing a transmission job in a quicker way. read more are a good mode to speed up the daily tasks. Select the data and add together the filter using either of the post-obit shortcuts:

Press the keys "Shift+Ctrl+L" together.

Press the keys "Alt+D+F+F" together.

Note: The preceding shortcuts for adding filters Using sorting and filtering, we tin can meet the data category wise. With filtering information rapidly you tin easily navigate through menus or clicking through a mouse in less time. read more than are toggle keys. Repetitive pressing helps to turn on and turn off the filters.

How to Add Filters in Excel?

Nosotros can filter numbers using advanced techniques. Permit us consider some examples to understand the working of filters in Excel.

Example #i–"Number Filters" Option

Working on the data under the preceding heading (methods of filtering in Excel), we want to utilise the following filters:

a. To filter cavalcade B (invoice value) for numbers greater than 10000

b. To filter cavalcade B for numbers greater than 10000 but less than 20000

Let us go through the two cases one by i.

a. Filter numbers greater than 10000

Step ane: Open up the filter in column B (invoice value) by clicking on the filter symbol.

Footstep 2: In "number filters," choose the "greater than" pick, as shown in the following paradigm.

Step iii: The "custom autofilter" box appears.

Footstep 4: Enter the number 10000 in the box to the right of "is greater than."

Step five: The output displays the invoice values greater than 10000. The symbol within the red box is the filter icon. It indicates that the filter has been applied to column B.

b. Filter numbers greater than 10000 merely less than 20000

Footstep 1: In "number filters," choose the "greater than" option.

Step two: In the "custom autofilter" box, select "is less than" in the second box to the left-hand side. This is shown in the post-obit paradigm.

Step 3: Enter the number 10000 in the box to the correct of "is greater than." Enter the number 20000 in the box to the correct of "is less than."

Step iv: The output displays the invoice values greater than 10000 but less than 20000.

Instance #ii–"Search Box" Selection

Working on the data nether the preceding heading (methods of filtering in Excel), we accept replaced the first cavalcade (city) with product IDs.

We desire to filter the details of production ID "prd 1."

The steps are listed every bit follows:

Step one: Add filters to the columns "product ID" and "invoice value."

Footstep 2: In the search box A search box in Excel finds the needed information past typing into it, then filters the data and displays only that much info. When working with big datasheets, this uncomplicated tool may save a lot of fourth dimension. read more than , enter the value that is to be filtered. So, enter "prd i."

Stride 3: The output displays simply the filtered value from the list, as shown in the post-obit image. Hence, we tin see the invoice value of the production ID "prd 1."

Choice while you Drop Down the Filter Part

Sort A to Z and Sort Z to A: If yous wish to arrange your data ascending or descending order.
Sort by Color: If you want to filter the data by color if a cell is filled by colour.
Text filter: When yous want to filter a column with some verbal text or number.
Filter cells that brainstorm with or end with an exact character or the text
Filter cells that contain or do not contain a given character or discussion anywhere in the text.
Filter cells that are exactly equal or not equal to a detailed grapheme.

For example:

Suppose you want to use the filter for a specific item. Click on to text filter and choose equals.

Information technology enables you the one dialogue, which includes a Custom Auto-Filter dialogue box.

Enter fruits under category and click Ok.

At present yous will become the information of fruits category just as shown below.

The Techniques of Filtering in Excel

The post-obit techniques must be followed while filtering information:

If the dataset is large, type the value to be filtered. This filters all the possible matches.
If numerical information has to exist filtered by specifying the greater than or the less than number, use the "number filters" option.
If data has to exist filtered by the color of specific rows, utilize the "filter by colour" option.

Frequently Asked Questions

ane. What are filters and how to add them in Excel?

Filtering is a technique which displays the required information and removes the unwanted data from the view. It helps the user focus on the relevant data at a given time.

The steps to add together filters in Excel are listed equally follows:
• Ensure that a header row appears on top of the data, specifying the column labels.
• Select the data on which filters are to be added.
• Add filters past any of the three given methods.
o Click the "filter" option under the "sort and filter" (editing section) drop-down of the Home tab.
o Click the "filter" option nether the "sort and filter" section of the Data tab.
o Press the keys "Shift+Ctrl+L" or "Alt+D+F+F."

Note: Every bit soon as the filters are added, a drop-downwards arrow appears on the detail column header.

ii. How to utilize filters to one or more than columns?

The steps to apply filters to 1 or more columns are listed as follows:
• Click the drop-down arrow of the cavalcade to be filtered.
• Uncheck the "select all" choice which helps deselect all data.
• Select the boxes to be displayed.
• Click "Ok."
The drop-down arrow changes to the filter icon as soon as a filter is applied. When filters are applied to multiple columns, the filter icon appears on each one of them. Hovering over the filter icon shows the filters that have been applied.

Note: The drib-down arrow on a column header indicates a filter is added. The filter icon indicates a filter has been applied.

iii. How to use filters in Excel?

The filters tin exist practical to numbers, text values, and dates. These cases are discussed equally follows:
Filter numbers
• Click on the "number filters."
• Select whatsoever of the options like "equals," "does not equals," "greater than," "less than," "betwixt," "above average," then on.
• Specify the required fields in the dialog box that appears. This box may or may not exist displayed.

For example, in "equals," enter the number against which the values should exist compared. The filtered results show the matching numerical values.

Filter text and date values
• To filter text and appointment values, select "text filters" and "date filters" from the respective drop-downward arrows.
• The "text filters" allow filtering text strings which contain specific characters or words. The "date filters" allow filtering dates for a item year, month, week, and then on.

Note: The "plus" and the "minus" sign of the engagement filters are used for expanding and collapsing the various levels respectively.