How to Create a Data Entry Form in Excel without VBA

Search
Generic filters

The best way to enter data in a table is Data Entry Form.  But, creating a form to enter data it not a cup of cake.

Yes, that’s right.

While struggling with this problem, we have found that you can create a form in Excel for data entry with only a single click.

The best part about this form is it’s pre-built and flexible. Just look at the below example.

 
data entry form in excel

Here’s the idea:

To create this entry form, the only thing you need to do is to activate it.

Table of Contents

How to Create Data Entry Form in Excel

In a short while, you will know that how helpful this tool is. But, this is the drawback is that there is no listing of this data entry form in the ribbon. So, before you start you have to make it appear in excel ribbon.

Also, before you activate this form, you need a table to enter data or at least headers of the data.

table heading to use data entry form in excel

To activate it, click any of the cells in the table or just select one of the heading cells and use one of the below two methods.

  1. Use the keyboard shortcut key: Alt + D + O + O.
  2. Add an icon to quick access toolbar.
    1. File ➜ Options ➜ Quick Access Toolbar.
    2. Choose Command From ➜ Commands Not In Ribbon ➜ From ➜ Add ➜ Click OK.
add data form icon on quick access toolbar in excel

After using one of the above options, it will create a data entry form for you just like below.

open data entry form in excel

And you see, all the headings of your data table have automatically captured in the form.

How to Use Data Entry Form in Excel?

To use this form you need to understand some basic things which I have mentioned ahead.

#1. Create New Entry

To create a new data line fill all the input boxes with data click on the “New” button. Now if you look at the below example you’ll find that there is no input bar for the amount column.

No, there is no bug.

there is a formula in the amount column to multiply the quantity by the price.

That means this form is quite smart to discover the columns where there is no need to input data.

enter data in a table with this data entry form in Excel

#2. Navigate Entries

To navigate all the entries which you have entered in your data, you can use the scroll bar or you can use “Find Prev” & “Find Next” buttons.

scroll through entries with this data entry from in Excel

#3. Edit Entries

To edit an existing entry you have to first navigate to that entry by the using method mentioned above. Once you navigate, 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 can also restore it with restore button.

It will restore the previous data in all the input boxes.

edit entries with this data entry form in Excel

#4. Delete Entries

There is delete button also to delete an entry you have. A pop window will appear for the confirmation of the deletion and it will instantly delete that entry from your data table.

delete entry using this data entry from in Excel from table

#5. Find a Specific Entry

If you are working on a large data, you can use “Criteria” button to find a specific entry.

For example, if you want to find entries from below table which match some specific criteria, just click on criteria button, enter the criteria in the input box and press Enter.

You will get the data entries matching your criteria. You can also use your criteria in more than one input box.

find a specific entry in this excel data entry form with a criteria
 

Important Points about this Data Entry Form

Well there some important points which you should know to play well with this form.

Conclusion

In the end, I just want to say that this data entry form is very useful although it has very few options.

If your requirement is not so high you can use this tool rather than clubbing in some high-grade user-forms.

I hope you found this tool helpful in your work and now tell me one thing.

 

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.

How to CONCATENATE a RANGE of Cells [Combine] in Excel

How to CONCATENATE a RANGE of Cells [Combine] in Excel

Table of Contents How [CONCATENATE + TRANSPOSE] to Combine Values The best way of  combining text from different cells into …

Deselect Cells in Excel

How To Deselect Cells in Excel

Table of Contents How to Deselect Cells in Excel After adding the deselecting cells feature in Excel, this  becomes so  …

Select Non-Contiguous Cells in Excel

Learn how to Select Non-Contiguous Cells in Excel

Table of Contents Select cell without using Ctrl Key By following these simple steps we can do it. At first, …

Format Painter in Excel

How to use Format Painter in Excel

Table of Contents Where’s the Format Painter Button? It’s there on your Home Tab. If you look at the clipboard …

Apply Strikethrough in Excel [Shortcut + Command]

How to apply Strikethrough in Excel [Shortcut + Command]

Table of Contents Shortcut Key to Apply Strikethrough to a Cell Let’s say you’re in hurry and don’t want to …

Formula Bar in Excel

Formula Bar in Excel

Table of Contents Key Points To Consider Using Formula Bar You can hide or unhide the formula bar. You can …

Basic VBA Macro Codes in Excel

Custom Header & Footer

Sub CustomHeader() Dim myText As String myText = InputBox(“Enter your text here”, “Enter Text”) With ActiveSheet.PageSetup .LeftHeader = “” .CenterHeader …

Get Macro Code

Add Header and Footer Date

Sub DateInHeader() With ActiveSheet.PageSetup .LeftHeader = “” .CenterHeader = “&D” .RightHeader = “” .LeftFooter = “” .CenterFooter = “” .RightFooter …

Get Macro Code

Open Calculator

Sub OpenCalculator() Application.ActivateMicrosoftApp Index:=0 End Sub In Windows, there is a specific calculator and by using this macro code you …

Get Macro Code

Unmerge Cells

Sub UnmergeCells() Selection.UnMerge End Sub Unmerge Cells code simply uses the unmerge options which you have on the HOME‌ tab. …

Get Macro Code

Remove Text Wrap

Sub RemoveTextWrap() Range(“A1”).WrapText = False End Sub This code will help you to remove text wrap from the entire worksheet …

Get Macro Code

Auto Fit Rows

Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub You can use this code to auto-fit all the rows in a worksheet. When …

Get Macro Code

Auto Fit Columns

Sub AutoFitColumns() Cells.Select Cells.EntireColumn.AutoFit End Sub Use above Excel macro code to auto fit rows in all your sheet at …

Get Macro Code

Insert Multiple Columns

Sub InsertMultipleColumns() Dim i As Integer Dim j As Integer ActiveCell.EntireColumn.Select On Error GoTo Last i = InputBox(“Enter number of …

Get Macro Code

Insert Multiple Rows

Sub InsertMultipleRows() Dim i As Integer Dim j As Integer ActiveCell.EntireRow.Select On Error GoTo Last i = InputBox(“Enter number of …

Get Macro Code