Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

How To Use Default Data Entry Form In Excel [Hidden Feature]

use-default-data-entry-user-form-without-VBADo you know there is no need to write a single line of coding for a user form?

Yes, there is no need to write any type of code for a user form.

This is a firm truth that data forms are widely used to enter data into the spreadsheets.

It is also very right, that we always found data entry easier using the data entry form.

So by taking care of this, Microsoft Excel is loaded with a built-in data entry form.

You can use this form to enter your data into worksheet very easily.

Format of Data entry Form In Excel

Before you use this entry form, the first thing you have to do is to activate this form (not about installing a software).

Activate Default Data Entry Form

In a short while, you will discover that how helpful is this tool is. But, this is hard to believe that there is no listing of this form in the ribbon by Microsoft.

So, we have to just make it appear before we use it. And, before we activate this form, we need a table to enter our data or, at least, we need headers of the data.

A Table Is Required To Use Data entry Form In Excel

To activate this data entry form, click any of the cells in the table or just select one of the heading of your data (If you are creating a fresh table).

Now, use one of the below methods to activate the form.

  • Press Alt + D + O
  • Run this macro
Sub DataForm()
 ActiveSheet.ShowDataForm
End Sub
  • Add data form icon to Quick Access Toolbar. File -> Options -> Quick Access Toolbar -> Choose Command From -> Commands Not In Ribbon -> From -> Add -> Click OK. It will add a form icon in your Quick Access Toolbar.

Add Button To Use Data entry Form In Excel

After using one of the above options, you will get a data entry form like this. And, if you notice all the headings of your data table automatically captured in the form.

Use Data entry Form In Excel With Different Components

How It Works?

Creating New Data Entries:  To Create a new data entry just click on the “New” button.

Fill all the input boxes with data & click the close button.

You can also press enter key once you fill your last input box in the data form. Now, if you look at the below example you’ll find that there is no input form for the amount column.

No, there is no bug in the data form.

I have put 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.

Use Data Entry Form In Excel Add Entry

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.

Use Data Entry Form In Excel Navigate Entry

Edit Entries: To edit an entry you have to first navigate your entry by the using methods mentioned above.

Once you navigate your entry, you can make your changes in any of your input boxes & after that just the click close button or 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.Use Data Entry Form In Excel Edit Entry

Delete Entries: To delete an entry you have to navigate to your entry & click on the delete button.

A pop window will appear for the confirmation of the deletion. It will instantly delete that entry from your data table.

Use Data Entry Form In Excel Delete Entry

Find Specific Entries: If you are working on a large data, you can use “Criteria” button to find some specific data entries.

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 & press enter.

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

Use Data Entry Form In Excel Find Entry

Close Form: To the close data entry form, just click on the close button & it will disappear.

Important Points To Remember

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

  • The maximum number of input boxes you can use in data entry form is 32.
  • The width of the input box is purely connected with the related column in the worksheet. If your worksheet columns are not sufficiently wide to show values, the same width will reflect in your data form also. And, on the other hand, if only one of the column is over in width, the same width will reflect for all input boxes in data form.

Automatic Formatting If You Use Data Entry Form In Excel

  • If you have a range with the name of “Database” then there is no need to select any cell from the table. It will always insert data in that table.
  • While using criteria to pull some specific data, you can use Wildcard Characters for a smart play.
  • While creating data entries if you will try to overwrite the data in the cell, excel will give you an alert message “Cannot extend list or database”.

Conclusion

In the end, I really want to say that this data entry form is very useful although it has very few functions and if your requirement is not so high you can use this tool rather than clubbing in some high-grade user-forms.

I hope you can make best out of it.

Over To You

Now, a quick question to you. Have you used this data entry form ever before?

Tell me in the comment box below.



  • Pingback: Find & Replace Option Can Do These Fantastic Things for You - ExcelChamps()

  • Pingback: Find & Replace option can do these fantastic things for you()

  • Sandip

    No. It’s awesome. Thank you so much.

    • Puneet Gogia

      You are welcome sandip.

      • Rrk Ramakrishnan

        is it possible to edit the underlying vba code to format the data to autofit in excel columns ? thanks in advance for your guidance.

        • Puneet Gogia

          You can use a separate VBA code to format your data instantly when you insert a data entry.

  • phanish goli

    how can i take a printout of the form with all details

    • Puneet Gogia

      Hello Phanish,

      Only Possibility is, to capture screen first. And, then take printout.

  • Randal McNally

    Is it possible to change the form itself? Like – move the text boxes and controls around a bit?