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

Everyone loves to have his data organized. We do love it even more to have organized data with minimum effort. The main motivation is to enter data to the existing dataset without any hassle and waste of time. To easily enter the data in the table, we can create a data entry form. In this article, we are going to learn how to create a data entry form in Excel in two separate methods.

For more clarification, I am going to use a dataset containing date, Name, Designation, Entry Time, Exit Time, and Working Hours columns. And show how you can add new data to the worksheet entry form using both the Excel worksheet directly and through the OneDrive form. Below, is a dataset that has been updated with new data using the form option from the ribbon menu. Each time new data is inserted, the number of rows increased by one to accommodate the new data.

how to create data entry form in excel

Watch Video – Create Data Entry Form in Excel

What is the Data Entry Form in Excel?

A data form helps us conveniently provide a means to enter one complete row of information in a table without scrolling horizontally. It is quite evident that it makes data entry quite easier than ever before. It lessens the possibility of putting information in the wrong column. In case we do not need any sophisticated or custom features, we can use the form to lessen our workload.

Microsoft Excel can produce a built-in data form for our table. All the column headers with options to input data are displayed in a single box in the data entry form. We can enter data up to a maximum of thirty-two columns. We can perform several operations in a data entry form, some honorable mentions are – entering new rows, finding rows by navigating, or (based on cell contents) updating and deleting rows. If we have a formula applied, we cannot change it from the form.

How to Create a Data Entry Form in Excel: 2 Separate Methods

Data Entry can be done by manually, but that would be cumbersome and not convenient. For this, we present two separate methods following which you can create a Data Entry Form quite easily in Excel. To avoid any kind of compatibility issue, try to use the Excel 365 edition.

1. Use the Quick Access Toolbar to Create a Data Entry Form in Excel

I have tried to explain the whole process of creating a data entry form in 4 simple steps. They are written below with images for clarification.

Step 1: Create an Excel Table

We can create a table with the available data.

  • Select the whole data (e., B6:F9).
  • Next, click on Insert.
  • Then, click on Table from the ribbon.

Creating Table from Ribbo Menu

A Create Table box will appear.

  • Check the My table has headers box as the range is automatically set due to the table selection in the first step.
  • Finally, Click OK.

how to create data entry form in excel

  • The table will be formed.

Table final form

Read More: How to Create an Excel Data Entry Form without a UserForm

Step 2: Add Form Command to Quick Access Toolbar

In order to create data entry form quite easily, we can add the Form command in the Quick Access Toolbar option.

  • Click on the Customize Quick Access Toolbar option.
  • Then, choose More Commands.

Adding Form option from More Commands

An Excel Options box will appear.

  • Pick All Commands from the Choose commands from section.
  • Next, choose the Form command and click on Add.
  • Finally, press OK.

adding Form to the customized quick access toolbar

  • Then, you will have the Form command in the Quick Access Toolbar.

form control showing in the quick access toolbar

Step 3: Add a New Set of Information

We can easily add a new set of information with the help of a data entry form.

  • Click any cell from the table (D8).
  • Next, click on the Form command from the Quick Access Toolbar.
  • Click on New from the Form Criterion Now, and input your information according to the columns.
  • Finally, press Close.

Form of the table showing with table headers to entry data

  • We can see the newly added information aligned in a row with the respective columns.

Showing the addition of a new entry in the table using the form

Step 4: Remove a Previous Set of Information

We can also remove the previous set of information.

  • Select the set of information by scrolling the cursor.
  • Then, click on Delete.

Removal of data entry from table

  • Click OK when the warning box is displayed.

warning messege showing that the record will be removed forever

  • The selected row will be deleted with their information.

table dataset without one data point

Step 5: Final Form Outline After Taking All Submission

After we make all the submissions, users can download the file and see the form submission result by themselves. We can see from the below sheet that all the answers to the questions are now present in the Excel sheet in the form of the data entry.

FInal outline of the data in Excel after the data entry done using the form

Read More: How to Create Data Entry Form with Drop Down List in Excel

2. Utilizing Microsoft Forms to Create Data Entry Form in Excel

Apart from the Excel worksheet, you can create data entry forms easily using the OneDrive form survey. Follow the below instruction step by step to make one form for you now.

Step 1: Go to OneDrive and Add a Form Survey

In the beginning, we can add the Form from the OneDrive web app.

  • For this, first log in to your OneDrive account and then go to the Homepage.
  • On the Homepage, Click on the New drop-down menu, and from the menu, click on the Forms Survey.

