How Does Data Template Work

Using Excel to store data records is an oft-used — if sometimes frustrating — culling to setting up a database. However, the data entry form in Excel is a pivotal tool. This article provides all the steps necessary to get started with the Excel data entry form. If y'all're looking for something easier, download the free Excel template to get started immediately.

Download a Costless Excel Data Entry Course Template

Excel Data Entry Custom Template

Download Excel Data Entry Grade Template

Use this gratuitous template to create a customized data entry form that allows you to apply multiple restrictions to a field (such equally numeric and five digits long for a ZIP lawmaking field), make a field mandatory, or brand it unique from all other records (for instance, for an order number). Enter the number of fields needed for each record, select a title and options for each field in the tape, and then motility to the second sheet to start entering data.

Meet how Smartsheet can assist you be more than effective

Picket the demo to run into how you can more finer manage your squad, projects, and processes with existent-time piece of work management in Smartsheet.

Watch a free demo

What Are Excel Forms?

Excel offers the power to make data entry easier by using a form, which is a dialog box with the fields for one tape. The class allows data entry, a search office for existing entries, and the ability to edit or delete the data.

The instance below has two fields per record. The grade allows upwardly to 32 fields per record.

In add-on to the navigation buttons, you tin use the following keyboard commands to navigate through the course:

Tab: Motion to the side by side field.
Shift+Tab: Move to the previous field.
Enter: Save the record and offset a new one.

Benefits of Using an Excel Data Entry Form

In addition to beingness able to enter data rapidly and accurately, the form allows users to run across more content without scrolling because the data appears in a vertical format (rather than horizontal). The course tin as well include information validation, including a dropdown list of pre-selected items in a cavalcade or ensuring that an entry meets certain criteria (such as a date or character length).

How Do I Create a Data Entry Form in Excel?

In general, the steps are to select an area in the sail, insert a table, add titles at the top of the columns, and then find the form option in the data menu. To create a form, see the steps below for your version of Excel.

How Do I Create a Information Entry Course in Excel 2016?

These instructions also use to Excel 365, and to Excel 2019 for PC. These steps were created with a different version of Excel so the interface will expect different, but the steps are the same.

On the chosen sheet, highlight the number of columns needed.
Open the Tables tab, click New, click Insert Table with Headers.
Change the default cavalcade headers, and adjust the width of columns if necessary.
Open the Data card and click Form...
The class will announced. The number of columns in the tabular array will lucifer the number of fields on the class. The column titles in the table will be the field titles on the form. You are at present ready to enter data records into the form.

Inserting a Record

Type data into the first field, tab to the next field, and repeat until all fields are completed. PressEnter on your keyboard to save the data and move to the side by side record.

Excel Data Entry Form With Data

Finding a Record

