Data Entry Form in Excel Without VBA

Data Entry Form Without VBA Code

How to Create a Data Entry Form in Excel Without VBA/Macro

Data entry is one of the important basic task needed when no machine-readable version of the information is readily available for further data processing. Data entry can be done in different ways e.g. filling data in spreadsheet, entering data directly to database and user form.  Among all these options, data entry form is one of the best ways to enter data in a table without making any mistake and it also helps users to do their job in most efficient way.

But, creating a form to enter data is not everyone cup of tea. One should have good understanding of Excel and have depth knowledge of VBA/Macro. If you are not comfortable with the coding part, then it would be difficult for you to develop a form. Even recording macro will not help here as it needs different logic and conditional statements.

While struggling with this problem, I have found an inbuilt Excel tool which will help you in creating a dynamic and fully featured data entry form in Excel with a single click. Yes, on a single click.

The best part about this form is its pre-designed, and dynamic in nature. Just look at the below example.

Data Entry Form (Without VBA/Macro)

Here’s the idea:

To create this  data entry form, the only thing you need to do is to activate it in your Excel application.

How to Create this Data Entry Form

In a short while, you will discover that how easy is this tool to create and very helpful to make data entry.

Excel has provided such an amazing feature in it’s repository but make it hidden for users. As this tool is not available in the ribbon, you need to make it appear before using it.

And before you activate this form, you need to create Excel file and a table to enter data or at least headers of the data.

Please follow the  below steps to create the file and activate the form feature.

  • Create a new Excel file and save the file with relevant name.
Save File (No macro required)
  •  Create the Table with required header and rename the worksheet. Here, we have renamed the sheet as “Employee Data” and headers are Employee ID, Employee Name, Gender, DOB, Department, Salary and Address.
  • Employee Table
  • Now, convert the range to Table. To do this, please select the range and press the shortcut key CTRL + T. In Create Table pop-up window, select ‘My Table has headers’ and then click on OK button.
  • Creating Table
  • Right click on Quick Access Toolbar and select ‘Customize Quick Access Toolbar…’
  • Customizing Quick Access Toolbar
  • Once you click on ‘Customize Quick Access Toolbar…’, Excel Option window will appear. In Excel Option dialog box
  • Customizing Ribbon
    1. Click on ‘Quick Access Toolbar’ in left pane
    2. Select ‘Commands Not in the Ribbon’ from the drop-down ‘Choose commands from’
    3. Find the ‘Form…’ and select it
    4. Click on ‘Add’ button to move the ‘Form’ to the right side list box
    5. Now, click on OK button to exit from Excel Options dialog box
  • Now, you can see the ‘Form’ button in Quick Access Toolbar.
  • Quick Access Toolbar with Form
  • To open the form, click any of the cells in the table or just select one of the heading cells and then Click on ‘Form’ button available in Quick Access Toolbar or alternatively you can also use shortcut key: Alt + D + O + O. After using any of the method to show the form, it will create a data entry form for you just like below. And if you notice, all the headings of your data table automatically captured in the form.
  • Data Entry Form

    How to Use this Data Entry Form

    To use this form you need to learn some basic things which I have mentioned in the below sections.

    Create New Entry

    To create a new data entry fill all the input boxes with data click on the “New” button. You can also press Enter or click on Close button (if you want to make only one entry and close the form).

    New entry

    Navigate Entries

    To navigate all the entries which you have entered in your table, you can use either the scroll bar or “Find Prev” & “Find Next” buttons to move from one record to other.

    Navigate all the entries

    Edit Entries

    To edit any record available in table, you have to first navigate to that entry by the using ‘Scrollbar’ or ‘Find Prev’/’Find Next’ button (as mentioned in previous step).

    Once you select the entry which you want to edit, you can make the changes in any of the input boxes and after that, just press enter key.

    ​While editing your entry (before pressing enter or clicking close button), if you want to restore your entry you can do it with restore button. ​It will restore the previous data in all the input boxes.

    Edit and Restore
     

    Delete EntriesTo delete a record available in the table, you have to navigate to that entry and click on the delete button.

    A pop window will appear for the confirmation of the deletion and it will instantly delete that entry from your data table once you will click on OK button.

    Delete Record(s)

    Find a Specific Entry

    If you table has several records and you are facing difficulties in finding the record then you can utilize the  “Criteria” button in the form to find a specific entry.

    For example, if you want to find entries from below table where ‘Gender’ is ‘Male’, just click on criteria button, enter the criteria in the input box and press Enter.

    Criteria
    Criteria value

    You will get the data entries matching your criteria. You can also use your criteria in more than one input box. If output will be more than one record then you can utilize ‘Scrollbar’ or ‘Find Prev’/’Find Next’ button to navigate from one record to other.

    Important points 

    Conclusion

    This hidden data entry form is very useful to create form quickly with several features and you can utilize it in your day to day activities. There is no doubt that it has some limitation and you will not be able to create form in the same way we design and develop in VBA.

    If your requirement is more than the features and options available in this Data Entry Form then you can explore our tutorial on creating Excel Form, User Form in VBA. Please refer the below link.

    Data Entry Form in Excel Sheet

    Advanced Data Entry form in Excel Sheet with Add, Search, Modify, Delete and Reset

    Data Entry Form in User Form with List Box

    Click Here to download the practice file.

    Please watch our YouTube tutorials.

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here