Adding the survey form form the Onedrive

Step 2: Set the Type of Inquiry and Add Questions

In the next step, Set the type of form, here we choose the Text type of inquiry.

Set Inquery Type

Step 3: Organize the Form with the Question Set

After this, we will receive a prompt for the input of questions.

  • Enter the questions and press Add New to add another question.

Set Questions

  • Do this repeatedly and we can see that all the questions are now added to the form.

Question set for the data entry purpose in Excel is done in the Microsoft Form

Step 4: Submit a Sample Response and Test the Output

For the sake of assessing our newly created form, we submitted a sample response to the questions we created just before.

  • We can see that there is an option showing over the response tab, Open in Excel. If we download the Excel sheet, we will see the data that we input shown here.

data entry count showing in the microsoft form response tab

Step 5: Submit More Responses and Complete Survey Form

Multiple users might enter multiple entries to the form. After we get all the responses, we can finally download the Excel sheet and see the form output.

As we can see that we have the ID, Start time, and Completion time of each of the participants. alongside obviously we also got the entries from the questions.

Show the output values after data entry in the onedrive form

Read More: 

Reasons for Data Entry Form Not Working

There could be some serious reasons which could lead to the failure of data entrance.

The reasons are:

Entry of More Than 32 Columns

If there are more than thirty-two columns in the table, it will not be able to create a form. It will show a warning message box.

List Not Extended

If the worksheet already holds some data outside the table, the table cannot be extended as there is already data input. So, better to clean prior to creating the data entry form.

Cursor Outside the Table

If the cursor detects a cell outside the table, it will not be able to take any further information. So, the data entry form will not work. So, make sure to place the cursor within the table.

Range Name

If you name, the range of something which is already for another range you may encounter an error. The form will always consider that range. So, the Data entry form will not work according to the given commands. Provide a unique name to the range.

Frequently Asked Questions

1. How to Create a Data Entry Form in Excel with a Drop-Down List?

For this, we need to add a drop-down list from the data validation tool.

  • First, go to the Data tab > Data tools > Data Validation > Data Validation.

adding data validation for dropdown list

  • After that, a new window will open, in that window, enter the data validation type in the Allow box and the output value range in the source range box.
  • Click OK after this.

set up data range and output range

  • Now we can see that there is a drop-down menu appearing in cell C4.

set up data range and output range-2

  • Now copy this drop-down menu and then paste this onto cell C3:C7.
  • And now you can enter the value using the drop-down menu in the range of cell C3:C7.

Ouput with the dropdown list

2. Can Multiple Users Input Data into the Same Data Entry Form?

Yes, multiple users can input data into the same data entry form if the file is saved on a shared network drive or cloud-based storage service. For example, in the case of a dual-entry system.

3. How Do You Automate Data Entry in Excel?

To automate data entry in Excel, you can use macros. Macros are a set of instructions that automate repetitive tasks, such as data entry. You can also customize the macro to perform other tasks, such as formatting the data or validating input. Additionally, you can assign a keyboard shortcut or create a button to run the macro more easily.

Things Need to Remember

  1. Define the data range: First, define the range of cells where you want to input data in the form.
  2. Use clear labels: Use clear labels for each data field to make it easy for users to understand what data they should enter in each field.
  3. Use drop-down lists: Use drop-down lists to provide predefined options for data fields that have limited choices, such as gender, country, or product type.
  4. Use data validation: Use data validation to restrict the type of data that can be entered in each field. This can help prevent errors and ensure data consistency.
  5. Use conditional formatting: Use conditional formatting to highlight errors or inconsistencies in the data as it is entered in the form.
  6. Use error messages: Use error messages to provide feedback to users when they enter invalid data in a field.
  7. Use clear instructions: Provide clear instructions for users on how to fill out the form.
  8. Evaluate the form: Test the form thoroughly to ensure that it works as intended and that users can easily input and submit data.

Download Practice Workbook


I have tried to explain the whole procedure in simple words on how to create a data entry form in Excel. I hope everyone will be able to understand it quite easily. Here first we presented the direct Excel worksheet form method using which we can create a form from an already existing table and then apply it to it. This is more time-consuming but free from complications. comparatively. Moreover, we also showed a procedure where users can create a data form and entry data relevant to them in the form of question and answer, and later see the output data in an Excel worksheet. This method is super intuitive and requires less formatting and time.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF