How to quickly locate and replace data

Uploaded time: August 26, 2021 Difficulty Beginner

A free Office suite fully compatible with Microsoft Office

Free Download

How to quickly locate and replace data

How to quickly locate and replace data

When processing data in a Spreadsheet, we somtimesquickly need tolocate the data among various data, or modify incorrect data in bulk. It would be convenient to use the Find and replacefucntion in WPS Spreadsheet. This video could be used in Microsoft Office Excel and Openoffice.

Take the following table as an example, which records students’ grades on various subjects.Supposed that we want to find the Egradefrom thesheet. 

1. Select all those ranges of cells that we want to find.

2. Click the Home and Find buttons in turn. (Tips: We can also use the shortcut key Ctrl+Fto open it)

3. Enter the contents that we want to find in the dialog box.(e.g. the grade E)

4. Click the find all button, and then we can see all the rows and cells that contain the E grade.


Later, we need to change all A grades as the B grades, because we found that we have made some mistakes about the grades A because of the teachersmisjudge. Since there is a considerable number of data, it would be tedious to correctall mistakes by hand.

The replace function of the WPS Spreadsheet can easily solve this problem.

1. Select all those ranges of cells that we want to find.

2. Click the Home button-and then click the replace buttonin turn.(Tips: We can use the shortcut key Ctrl+H to pop upthe dialog box in this step)

3. Enter the A, which needs to be replaced, into the Find whatinput box.

4. Enter theB, which will replace the cells filled with A, into the Replace withinpux box.

5. Click the Replace All button, and we can replace them all with one click.

It is also easy to mark the content that has been replaced.

1. Click the Option button in the replace window.

2. Choose the Format option.

3. Click Format-Patterns button in the dropdown box, and then on the right side click Replace with in turn.

4. After choosing a color, click the Replace All button.