Finding and replacing in spreadsheets (How to)

Last week I talked about the some of the additional options for finding and replacing in spreadsheets, and this week I’m going to talk about how to actually get to those options.

A quick vocabulary note before I begin:
Formulas: The actual contents of a cell. This includes functions, constants, references, mathematical operations, etc. It’s displayed in the formula bar, and also in the cell itself if you click in the formula bar.
Constants: A special type of formula where no operation is performed. 10 is a constant, as is =10, and ’10. However, =11-1 is not a constant.
Values: The results of the formula. It is displayed in the cell unless you have clicked in the formula bar while selecting that cell. For example, if a cell had the formula =11-1, it would have the value 10.

In Excel 97:

  1. Click on the header for the Edit menu or type alt-e.
  2. In the menu that opens, click on “Find…” or “Replace…”, or type “f” or “e” respectively.
  3. In the dialog box that pops up, enter what you want to find in the first box (or only box, if there’s only one), and, if replacing, enter what you want to replace it with in the second box.
    • Target: If you’re finding and not replacing, then in the second of the 2 drop-down menus below the search box, set “Look in:” to Formulas, Values (the results of formulas), or Comments. The default is Formulas. If you’re replacing won’t let you choose. It will only search and replace in formulas.
    • Scope: Excel 97 does not have a shortcut for searching all of the worksheets. Instead you will have to select the worksheets you want to search. Otherwise, it will only search the one you’re in. If you have more than one cell selected, then Excel will just search those cells.
    • Direction: In the first (or only) drop-down menu below the search box(es), set “Search:” to “By Rows” or “By Columns”. “By Rows” is the default.
    • Precision: Mark the checkbox for “Find entire cells only”. It’s to the immediate right of the dropdown menu(s).
  4. Click the “Find Next” button to find the first match after your location, the “Replace” button to replace the next match, or the “Replace All” button to replace all of them.

In Excel 2007:

  1. Go to the Home tab.
  2. Find the Editing group. In my version it’s on the right.
  3. In the Editing group, click on “Find & Select”. (There’s a picture of binoculars above it.)
  4. In the menu that appears, click on “Find” or “Replace”, or type f or r.
  5. In the pop-up window, enter what you want to find in the first box (or only box, if there’s only one), and, if replacing, enter what you want to replace it with in the second box.
  6. If desired, to get the complete list of options press the “Options >>” button.
    • Target: In the third of the 3 options, set “Look in:” to Formulas, Values, or Comments. The default is Formulas. If you’re replacing won’t let you choose. It will only search and replace in formulas.
    • Scope: In the first of the 3 options, set “Within:” to Sheet or Workbook. Sheet is the default, and will just search the worksheet you’re in. Workbook will search all of the worksheets in the spreadsheet. If you have more than one cell selected, then Excel will just search those cells.
    • Direction: In the second of the 3 options, set “Search:” to “By Rows” or “By Columns”. “By Rows” is the default.
    • Precision: Mark the checkbox for “Match entire cell contents”. It’s right next to the 3 dropdown menus.
    • Format: In Excel 2007 you can also search by format. Click on the “Format” button next to the box with the text you’re finding/replacing. You can choose from any of the cell formatting options. You can specify different formats for the search and the replacement – in fact, if you replace with the same text, but different formatting options, you’ll just be changing the formatting of cells with the text you’re searching for.
  7. Click the “Find Next” button to find the first match after your location, the “Find All” the “Replace” button to replace the next match, or the “Replace All” button to replace all of them.

In Google Spreadsheet:

  1. In the menu bar click on “Edit” -OR- type ctrl-H and skip step 2.
  2. In the menu that pops up, click on “Find and replace…” or type f.
  3. In the pop-up window, enter what you want to find in the first box (or only box, if there’s only one), and, if replacing, enter what you want to replace it with in the second box.
  4. All of the available options are visible already.
    • Target: Google Spreadsheet can only search in/for values, not formulas or comments. A search for 10 will find cells with =10 and =11-1, but not =10-1. It can only replace in constants, so replacing 10 with 9 in that set of cells would result in =9, =11-1, and =10-1.
    • Scope: Mark the checkbox next to “Search all sheets”. There does not appear to be a way to limit it to searching selected cells.
    • Direction: Google Spreadsheet can not specify the direction of the search.
    • Precision: Mark the checkbox for “Match entire cell contents”.
  5. Click the “Find” button to find the first match after your location, or the “Replace all” button to replace all of them. You can also click the “Replace” button to replace in the cell you’re currently on, but if the cell you have selected doesn’t contain what you’re searching for, that button will be greyed out.

In OpenOffice Calc:

  1. Click on the header for the Edit menu or type alt-e, or type ctrl-f and skip step 2.
  2. In the menu that opens, click on “Find & Replace…” and type “f”.
  3. In the pop-up window, enter what you want to find in the first box (or only box, if there’s only one), and, if replacing, enter what you want to replace it with in the second box.
  4. If desired, to get the complete list of options press the “More Options” button.
    • Target: Set the drop-down menu to Formulas, Values, or Notes . The default is Formulas. It can also replace in all three, but if you choose “Values” then it will only replace constants.
    • Scope: To search the entire spreadsheet, mark the checkbox next to “Search in all sheets”. Otherwise it will only search the worksheet you’re in. Open Office will let you search just your selected cells if you mark the checkbox for “Current selection only”.
    • Direction: Near the bottom of the dialog box, choose either “Rows” or “Columns” for “Search direction”. You can also mark the checkbox for “Backwards” if you wish to start the search by going left/up rather than right/down of your current location.
    • Precision: Mark the checkbox for “Entire cells”. It’s right above the “More Options” button, and visible even when the rest aren’t.
    • Styles: In Open Office you can’t search by format, but you can search by style. This might seem really limiting, and it is, but it’s more flexible than it seems at first glance. You can actually locate or create one cell with the format(s) you want, and define a style based on that, and thereafter you’ll be able to search for the style you just defined. And if you want to just change how everything in that style is formatted you can re-define it. I can see where this could be a huge advantage if I decided that, for example, I wanted all percentages to have a decimal place. Without this I’d have to go through and select only the cells with percentages, and then increase their decimal places. With this I just go to one, change the format, redefine the style, and just like that – everything’s re-formatted. Back on the topic of search & replace, however, it’s important to note that when you search for and replace styles then you’re only searching for and replacing styles. You can’t specify anything about the contents of the cells.
  5. Click the “Find” button to find the first match after your location, “Find All” button to highlight but not select all of the matches, the “Replace” button to replace the next match, or the “Replace All” button to replace all of them.