HOW TO FILTER DATA IN EXCEL?

INTRODUCTION

In practical applications, in maximum cases , we have a large data which can’t be managed by just having a look on the data and manually selecting it. If we want to search for a particular data, we can do that by using control + F  and then typing but it is again a cumbersome task to use it over and again and type the search word.

Or some other ways are to sort the data with respect to some column but in that case too, we’ll need to eliminate the non required data. But after processing the concerned data this time, we again need to bring back the other data. It is again not a very optimum way to manage the data. So , for such situations , to analyze the data by just selecting a particular field and concentrating on concerned data only, we have got this option named FILTER.

Filtering the data is another one of the widely used options in EXCEL . It gives us the facility of instantly opting to see any particular class of data and seeing only what is needed.

BUTTON LOCATION FOR FILTERING DATA IN EXCEL

The button location for the FILTER OPTION is under the DATA TAB >SORT AND FILTER section.

The visual location is shown in the picture below.

STEPS TO APPLY FILTER ON DATA IN EXCEL

The following steps are used to apply a FILTER on any data.

STEPS:

• Go to DATA TAB>SORT AND FILTER section. The location is shown above in the picture.
• Select all the COLUMN HEADERS. The complete row can also be selected. It’ll apply filters only on the columns with data but if by chance there is some data in some unintended column, a filter will be given there also. So it is better to select the column headers only.
• Click FILTER BUTTON.
• The filter will be applied on the table.
• Take care that there is no  gap between the headers and the data. The filter will be applied on all the contiguous ( Adjacent) data.
• The picture below shows the applied filter. The arrows appear in the lower right corner of the headers.

The above picture shows a FILTER APPLIED on the table. The arrows in the lower right corners shows that the filter has been applied.

The data shows the number of vehicles sold on the days of a week.

KEYBOARD SHORTCUT TO APPLY FILTER IN EXCEL

There are countless keyboard shortcuts and we need not to remember them all. But even then if we are using any special operation quire a few times weekly, it is a good idea to start using the keyboard shortcuts. Its always faster than the mouse operation. So keyboard shortcut to apply a filter is

• Select the headers.
• Press ALT+A+T [KEEP THE ALT PRESSED. A AND T SHOULD BE PRESSED SEQUENTIALLY. PRESS A FIRST THEN T.]
  
  
  

.

WHEN TO USE FILTER ON DATA

Let us see in which conditions we should use filter on our data.

We can apply different types of operations on the filtered data such as sum,subtraction etc. but the formulas should not be applied on the filtered data. If formulas are applied, it takes into account all the rows hidden in between. But we can check the sum etc. or search any specific information for the filtered data.

EXAMPLE TO APPLY A FILTER ON DATA

LET’S UNDERSTAND THE DIFFERENT PROCEDURES OF FILTER USING A DATA SAMPLE.

We have a number of vehicles delivered every day of week for three week. Now lets apply different filtering options on the data.

The given data is

STEPS TO APPLY FILTER

Here are the steps to apply filter on the given data.

• Select all the headers.
• Click FILTER button.

STEPS TO CHOOSE FROM AN APPLIED FILTER

Now, after the filter has been applied, let us see how to make the choices from the filters.

CLICK the arrow in the lower right corner of the filter header of the column, which you want to make the basis of filtering. A list with the available options will open. Select the checkbox for the values you want to keep and uncheck the values you don’t want.

In the picture below, we are trying to look at the options available for the DAY COLUMN of the example.

After clicking the drop down of the filter, we can see that different options are there. The options are the weekdays. We can choose any option from this list and only the chosen option and its counterparts in other column will remain. Rest will be hidden.

If we want to check the vehicles on monday and sunday only, we uncheck the other days,as shown in the picture above and the result is shown in next pic.

OTHER WAYS TO FILTER THE DATA

There are few more options to get the same results.CLICK ANY OF THE OPTIONS. 

SLICER-The advance way of filtering.
FILTER FUNCTION- If you want to automate  the filtering process