XL: How to AutoFilter Records Based on Cell Formatting

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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:

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

2. Format cells A2 and A4 as Bold.

3. On the Insert menu, point to Name, and then click Define.

4. In the Define Name dialog box, enter "bold_cell," (without the quotation marks) in the Names In Workbook box.

5. In the Refers To box, enter the following formula:

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

6. Click OK.

7. Enter the following in the same worksheet:

   C1: Bold? C2: =bold_cell

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

9. Select cell A1. On the Data menu, point to Filter, and then click Auto_Filter.

   This step puts the Auto_Filter dropdown arrows at the top of each field.

10. Click the dropdown arrow on the "Bold?" field and click True in the list.

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:

1. 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     B5: 4
   
   

2. Format cells A3 and A5 in the red font.

3. On the Insert menu, point to Name, and then click Define.

4. In the Define Name dialog box, enter "red_cell" (without the quotation marks), in the Names In Workbook box.

5. In the Refers To box, enter the following formula:

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

6. Click OK.

7. Enter the following in the same worksheet:

   C1: Red? C2: =red_cell

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

9. Select cell A1, on the Data menu point to Filter, and then click Auto_Filter.

   This step puts the Auto_Filter dropdown arrows at the top of each field.

10. Click the dropdown arrow on the "Red?" field and click 3 in the list.

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.

REFERENCES

"Microsoft Excel User's Guide," version 5.0, Chapter 10, "Creating Formulas and Links"

For more information about creating defined names in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

   how do i create a defined name

   defining, names