How To Filter An Excel Spreadsheet

Excel Cavalcade Filter (Table of Contents)

Filter Cavalcade in Excel
How to Filter a Column in Excel?

Filter Column in Excel

Filters in Excel are used for filtering the data by selecting the data type in the filter dropdown. By using a filter, we tin can make out the data that we want to see or on which we demand to work.

To access/apply a filter in any cavalcade of excel, go to the Data bill of fare tab; under Sort & Filter, we volition discover the Filter option.

How to Filter a Column in Excel?

To filter a column in excel is a very simple and easy task. Allow's sympathize the working of how to filter a column in Excel with an case.

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

We accept some sample data tables in excel, where we will apply the filter in columns. Beneath is the screenshot of a data set, which has multiple columns and multiple rows with various data sets.

For applying Excel Column Filter, select the top row kickoff, and the filter will be applied to the selected row only, every bit shown beneath. Sometimes when we work for a big set of data and select the filter directly, the current look of the sheet can be applied.

As we can encounter in the higher up screenshot, row 1 is selected, and it is set up to apply the filters.

Now for applying filters, go to the Data menu, and under Sort & Filters, select Filters .

Once we click on Filters, we can come across the filters will be practical in the selected row, as shown in the below screenshot.

The top row 1 now has the dropdown. This drop-downward is those things by which nosotros can filter the data as per our need.

To open the drop-downwards option in an applied filter, click on the down arrow (equally shown below) or go to any column top and printing Alt + Down.

A drop-downwardly menu will appear, as shown in the beneath screenshot.

Equally we tin can see in the higher up screenshot, in that location are few filter options provided past Microsoft.

Sort A to Z / Sort Oldest to Newest (for dates) / Sort Smallest to Largest (for numbers)
Sort Z to A / Sort Newest to Oldest (for dates) / Sort Largest to Smallest (for numbers)
Sort past Color
Clear Filter From "Product Type" (This would entitle the name of columns where a filter is applied)
Filter past Color
Text Filters
Search/Manual Filter

As we tin run across in the first screenshot, where data is in a randomly scattered format. Let us utilize the filter and come across what changes happen in the data. For that, go to column A and in the drib-down carte du jour, select only Desktops, equally shown in the below screenshot, and click on OK.

One time we do it, we volition see, the data is now filtered with Desktop. And whatever the data is there in w.r.t. Desktop in the remainder of the columns will too get filtered, as the screenshot below.

As nosotros can run into in the higher up screenshot, data is at present filtered with Desktop, and all the columns are too sorted with data bachelor for Desktop. Also, the line numbers, which are circled in the above screenshot, are also showing the random numbers. This means that the filter which we have practical was in a random format, so the line numbers take also been scattered when we applied the filter.

At present, let's try to utilize the text filter, which is a very interesting part of filtering the data. For that, go to any of the columns, and click on the drop-down button to see the filter options.

Now become to Text Filters.

We volition observe a few more options bachelor for filtering the information, as shown in the below screenshot.

The highlighted portion of Text Filters in the box has Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Incorporate, and Custom Filter.

Equal: With this, nosotros can filter the data with an exact equal give-and-take available in the data.
Does Not Equal: With this, we tin filter the data with a word that does not match the available words in the data.
Begins With: This filters the information, which begins with a specific word or alphabetic character, or character.
Ends With – This filters the information, which ends with a specific give-and-take or alphabetic character, or character.
Contains: With this, nosotros tin can filter the data which contains any specific word or letter of the alphabet, or character.
Does Not Contain: With this, nosotros tin can filter the data which does not contain whatever specific word, alphabetic character, or grapheme.
Custom Filter: With this, we tin utilize whatsoever combination of the higher up-mentioned Text Filters in information together to get data filtered more deeply and specific to our requirement. Once we click on Custom Filter, nosotros will get a box of Custom AutoFilter, every bit shown in the beneath screenshot.

Every bit we can see in the above screenshot of Custom AutoFilter, it has to two filter options at the left sides, which And and Or bank check-in circles separate. And the other 2 boxes provided on the left side are for filling the criteria values. This tin be called a smart filter.

There are unlike means of applying the Excel cavalcade filter.

Data menu -> Filter
By pressing Ctrl + Shift + L together.
By pressing Alt + D + F + F simultaneously.

Pros of Excel Column Filter

By applying filters, we can sort the data as per our needs.
By filters, performing the analysis or any work becomes easy.
Filters sort the information with words, numbers, jail cell colors, font colors, or with any range. Also, multiple criteria tin exist used as well.

Cons of Excel Column Filter

Filters tin can be applied to all kinds of range sizes, but information technology is not useful if the data size increases upwards to a certain limit. For some cases, if the information is going across 50,000 lines, then it becomes slow, and sometimes it does non bear witness data bachelor in whatsoever cavalcade.

Things to Call up

If you are using filter and freeze panel together, then first apply the filter and and then employ freeze console. Past doing this, information will exist frozen from the middle portion of the canvas.
Avoid or be cautious while using a filter for huge sets of data (peradventure for 50000 or more than). Information technology will accept a lot more time to become practical, and sometimes the file too gets crashed.

Recommended Manufactures

This has been a guide to Filter Column in Excel. Here we talk over how to filter a column in Excel forth with practical examples and a downloadable excel template. You can also go through our other suggested articles –

Excel AutoFilter
Excel Data Filter
Advanced Filter in Excel
VBA Filter