PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

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.

FILTER ON EXCEL

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.
apply filter on excel
THE APPLIED FILTERS ON A TABLE

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.<br><br>Filtering option just hides the rows which do not fulfill the
conditions. So never apply any kind of operation. Its just for analyzing
the 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

DAYNUMBER OF VEHICLES
SUNDAY 43
MONDAY32
TUESDAY12
WEDNESDAY54
THURSDAY66
FRIDAY65
SATURDAY54
SUNDAY34
MONDAY23
TUESDAY45
WEDNESDAY65
THURSDAY67
FRIDAY98
SATURDAY78
SUNDAY67
MONDAY56
TUESDAY45
WEDNESDAY34
THURSDAY23
FRIDAY23
SATURDAY45
filter for excel
THE SAMPLE DATA FOR FILTER APPLICATION IN EXCEL

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.

filter data in excel
CHOOSING THE OPTION FROM THE FILTER LIST

After clicking the dropdown 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.

filter excel data
TABLE AFTER CHOOSING SUNDAY AND MONDAY OPTIONS

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.

 
 

OTHER WAYS TO REACH THIS ARTICLE

  • FILTER DATA IN EXCEL, SEPARATE FIELDS IN EXCEL, SEGREGATE IN EXCEL

YOU MAY LIKE

PREVIOUS ARTICLE
NEXT ARTICLE

LEARN

Encourage us by following at …

Facebook Twitter Pinterest

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: