Microsoft

Use Excel's Find feature to find and select cells

When you need to select several cells with a similar attribute, you can use Excel's Find feature to make the process eaiser.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
michael_boardman
michael_boardman

Depending on what you need to do, it might be worth checking out the ASAP add-in. It does a lot more than find and select, and seems very reliable. Even as a home user on the free version, it's handy to have available. For many business users, it would be invaluable (and no, I have no connection to the author!)

Editor's Picks