One of the useful use of Excel is to create a data entry form. That can be created using user form or without userform. But many people don’t like to use user form or VBA. So in this article, I’ll show a quick and useful way to create an Excel data entry form without a userform with sharp steps and clear images.
You can download the free Excel workbook from here and practice on your own.
How to Insert Form Command in Quick Access Toolbar
Here’s the dataset that we’ll use to explore the method, it represents the published year and price of some best seller books.
To make an Excel data entry form, we’ll need to use the Form command. So first we’ll see how to add it to the Quick Access Toolbar.
Steps:
- First, click on the Customize Quick Access Toolbar icon on the top left side of the Excel window.
- Then select More Commands from the drop-down list.
Soon after you will get the Excel Options dialog box to customize the Quick Access Toolbar.
- Next, select Commands Not in the Ribbon from the Choose commands from box.
- Then scroll down and select Form.
- Later, press Add>> button.
- Now see, the command is added, just press OK.
The Form command icon will be visible on the Quick Access Toolbar like the image below.
How to Create a Table to Make a Data Entry Form without a UserForm
The Form command needs Excel Table to make a data entry form. So now we’ll create Excel Table for our data.
Steps:
- Select any data from the dataset.
- Then press CTRL + T to insert a table.
- It will automatically select the data range, just press OK.
Excel has changed the data range to a Table.
Using Form Command to Create an Excel Data Entry Form without a UserForm
Now, it’s time to execute our final task- apply the Form command in the Excel data Table.
Steps:
- Firstly, select any data from the Table.
- After that, just click the Form command icon from the Quick Access Toolbar.
Soon after, a data entry form dialog box will appear and the title will be according to your sheet name.
Read More: How to Create Data Entry Form in Excel VBA (with Easy Steps)
Similar Readings
- How to Populate an Excel Spreadsheet from a Web Form
- Create Data Entry Form with Drop Down List in Excel (2 Methods)
- How to Create a Data Log in Excel (2 Suitable Ways)
How to Use Excel Data Entry Form
Have a look that there are some command buttons in the dialog box by which we can add, delete, search or modify data. In this section, we’ll learn about those operations.
Navigate Entries
Using the Find Next and Find Prev buttons from the data entry dialog box, we can easily move to the next data or previous data.
Steps:
- To move to the next entry, just press the Find Next button.
Here you see, it moved to the next entry.
- Now to go back to the previous entry, press the Find Prev button.
We are now back to our previous entry.
Read More: How to Automate Data Entry in Excel (2 Effective Ways)
Search an Entry
Also, you can search for any entry by using the Criteria button.
Steps:
- First, click on the Criteria button, and all the entries will be empty.
- At this moment, type the search keyword in the field. You can search for full data or partial data. I searched for 2015.
- Then just hit the Enter button.
Then you will see that it is showing all the corresponding data in the other fields. If there is more than one search result then you can navigate the result using the Find Next and Find Prev buttons.
Add New Entry
Now let’s see how to add an entry using the data entry dialog box. That means we won’t add data to the sheet, will add it from the dialog box, and then it will be automatically added to the table in the sheet.
Steps:
- Click on the New button.
- Later, add new data in the fields and press the Enter button to insert it.
Soon after you will get the data added to the Excel Table.
Edit an Entry
If you want to edit any entry from any field then also it is possible and that’s very easy to do. Here I’ll edit the published year of the first book from 2020 to 2019.
Steps:
- Write the new data in the corresponding field, I wrote 2019.
- Then just hit the Enter button.
Now see, it is updated in the Excel Table.
Restore an Entry
While editing data, if you suddenly decide that you don’t want to edit and restore the previous data then just use the Restore button.
Step:
- To undo from any moment, just click the Restore button.
I was editing the first book name, erased some words, and then pressed the Restore button.
And see, it is back to the previous state.
Delete an Entry
Lastly, we’ll learn how to delete an entry. The Delete button will be used in this regard.
Steps:
- Navigate to the data that you want to delete. I chose the newly added data.
- Next, just click the Delete button.
A pop-up notification box will appear to ensure the deleting operation.
- Press OK to proceed.
Soon, you will see that the data is deleted successfully.
Conclusion
I hope the procedures described above will be good enough to create an Excel data entry form without a UserForm. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.
Related Articles
- How to Make a Fillable Form in Excel (5 Suitable Examples)
- Automatically Insert Timestamp Data Entries in Excel (5 Methods)
- How to Restrict Data Entry in Excel Cell (2 Simple Methods)