The power of Find All uncovered (Part II)

If you are amazed by what I have revealed yesterday, read on. Here is another application for the Find All function.

Let's use the same list given yesterday. Instead of deleting the row, I will show you how to replace those cells with the statement "I want to replace this cell with this statement" in less than 10 seconds.

For those who did not have the list, it is given below. Please put them into a new worksheet.

internet marketing software
microsoft internet marketing
internet marketing filter
internet marketing program
free internet marketing
free internet marketing software


1. Highlight the list in the Excel Worksheet.
2. Go to Menu and select Edit, Find or use "Ctrl F" (shortcut key)
3. Type in the word "software"
4. Click on the "Find ALL" button
5. Select the list by
a) Go to the first item in the list.
b) Hold on the shift key and click on the last item in the list.
c) You have highlighted the entire list (See diagram)
6. Close the find and replace dialog box.
From here, type the statement "I want to replace this cell with this statement" into the Formula Bar. For those who do not know where is the formula bar, it is located just below the icons and to the right of the symbol fx.

The power of Find All uncovered

In the find and replace box, there is a button that is seldom used by most users. That is the Find All function. It is a very powerful function if you know how to make use of it. And here is one of its uses:

Assuming that you have a list of words as shown below:

internet marketing software
microsoft interne marketing
interet marketing filter
internt marketing program
free intrnet marketing
free interet marketing software


You wanted to delete the rows if the word "software" is found within the phrases. One of the simplest and tedious way is to go thru the list one by one and when the word is found, delete the row.
Here is a faster way.
1. Highlight the list in the Excel Worksheet.
2. Go to Menu and select Edit, Find or use "Ctrl F" (shortcut key)
3. Type in the word "software"
4. Click on the "Find ALL" button
5. Select the list by
a) Go to the first item in the list.
b) Hold on the shift key and click on the last item in the list.
c) You have highlighted the entire list (See diagram)
6. Close the find and replace dialog box.
7. Delete the entire rows using "Ctrl -" (shortcut key)
8. Now you have a list without the word "software"

Maximising the use of Find and Replace

The find and replace feature is used to replace word(s) or phrase(s). And most people only know how to use this feature to replace full word(s) or phrase(s). It may not occur to them that the usability of this feature could be enhanced by understanding a little bit more about the words or phrases or abbreviations you want to replace. Let me share with you what I mean.

Assuming if you have a long list of company names (a short list of 4 companies is shown below for illustration purpose).

ABC PL Co
EFR Placement Co
THG Gold-Plating Co
JHG PL Co

Assuming that you want to spell the word PL in full (i.e. Private Limited). If you are to find ("PL") and replace it with ("Private Limited"), you will replace the first & last company name correctly. At the same time, you will change the word "placement" to "Private Limitedacement" and the word "Gold-Plating" to "Gold-Private Limitedating", something you do not wish to do.

In most cases, people would have stopped here thinking that you have to do this manually. If you are one of them, read on.

PL is an isolate "word" in the listing while Placement and Gold-Plating are part of a word. So to effectively replace PL without touching Placement and Gold-Plating, you should find " PL " (note the spaces before and after) and replace it with " Private Limited " (note the spaces before and after). With this slight change, you would not have to waste time replace the word manually and save the time for other task.

You can also use this tip with any program that has a find and replace function.

Drop me a note if you think this is useful to you.

How to use the offset Function (Part IV)

From part III, you have learnt how to draw a dynamic Excel Chart. In this part, you will learn how to create a dynamic validation list using the same formula.

For those who don't know about data validation, it's purpose is to limit the type of data users could enter into a cell. If a data validation is a list, it will only allow users to select from the list.

Copy the formula "=OFFSET(A1,0,0,COUNTA($A$1:$A$100),1)" into the list as shown below. Click "OK" and you are done. Now, when you add or delete the listing in column A, the data validation list will get updated automatically.

How to use the Offset Function (Part III)

In this part, we will be sharing with you how to use the offset function to create dynamic ranges.

Before you could do that, you need to know 2 more functions from Excel. One is the COUNTA function and the other is NAMED RANGES.

COUNTA function counts the number of non blank cells in a pre-defined range. For example, if within a selected range from A1 to A100, the first 20 rows contains data, then counta will return 20 indicating that there are 20 rows that are non-blank cells. This COUNTA function will replace the height of the offset as shown =OFFSET(A1,0,0,COUNTA($A$1:$A$100),1). It makes the range dynamic and varies according to length of the list. In this case, we will be able to find out how many datasets are there in a chart data series.

After defining the formula that will vary according to the length of the list, you need to give it a name. Do this from the menu bar insert, name, define to create the range name. See the diagram below:



In the dialog box that follows, inout the name of the range (in this case called "chart_range") and input the formula "=OFFSET(A1,0,0,COUNTA($A$1:$A$100),1)" in the refers to box.



Then create the chart defining the range either as a x-axis or y-axis.

Once that is done, you have a dynamic chart that changes as more datasets are added or removed.