AutoFilter is a great tool to help you find the information you are looking for in a large spreadsheet. It lets you apply a filter to a spreadsheet so that it only shows the rows you want to see. For example, if you had previewed an invoice register for the entire year in Peachtree, it may be hard to find what you need on it. But if you send it to Excel, you could quickly and easily get to the information you want by using AutoFilter.
In Excel, click anywhere in the table of information that you want to filter. Then click on the Data menu and choose Filter – AutoFilter. (If you are using Excel 2007, click on Home, then “Sort & Filter” and then Filter.) Now you will see a small button with a down arrow on the right side of each cell in the header row. If you click on one of them, you will see a list of every entry in that column. Just choose what you want to see and Excel will hide all other rows. Note that in this example there were no empty rows in our data so Excel could figure out what area we wanted to filter. If your data has empty rows, you will need to manually select the entire region that you want to filter before choosing Autofilter from the menu.
Using our example of the invoice register, you could click on the down arrow next to Name and choose the name of a customer. (Excel 2007 users uncheck “Select All” and check the customer name you want to see) Excel will then show you just the rows for that customer. Now, if you want to look at just a certain date range for that customer, you can click the button in the date column and choose Custom (2007 users choose Date Filters). In the first box, choose “is greater than or equal to”. In the box to the right of that, enter your starting date. Then make sure the “And” option is selected. In the next row choose “is less than or equal to” and enter your ending date. When you click OK you will see just invoices in the date range you selected for the customer that you had selected before that.
To go back to showing everything, you can either go back and choose “All” on each column with an active filter, or just turn AutoFilter off the same way you turned it on.
Here is another great use for AutoFilter. Let’s say you have a spreadsheet with a column for the year, and you suspect that it contains errors. Since each entry is only listed once in the drop down AutoFilter list, you can easily spot data that doesn’t belong. Just turn on AutoFilter and click the button next to Year. If you see 1909, 2008, and 2009, not only have you quickly confirmed that there was a problem without scrolling through the entire spreadsheet, but you can just click on 1909 and see each row that needs to be corrected.
