
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.
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
- 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
DAY | NUMBER OF VEHICLES |
SUNDAY | 43 |
MONDAY | 32 |
TUESDAY | 12 |
WEDNESDAY | 54 |
THURSDAY | 66 |
FRIDAY | 65 |
SATURDAY | 54 |
SUNDAY | 34 |
MONDAY | 23 |
TUESDAY | 45 |
WEDNESDAY | 65 |
THURSDAY | 67 |
FRIDAY | 98 |
SATURDAY | 78 |
SUNDAY | 67 |
MONDAY | 56 |
TUESDAY | 45 |
WEDNESDAY | 34 |
THURSDAY | 23 |
FRIDAY | 23 |
SATURDAY | 45 |
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 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.
OTHER WAYS TO FILTER THE DATA
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
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.