How to make and use Excel data entry form

In this tutorial, you will learn how to activate a built-in input form in Excel and how to add a special button to your worksheet to open the form.

When it comes to inputting information in Excel spreadsheets, most people do it the traditional way - cell by cell, row by row. To make the process more use-friendly and less time-consuming, you can use a special data entry form. It will help you focus on individual records, one at a time, without having to scroll horizontally between columns.

Excel data entry form

You may not know it, but Excel has a special form for entering data in a spreadsheet that makes the input process faster, more user-friendly and less error-prone, especially in large worksheets.

How do users normally populate Excel tables? By typing data directly into cells. Sometimes, you scroll up to see the column headings and sometimes scroll to the right and then come back to the beginning of the table. With large data sets, there is a possibility to mix up something and enter information in a wrong cell.

To facilitate input, you can ask Excel to display one row of information in a handy dialog box. Here, you can accurately add new records as well as update and edit the existing ones. You can also set up Data Validation to ensure that only the entries meeting certain criteria are accepted.

Data entry forms are supported in Excel 365, Excel 2019, Excel 2016, Excel 2010 and Excel 2007, but not available in Excel Online (Excel for the web).

Here's how Excel's Data Entry Form works:
Excel Data Entry

Notes:

  • A data form is limited to a maximum of 32 columns / fields.
  • Since a data form is a modal dialog window, it cannot be printed. Moreover, you cannot print a worksheet until you close the data form.

How to add the Form tool to Excel

Although the Form tool exists in all versions of Excel 2007 through Excel 365, it is hidden by default. So, first you need to make it visible by adding the From button to the ribbon or the Quick Access Toolbar, or both.

To add the Form tool to the Quick Access Toolbar (QAT), these are the steps to perform:

  1. Click the small down arrow at the far-right of the QAT, and then choose More Commands in the pop-up menu. Adding a command to the Quick Access Toolbar
  2. In the Excel Options dialog box that opens, under Choose commands from, select All Commands or Commands Not in the Ribbon.
  3. Scroll down the list of command on the left until you see Form… and click on it.
  4. Click the Add button in the middle to move the Form to the list of QAT commands on the right.
  5. Click OK to save the changes and close the dialog. Add the From button to the QAT.

The Form icon will immediately appear in your Quick Access Tollbar and will be available in all your workbooks. The From icon is added to the Quick Access Toolbar.

Believe it or not, you've successfully coped with the main challenge. Once the Form tool is in your Excel, creating a data entry form for any table (a new or existing one) takes just a single button click.

Tip. In a similar fashion, you can place the Form tool on the ribbon. For the detailed instructions, please see How to add a button to Excel ribbon.

How to make data entry form in Excel

A data entry form is only available for a fully functional Excel table. To get the form, you just need to put your data in a table and the click the Form button. The detailed steps follow below:

  1. In your worksheet, type the column headings in the topmost row as usual. If you want an input form for an existing data set, skip this step.
  2. Select any cell in your dataset and click the Ctrl + T keys simultaneously. This will select all the data and convert it to a table.
  3. Place the cursor anywhere within the table and click the Form button. Done!

To keep things simple, let's make this small table as an example: Create a fully functional Excel table.

Clicking the Form button automatically creates a data entry form for your table with fields corresponding to the column headings: Clicking the Form button to create a data entry form for the table.

As you can see, the Excel input form has a handful of different buttons. Here's a brief explanation of what each button does: Buttons of the Excel input form

Aside from the buttons, you can use the following keys for navigation:

  • Tab - get to the next field.
  • Shift + Tab - get to the previous field.
  • Enter - save the current record and start a new one.

How to add a new record

To add a new record to your table using the data entry form, perform these steps:

  1. Select any cell in your table.
  2. Click on the Form button on the Quick Access Toolbar or on the ribbon.
  3. In the input form, click the New button.
  4. Type the information in the appropriate fields.
  5. When done, hit the Enter key or click the New button again. This will add the record to the table and get a blank form for the next record. Add a new record using the data entry form

Tip. To quickly enter specific information, you can use the same shortcuts that you use in your worksheets. For example:

  • Press Ctrl + ; to insert today's date.
  • Press Ctrl + Shift + ; to enter the current time.

To go through the records one by one, you can use the Find Prev and Find Next buttons or the vertical scroll bar. To find records that meet certain conditions, use the Criteria button.

For example, to locate all the projects assigned to the Design dept., type Design in the Department field, and press Find Next: Search for records that meet certain criteria.

The wildcard characters can help you broaden the search. For instance, to discover the projects that contain "skill" anywhere in the project name, type *skill* in the Project field. Use wildcard characters to broaden the search.

