Microsoft KB Archive/213923

= How to use AutoFilter to filter records based on cell formatting in Excel =

Article ID: 213923

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Office Excel 2003

-



This article was previously published under Q213923



For a Microsoft Excel 98 and earlier version of this article, see 151449.

IN THIS TASK
SUMMARY
 * Filter Based on a Bold Font
 * Filter Based on a Red Font

REFERENCES



SUMMARY
This step-by-step article describes how to use the AutoFilter feature of Microsoft Excel to filter records in a database based on cell formatting in certain fields.

back to the top

Filter Based on a Bold Font
  Start Microsoft Excel, and then type the following data in a new worksheet:   A1: Name      B1: Amount A2: Bob      B2: 1 A3: Sue      B3: 2 A4: Pat      B4: 3 A5: Tom      B4: 4  Select cells A2 and A4. On the Format menu, click Cells. On the Font tab, click Bold under Font style, and then click OK. On the Insert menu, point to Name, and then click Define. In the Define Name dialog box, type bold_cell in the Names in workbook box.</li> In the Refers to box, type the following formula:

=GET.CELL(20,OFFSET(INDIRECT("A2"),ROW-2,0))

</li> Click OK.</li> Type the following data in the same worksheet:

C1: Bold?

C2: =bold_cell

</li> Select cell C2, grab the fill handle, and then fill the formula in cell C2 down to cell C5 so that all of the cells in the range C2:C5 have the formula:

=bold_cell

After you fill the formula down, the following data is displayed in column C based on the current cell formatting:

C1: Bold?

C2: TRUE

C3: FALSE

C4: TRUE

C5: FALSE

</li> Select cell A1.</li> On the Data menu, point to Filter, and then click AutoFilter.

The AutoFilter arrows appear at the top of each field.</li> Click the arrow on the Bold? field, and then click TRUE in the list that appears.</li></ol>

Your list should be filtered so that only the records in which the name has been formatted in bold are displayed (row 2 and row 4).

NOTE: If you change the cell formatting of cell A3 to bold and remove the bold formatting from cell A4, you must recalculate the worksheet by pressing F9 in order for the values in column C to be updated.

back to the top

Filter Based on a Red Font
<ol>  Start Microsoft Excel, and then type the following data in a new worksheet: <pre class="fixed_text">  A1: Name      B1: Amount A2: Bob      B2: 1 A3: Sue      B3: 2 A4: Pat      B4: 3 A5: Tom      B4: 4 </li> Select cells A3 and A5.</li> On the Format menu, click Cells.</li> On the Font tab, click Red under Color, and then click OK.</li> On the Insert menu, point to Name, and then click Define.</li> In the Define Name dialog box, type red_cell in the Names in workbook box.</li> In the Refers to box, type the following formula:

=GET.CELL(24,OFFSET(INDIRECT("A2"),ROW-2,0))

</li> Click OK.</li> Type the following data in the same worksheet:

C1: Red?

C2: =red_cell

</li> <li>Select cell C2, grab the fill handle, and then fill the formula in cell C2 down to cell C5 so that all of the cells in the range C2:C5 have the formula:

=red_cell

After you fill the formula down, the following data is displayed in column C based on the current cell formatting:

C1: Red?

C2: 0

C3: 3

C4: 0

C5: 3

NOTE: Cells C3 and C5 return a value of 3 because this is the index number of the red font color.</li> <li>Select cell A1.</li> <li>On the Data menu, point to Filter, and then click AutoFilter.

The AutoFilter arrows appear at the top of each field.</li> <li>Click the arrow on the Red? field, and then click 3 in the list.</li></ol>

Your list should be filtered so that only the records in which the name has been formatted in red are displayed (row 3 and row 5).

NOTE: If you change the cell formatting of cell A2 to red and remove the red formatting from cell A3, you must recalculate the worksheet by pressing F9.

Note that in both of the examples, because of the way that the reference in the defined name is structured, you must put the defined name that uses this formula in column C and the field in which you are looking for a particular formatting in column A.

back to the top

<div class="references_section">