How to Create an Autofill Form in Excel (Step by Step Guide)

In Excel, editing or checking a huge dataset requires tedious work. Therefore, users tend to create an Autofill Form in Excel to quickly check, add, or delete entries. An autofill form turns out to be a handy tool while data entering in Excel worksheets.

Let’s say we have a dataset containing student information such as Student IDs, Names, Height, and Weight. We want to check those entries and add new entries using an Autofill Form.

In this article, we demonstrate the step-by-step process to create an Autofill Form in Excel.

Watch Video – Create an Autofill Form in Excel

What Is an AutoFill Form in Excel?

Autofill Form is a data entry or checking tool. It has a unique structure that includes all the column headers of any Table data as its own data entry box. Data existing within the Table gets fetched in its (Autofill Form) data entry boxes. Users can move around their data by clicking on the Scroll bar. Also, new entries can be added, deleted, or find within the dataset using its offered keys.

How to Create an Autofill Form in Excel: 4 Easy Steps

Creating an Autofill Form is an easy task. Follow the below steps to create one.

Step 1: Setting Up Data to Create an Autofill Form

🔼 Before creating an Autofill Form, users need to set up their data as a table. To do so, we need to insertan Excel Table. Highlight the desired range then go to the Insert tab > Click on Table (in the Tables section).

🔼 Excel brings the Create Table dialog box. Tick the My table has headers option then click OK.

🔺 In a moment, Excel creates a Table containing all the highlighted data in it as depicted in the image below.

Step 2: Adding Data Entry Form Tool to Quick Access Toolbar

Apparently, there are no such commands or features in the Excel Ribbon. Users have to add it using the Customize Quick Access Toolbar options.

🔼 Click on the Customize Quick Access Toolbar Icon as shown in the below image. Afterward, click on the More Commands option.

🔼 The Excel Options window appears. In the window, click on Quick Access Toolbar > Choose All Commands under Choose Commands from option > Click on Form > Click on Add > At last, Click on OK.

🔺 After returning to the worksheet, you can see the Form tool appears in the Quick Access Toolbar region.

Now, you are good to go for creating an Autofill Form in Excel.

Step 3: Parts of Autofill Form

🔼 Entry boxes of an Autofill Form rely on the data Table. The Autofill Form incorporates all the Table headers as its entry boxes. The parts or what you can say Keys of an Autofill Form are static. There are multiple keys within an Autofill Form. The keys do the basic operations such as adding new entries, deleting them, or going around the entries.

⏩ New: Users can add new entries by clicking on this key. Also, after editing an existing entry users can add a new entry by just clicking on it.

⏩ Delete: Clicking on this key deletes the loaded entry in the form.

⏩ Restore: While editing an existing entry users can restore the previous entry in the form just by clicking on it. Make sure users don’t apply New or press the Enter key.

⏩ Find Prev: Clicking on this key takes you to the previous entry.

⏩ Find Next: This key finds the next entry.

⏩ Criteria: To find a specific entry or record, users can use this key.

⏩ Close: This key closes the Autofill Form.

⏩ Scroll Bar: To move around up or down rows, just move the scroll bar or click on the Up or Down arrow.

Step 4: Creating an Autofill Form

After setting up all the data and getting familiar with the Autofill Form’s parts, users can simply create an Autofill Form.

🔼 Place the cursor on any cell then click on the newly added Form quick access tool.

🔼 Instantly, Excel brings up the Autofill Form with the selected entry. Now, users can move around the entries to edit, check, or add new entries using the Autofill Form.

⧭ Benefits of Using or Creating an Autofill Form in Excel

Data Entry is time-consuming work. Also, it’s highly error-prone and requires immense concentration while conducting it. An Autofill Form can offer an easy alternative to Data Entry. So, an Autofill Form provides multiple benefits to users in data handling in Excel.

Move Around the Entries

An Autofill Form is an efficient tool to allow users to move up or down within the Data. Users can instantly go to the next entry or any entry just by clicking on Next or moving the Scroll bar.

Adding New Records or Editing Them

Editing existing records or adding new records to the data Table has never been so easy without an Autofill Form. Just click on the New key to add a new entry or record.

Searching Entry or Record

Autofill Form also comes in handy when users try to find a specific entry using any field.

🔼 Click on the Criteria key.

🔼 Type the desired entry you want to find the record of.

🔼 Press or hit Enter, instantly the Form gets all the fields of the respective entries. This allows users quick access or able to check each record’s field using a specific field.

Download Excel Workbook

Practice with the following dataset.

Creating an Autofill Form.xlsx

Conclusion

In this article, we demonstrate step by step depiction of creating an Autofill Form in Excel. However, users need to set up their data as an Excel Table before creating an Autofill Form. Hope this article clears your understanding and helps you to create an Autofill Form in Excel. Comment, if you have further inquiries or have anything to add.

<< Go Back to Data Entry Form in Excel | Data Entry in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!