WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android


How to quickly locate and replace data

Uploaded time: August 26, 2021 Difficulty Beginner

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. 

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.