Show
Key Skills > Filters > AutoFilters Use an AutoFilter to hide some of the data in your worksheet. For example, you can focus on sales to a specific customer, or print a list of your largest orders Create an Excel Table1. Set up the Excel TableIn Excel 2007, and later versions, you should create an Excel Table from your data, and use its built in filtering features. If you are using an Excel Table, you can skip to the next section, Filter One Column. Note: For Excel 2003 AutoFilter instructions, please go to Excel 2003 AutoFilter Basics If your data is not in an Excel Table, follow these steps to prepare your list for an AutoFilter.
2. Turn on FilteringIn Excel 2007 and later, if you're using an Excel Table, it has built-in filtering features. If your data is not in an Excel Table, follow these steps to add an AutoFilter.
A dropdown arrow appears at the right side of each column heading. Filter One ColumnWith the Excel Table, or AutoFilter, in place, you can start filtering the individual columns. You can filter on just one column, such as Customer, or two or more columns. For example, to filter for a specific customer, follow these steps:
In the filtered worksheet, you'll see some changes:
Filter Two ColumnsAfter you have filtered one column, you can refine the filtered results, by filtering two or more columns. For example, after you filter for a specific customer, follow these steps to add a product filter:
Clear an AutoFilterAfter you have applied one or more filters, you can:
To save time, you can
Clear a Specific FilterTo clear a specific filter, and leave other filters turned on:
Clear All FiltersInstead of clearing a specific filter, you can clear all the filters from the Excel Table, in one step:
Add Clear All Button to QATIf you spend lots of time clearing filters, make that task easier - put the Clear All button on your Quick Access Toolbar (QAT). This video shows the steps. Filter With Search BoxInstead of scrolling through a long list of items, when filtering an Excel table column, use the Search Box feature, to find specific text strings. Watch this short video to see how to filter a column by using the built-in Search Box. This feature was introduced in Excel 2010. There are written steps below the video. Use the Search BoxIn this example, we'll use the Search Box to look for product names that contain "am". To use the Search Box when filtering, follow these steps:
Filter for Blank CellsIf there are any blank cells in a column, the drop down list in the heading cell will show one additional item -- (Blanks) -- at the end of the item list. To show only the rows with blank cells in that column, remove the check marks from the other items in the list. Filter for Highest and Lowest ValuesIf a column contains numbers, you can filter to find the highest or lowest numbers that column. In this example, you'll filter for
the 5 lowest values in the Total column.
Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items. Filter for Conditional Formatting ColourIn addition to filtering for text or numbers, you can filter by the cell fill colour, even if that colour was applied by conditional formatting.
Filter for Date Range ColourYou can filter a list of dates for a date range, by using the Between date filter, as shown in the next section. With that method, you'll have to type the dates in the filter settings, each time you want to use a different date range. This video shows how to add Conditional Formatting to the date cells, so the font colour changes slightly, based on the start and end dates entered on the worksheet. Then, you can use the Filter by Color option, to show only the dates in the specified range. This technique is the second method shown in this video, starting at the 2:00 mark Filter for a Specific Date RangeIf a column contains dates, you can filter to find a specific date or date range, or a dynamic date range. In this example, you'll filter for the orders received in January 2011.
Filter for a Dynamic Date RangeIf a column contains dates, instead of finding a specific date range, you can filter for a dynamic date range, such as Last Week, Next Month, or Tomorrow. In this example, you'll filter for the orders received in January 2011.
Update an AutoFilterWhen you open the workbook at a later date, the AutoFilters don't refresh automatically. For example, if you used a dynamic date filter, such as Yesterday, the correct day's results won't show in the filtered table when you open the file the next day. Or, if new data was added to the table, it won't be automatically filtered. To see the updated filtered results, you can reapply the filter.
This will update any dynamic filters in the table, and will also revise the results for any new or changed data. You could create a macro to update the filter, and set it to run when the file opens. Delete Filtered RowsAfter you apply a filter, you can delete the filtered rows.
Check the ResultsAs soon as you delete the rows, clear the filter, and check the results:
Create a Custom FilterSome of the AutoFilter options in the drop down lists end with three dots, such as Begins With... These filters, as well as the Custom Filter option, open the Custom AutoFilter window. In there, you can enter one or two criteria, to create a customized
filter. To filter for one criterion:
In the screen shot below, the Custom Filter will find records where the customer name begins with "M". To filter for two criteria:
In the screen shot below, the Custom Filter will find records where:
Which of the following statements describes the reason you would want to record a macro with absolute references rather than relative references?Which of the following statements describes the reason you would want to record a macro with absolute references rather than relative? You want to preserve the integrity of any cells referenced by formulas in the macro.
Which dialog box do you use to transpose rows and columns?Hold down the Ctrl+Alt+V keys (Command+Ctrl+V on the Mac) to invoke the Paste Special dialogue box again. Check the Transpose option and click OK. This will convert the columns to rows.
Which of the following procedure is not a way to sort a table by the content of one column?Which of the following procedures is not a way to sort a table by the contents of one column? Click an AutoFilter button in the total row.
Which of the following functions returns one value if a condition is true and different value when the condition is not true?Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false.
|