Everyone loves to have his data organized. We do love it even more to have organized data with minimum effort. In order 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 data entry form in Excel.
For more clarification, I am going to use a dataset containing date, Name, Designation, Entry Time, Exit Time, and Working Hours columns.
Download Practice Workbook
Data Entry Form
A data form helps us conveniently providing 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 an information in a wrong column. In case, we don’t 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 32 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 rows and deleting rows. If we have a formula applied, we can not change it from the form.
Step by Step Process to Create Data Entry Form in Excel
I have tried to explain the whole process to create data entry form in 4 simple steps. They are written below with images for clarification.
1. Create a Table
We can create a table with the available data.
- Select the whole data(i.e. B4:G9).
- Nextly, click on Insert.
- Then, click on Table from the ribbon.
A Create Table box will arrive.
- Check the My table has headers box as the range is automatically set due to the table selection in the 1st step.
- Finally, Click OK.
The table will be formed.
Read More: How to Create an Excel Data Entry Form without a UserForm
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.
- Click on the Customize Quick Access Toolbar
- Then, choose More Commands.
An Excel Options box will appear.
- Pick All Commands from the Choose commands from
- Next, choose the Form command and click on Add.
- Finally, press on OK.
Then, you will have the Form command in the Quick Access Toolbar.
Read More: Types of Data Entry in Excel (A Quick Overview)
- How to Create an Autofill Form in Excel (Step by Step Guide)
- How to Automate Data Entry in Excel (2 Effective Ways)
- How to Create Data Entry Form with Drop Down List in Excel (2 Methods)
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(e. C5).
- Next, click on the Form command from the Quick Access Toolbar.
- Click on New from the Form Criterion box.
- Now, input your information according to the columns.
- Finally, press Close.
We can see the newly added information aligned in a row with the respected columns.
4. Remove a Previous Set of Information
We can also remove a previously set of information.
- Select the set of information by scrolling the cursor.
- Then, click on Delete.
- Click OK when the warning box is displayed.
The selected row will be deleted with their information.
Read More: How to Restrict Data Entry in Excel Cell (2 Simple Methods)
Reasons That May Lead to 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 32 columns in the table, it won’t 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 can’t be extended as there is already data input. So, better to clean prior to creating data entry form.
Cursor Outside the Table
If the cursor detects a cell outside the table, it won’t be able to take any further information. So, the data entry form won’t work. So, make sure to place the cursor within the table.
If you name the range something which is already for another range you may encounter error. The form will always consider that range. So, the Data entry form won’t work according to the given commands. Provide a unique name to the range.
For more expertise, you can practice here.
I have tried to explain the whole procedure in simple words on how to create data entry form in Excel. I hope everyone will be able to understand it quite easily. For further queries, comment below. You can visit our ExcelDemy site for more information on Excel.