Using Find and Replace in a Spreadsheet

by Allen Wyatt
(last updated March 6, 2014)

1

Spreadsheets can hold quite a bit of information. In fact, over time any given spreadsheet may become a catchall for all sorts of data. Over time, finding the information you know you placed in the spreadsheet can seem like trying to find a needle in a haystack.

That's where the Find and Replace tool comes in handy. To use the tool, choose Find and Replace from the Edit menu. Sheets displays the Find and Replace dialog box. (See Figure 1.)

Figure 1. The Find and Replace dialog box.

The two primary controls in the dialog are the Find and Replace With boxes. They are pretty self-explanatory, but all you need to do is enter what you want to search for in the Find box and what you want to replace it with in the Replace With box. (If you don't really want to do replacing at this point or if you want to delete what you are finding, leave the Replace With box empty.)

If uppercase and lowercase letters matter in what you entered, select the Match Case check box. The Match Entire Cell Contents check box is important as it indicates whether you want to search only for entire cells or search within cells. There is also a check box to indicate whether Sheets should look in only the current sheet or in all sheets. (Understand—the Search All Sheets check box controls whether searching is done on all sheets in the open spreadsheet, not whether other spreadsheets in Drive are searched.)

When you are ready to begin looking, click the Find button. As Find and Replace locates matches, it displays them. You can then choose to replace them or skip that instance and move to the next. The Replace All button allows you to replace all instances of what you are looking for in a single operation.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Continuing Macro Lines

Program a macro, and you can easily find that some lines get very long. If you want to shorten the lines so they are more ...

Discover More

Inserting the Current Month

Need to add the name of the current month to your document? Word includes a field that can make the addition easy, and it ...

Discover More

Keyboard Control of the Find and Replace Dialog Box

Hate to take your hands off the keyboard? This tip explains how you can use the keyboard to work with the Find and ...

Discover More
More DriveTips

Determining the Name of the Day of the Week

What day of the week is that date? Sheets can let you know by using the TEXT function to return a weekday name for a ...

Discover More

Adding a Drawing to Your Spreadsheet

Numbers alone do not define spreadsheets. Google Drive allows you to also add drawings to a spreadsheet, which can be ...

Discover More

Filtering Data

When you get a lot of data in a spreadsheet, it can be hard to zero in on the data you really need to work with. That's ...

Discover More
Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 7?

2014-03-06 05:31:24

JD Murphy

Find and Replace deletes formatting - italics - bold etc. CAREFUL!!!
Any way of avoiding this?
JD