Find All function

Using the Find All Function seems pretty straight forward. What need to be done is to enter the word or phrase you wish to find and click on the Find All Button. The lower portion of the find and replace dialog box will show you a list of the cells that contains the word or phrase you are finding.

Is that all? I thought so initially and dismissed the Find All function as useless (for a few years) until…….. I made this GREAT DISCOVERY.

You can actually select all the cells found in the results. And with that you are able to do wonders. Here is how the Find All function turns useful for me.

Assuming you have a list as shown.

You need to delete all the company data given in the list, such that row 8, 12 and 16 look like row 4. How could you do that quickly even when there are tens or hundreds of rows to delete?

1. Highlight the range B4 to F4 and activate the copy function.

2. Highlight the entire column B and activate the find function.

3. When the dialog box appears, enter the word “Company” in the Find what box.

4. Instead of clicking on the find button, click on the Find All button.

5. A list of results showing Row 4, 8, 12 and 16 matches the find what criteria.

6. Select all the results by first click on the first result, press & hold the shift key and then use the mouse button to click on the last result. The list will be highlighted as shown below:

7. Close the find and replace dialog box.

8. Activate the paste function to paste the range you have copied in step 1. All the rows are cleared of data.

Using this same approach, you could clear unwanted cells or do mass changes to your worksheet in a few minutes. And you could perform actions you could never do with the Replace All function. Drop us a mail at the contact us page if you find this discovery useful. That’s all for the excel Find All function.