Use Excel’s Find feature to find and select cells

Using the Go To feature in Microsoft Office Excel, you can quickly select cells with common attributes. There are a number of Go To tricks, such as filling blank cells, but this feature won’t always fill the bill. For instance, you might want to select all the cells that contain a specific format so that you can change it. Go To can’t handle that. Conditional formatting will highlight those cells, but it won’t select them. When you need to find and select, try Excel’s Find feature.

Now, let’s suppose you want to change the color of the blue cells in the sheet below. You could select each range individually or use Find to select them all, as follows:

1. Click the Home tab.
2. Choose Find from the Find & Select dropdown. Or, press [Ctrl]+F. In Excel 2003, Find is on the Edit menu.
3. In the resulting dialog, click Options.
4. Click the Format dropdown and select Choose Format From Cell.
5. Click any cell with the format in question. In this case, click B5.
6. Click Find All and Excel will list all the cells, but you’re not done.
7. Press [Ctrl]+A to select all of referenced cells in the list.
8. Then, click Close to select those cells.

With the cells selected, you simply apply the new format. In this case, to change the fill color of all the selected cells, you’d select a new color from the Fill Color dropdown. Take a close look – the cells lost some formatting that you probably meant to keep (some borders). I did that on purpose because this feature is very literal, so be careful how you apply it. Don’t forget that you can press [Ctrl]+Z to undo the change.

You can also use this feature to find functions. The example sheet has several SUM() functions. To quickly find and select those cells, you’d use the same feature. Instead of specifying a format, you’d enter SUM() in the Find What control, press [Ctrl]+A to select the entire list and then click Close. (Notice that I cleared the format.)

This feature will search formulas, values, and comments. Formulas is the default, so we didn’t have to change the Look In option in the previous task. You might use this feature to find all the comments made by a specific person or all the values that contain a specific character. Just remember to select all the found items before closing.