Filter data by font or fill color

Most of the time, you filter data based on the cells' values. You might want to see only the data that pertain to a particular company. Or you may want to see only records in which the Profit column exceeds 1,000. Whatever the case, filtering by cell value is by far the most common filtering method.

Excel 2007 and 2010, which offer a great variety of formatting options, have expanded users' filtering capabilities. Now the colors that you add to your spreadsheet can lend a hand when it comes to filtering.

Let's say that we have color-coded a list of contacts based on whether we've verified that the contact information is correct. Rows with a green fill color are verified and correct; rows with an orange fill color need to be deleted or edited. Rows with no fill color still need researching. Filtering the list to show only rows with no fill color can make it easier to complete the task of verifying the contacts list information.

To filter data by fill color:

• Open your workbook in Excel.
• Right-click on a cell within your data that matches the color by which you want to filter. In our example, we'll right-click on a cell with no fill color because those are the records we want to view.
• Choose Filter > Filter By Selected Cell's Color from the shortcut menu.

Excel automatically applies a filter to the data and displays only the rows with no fill color. Note that there is also a menu option for filtering based on a cell's font color.