How to Create a Data Entry Form in Excel [One-Click, No VBA]

Data Entry Form is one of the best ways to enter data in a table.

But, creating a form to enter data it not a cup of cake.

Yes, that's right.

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

The best part about this form is it’s pre-designed 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.

How to Create this Data Entry Form

In a short while, you will discover that how helpful this tool is.

But, this is hard to believe that there is no listing of this data entry form in the ribbon. So, you have to make it appear before we use it.

And 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 if you notice, all the headings of your data table automatically captured in the form.

How to Use this Data Entry Form

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

#1. Create New Entry

To create a new data entry 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.

We have 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 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 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 entries with this data entry form in Excel

#4. Delete Entries

To delete an entry 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.

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.


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.

Have you ever used this form before?

Please share your views in the comment section, I’d love to hear from you and please don’t forget to share this tip with your friends.

You Must Read this Next

Content Protection by


  1. Nang 19 Nov, 18 at 5:53 am - Reply

    how to add dropdown in a form without using VBA?

  2. Dilshad Ahmad 16 Oct, 17 at 12:31 pm - Reply

    It is possible with photo to add.Any option

    • Puneet Gogia 18 Oct, 17 at 8:59 am - Reply

      Not really.

  3. Shyam Sharma 10 Sep, 17 at 4:43 pm - Reply

    Puneet, i am unable to understand the benefit of it as all these can be done in any range or table…….

  4. ajay 28 Aug, 17 at 4:03 am - Reply

    download link pls!!!

  5. Randal McNally 13 Oct, 16 at 12:44 pm - Reply

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

  6. phanish goli 8 Aug, 16 at 12:25 pm - Reply

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

    • Puneet Gogia 8 Aug, 16 at 12:54 pm - Reply

      Hello Phanish,

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

  7. Sandip 12 Jul, 16 at 5:17 pm - Reply

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

    • Puneet Gogia 13 Jul, 16 at 2:08 am - Reply

      You are welcome sandip.

      • Rrk Ramakrishnan 6 Mar, 17 at 11:01 am - Reply

        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 8 Mar, 17 at 2:00 pm - Reply

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

Leave A Comment