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 clean data(delete errors) in WPS Office excel

January 21, 2022

Are you ready to master how to clean data in excel? After learning this WPS free course of how to clean data in excel, you will be advanced from a beginner in WPS Spreadsheet. Today we will learn 7 common errors in excel and how to correct them with easy steps.

· What is error in WPS Spreasheet?

Here comes 7 common errors in excel. Weve put them together, from misplaced dates to incorrect calculations, for you to solve annoying mistakes.

1. Hashtags error (#####)

Both narrow width of column and invalid dates calculation can trigger the Hashtags error. We have learned this in previous tutorials.

Click here to see how to delete Hashtags.

2. Division Error  (#DIV?0!)

When you divide a number by zero, the #DIV/0! error will come to you.

3. Name Error (#NAME?)

As its name suggests, when typing the wrong range name within a formula, you will come across the #NAME? error.

In this case, the wrong formula is =SUM(A10:B)

4. Reference Error (#REF!)

When entering an invalid cell reference, you will receive the #REF! error.

In this case, the reference cell A18 has been deleted, and the system thus returns the #REF! error.

5. Null Error (#NULL!)

Sometimes, you may forget to add commas between intersections, eg,SUM(A5:16 A10:A12) or calculation symbol, like what we did in the following example.

6. Value Error (#VALUE!)

WPS Spreadsheet displays the #VALUE! error when it cannot understand the argument of the formula you write.  

In this case, cell A24 contains text values that are unsuitable for numeric calculation.  

7. #NUM! Error (#NUM!)

When WPS cannot perform a calculation or there are invalid numeric values, the #NUM! error occurs.

In this case, 2^10000 is too large to be shown, so the #NUM! error can be found.

Fortunately, after learning todays tutorials, you can know why errors appearin your data and how toget rid of these errors.

· How to remove errorsdata in WPS Spreadsheet?

1. Let's highlight them first. Select your data, then head to Home > Conditional Formatting > New Rule.

2. In the pop-up dialog, you can select Format only cells that contain at Select a Rule Type, and select Errors at Format only cells with. Then, click Format to decide how you want the errors to display. Finally, click OK.

delete error.gif

3. To get rid of them, select your data area, then press Ctrl+G to open the Go To dialogue box. Then check Errors at data. This time, all mistakes in this area will be selected.

4. Right-click any error, and then click Clear Contents to remove them. Now we haveremoved these mistakes.

To explore more about how to clean data:

Excel is the most powerful tool to manage and analyze various types of Data. This free WPS Spreadsheet tutorial for beginners covers in-depth lessons for Excel learning and how to use various Excel formulas, tables and charts for managing small to large scale business process.

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