Filters can be used to narrow down the data in a spreadsheet, allowing you to view only the information you need.
In Excel , if your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in the worksheet, allowing you to view only the information you need.
How to filter data
For example, we'll apply a filter to the device log spreadsheet to show only available laptops and projectors.
1. For filtering to work correctly, your spreadsheet must include a header row, which is used to identify the name of each column. In this example, the spreadsheet is organized into different columns identified by the header cells in row 1: ID#, Type, Equipment Detail , etc.
2. Select the Data tab , then click the Filter command.
3. A drop-down arrow will appear in the header cell for each column.
4. Click the drop-down arrow for the column you want to filter. For example, this will filter column B to view only certain types of devices.
5. The Filter menu will appear.
6. Uncheck the box next to Select All to quickly deselect all data.
7. Select the box next to the data you want to filter, then click OK. This example will select Laptop and Projector to view only these types of devices.
8. The data will be filtered, temporarily hiding any content that doesn't meet the criteria. In this example, only laptops and projectors are displayed.
Filtering options can also be accessed from the Sort & Filter command on the Home tab.
How to apply multiple filters
You can apply multiple filters to help narrow your results. This example has filtered the spreadsheet to show only laptops and projectors. However, the author wanted to narrow it down further to show only the laptops and projectors that were checked in August.
1. Click the drop-down arrow for the column you want to filter. This example will add a filter to column D to view information by date.
2. The Filter menu will appear.
3. Select or deselect the boxes depending on the data you want to filter, then click OK. The example will deselect everything except August.
4. A new filter will be applied. In the example, the spreadsheet is now filtered to show only the laptops and projectors that were checked in August.
How to remove a filter
After applying a filter, you may want to remove it from the spreadsheet so you can filter the content in different ways.
1. Click the drop-down arrow for the filter you want to remove. For example, this will remove the filter in column D.
2. The Filter menu will appear.
3. Select Filter From [COLUMN NAME] from the Filter menu. For example, select Clear Filter From "Checked Out" .
4. The filter will be removed from the column. The previously hidden data will be displayed.
To remove all filters from the spreadsheet, click the Filter command on the Data tab.
Advanced filtering features
If you need a filter for something specific, basic filtering might not give you enough options. Fortunately, Excel includes several advanced filtering tools, including search, text, date, and number filters, which can narrow down results to help you find exactly what you need.
How to filter by search
Excel allows you to search for data containing a specific phrase, number, date, etc. For example, this feature would be used to display only Saris brand products in the device log.
1. Select the Data tab , then click the Filter command. A drop-down arrow will appear in the header cell for each column.
Note : If you have already added filters to your spreadsheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. For example, this will filter column C.
3. The Filter menu will appear. Enter your search term into the search box. The search results will automatically appear below the Text Filters field as you type. For example, you would type "saris" to find all Saris brand appliances. When you're finished, click OK.
4. The spreadsheet will be filtered by the search term. In this example, the spreadsheet is currently filtered to show only Saris brand devices.
How to use advanced text filters
Advanced text filters can be used to display more specific information, such as cells containing certain characters or data, or to exclude a specific word or number. For example, you might want to exclude any items containing the word "laptop".
1. Select the Data tab , then click the Filter command. A drop-down arrow will appear in the header cell for each column.
Note : If you have already added filters to the spreadsheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. For example, this will filter column C.
3. The Filter menu will appear. Hover over Text Filters , then select the desired text filter from the drop-down menu. For example, select Does Not Contain. to view data that does not contain specific text.
4. The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, then click OK. For example, we would enter "laptop" to exclude any items containing this word.
5. The data will be filtered by the selected text filter. In this example, the spreadsheet now displays items that do not contain the word "laptop".
How to use advanced digital filters
Advanced number filters allow you to manipulate numbered data in various ways. For example, they might only display certain types of devices based on their ID number range.
1. Select the Data tab on the Ribbon, then click the Filter command. A drop-down arrow will appear in the header box for each column.
Note : If you have already added filters to the spreadsheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. For example, this will filter column A to view only certain IDs.
3. The Filter menu will appear. Hover over Number Filters , then select the desired number filter from the drop-down menu. For example, select Between to view ID numbers within a specific range of numbers.
4. The Custom AutoFilter dialog box will appear. Enter the desired number(s) to the right of each filter, then click OK. For example, if you want to filter ID numbers greater than or equal to 3000 but less than or equal to 6000, it will display ID numbers within the range of 3000-6000.
5. The data will be filtered by the selected numerical filter. In this example, only items with ID numbers from 3000 to 6000 will be displayed.
How to use the advanced date filter
Advanced date filters can be used to view information from a specific time period, such as last year, the next quarter, or between two dates. This example would use an advanced date filter to view only devices that were tested between July 15th and August 15th.
1. Select the Data tab , then click the Filter command. A drop-down arrow will appear in the header cell for each column.
Note : If you have already added filters to the spreadsheet, you can skip this step.
2. Click the drop-down arrow for the column you want to filter. For example, this will filter column D to view only a specific date range.
3. The Filter menu will appear. Hover over Date Filters , then select the desired date filter from the drop-down menu. For example, select Between. to see the device has been checked from July 15th to August 15th.
4. The Custom AutoFilter dialog box will appear. Enter the desired date(s) to the right of each filter, then click OK. For example, if you want to filter the dates after or exactly on July 15, 2015 and before or exactly on August 15, 2015, a range between these dates will be displayed.
5. The spreadsheet will be filtered by the selected date filter. Now you can see which items were checked in between July 15th and August 15th.