Use Excel list to realize batch input function

In the process of using Excel, we usually need to enter a large amount of data. This is the basis for ensuring our smooth completion of the work. However, in the process of inputting data, especially when inputting a large amount of data, there are often some inadvertently input errors, which will seriously affect the correctness of the results we get. So, how can you effectively control the occurrence of this input error? In addition to being careful, careful, and careful, it is also important to complete the necessary settings in Excel to minimize the occurrence of errors.

Set up a list of data

Sometimes the data we need to enter is one of some repetitive data, such as the department where the unit employee belongs. The number of units in the unit is limited, if we all through the keyboard manual entry of each employee's work department, it is time-consuming and laborious, but also error-prone. Therefore, we might as well specify a list of data for these departments. Just click to select the appropriate department in the Drop-down list when you enter the entry. This can not only improve the speed of input, but also make the quality of input to be guaranteed.

First select all the cells that you want to fill out in the Employee department, click the Data Validation button in the Data Tools feature group on the Data tab of the Ribbon, click Data Validation on the pop-up menu, and open the Data Validation dialog box.

Click the Settings tab in the dialog box and select the "sequence" option in the Drop-down list below "Allow". then enter the name of each department in the "source" input box (Ministry of Personnel, workshop, workshop, production, technical department, office), separated by commas in English, as shown in Figure 1. Click on the "OK" button to close the dialog box.

Figure 1 Excel Setup sequence

We can also enter individual department names in a column of blank cells in a worksheet, such as in a H1:h6 cell range. You can also get the same effect by typing "= $H $ $H $" in the input box under "source".

Now position the mouse over any cell in the range of cells that you just selected, a drop-down arrow appears on the right, and click on it to see the Drop-down list that we just set, as shown in Figure 2, and click on the item to complete the entry.

Figure 2 Drop-down list in Excel

Second, use in other worksheets

If we want to be able to use this department Drop-down list in other worksheet cell ranges, we can use a custom name to accomplish this task.

In the Blank Unit column input the corresponding department name, for example H1:h6 cell area. Then select the cell range and click the Define Name button in the defined Name feature group on the Formula tab of the Ribbon to open the New Name dialog box. As shown in Figure 3, enter a name in the input box to the right of name, such as "Bumen". Select Workbook in the scope Drop-down list, and the range of cells that we select is automatically used in the input box to the right of reference location. Once you are sure, you can specify the name of "Bumen" for the range of cells that we select.

Figure 3 Excel New Workbook

All you have to do now is to use this department list on the current worksheet after you select the appropriate range of cells in the worksheet, open the Data Validation dialog box, and then enter =bumen in the source input box.

If you think this custom name method is a bit verbose, the following method is much simpler.

Select the cells that have the data validation set up, and then press the "CTRL + C" key to copy. Then position the mouse on the target list

Wenge, click the small triangle under the "Paste" button in the Clipboard feature section of the Ribbon "Start" tab, and click "Paste" in the pop-up menu to open the Paste Selective dialog box. Selecting the Validation single option is OK, as shown in Figure 4.

Figure 4 Excel Paste Selective