Finding and replacing in spreadsheets

Most people are probably familiar with how to find and replace text in the average word processor. The basic principles are the same in spreadsheets, but there’s four main additional things to consider: target, scope, direction, and precision.

Search Target: Do you want to search in the formulas, or in their results?
Suppose you have the formula =5+10. The result would obviously be 15. So if you search in the formula for 15, you’ll never find it — you’d have to search for results. Alternatively, if you wanted to change all the cells that refer to A1 so they refer to C1 instead you’d never be able to do it by searching the results – you’d have to search the formulas. In a week or two I’m going to give an example of a project that relies on searching and replacing in formulas.

Search Scope: Do you want to search your selection, or the entire worksheet, or all the worksheets in the workbook?
Often you only care about the values in a certain group of cells. Then you can limit it to that area the same way you’d limit it in a word processor: by selecting it, and telling the program to search just the selected cells. At the other extreme, you might want to check all the worksheets for something — maybe it’s “What sheet had the tax rates on it?” or maybe it’s “Is any formula actually using this named variable?” In between the two there’s the option to just search the entire worksheet that you’re in. This is often the default.

Search Direction: Do you want to search by rows, or by columns?
This is rather like choosing the print order for your document. If you choose to search by rows, you’ll get a match in Z1 before you find the match in A2. Alternatively, if you search by columns you’ll find the match in A1000 before the one in B1.
If you’re working with normal or small amounts of data, this doesn’t really matter. But more data means slower searches, and so if you have a lot of data, or are expecting a lot of hits, you might take a moment to consider which way you want it to go.

Search Precision: Do you want to find cells that exactly match, or just ones that contain what you’re looking for?
In most word processors you can select “match case” when doing a search, which means that whether the letters are upper or lower case matters — “This” won’t match with “this” or “THIS”. Therefore, a “match case” search is more precisely focused than one without that limiter. You can do that in spreadsheets, too, but you can also limit it to results where the contents of the cell exactly match your search, rather than just containing it. If you had cells with 1, 5, 10, and 15, then a normal search for 1 would turn of 1, 10, and 15. However, a search limited to entire cells would only turn up the one with 1.

Next week I’m going to talk about how to find these options in various programs, followed by an example and then a discussion of wildcards in searches.