Filter option

Hi friends, hope calculation of formulas are not going incorrect now after setting the ‘precision as displayed’ indicator, lookups are in place for starting your audit assignments and also searching of internet has become easier than before. Our friend Bahadur Singh is now in a new dilemma, how to convert data to information?

He downloaded a sales register file from an accounting package / ERP that has huge data with some header. The data contains posting date, invoice number, customer name, amount, narration. He feels that this data is very difficult to analyse. He wants to perform the following functions but is not sure how to do the same:
1. Select top 10 invoices by amount.
2. Select invoices with customer name beginning with alphabet ‘A’.
3. Select invoices with narration containing words ‘cash’.
4. Select invoices for amount more than Rs. 1 Lac.

There are many more such questions that he wants to answer but the only option he knows is going through each and every record and identifying the above needs. In case the data set is for few records approx 20 in numbers one may go through each record and select these records but what if the number of records ranges in few thousands.

To satisfy the above needs excel has a auto filter function that enables a user to answer such queries easily.

Let us see this function in detail, its features and usage.

To use the filter function select the whole range of database (though this is not mandatory it is recommended as in case the database has a blank row or column than it may not consider the entire database if it is not selected) and then go to
Data >> Filter >> AutoFilter. This will bring drop down boxes in all the cells of header. The screen short gives a view of how the database would now look.

Once we have the drop down boxes on the header of data converting this data to information and replying to various questions is just a matter of few seconds. Let us now see the usage of this option:
1. Displaying top/bottom N number of records: In the drop down menu select option ‘(Top 10…)’. This will give a selection option for Top/Bottom and number of records needed. Select OK to get only the required records. All the other records are automatically hidden. Hence one can always use this option to select Top 10 records while auditing, doing Pareto analysis, etc.
2. Displaying a particular record: When one is in search of a particular record than it can be directly selected from the drop down menu. Example, in the above database if one is searching for invoices raised to customer name ‘Rahul Patel’ then by selecting the same in the drop down menu of ‘customer name’ all the records with customer name as ‘Rahul Patel’ will be available.
3. Displaying the records with particular field being blank / non-blank: In case any of the column of database has blank in particular records selection of blank or non-blank records is made easy by just selecting the same in drop down menu. In any drop down menu the last two options are ‘(Blanks)’ and ‘(NonBlanks)’ respectively.
4. Custom Selection: The custom option of filter helps replying answers like records greater than or less than a particular amount, beginning or ending with a particular alphabet, not beginning or not ending with a particular alphabet, etc. Selecting the ‘(Custom…)’ option in the drop down menu can do this. To list down the custom options available:
i) equals
ii) does not equal
iii) is greater than
iv) is greater than or equal to
v) is less than
vi) is less than or equal to
vii) begins with
viii) does not begin with
ix) ends with
x) does not end with
xi) contains
xii) does not contain
Selection of the above options based on the needs can help us convert data to information in less than few seconds. The same can answer many analytical questions. In addition one can also use ‘And’ “Or” option to select two options of the above.

To come out of the filter mode, one may select ‘(All)’ from the drop down menu and in case one does not need filter at all than the drop down boxes can be removed by once again selecting:
Data >> Filter >> AutoFilter. This will remove the drop down boxes and the entire data will now be visible.

Important points while using of Auto Filter option:
Using of sum function on the visible records: Once you select a filter option based on the requirement and than try to use the sum function on the visible records, it will always give incorrect results. This is because in filter mode all the records that do not meet your conditions are hidden. Usage of sum function on the visible records does not ignore those records that are hidden and hence result of sum function includes all the records in that range whether hidden or not. In such a scenario one has to use the subtotal function. The syntax has to be =subtotal(9,range).
In this syntax subtotal ensures that only visible cells are considered, 9 would suggest that we need to use addition of visible cells and range would be the selection of cells that the user wants to be considered.


Bahadur Singh is happy learning about this function. It has enabled him answer all the questions and eased analysis of any data irrespective of the size.