Excel has a filtering tool that allows you to easily filter a data list while working with large amounts of data. Knowing how to properly use this tool is central to fully utilizing Excel's capabilities. The following articles about the filtering tool will help you determine how to best use it in Excel.

Tips, Tricks, and Answers

The following articles are available for the 'Filtering Data' topic. Click the article''s title (shown in bold) to see the associated article.

   Clearing Only Filtering Settings
When you filter data in a worksheet, Excel also allows you to apply sorting orders to that data. Here is a behind-the-scenes discussion of how to clear the filtering settings without affecting the sorting settings.

   Column Formatting Based On a Filter
When working with filtered data, you may want to specially format a column that has a filter applied to it. Here are a couple of ways you can approach this challenge.

   Copying Comments when Filtering
The filtering feature in Excel allows you to quickly copy unique information from one data list to another. If you want to copy the comments or notes associated with that information, you are out of luck; filtering won't do it. There are workarounds you can use to get the same result, however.

   Copying the Results of Filtering
Filtering is a great asset when you need to get a handle on a subset of your data. Excel even makes it easy to copy the filtered data to another location, as described here.

   Counting Filtered Rows
The filtering capabilities of Excel are indispensable when working with large sets of data. When you create a filtered list, you might want to know how many rows are displayed in that list. Here's a variety of methods you can use.

   Deriving a List of Albums by a Music Artist
You can use Excel to keep what is essentially a small, simple database of information. Getting information from the database can be equally simple, as the ideas in this tip demonstrate.

   Displaying Multiple Filtered Colors
The filtering capabilities of Excel are quite helpful in taming a large amount of data. While you can filter by color, the program won't allow you to display multiple colors in the filtering results. This tip provides two ways you can get around this limitation.

   Enabling Filters by Default
Filtering can be a powerful way to work with large amounts of data in a worksheet. If you use filtering quite a bit, you may want to have those filters turned on all the time. Here is a discussion on ways you can approach that desire.

   Entering a Date in a Filter
The filtering capabilities of Excel can come in handy for zeroing in on the data you want to work with. When your data includes a lot of dates, however, it may seem cumbersome to pick a date from those available. A better approach is to create a custom AutoFilter to narrow down your data.

   Filtering Based on Comparing Two Cells
Filtering can be very helpful in allowing you to see only those data records that meet certain criteria. In this tip you discover how to display records based on the comparison of two cells in those records.

   Filtering Columns
The filtering tools provided in Excel make it easy to filter a data list so that only certain rows are displayed. What if you want to filter your information so that only certain columns are displayed, however? Excel doesn't provide a direct way to do this, but there are several workarounds you can adapt to your needs.

   Filtering for Comments (Notes)
Excel makes it easy to filter a data table based on various values in that table. It isn't so easy to filter according to whether a cell contains comments or notes. Here are a few ways you can make the filtering easier.

   Filtering for Purchases within a Given Month
Filtering is a great tool when dealing with large data sets. Knowing how to apply a filter, though, can be a bit tricky at times. Here are two ways to use filtering to get a count of customers purchasing a specific number of times within a target month.

   Filtering to a Date Range in the Past
If you have a large number of data records, each with an associated date, you might want to filter that data so you see only the records from within the past month or so. You can do this easily by using the technique described in this tip.

   Finding Rows with Values in Two Columns
When you use Excel to input and store information, you need to be concerned with whether the information meets your expectations. For instance, you may want to make sure that each row has a value in only one of two columns, and not in both columns. This tip examines ways you can find where the data doesn't meet this expectation.

   Hiding Graphics when Filtering
Excel allows you to set up graphics so that they are associated with cells and even stay with the cells when the cells are sorted. But what about when you filter the list? Here's how to make sure that the graphics are no longer visible when the list is filtered.

   Limits on Filtering
Filtering your data is a very power capability in Excel. What, however, are the limits on how many rows you can filter? This tip helps clarify the question.

   Macro Fails after Filter
When developing a macro that others may use, you might want to test it out to make sure it works properly if a filter is in effect. If it doesn't, you can turn off the filter with a simple single-line command.

   Non-PivotTable Slicers and Timelines
When working with a PivotTable, slicers and timelines can make short work of large data sets. This tip looks at all the places in Excel that you can actually use these tools.

   Not All Rows are Filtered
When you are working with large amounts of data in a worksheet, filtering that data can make the process much simpler. What, however, if your filtering attempts don't include all the rows that you want included? Here's what may be causing the problem.

   Performing Calculations while Filtering
The advanced filtering capabilities of Excel allow you to easily perform comparisons and calculations while doing the filtering. Setting up the filter is easy, as described in this tip.

   Removing Duplicates Based on a Partial Match
Some types of data may have certain fields that contain partially identical information. In such cases you may want to delete cells where those matches occur. Here's some ideas on how to approach the problem.

   Removing Filters and Unhiding Rows and Columns on Multiple Worksheets
Need to remove filters and display all rows and columns in all your worksheets? It is not easy to do manually, but with a macro it is a snap.

   Showing Filter Criteria on a Printout
When you print out a filtered worksheet, you may want some sort of printed record as to what filtering was applied to the worksheet. Here are a couple of ways you can get that printed record.

   Skipping Rows when Filling
Using the fill handle is a great way to quickly fill a range of cells with values. Sometimes, however, the way to fill cells with what you want may not be immediately clear. What if you want to fill a range of cells but skip every other cell, leaving it blank?

   Suppressing Zero Values in PivotTables
PivotTables are great for digesting and analyzing huge amounts of data. But what if you want part of that data excluded, such as information that has a zero value? Here's a couple of quick ideas.

   Using a Filtered Value in a Formula
Accessing filtering criteria for use in a formula can be a real need for some worksheet designs. Getting to that information can be a bit tricky, however. Here are some ideas on how to go about it.