When using WPS Spreadsheet to process data, we can customize cell format with codes. In this video, you will learn about several practical codes for custom cell format.

Take the worksheet here as an example. Select Column E, right-click on it and choose Format Cells in the menu. You can also use the shortcut key Ctrl+1 to pop up the dialog. Get into the tab of Custom. Then we can see m/d/yyyy in the input box. Here m, d, and yyyyare respectively placeholders for month, day, and year.

Here are the explanations about the placeholder digits. If we input double m, August will present as 08 in the cell. And if we enter triple m, August will present as its abbreviation Aug. The table on the screen presents a list of codes, which can be used to set cell formats for dates.

As we can see, there are several number signs (#) and zeros (0) in the selection box. They stand for placeholders of umbers. Number sign (#) as a placeholder, will only indicates a significant zero in a decimal. Select cell G5, open the Format Cells window, and enter ####.##. Then the number in the cell will present as 2021.9.As you can see, it does not show significant zeros. When we change the code as 0000.00. The number in the cell will present as 2021.90.In this case, what do zeros (0) in the codes means? They are actually placeholders, and insignificant zeros would add to the right of the decimal place. Therefore, a zero (0) is often used to present a decimal, while the number sign (#) is for a positive number.

The codes introduced just now seems to be complex. However, if we get their formation rules, they can help with custom cell format settings. Here is the structure of custom codes, which is divided into four parts with semicolons. Each part defines the cell format respectively when we enter a positive number, a negative number, a zero (0), and text content.

Here is an example for your better understanding of it.

Select a cell range, and use shortcut keys to pop up the Format Cells window.Then, enter the codes into the input box. As is shown previously, the codes are divided into several parts with semicolons.

• The first part defines the cell format when we enter a positive number.

• The second part means that the negative number entered into the cellwill turn red.

• The third part sets the cell format when a zero (0) is entered. This example will present a minus symbol (-) when inputting a zero (0).

• The fourth part indicates the content to be shown when we input text content rather than a number. This example shows Only fill in numbers.

Click OK to complete the settings. Then, when we enter a negative number, it would turn red. When we input a zero (0), it would present as a minus symbol (-). And when we input text content rather than a number, we would see the note Only fill in numbers.

Did you get it?

To be office excel advanced, you could learn how to use WPS Office Spreadsheet online in WPS Academy.