When dealing with numbers and dates, the logical operators such as greater than (>), less than (<), equal to (=), not equal to (<>) and others come in handy. For example, to view the records where the start date is prior to 1-Mar-2021, use "<1-Mar-2021" or "<3/1/2021" for the criteria: Use local operator to express numeric criteria.

Note. Criteria on a data form are not case sensitive. Say, when searching for the Design dept., "DESIGN" and "design" will also be found.

How to update and restore records

If some entry is outdated or contains wrong information, you can use the Criteria or navigation buttons to get to the record, update the incorrect field, and press Enter to commit the updated data to the table.

If you made some accidental changes but have not pressed the Enter key yet, you can revert to the original record by clicking the Restore button. If you've already hit Enter and the changes were passed over to the table, press Ctrl + Z to undo. Updating and restoring records

How to use Data Validation with data entry form

To restrict user input to a specific data type, you can set up a data validation rule for one or more columns in your table, and your rules will be automatically carried over to the data entry form.

For example, to limit the Budget to numbers in a given range, we create this rule: Restrict user input using data validation.

If someone tries to enter a value that does not conform to the rule you've set, an error alert will be displayed (either the standard or your custom one): Invalid data error alert

Limitation: drop down lists are unavailable on a form

The use of data validation with input forms has one limitation - drop down lists do not appear in data entry fields. But even though a drop-down list does not show up inside the form, its restrictions are still in effect. If you enter a value that is not in the list, the form will reject it just as Data Validation normally would. Drop down lists are unavailable on data entry forms.

Formulas in data entry forms

In case one or more columns in your table are calculated by formulas, you won't be able to alter those formulas using the form because the formula result appears as text, not as an editable field. Although that may sound like another limitation, in fact it is done for a reason. When you change a table formula in one cell, the formulas in all other cells in the same column change automatically. To avoid messing up your data, formula editing on the input form is blocked.

For example, you could use the following formula to find out if the actual cost is under, over or within the budget based on the 5% threshold:

=IF(ABS([@[Actual cost]]/ [@Budget] - 1)<=5%, "Within budget", IF([@[Actual cost]]/ [@Budget]- 1>5%, "Over budget", IF([@[Actual cost]]/ [@Budget]- 1<5%, "Under budget", "")))

If you are using a Microsoft 365 subscription, then you could wrap the above formula in the LET function like shown below. This will make your formula more compact, easier-to-understand and faster-to-calculate:

=LET(dif, E2/D2-1, IF(ABS(dif)<=5%, "Within budget", IF(dif>5%, "Over budget", IF(dif<5%, "Under budget", "")))) Calculated column in a table

On the data entry form, you will only see a non-editable calculated result, not the formula: On the data entry form, the formula result appears as text.

How to open data entry form with VBA

If you are setting up a table for someone else, your users may not know where to look for the Form button. Moreover, they may not know that such a thing even exists :) To bring it to focus, you can open the input form using a macro. And to run that macro, place a special button directly in the worksheet.

Assuming the current sheet has a table, you need just a single code line to open the form:

Sub OpenDataEntryForm() ActiveSheet.ShowDataForm End Sub

However, there is a crucial caveat - the above code only works if either:

  • Your table begins in A1, or
  • There is a name "Database" referring to your table (a defined name, not a table name!).

To overcome these limitations, you can use the advanced version of the code that specifies the cell where your table begins (B2 in our case), gives the name "database" to the current region, opens the form, and then deletes the name:

Sub OpenDataEntryForm() Dim nName As Name Range("B2").CurrentRegion.Name = "database" ActiveSheet.ShowDataForm For Each nName In ActiveWorkbook.Names If "database" = nName.Name Then nName.Delete Next nName End Sub

Note. If you don't delete the name "database" in the code, you won't be able to open the form (even manually) for any other table in that particular workbook.

To open a data entry form for a table in another sheet, activate the target worksheet (Sheet1 in the below example) before executing the main code:

Sub OpenDataEntryForm() Dim nName As Name Worksheets("Sheet1").Activate Range("B2").CurrentRegion.Name = "database" ActiveSheet.ShowDataForm For Each nName In ActiveWorkbook.Names If "database" = nName.Name Then nName.Delete Next nName End Sub

After inserting the code, you can place a button form control onto your worksheet and assign a macro to it. Or you can create a button from a shape or another object. The detailed instructions are here: How to create a macro button in Excel.

And now, you users can comfortably open the input data form by clicking a colorful vivid button. They do not even have to bother about putting the cursor within the table - the form will open no matter which cell is active at the moment. Open data entry form with VBA

If you have little experience with VBA, a couple of end-to-end tutorials may prove helpful:

