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 formatting) in WPS Office excel

January 21, 2022

Welcome to WPS Official Academy to check the contents of How to clean data in WPS Office excel. Today we will learn how to clear all formatting in data with easy steps.

· What is WPS Office?

WPS Office is the best alternative to Microsoft 365.

You can use it to create, edit and share Word, PPT, Excel and PDF. Versions are available for PCs including Mac, Windows, Linux, etc. Mobile versions include iOS and Android.

If you often get distracted by messy formatting in a table, dont worry. The good news is, removing them is very easy yet effective, and this practice will not affect the contents of your cell.

· Example to clean formatting in data:

Too much formatting in this table makes the data hard to read. We can fix it with powerful functions in WPS Spreadsheet.

Option1: Use the Format button

1. Select the area that contains data.

2. Go to the Home tab > Format > Clear > Formats


Option2: Use the shortcut menu

1. Select the area that contains data.

2. Right-click the area to activate the almighty shortcut menu, then click Clear Contents > Formats.

Option3: Use Format Painter

1. Select the cell with the format that you want to apply to other areas.

2. Head to the Home tab, click Format Painter,and then select the data range.

Also, you may notice the small green triangles in the upper right of cells A5 through A7, which imply empty strings in the contents. To solve this, we can right-click them > Clear Contents > Specials > Space.


The above methods can help you remove formatting from your selected cells. Here, we have mastered three easy ways to clear the format in a table. Did you get it?

· To explore more about how to clean data:

Click here to see the differences between each format in WPS Academy.

Click here to explore the general and the special format in WPS Academy.

Click here to learn how to set format cell values. Specifically, click here to learn how to set the date format cells in WPS Academy.

In this free WPS Office tutorial, we have learned how to clear formattingin Excel. Above are the easy and professional steps to remove formattingin WPS Spreadsheet, similar to Microsoft Office and are available for Windows, PC, Mac, Linux, Android, and iOS devices.

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