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

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.

Filter Based on a Bold Font

1. 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
   					

2. Select cells A2 and A4.
3. On the Format menu, click Cells.
4. On the Font tab, click Bold under Font style, and then click OK.
5. On the Insert menu, point to Name, and then click Define.
6. In the Define Name dialog box, type bold_cell in the Names in workbook box.
7. In the Refers to box, type the following formula:
   =GET.CELL(20,OFFSET(INDIRECT("A2"),ROW()-2,0))
8. Click OK.
9. Type the following data in the same worksheet:
   C1: Bold?
   C2: =bold_cell
10. 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
11. Select cell A1.
12. On the Data menu, point to Filter, and then click AutoFilter.
    
    The AutoFilter arrows appear at the top of each field.
13. Click the arrow on the Bold? field, and then click TRUE in the list that appears.

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.

Filter Based on a Red Font

1. 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
   					

2. Select cells A3 and A5.
3. On the Format menu, click Cells.
4. On the Font tab, click Red under Color, and then click OK.
5. On the Insert menu, point to Name, and then click Define.
6. In the Define Name dialog box, type red_cell in the Names in workbook box.
7. In the Refers to box, type the following formula:
   =GET.CELL(24,OFFSET(INDIRECT("A2"),ROW()-2,0))
8. Click OK.
9. Type the following data in the same worksheet:
   C1: Red?
   C2: =red_cell
10. 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.
11. Select cell A1.
12. On the Data menu, point to Filter, and then click AutoFilter.
    
    The AutoFilter arrows appear at the top of each field.
13. Click the arrow on the Red? field, and then click 3 in the list.

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.

For more information about how to create defined names, click Microsoft Excel Help on the Help menu, type create a name to represent a formula or a constant value in the Office Assistant or the Answer Wizard, and then click Search to view the topic.