Automatic data entry guide in Excel

Autofill allows Excel to automatically fill in data in rows or columns through user-defined settings. The automatic data entry mode in Excel is applied to some default fields such as day, date, month, etc. And you can also set up Excel to autofill with other fields. This will save you a lot of time when working with large spreadsheets and increase productivity. In this article, Buffcom.net will guide you on how to use Autofill with just a few basic steps.

Setting up automatic data entry

In Excel, some fields are set up for automatic data entry, such as day, date, month. To automatically enter data into these fields, you only need to enter a value in the first cell, then left-click and drag down. Then, the following values will be automatically filled in until the selected cell is reached.

Specifically, for the “day” field, when you enter “Monday” and drag down, the cells below will automatically fill in “Tuesday” and “Wednesday” until “Sunday,” then return to “Monday.” The “month” field works similarly.

Automatic data entry guide in Excel

You can also set up Excel to autofill with other fields. You can do this in one of two ways:

Method 1: Enter directly into the Custom List

Suppose you want to automatically enter the region field: North, Central, South. That is, when you enter “North” and drag down, Excel will automatically enter “Central” and “South.” To set up, do the following:

Step 1: On the toolbar, select File, then select Options.

Automatic data entry guide in Excel

Step 2: When the Excel Options dialog box appears, select the Advanced tab. Scroll down to the General section, then select Edit Custom Lists.

Automatic data entry guide in Excel

Step 3: Customize in Custom Lists:

• Select NEW LIST in the Custom Lists section.
• Enter “North,” “Central,” “South” (with line breaks) in the List entries field.
• Select Add, then press OK.

Automatic data entry guide in Excel

You have completed the setup. Now try entering “North” and dragging down to see Excel automatically fill in the following values.

Automatic data entry guide in Excel

Method 2: Input data from a spreadsheet into Custom List

Method 1 can only be used when the data field has few values and can be manually inputted. However, when dealing with large amounts of data, you can use Method 2. Suppose you need Excel to automatically input the names of 63 provinces/cities. Beforehand, you need to have a list of the 63 provinces/cities in an Excel spreadsheet. Then follow these steps:

Step 1: Select File on the toolbar, then choose Option.

Automatic data entry guide in Excel

Step 2: When the Excel Options dialog box appears, select Advanced. Scroll down to find General and then choose Edit Custom Lists.

Automatic data entry guide in Excel

Step 3: Customizing in Custom Lists:

• Choose NEW LIST in the Custom Lists section.
• Click on Import list from cells and select the prepared data of the 63 provinces/cities.
• Choose Import, then click OK.

Automatic data entry guide in Excel

Now you have completed the setup. Let’s try inputting the name of the first province/city and then drag down to have Excel automatically fill in the remaining values.

Automatic data entry guide in Excel

Deleting the autofill data entry field

To delete the autofill data entry field in Excel, follow these steps:

Step 1: Select File on the toolbar, then choose Option.

Step 2: When the Excel Options dialog box appears, select Advanced. Scroll down to find General and then choose Edit Custom Lists.

Automatic data entry guide in Excel

Step 3: Select the field you want to delete, then choose Delete and press OK.

Automatic data entry guide in Excel

Excel will now notify you that this field will be deleted, press OK to complete.

Automatic data entry guide in Excel

In conclusion, autofill is a powerful feature in Excel that can save users a lot of time and effort in data entry. Whether you want to input a simple list of values or a complex series of patterns, autofill can help you complete the task quickly and accurately. By following the steps we have outlined above, you can customize your own autofill lists and use them repeatedly in your worksheets. Remember to double-check the data and format before you apply autofill, and be cautious with sensitive or confidential information. With practice and creativity, you can master autofill and enhance your productivity in Excel. Thank you for reading, and happy autofilling!