Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Close

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.

Excel Tips - How to Select All Cells Matching A Criteria in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: info@intendhomes.com

Article: Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.

About The Author

Mike Marko

We at IM Marketing Consultant Services are your total internet marketing solutions company. We can help you look at the "big picture" and optimize your internet marketing. I have six sigma lean training; and when you combine that with internet marketing, helps get results that affect your business bottom line. I practice Law of Attraction, and have been investing in real estate since 2008. We have learned to make online marketing the ultimate path to prosperity in business and love teaching it to others.