It is important that you have ability to filter data into subsets. If you are working with large set of data, you need some mechanism to create a subset of data to perform effective analysis. Excel filter feature allows you to limit data in excel worksheet according to some criteria. Excel provides you two versions of filter, AutoFilter and Advanced Filter. In this post I will explain AutoFilter and in next post “Advanced filter”. This post is written and tested in Microsoft excel 2003. It is same for Office XP and Ms Excel 2007( excel 2007 has little change in menu layout).
Figure below shows date wise sale data of products. For the sake of simplicity it contains only three dates and three products; in reality you may have hundreds of products showing whole year sale.
Sample worksheet for AutoFilter
AutoFilter
AutoFilter provides ready made basic filters. This can be done just selecting from a drop down list.
To apply excel filter select cells from B4 to B13 and click on Data menu, point to Filter and click on AutoFilter as shown in below figure B.
Figure B for AutoFilter
A drop down arrow appears in B4 column. If you click on arrow it show ready made filter criteria. Now click on arrow and choose “Product A”. This will filter “Product A” as shown in figure C
Notice two choices in drop down, top 10 and custom. To examine this, clear filter by clicking on Data menu, point to Filter and again click on AutoFilter. Now select cells in Value column from E4 to E14 and apply AutoFilter as before. As shown in figure D.
Figure D for AutoFilter
Now click on arrow in value and choose “Top 10”, the Top 10 AutoFilter Dialog Box appears. Now click in the middle box delete 10 and type 2 and click ok as shown in figure E.
Figure E for AutoFilter
Now the filtered result only contains top two sales in “Value” column.
If you want to check sales values greater than $500, click on arrow and click on “(Cusotm…)”, the Custom AutoFilter Dialog box appears. Here fill values as shown in figure and click ok.
Figure F for AutoFilter
Now filtered results contain only sales values grater than 500.
Now here is problem what will you if you want to find sales values greater than 500 on 02-jan-07. To do this remove filters and select all the data and apply filters as shown in figure G.
Figure G for AutoFilter
A filter option for all columns is available now, from first column choose “02-jan-07” and apply custom filter on “Value”.