Excel data entry form not working

If a data form does not launch in your Excel, the following troubleshooting tips may help determine the reason.

Too many fields in the data form

When attempting to create a data form for a table containing more than 32 columns, you will get this error message: Too many fields in the data form

In this case, you will have to either reduce the number of columns in the source table or insert one or more empty columns breaking your big table into a few smaller ones. And then, you can create a separate data form for each smaller table.

Cannot extend list or database

If you are trying to add a new row of data, but Excel says that it cannot extend a list or database, that means there is some other data below your table and adding a new record would overwrite that existing data. Cannot extend list or database

To resolve this error, remove any data below your table, so it can extend downward. If you do not see anything underneath the last row, the problem is most likely in spaces, empty strings or non-printing characters. In this case, use the Clear All command to clear the workspace.

The cursor is outside the table

When clicking the Form button results in the below error message, most likely the cursor is not within the table. Simply click any cell in the table, and then try to open the form again. An input form does not open because the cursor is outside the table.

There is a named range "Database"

In case there is a range named "database" in your workbook, the form will always refer to that range, even when you open it for a table. Weird! If the "database" range is on another sheet, the following error will occur. To fix it, just give some other name to the range. A named range Database prevents opening the data entry form.

That is how to create and use a data entry form in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel Data Entry Form (.xlsm file)

Table of contents

