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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset-Create an Autofill Form in Excel

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


Download Excel Workbook

Practice with the following dataset.


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.

Autofill Form-Create an Autofill Form in Excel

Read More: How to Create Data Entry Form in Excel (Step by Step)


Steps to Create an Autofill Form in Excel

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 an Excel Table. Highlight the desired range then go to the Insert tab > Click on Table (in the Tables section).

Setting up the data-Create an Autofill Form in Excel

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

Table Creation

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

Final Set up


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.

Quick Access Toolbar-Create an Autofill Form in Excel

🔼 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.

Adding Form

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

Added Tool-Create an Autofill Form in Excel

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

Read More: How to Create Data Entry Form in Excel VBA (with Easy Steps)


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. The parts or keys of an Autofill Form are

Parts of the Form-Create an Autofill Form in Excel

⏩ 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.

Form Creation-Create an Autofill Form in Excel

🔼 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.

Autofill Form


Similar Readings


⧭ 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.

Move around the entries


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.

Adding new records or edit them


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.

Search entries-Create an Autofill Form in Excel

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

Criteria Key

 🔼 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.

Search Result-Create an Autofill Form in Excel


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo