3 basic methods to avoid data entry errors in Excel

You can spend a lot of time in formatting and calculating formulas in your spreadsheet, but all your work is wasted if someone enters a value that doesn't fit into it. Normally, you will correct the input errors when encountering this situation, but what is the most practical thing that you can avoid data entry errors? Please apply the following basic methods:

1. Confirm the input value

Using the Data Validation feature in Excel can eliminate inappropriate data when users enter. That is, you will specify the conditions that a value must meet, and then Excel will reject the value that does not meet the criteria given and the input error message.

To confirm the input data, from the MS Excel 2007/2010 window interface, browse to the Data tab, click the Data Validation icon and select Data Validation (With Excel 2003 version, select Validation from the Data list), at In the window that appears, click the Settings tab , in the Validation criteria box, select Decimal for the Allow , between item for the Data item and enter the Minimum and Maximum values ​​below and click OK to acknowledge. For example, here we enter a value between 1 and 1000, then if the user enters data outside this data area, there will be an error message immediately.

Note: You must select the data area on the spreadsheet before performing the validation.

In addition, you can set the input condition notification by selecting the Input Message tab and entering the title and display content. To create a warning in Vietnamese-this warning appears immediately after entering the wrong data (the default is English), you select the Error Alert tab, choose the type displayed in the Style section can be Stop , Warning or Information , then enter the title and content of the alert.

2. Do not use the AutoComplete feature

Excel AutoComplete is a feature that automatically completes input data as soon as the user enters the appropriate input value or has a character that is similar to the existing value. And so, AutoComplete will reduce the input time but it can increase the probability of incorrect input when there are many similar values.

To disable this feature, click the Office Button button and select Excel Options , in the left pane click Advanced , then uncheck the Enable AutoComplete For Cell Values ​​text and OK (For Excel 2003 you must Select Options from the Tools menu , and select the Edit tab.

3. Using a dynamic list (Dynamic list)

Adding or deleting values ​​will not update items in a confirmed list if we do not use the reference feature in Excel. For Excel 2007 and 2010, when converting an existing list into a table and then creating a new list linked through this table. Then, if the user adds or deletes the data from the table, the list will automatically change.

When you add or delete data in column L, list N will automatically update accordingly

First, select the data area on a single column, then on the Insert tab, select the Table icon from the Tables group, in the create Table window, check the check box before My table has the headers and click OK . Then this entire data has been converted into a table. Now select any blank box then go to the Data tab and click on Data Validation as above, but in the Settings tab , for Allow you have to select List , click on Soucre box and the data zone is created. Click OK , the newly created list will look like you used the Filter feature.