In one case you take entered information, you may want to find a record. To do then, click Criteria, enter the search term into the appropriate field (e.g. "Chicago" into a city field). Click Find Adjacent. The kickoff matching tape will appear in the form (the records displayed in the sheet won't exist filtered). Click Find Next to scroll through the remaining records, or click Observe Previous to scroll upward. You can utilize wildcard characters such as "*" and "?" when searching the records.

Editing a Record

Once the desired record is displayed in the form, select the field you lot desire to change and edit the text. Hitting Enter on your keyboard to save changes.

Deleting a Record

Once the desired record is displayed in the grade, click Delete, then click OK.

How Practice I Create a Data Entry Class in Excel 2013?

The steps for calculation, finding, editing, and deleting a tape are the same as for Excel 2016, then follow the steps to a higher place. The appearance may be slightly different. In general, the steps are to select an area in the sheet, insert a tabular array, add together titles at the top of the columns, and and then observe the form option in the data menu.

How Do I Create a Data Entry Form in Excel 2010?

In full general, the steps are to select an expanse in the sheet, insert a table, add titles at the top of the columns, and then observe the form option in the data card. You can follow the steps for calculation, finding, editing, and deleting a record in Excel 2016 higher up. Note that the interface may be slightly different.

How Exercise I Create a Data Entry Form in Excel 2007?

Select an area in the sheet, insert a tabular array, add titles at the top of the columns, then discover the form option in the data menu. Follow the steps higher up for Excel 2016 to add, find, edit, or delete. The interface may be slightly unlike.

How Do I Create a Information Entry Form with a Single Click?

You tin relieve a step past adding a Form control button to the toolbar. To do this, right-click a toolbar, and click Customize Toolbars and Menus…
Next, click Commands, click All Command, scroll to and click Form… Drag it to the toolbar of your choice.
Afterward you've set upwardly the table, select information technology, and and then click the Forms command that you've added.

Calculation a Drib-Down List to an Excel Data Entry Form

A drop-down card presents a predefined list of options in a information entry form field. This is useful when you need to enter a set list of employees, products, part numbers, etc. into a field.

Add a new sail to the workbook (you lot can likewise use a blank sheet) by clicking the plus sign on the tabs at the bottom of the window.

While not required, it makes sense to modify the name of the sheet to something like "data." Double-click the new canvas'southward championship tab and type the new name.
Enter the list of options into a column in the new sheet. If more than than one field in the form needs a dropdown list, repeat the process in some other column. If you accept more one field with a dropdown list, the data column for each list tin can be on the aforementioned sheet or on different sheets, depending on your preference.
Return to the worksheet with the table and select the column that will have the dropdown list.
On the ribbon, click Data, click Validate, click arrow next to the Allow field and click List.

Ensure the In-cell dropdown selection is checked and put the cursor in the Source field.
Click on the tab for the sail with the list (in our instance, the Data sheet), highlight the list of items (the sheet and jail cell reference will be added to the source field), and click OK.
Show the data entry grade based on the steps above. The field connected to that column will only let entries from the list on the other canvass.

Note: In some older versions of Excel, the dropdown will not appear in the course, but the field volition still be restricted to the list in on the data sheet.

Other Types of Cell Validation

In addition to a restricted list, the grade fields tin have other restrictions. Select the column and click Data on the ribbon, click Validate, click the Allow field, and so choose one of the options below:

Make a Column a Engagement or Time: Select Date or Time. If needed, enter start and stop values to constrain the starting and ending date or times.
Brand a Cavalcade Numeric: Select Whole number or Decimal. If needed, enter start and/or ending values to constrain the range.
Restrict the Length: Select Text length. If needed, enter start and finish values to constrain the range.

How Do I Create a Submit Button in Excel?

A submit button is a user-created command button that runs a macro when clicked. The macro can exist any valid macro, such as sorting data, removing blank rows, or saving a sail as a PDF.

There are ii ways to create a push. 1 uses ActiveX, the other uses Visual Bones (VBA), so they are across the telescopic of this article. If y'all have experience with ActiveX or Visual Basic, Microsoft'due south support site offers instructions.

How Do I Insert a UserForm in Excel?

A UserForm is an Excel tool that is like to a information entry form. However, it is more than flexible considering information technology allows the use of checkboxes, radio buttons, multiple selections, and scrolling lists to enter information in addition to text boxes. Creating Userforms requires knowledge of Visual Basic. If y'all have experience with Visual Basic, a tutorial for creating UserForms tin can be found hither or here.

How Do I Create a Data Entry Class with VBA?

A information entry form tin can be created with VBA in the aforementioned way as a UserForm in Excel.

Streamline Data Collection with Smartsheet Forms

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and suit every bit those needs change.

The Smartsheet platform makes information technology like shooting fish in a barrel to plan, capture, manage, and study on piece of work from anywhere, helping your team be more effective and get more than done. Report on key metrics and go real-fourth dimension visibility into work as information technology happens with gyre-up reports, dashboards, and automated workflows built to keep your team connected and informed.

When teams accept clarity into the work getting done, in that location's no telling how much more than they can accomplish in the same amount of time. Try Smartsheet for complimentary, today.