There is a very effective method in Excel that allows us to search and view certain information from our spreadsheets based on a series of criteria. Learning to use filters in Excel is an excellent way to separate the data that interests us from those that do not, focus on the subject we want to work on and decide which values we want to take into account and which ones we want to exclude at all times. In this post we will see in detail how can you use the filter function in excel and get the most out of it.
As you can check if you keep reading, this function is very practical for many situations. For example, if we are working with a sales file, it helps us locate the dates with the most operations, the best-selling products, or the best-functioning points of sale. Everything simply and quickly.
Next, we are going to see what are the steps to follow to create filters in Excel, how to use them efficiently and how to eliminate them when we no longer need them.
Create filters in Excel
Let's see how you can use the Filter function in Excel. Once we have a spreadsheet open on the screen, the method is really simple. You just have to follow these steps:
- First, we must select any cell in the range.
- Then we go to the ribbon of tools at the top of the screen, specifically to the group "Sort and Filter".
- There we select the button "Data".
- Then click on the button "Filter".
After clicking on “Filter”, some arrows (see image above) in each of the data column headers. They are the ones that will allow us to filter the information.
There are other secondary methods, although equally valid, to create filters in Excel. For example, from the Start menu, we can go to "Modify" and then choose the "Sort and filter" option.
Filter by ranges
Here we will begin to understand the usefulness of the arrows. To filter a range of data in Excel, you must select column header arrow and then choose filter that we want to apply (text, numbers, etc.). After setting the desired criteria for filtering the data, we click “OK”. That easy.
Filter data from a table
This is another very handy way to filter in Excel: put the data in a Excel table, so we can add filters to each column header automatically. By doing so, it is then possible to select the arrow in the header of each column, applying the corresponding filter.
Filter by values
Another interesting option that we have when filtering in Excel is to use the text box to search. This is especially convenient when we are working with a very large file. In this way, all you have to do is type the word or term you want to filter in the box.
Clear filters in Excel
Data search operations can become impossible in the event that there is a filter activated without our knowledge. In this type of situation, one of the first things we should do is Remove or delete any possible filters that may exist.
These are the three methods that we can apply:
Clear a filter that has been applied to a specific column
Basically, it is about undoing what we have explained in the previous section when we talked about applying a filter to a range of values or a column. To delete an applied filter, whatever it may be, you must select the filter icon in the column header again and then click on the option "Clear filter".
Clear all filters
Sometimes the problem is not that a single filter has been applied, but several. In order not to waste time, it is more practical to delete all the filters at once than to do it one by one. The steps to follow are the following: First we go to the tab "Data", then we choose the option "Sort and filter" and finally we click on the option "Delete". All applied filters will be disabled.
Clear all filters
Despite having a similar title, there is a subtle but important difference between what is explained in the previous section and in this one: we are going to remove all filters from document, not just those in a particular column or range. To apply this radical solution, what we must do first is go to the "Data" tab and click on the "Filter" button there.
There are other ways to execute this action: with a keyboard shortcut (the combination Alt+D+F+F) or from the Home tab, following the path Modify > Sort and filter > Filter.