Using Microsoft Excel's Autofilter is a quick and easy way to manage large amounts of data, filter information, and find what you need. After entering your data, you will need to select and sort them by customizing the automatic filter to your liking. This 5-step process can be learned in minutes, improving your knowledge of the Excel program and also the speed with which you work on spreadsheets.
Steps
Step 1. Enter all the data, or open the spreadsheet containing your data
It is a good idea to head the columns to categorize the underlying data details. If you haven't already created headers, do so before dealing with the data filter.
Step 2. Select all the data you want to filter
Since the "Automatic Filter" option is an automatic process that does not receive specific inputs on how to filter the data, it is recommended that you select all the data entered in the sheet; this is to avoid losing data associations in the rows and / or columns
Step 3. Click on "Data", then select "Filter"
Step 4. You will notice the appearance of some drop down buttons in the category columns
Use these buttons to set the filtering options.
- Sort Smallest to Largest - This option will sort the data in ascending order, based on the data in the relevant column. The numbers will be sorted in ascending order (1, 2, 3, 4, 5, etc.) and the words in alphabetical order (a, b, c, d, e, etc.).
- Sort Largest to Smallest - This option will sort the data in descending order, based on the data in the relevant column. The numbers will be sorted in reverse order (5, 4, 3, 2, 1, etc.) and the words in reverse alphabetical order (e, d, c, b, a, etc.).
- Top 10: This option sorts the first 10 rows of data in the spreadsheet (when the initial setting is "Select All"), or the first 10 rows of data of the filtered selection.
- Custom Filter: You can customize how Excel sorts data based on ranges of data and information.
- List of Values: You can sort the data based on all other values listed for that column. Excel groups the same values together. For example, employees living in the same city can be sorted using only one value.
Step 5. To turn off automatic filtering, repeat step 3
Advice
- The presence of drop-down buttons indicates which columns the filters will be applied to. If the button arrow is blue, it means that a filter has been applied from that menu. If the arrow on the button is black, it means that no filter has been applied in that menu.
- Back up your data before using the automatic filter. While the automatic filter can be safely turned off, any changes made to the data can overwrite existing information, so be careful.
- The automatic filter organizes the data vertically, in other words the filter options can only be applied to columns, and not to rows. However, by inserting categories in each row, and then filtering only the column of those rows, you can achieve the same effect.
- The filter will not work if you include empty cells in your selection.
Warnings
- Save your changes often unless you have backed up your data and intend to overwrite it.
- Filtering your data does not mean deleting the rows, but simply hiding them. Hidden lines can be re-displayed by selecting the line above and below the hidden line, right clicking on the "Unhide" option.
