Is excel data messy? I’ll teach you a few easy ways



[pconline skills] almost all the administrative partners meet this situation. They send an empty excel form to their colleagues, but they fill it in in a mess. In addition to the heavy statistical analysis in the later stage, it also wastes a lot of time to correct mistakes. How can we solve this problem?

1. Data validity

“Data validity” can avoid many problems of front-end input. Take “age” as an example, first select the cell to be processed, click “data” → “data tool” → “data verification”, change the “allowed” value to “integer”, and then set the “minimum value” and “maximum value” to “0” and “100” respectively (that is, the age will never exceed these two numbers). After clicking OK, this field will only receive numbers between 0 and 100, and other values cannot be entered. Similarly, date, time, fixed length text (such as mobile phone number entry, to avoid users missing individual numbers) can also be standardized by using this function.

2. Make good use of “sequence”

If the content to be entered has a high repetition rate and is relatively uniform (such as department, job, etc.), then the “sequence” function can be considered. Select the cell to be processed first, and click “data” → “data tool” → “data verification”. Change the “allowed” value to “sequence”, and fill in the default options in the “source” area below (separated by commas). After this processing, in addition to avoiding all kinds of errors caused by nonstandard input, it can also automatically generate a drop-down menu when input, improving the user’s input experience.

3. Preset field format

When inputting dates, some people will get used to the standard format of “2021 / 02 / 04”. Of course, more people will use the simpler looking “2-4”. To make the field look more neat, just change the cell type to date, and then pick a date style. Later no matter what kind of input habits, excel will automatically unify to the preset style. In addition to the date format, we will also use this function to constrain the amount format, such as whether to add currency symbol? How many places after the decimal point? It is a common practice in daily life.

4. Protection formula

If no protection is added to the formula, the formula will either be covered or tampered with unintentionally, resulting in many strange errors. The solution is: select the whole table with Ctrl + A, right-click set cell format → protect, and cancel the check box in front of lock. Next, select only the cells with formulas, right-click set cell format → protect again, and check the box before lock. The read-only property of the cell with formula is set between this check and another check. Finally, click “review” → “protection” → “protection sheet” → “OK” to make the setting take effect. In the future, there is no need to worry about the formula being covered in the input.

5. You can only modify your own region

If the form needs to be transferred among multiple departments, we should consider how to avoid that the contents filled in later will not cover the previous contents. It is recommended that you use Excel’s “regional permission editing”. This function is located in “review” → “protection” → “allow editing area”. It is used to set passwords for different cell areas, so as to distinguish the editing permissions of different people (or departments). Before using it, you should select an area and click to enter the “allow editing area” panel to establish the edit permission and password for the first Department (person). Then click “new”, select a new scope and password, set up an editing area for the second Department (person), and so on to complete the setting of other departments (person). Finally, click “protection worksheet” in the lower left corner to make the setting effective. In this way, when the form is distributed, everyone can only edit in their own area, and other areas cannot be edited due to the wrong password, thus avoiding the embarrassment of deleting other people’s information by mistake.

6. Using the cloud form of WPS to solve the problem

In addition to excel, the cloud form function of WPS can also achieve the above purpose. The specific method is similar to excel. First, select the area to be set, and click “cooperation” → “regional permission”. The difference is that excel confirms the right by password, and the user needs to be informed of the password after completion. WPS is to confirm the rights through the account. Users can automatically obtain their own editing rights as long as they log in to their own account. In addition, WPS also supports multi person cooperation, and its work efficiency is higher than excel.

7. Use WPS cloud form to solve the problem

In addition to the cloud table, WPS also supports a cloud form function, which is more suitable for data collection. Unlike the cloud form, what the cloud form presents to the user is a single record. In addition to being unable to see the information filled in by others, the form builder can also use the built-in function module of the form to realize the field validity management of gender, mobile phone number and date (similar to the data validity of Excel). In addition, cloud forms support “optional” and “required”, and can also limit whether the fillers can fill in repeatedly. The submitted data will be automatically sorted into the WPS cloud form, and the mobile phone and PC can be entered at the same time, which is another major feature of WPS.

Write at the end

The validity of data in table processing is a commonplace. In the stand-alone era, we don’t have many choices, but with the addition of cloud form and cloud form, data collection has become easier and easier. Each of the above methods has its own advantages and disadvantages. It is not easy to say that one method is the best. Only choose the most suitable, is the best. Well, here are some tips to share with you in this issue. If you feel that today’s content is helpful to you, please give Xiaobian some praise!

Author: Xuan dad in kindergarten

 


发表评论

邮箱地址不会被公开。 必填项已用*标注