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.

4 ★ | 1 Vote

May be interested

  • How to fix corrupted Excel files effectively on your computerHow to fix corrupted Excel files effectively on your computer
    when encountering an excel file error, users may feel annoyed and do not know how to fix it. common errors such as not being able to open excel files, slowness or font errors can all be resolved with simple but effective methods.
  • How to fix Autofill errors in ExcelHow to fix Autofill errors in Excel
    are you facing autofill problems in excel? here are some ways to help you quickly fix data autocomplete errors in microsoft excel.
  • 7 ways to clean up data in Microsoft Excel7 ways to clean up data in Microsoft Excel
    spelling errors, extra spaces, duplicates, formula errors, and blank cells can all cause trouble when parsing spreadsheet data.
  • Common errors in Excel and effective ways to fix themCommon errors in Excel and effective ways to fix them
    when working on excel, many people encounter errors such as #value!, ##### or #ref! that interrupt their work. these errors are often caused by incorrect formulas, data formats or reference errors. below are the causes and solutions to help you handle them quickly.
  • Notes when typing formulas to avoid typing errors in ExcelNotes when typing formulas to avoid typing errors in Excel
    when entering the calculation formula using the functions in excel, sometimes you cannot find the error because of error. the following article gives detailed instructions on how to avoid entering corrupted formulas in excel.
  • How to fix Errors were detected while saving files in Excel 2010How to fix Errors were detected while saving files in Excel 2010
    in the course of working as well as creating excel data tables, you'll get a lot of errors like errors were detected while saving files in excel, affecting saving excel files.
  • 7 most common Excel errors for office people7 most common Excel errors for office people
    it is true that excel is not simply setting up tables and entering numbers. in fact, you will encounter a lot of problems when using this software and unfortunately not everyone knows how to fix it.
  • How to create drop down lists in ExcelHow to create drop down lists in Excel
    how to create drop down lists in excel. to limit the typing of misspelled data and the same information but the data is not identical due to the person entering at uppercase, sometimes lowercase or extra spaces that make the statistics error, therefore, when designing a data entry, every employe
  • How to create a newline in Excel 2020How to create a newline in Excel 2020
    simple way down line in excel 2020. during data entry in excel, there is much longer content than the width of the cell, so the data is overflowed to other cells. so you want to go down the line so that the data is displayed in a full cell, help ch
  • Fix error There was a problem sending a command to the program in ExcelFix error There was a problem sending a command to the program in Excel
    excel is a software built into the microsoft office office suite, often used to calculate data based on basic functions. and like other software, excel also has many different errors, one of the most common errors is that you cannot open the excel file after downloading it on the network and the message display screen was a problem sending một lệnh để chương trình. today, tipsmake.com will guide you to fix very common errors in excel.