Microsoft KB Archive/151449

= How to AutoFilter Records Based on Cell Formatting =

Article ID: 151449

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q151449





SUMMARY
This article describes how to use the AutoFilter feature of Microsoft Excel to filter records in a database where a particular field has been formatted to a bold font.



MORE INFORMATION
This article contains two examples that describe using the AutoFilter command to filter records based on cell formatting in certain fields.

Filtering Based on a Bold Font
To set up the example, follow these steps:

  In a new worksheet, enter the following data:

      A1: Name      B1: Amount A2: Bob      B2: 1 A3: Sue      B3: 2 A4: Pat      B4: 3 A5: Tom      B4: 4  Format cells A2 and A4 as bold. On the Insert menu, point to Name, and then click Define. In the Define Name dialog box, enter "bold_cell," (without the quotation marks) in the Names In Workbook box. In the Refers To box, enter the following formula:

=GET.CELL(20,OFFSET(INDIRECT("A2"),ROW-2,0)) Click OK.</li> Enter the following in the same worksheet:

C1: Bold?

C2: =bold_cell</li> 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" (without the quotation marks).

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. On the Data menu, point to Filter, and then click Auto_Filter.

This step puts the Auto_Filter drop-down arrows at the top of each field.</li> Click the drop-down arrow on the "Bold?" field and click True in the list.</li></ol>

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

Note that if you change the cell formatting of cell A3 to bold and remove the bold formatting from cell A4, you must recalculate the worksheet in order for the values in column C to be updated.

Filtering Based on a Red Font
To set up the example, follow these steps:

<ol>  In a new worksheet, enter the following data:

<pre class="fixed_text">      A1: Name    B1: Amount A2: Bob    B2: 1 A3: Sue    B3: 2 A4: Pat    B4: 3 A5: Tom    B5: 4 </li> Format cells A3 and A5 in the red font.</li> On the Insert menu, point to Name, and then click Define.</li> In the Define Name dialog box, enter "red_cell" (without the quotation marks), in the Names In Workbook box.</li> In the Refers To box, enter the following formula:

=GET.CELL(24,OFFSET(INDIRECT("A2"),ROW-2,0))</li> Click OK.</li> Enter the following in the same worksheet:

C1: Red?

C2: =red_cell</li> 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" (without the quotation marks).

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

C1: Bold?

C2: 0

C3: 3

C4: 0

C5: 3

Note that cells C3 and C5 return a value of 3 because this is the index number of the red font color.</li> Select cell A1, on the Data menu point to Filter, and then click Auto_Filter.

This step puts the Auto_Filter drop-down arrows at the top of each field.</li> Click the drop-down arrow on the "Red?" field and click 3 in the list.</li></ol>

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

Note that 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 (in Microsoft Excel for the Macintosh, press COMMAND+=) in order for the values in column C to be updated.

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.

<div class="references_section">