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.

3 basic methods to avoid data entry errors in Excel Picture 1

3 basic methods to avoid data entry errors in Excel Picture 2

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.

3 basic methods to avoid data entry errors in Excel Picture 3

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.

3 basic methods to avoid data entry errors in Excel Picture 4
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.

Samuel Daniel

Update 25 May 2019

You should read it

May be interested

Automatically convert Important email to Normal in Microsoft Outlook 2010
have you ever received important marked emails? how to avoid distraction when most of these important messages are just a sender's joke?
Effectively use table features in Excel 2010
for repeating or similar data in microsoft excel, you can use the table feature to work most effectively. microsoft office 2010 version has new improvements in terms of graphics, so the use of the table also becomes simpler.
5 tips 'VIP' on Office 2010
the following 5 tips can be applied to any program in microsoft office, from using shortcuts to opening documents of old office versions.
Editor and tabbed document management for Microsoft Office
tabs are an easy way to navigate to multiple documents or pages without having to open multiple times of the same application. tabbed browsing is very popular in all web browsers.
Use document comparison feature in Word 2010
comparing documents together is a little-known feature of microsoft word 2010 but is really handy for everyone, especially for those who work with multiple documents.
Add Structure to Diagram in Visio 2010 using List and Container
in the microsoft visio 2010 model design application, one of the most used features is to assign a structure - structure to the chart - diagrams using containers, lists and callouts. in the following article, we will go deeper and learn more about the above concepts and how to use them accordingly ...