Have you ever been inundated with large amounts of data and not enough time to figure out how to make sense of it all or even worse to analyze it? Sure you have, we all have. In any line of work, from the corporate world to the small business owner to the college professor or PhD student, we have all been there and we know that there are tools out there like Excel that could really help us navigate through the sea of raw data and make sense of it. Pivot tables work well for making reports from vast of amounts of data. But, we think it is difficult or we just don’t have the time to use the help feature and go through a bunch of steps to get to the one step that we actually need. Well, fear no more because in this tutorial, I will show you some basic steps to using the auto filter and advance filter functions in Excel.
Please don’t be scared of them, although, the filtering functions are very powerful, they can be learned and the more you play with them the better you will get. The Autofilter and Advanced Filter functions in Excel are used to display specific groups of data and hide from view the rest of the data and this way help you find and focus on the data that you actually need. It is different from the sorting function, which actually regroups data or resorts the data in a different way, for example, in alphabetical order.
In this tutorial, we’ll use fictitious data from Breeze Distributor Company. If it sounds familiar, it’s because we have used this fictitious company in other tutorials. In this tutorial, we’ll analyze product, account manager and sales data for the month of January 2007.
Breeze Distributor Company – January 2007
|Fruit Drink||Juan Garcia||$800.00|
|Fruit Drink||Benny Lopez||$1,678.00|
|Fruit Drink||Steve Brady||$965.00|
|Fruit Drink||Benny Lopez||$1,100.00|
|Fruit Drink||Juan Garcia||$1,009.00|
Let’s get started!
1. Go to Start>Programs>Microsoft Excel to open the application.
2. Once Microsoft Excel is opened, go to File>New to open a new spreadsheet. Please enter the information provided at the beginning of the tutorial. Please enter all the data as it is shown below. Then go to File>Save As and save it to ensure you don’t loose any of your work.
3. Place the cursor on the cell labeled “Products” and go to Data>Filter>AutoFilter to activate the autofiltering function.
4. Drop down menus will appear for the column headers ,Products, Account Managers and Sales. You can now click on the drop down arrows and see a list of options. You can view the data in ascending and descending order or just pick one of the categories displayed.
5. If you select for example “Snacks”from the “Product” drop down filtering box, you will see that it will hide all the other product information, and only display the account manager and sales figures associated with the “snack” product.
Please Note: If you would like to see more specific information, for example, view sales and product information for only two account managers, then the Advanced Filter function will very useful. With this function, you can give Excel very specific instructions on the data that you want to isolate and view. In the next steps, I will show how to achieve to this.
6. Because we will continue to use the same spreadsheet, let’s deactivate the Autofilter function. Go to Data>Filter>Autofilter and click on Autofilter and the dropdown boxes will disappear from the main column headings, “Product, Account Manager and Sales.”
7. In this step, we will begin to use the Advanced Filter function. With Advanced Filtering, you need to tell Excel your criteria and in essence, write it on the same spreadsheet. You will need to insert about four new rows just above the raw data, so we’ll insert these rows above the spreadsheet title “Breeze Distributor Company” January 2007, to insert new rows, place the cursor on the title row and go to Insert>Rows and repeat this step about four times and make sure there is a blank row between the instruction area and the raw data. Then, enter the titles Product, Account Manager and Sales under columns A, B and C. It should look that the image below.
8. In this example, we are going to ask Excel to find two sets of criteria, and isolate and display this specific information. We are going to ask Excel to isolate information for just two account managers, Paul Young and Mary Ruiz. To enter this set of instructions, you need to enter it the following way so that it makes sense to Excel, =”=entry.” So please enter =”=Paul Young” and right below =”=Mary Ruiz under the Account Manager column, and press enter.
9. Now, go to Data>Filter>Advanced Filter and select it. Now select all the raw data under “Unit Range”, and select the new set of instructions that you just entered for the “Criteria Range,” and click OK.
10. Now, only the product and sales information related to Paul Young and Mary Ruiz will be displayed.
Fantastic! See that it was not so hard. Now remember that the criteria you enter when using Advanced Filtering can be more complex and I will expand on these different scenarios in another tutorial, but for now, practice and play with these simple steps as much as you can, even if you make mistakes, the more you use them, the better you will get.If you found this helpful, please share it below. Thank you!