Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010
Let’s say you need to select all cells matching a criteria like a specific word, character or value and you want to look at each cell individually… and then possibly edit them. Typically, these cells are scattered all around the worksheet. What’s the best way to locate all of these cells?
Let’s look at an example. Say you want to locate all cells in your spreadsheet that contain the word Rental. Press CTRL+F and type Rental in the ‘Find what’ field. Click the Find All button and all cells containing the word ‘Rental‘ will be listed at the bottom of the ‘Find and Replace‘ dialog.
Now, here’s the best part! Now with the ‘Find and Replace‘ dialog still open, pressing CTRL+A will select all of the items listed at the bottom of the dialog, and it will also select these cells on the worksheet.
At this point, you could close the Find and Replace dialog and all of the ‘found’ cells will still be selected on the worksheet. However, the problem is that once you click any cell in the sheet, all of the ‘found’ cells will be unselected.
Instead, move the ‘Find and Replace’ dialog out of your way by clicking on and dragging its header. You can now continue editing your worksheet while the dialog is still open.
When you need to jump to another of the ‘found’ cells, click its reference in the dialog and it will be selected on the worksheet.
Side Note: you can resize the Find and Replace dialog by dragging any of its edges.
In some situations, I close the dialog and, with all of the ‘found cells’ selected on the worksheet, I immediately use a Fill color to easily identify the cells for later editing.
Locate and Select All Cells Matching a Criteria Like a Link to Other Workbooks
Another useful application of this ‘Find All‘ trick is to select all cells matching a criteria in a worksheet that have formulas linked to other workbooks.
Formulas that link to other workbooks include the referenced file name surrounded by square brackets . Doing a ‘Find All‘ for the [ character, will locate all cells with a link to other workbooks and display them in the bottom section of the ‘Find and Replace‘ dialog.
Again, you can either leave the ‘Find and Replace‘ dialog open or you can close it and temporarily apply a Fill color to all the cells.
If this article was of value to you, please comment below and share.
By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.