XL: How to AutoFilter Records Based on Cell Formatting

ID: Q151449

The information in this article applies to:

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:

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.

Enter the following in the same worksheet:

C1: Bold?
C2: =bold_cell

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

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.

Click the drop-down arrow on the "Bold?" field and click True in the list.

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:

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

Format cells A3 and A5 in the red font.

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

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

In the Refers To box, enter the following formula:

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

Click OK.

Enter the following in the same worksheet:

C1: Red?
C2: =red_cell

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.

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.

Click the drop-down arrow on the "Red?" field and click 3 in the list.

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.

REFERENCES

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

For more information about creating defined names in Microsoft Excel version 5.0, click the Search button in Help and type:


   defining, names

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

Additional query words: 5.00a 5.00c 8.00 97 98 XL98 XL97 XL7 XL5

Keywords : kbualink97 xlformat xllist
Version : WINDOWS:5.0;5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type :