How to quickly locate and replace data

Uploaded time: August 26, 2021 Difficulty: Primary

How to quickly locate and replace data

How to quickly locate and replace data

Tips: you can turn on the volume by clicking

Graphic skills

When processing data in a Spreadsheet, we somtimes quickly need to locate the data among various data, or modify incorrect data in bulk. It would be convenient to use the Find and replace fucntion 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 E grade from the sheet. 

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+F to 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 teachers misjudge. Since there is a considerable number of data, it would be tedious to correct all 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 button in turn. (Tips: We can use the shortcut key Ctrl+H to pop up the dialog box in this step)

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

4. Enter the B, which will replace the cells filled with A, into the Replace with inpux 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.