How to make and use Excel data entry form

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:

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.
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.

The Form icon will immediately appear in your Quick Access Tollbar and will be available in all your workbooks.

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.

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:

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

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

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.

How to search for records

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:

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.

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:

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.

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:

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):

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", ""))))

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

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:

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:

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:

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.

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

• How to insert and run VBA code
• How to run macro in Excel

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.

Practice workbook for download

Excel Data Entry Form (.xlsm file)