49 comments

  1. I have a text column for notes. Is there a way to display multiple lines of text in the form?

  2. I have a database of 900+ line of employee details (eg. emp no., name, dept, position.. etc).
    and an excel form template which need to key in user's information data.
    may i know except VLOOKUP and key in emp no. 1 by 1, is there any other easier way to fill in the data automatically?

  3. I got nice job

  4. Hi Svetlana this is really useful, thank you!
    I have created a spreadsheet with the dates across the top as the headers, so that each day is a new form entry.
    I would like to create a form that therefore offers fields from the vertical column, to be completed on a daily basis.
    Can you please advise on how to switch the axis in the table, without my having to recreate the spreadsheet?
    Many thanks
    Tracy

  5. Hi Svetlana,
    Thank you for the awesome tutorial - it's very helpful!
    I have a question related to validation rules inside the forms:
    I have a spreadsheets with the following columns (in that order):
    Name, Data, Region, Route, Comment.
    I added a list validation rule to the Region and Route columns (Route - 1,2,3,4,5 etc. Route - 1A, 2A, 3A, etc.)
    When I enter data in the form and (purposely) enter the wrong data in the Route columns for example, I get the custom validation rule message telling me I entered the wrong, I click 'Retry', enter the correct data and press 'Enter.
    A new entry is added to the table with the information I entered, but any data I entered before the 'Route' column is blank (in this case, the name and data columns). The rest of the columns are populated.
    It appears that if a validation rule is triggered its resets the columns prior to the cell where the validation rule is set.
    Do you know what am I doing wrong and how to solve it?

  6. Sorry, forgot to add this, in my previous posted question...

    I am going to use Data Validation (Custom Formula) to validate if a User is Adding or Removing a row.

    This is checked against a fixed integer compared to another named cell, with (CountA(array of column A)
    And then I have a calculated Validation column, initially set to TRUE.
    Whenever a row is attempted or removed, the Validation Check (for Validation Column) should kick-in when if finds: NumberOfRows static count
    It seems not to be kicking in, at all. Instead is says FALSE and it displays the Validation Error triangle, in the upper right cell corner.
    Any clues why this is so ?

    I surely had hoped, when I attempted a Sheet Protection to cover above situation - to disallow Add/Remove rows - that the Form would adapt to this.
    Instead it throws a general error saying *You have to Unprotect sheet* prior to opening Form.

    • Hi!
      Your request goes beyond the advice we provide on this blog. I can't see your workbook. If you have a specific question about the operation of a function or formula, I will try to answer it.

      • Hllo sir,,
        Could I ask some questions will u be able.to reply???

  7. Excellent and thorough article :-)
    I want to go a bit more deeper into the use case utilizing 'Formulas in data entry forms' you describe.
    I fully understand not to have new/recalculated formulas to occur for entries not being changed by the Data Entry Form.
    So can one rely on this, and making below feasible ?
    ----------------------------------
    Use Case
    I want to have a calculated cell for each row holding the UserID for the user last modifying it. It has obviously to be Calculated, to hinder change by the Form. Its not going to be as a Security Measure, just to ensure that a central team can collate information on who contributed.
    I imagine that a default value is set, also calculated in (a smart way). So any other value that appears in the table column, is there because of someone changed it.

      • Thanks Alexander
        It might be a circumvention, indeed. I will try this.

        But FYI: I did try to code some Event Triggered code, but these seems not to be triggered when using Forms this way :-(

  8. Is there any VBA code that would display the custom drop down lists already built as valiation, on the form? We have a dozen custom drop down lists in our orders worksheet? Size, style, color, shipping method, etc.
    Thanks

  9. Hi, Your tutorial on built-in form was very helpful. I have few questions regarding form outlay, table presentation which if at all possible to alter (customize).
    Is there any way, procedure to change table outlay from vertical to horizontal i.e; if instead of having a long table have it horizontal.
    Second how to change the background color.

    Regards

  10. How to restrict use of special characters in text using data validation?

  11. it still work on sharepoint to?

  12. how does this form work when working on a sheet shared with other users. will they still need to the save the sheet before adding the entry to avoid overlap?

  13. I can't find this Form tool in the Quick Access Toolbar. I am using Mac/microsoft 365 subscription...and the excel is Beta Channel. Is it possibly the reason that this Form Tool can't be found?

    • Hi Christine,

      The Form tool is not visible by default, and you need to add it to the Quick Access Toolbar first. Please check the "How to add the Form tool to Excel" section of this tutorial for the detailed instructions.

  14. Hi , is it possible to create a drop down list within the excel form itself? Thank you

    • Hi Svetlana.
      Thank you for the detailed information.
      I have the same question as Tarmizi Rahman.... I have a dropdown list on one of my columns. When I use the Data Entry Form, I don't get the dropdown list. If I then type in something that is not part of the dropdown list, I get an error message and can retry. This all works, but it would be so much better if the dropdown list was carried through to the form, so that entries can just be selected from the list while in the form.
      Many thanks.
      Rob

  15. Hi! This is so helpful - thank you so much! Is it possible to put a button on one sheet that opens a data entry form for a range in another sheet (in the same workbook)?

    • Hi Keri,

      Thank you for this good question! Sure, it's possible. To our sample code, just add the following line to activate the target worksheet (Sheet1):
      Worksheets("Sheet1").Activate

      I've added this example to the tutorial for other users to know, and you can find the complete code in this section: Open data entry form with VBA.

  16. Hi,
    Love your site.
    I don't see how this works in the online version. Can you verify this works on office (Microsoft 365)?

    • Hi Mickey,

      Data entry form is only available in a desktop app, including Excel for Microsoft 365. In Excel Online it is not supported.

  17. Hello. The "form" option does not appear in my Excel for Mac when I try to add it to the QAT as described.

    • Hello Philip,

      Like all other tutorials on our blog, this one targets Excel for Windows. From this forum thread, it appears that Excel 2019 for Mac does not have the Form feature. But I don't use Mac and cannot state this with certainty.

  18. In the beginning you mentioned this would work with dozens, even hundreds of columns. I keep getting the error that I have too many fields in my form (I have 95 field columns). How can I create a data entry form?

    Thanks,
    Dan

    • Hello Dan,

      Sorry for misleading you, my bad. After additional research, it appears that a data form is limited to only 32 columns. I have added this limitation to the article.

      A possible workaround (if applicable in your case) might be to insert a couple of blank columns breaking your table into 3 smaller tables. And then, you can create three separate data forms.

  19. Hi. I just found your site and it's great!
    In my case, I have months across the top of my table (Col B is Jan, Col c is Feb, etc.) and Expenses are listed by Row (labor Row 2, materials Row 3, etc). I want to create a form to enter next month's data. How can I use a form to do that?

    Thanks
    David

    • I was hoping for the same thing - input by column, not by row. Looks like it's not possible unless you have a ghost/helper worksheet to collect the data by row, then formulas/VBA to transpose it to columns. A lot of work for little benefit :( Looks like we need to request transposed input from Excel.

  20. I am trying to use the below comment in my VBA file to place a formula in the column 'AO' Since the holiday lists might vary based on regions, im using if condition to validate the region from column 'M' and then capture the formula in AO column. But getting Run-time error '1004' error while using the below code. Can anyone help to fix this..

    Dim slr As Long, LastRow As Long, i As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow

    If Range("M" & i).Value = "Texas" Then Range("AO" & i).Formula = "=MAX(0,NETWORKDAYS(MAX(AO$1,$R(i)),MIN(DATE(YEAR(AO$1),MONTH(AO$1)+1,0),$S(i)),$L$3:$L$12))" end if If Range("M" & i).Value = "Oklahoma" Then Range("AO" & i).Formula = "=MAX(0,NETWORKDAYS(MAX(AO$1,$R(i)),MIN(DATE(YEAR(AO$1),MONTH(AO$1)+1,0),$S(i)),$K$3:$K$12))" end if

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)