1. Excel Forms Online
2. Office Forms Excel Online
3. Create Fillable Form In Excel
4. Excel To Web Form
5. Forms In Excel Online

Data entry can sometimes be a big part of using Excel.

How to Create Online Forms in Excel Step 1. Download a utility to convert Microsoft Excel spreadsheets into online HTML forms. Excel does not offer native. Install the spreadsheet utility from the downloaded file. Spreadsheet Converter downloads as an '.exe' file. Creating a Form in Excel Online If you’re working with Excel Online, you can also create forms. Go to the Insert tab ➜ click on the Forms button ➜ select New Form from the menu. This will create a form that’s linked to the current workbook. Collaborate for free with an online version of Microsoft Excel. Save spreadsheets in OneDrive. Share them with others and work together at the same time.

With near endless cells, it can be hard for the person inputting data to know where to put what data.

A data entry form can solve this problem and help guide the user to input the correct data in the correct place.

Excel has had VBA user forms for a long time, but they are complicated to set up and not very flexible to change.

In this blog post, we’re going to explore 5 easy ways to create a data entry form for Excel.

Video Tutorial

Excel Tables

We’ve had Excel tables since Excel 2007.

They’re perfect data containers and can be used as a simple data entry form.

Creating a table is easy.

1. Select the range of data including the column headings.
2. Go to the Insert tab in the ribbon.
3. Press the Table button in the Tables section.

We can also use a keyboard shortcut to create a table. The Ctrl + T keyboard shortcut will do the same thing.

Make sure the Create Table dialog box has the My table has headers option checked and press the OK button.

We now have our data inside an Excel table and we can use this to enter new data.

To add new data into our table we can start typing a new entry into the cells directly below the table and the table will absorb the new data.

We can use the Tab key instead of Enter while entering our data. This will cause the active cell cursor to move to the right instead of down so we can add the next value into our record.

When the active cell cursor is in the last cell of the table (lower right cell), pressing the Tab key will create a new empty row in the table ready for the next entry.

This is a perfect and simple data entry form.

Data Entry Form

Excel actually has a hidden data entry form and we can access it by adding the command to the Quick Access Toolbar.

Add the form command to the Quick Access Toolbar.

1. Right click anywhere on the quick quick access toolbar.
2. Select Customize Quick Access Toolbar from the menu options.

This will open up the Excel option menu on the Quick Access Toolbar tab.

1. Select Commands Not in the Ribbon.
2. Select Form from the list of available commands. Press F to jump to the commands starting with F.
3. Press the Add button to add the command into the quick access toolbar.
4. Press the OK button.

We can then open up data entry form for any set of data.

1. Select a cell inside the data which we want to create a data entry form with.
2. Click on the Form icon in the quick access toolbar area.

This will open up a customized data entry form based on the fields in our data.

Microsoft Forms

If we need a simple data entry form, why not use Microsoft Forms?

This form option will require our Excel workbook to be saved into SharePoint or OneDrive.

The form will be in a browser and not in Excel, but we can link the form to an Excel workbook so that all the data goes into our Excel table.

This is a great option if multiple people or people outside our organization need to input data into the Excel workbook.

We need to create a Form for Excel in either SharePoint or OneDrive. The process is the same for both SharePoint or OneDrive.

1. Go to a SharePoint document library or a OneDrive folder where the Excel workbook is going to be saved.
2. Click on New and then choose Forms for Excel.

This will prompt us to name the Excel workbook and open up a new browser tab where we can build our form by adding different types of questions.

We first need to create the Form and this will create the table in our Excel workbook where the data will get populated.

Then we can share the form with anyone we want to input data into Excel.

When a user enters data into the form and presses the submit button, that data will automatically show up into our Excel workbook.

Power Apps

Power Apps is a flexible drag and drop formula based app building platform from Microsoft.

Excel Forms Online

We can certainly use it to create a data entry from for our Excel data.

In fact, if we have a table of data set up, Power Apps will create the app for us based on our data. It can’t be any easier than that.

Sign in to the powerapps.microsoft.com service ➜ go to the Create tab in the navigation pane ➜ select Excel Online.

We’ll then be prompted to sign in to our SharePoint or OneDrive account where our Excel file is saved to select the Excel workbook and table with our data.

This will generate us a fully functional three screen data entry app.

1. We can search and view all the records in our Excel table in a scroll-able gallery.
2. We can view an individual record in our data.
3. We can edit an existing record or add new records.

This is all connected to our Excel table, so any changes or additions from the app will show up in Excel.

Power Automate

Power Automate is a cloud based tool for automating task between apps.

But we can use the button trigger to make an automation that captures user input and adds the data into an Excel table.

We’ll need to have our Excel workbook saved in OneDrive or SharePoint and have a table already setup with the fields we want to populate.

To create our Power Automate data entry form.

1. Go to flow.microsoft.com and sign in.
2. Go to the Create tab.
3. Create an Instant flow.
4. Give the flow a name.
5. Choose the Manually trigger a flow option as the trigger.
6. Press the Create button.

This will open up the Power Automate builder and we can build our automation.

1. Click on the Manually trigger a flow block to expand the trigger’s options. This is where we’ll find the ability to add input fields.
2. Click on the Add an input button. This will give us options to add a few different types of input fields including Text, Yes/No, Files, Email, Number and Dates.
3. Rename the field to something descriptive. This will help the user know what type of data to input when they run this automation.
4. Click on the three ellipses to the right of each field to change the input options. We’ll be able to Add a drop-down list of option, Add a multi-select list of options, Make the field optional or Delete the field from this menu.
5. After we have added all our input fields, we can now add a New step to the automation.

Search for the Excel connector and add the Add a row into a table action. If you’re on an Office 365 business account, use the Excel Online (Business) connectors, otherwise use the Excel Online (OneDrive) connectors.

Now we can set up our Excel Add a row into a table step.

1. Navigate to the Excel file and table where we are going to be adding data.
2. After selecting the table, the fields in that table will appear listed and we can add the appropriate dynamic content from the Manually trigger a flow trigger step.

Office Forms Excel Online

Now we can run our Flow from the Power Automate service.

1. Go to My flows in the left navigation pane.
2. Go to the My flows tab.
3. Find the flow in the list of available flows and click on the Run button.
4. A side pane will pop up with our inputs and we can enter our data.
5. Click Run flow.

We can also run this from our mobile device with the Power Automate apps.

1. Go to the Buttons section in the app.
2. Press on the flow to run.
3. Enter the data inputs in the form.
4. Press on the DONE button in the top right.

Whichever way we run the flow, a few seconds later the data will appear in our Excel table.

Conclusions

Whether we require a simple form or something more complex and customize-able, there is a solution for our data entry needs.

We can quickly create something inside our workbook or use an external solution that connects to and loads data into Excel.

We can even create forms that people outside our organization can use to populate our spreadsheets.

Let me know in the comments what is your favourite data entry form option.

Using Excel to store data records is an oft-used — if sometimes frustrating — alternative 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 you’re looking for something easier, download the free Excel template to get started immediately.

Download a Free Excel Data Entry Form Template

Create Fillable Form In Excel

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

See how Smartsheet can help you be more effective

Watch the demo to see how you can more effectively manage your team, projects, and processes with real-time work management in Smartsheet.

What Are Excel Forms?

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

The example below has two fields per record. The form allows up to 32 fields per record.

In addition to the navigation buttons, you can use the following keyboard commands to navigate through the form:

• Tab: Move to the next field.
• Shift+Tab: Move to the previous field.
• Enter: Save the record and start a new one.

Benefits of Using an Excel Data Entry Form

In addition to being able to enter data quickly and accurately, the form allows users to see more content without scrolling because the data appears in a vertical format (rather than horizontal). The form can also include data validation, including a dropdown list of pre-selected items in a column 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 sheet, 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 Data Entry Form in Excel 2016?

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

1. On the chosen sheet, highlight the number of columns needed.
2. Open the Tables tab, click New, click Insert Table with Headers.
3. Change the default column headers, and adjust the width of columns if necessary.
4. Open the Data menu and click Form...
5. The form will appear. The number of columns in the table will match the number of fields on the form. The column titles in the table will be the field titles on the form. You are now 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. Press Enter on your keyboard to save the data and move to the next record.

Finding a Record

Once you have entered data, you may want to find a record. To do so, click Criteria, enter the search term into the appropriate field (e.g. “Chicago” into a city field). Click Find Next. The first matching record will appear in the form (the records displayed in the sheet won’t be filtered). Click Find Next to scroll through the remaining records, or click Find Previous to scroll upward. You can use 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 want to change and edit the text. Hit Enter on your keyboard to save changes.

Deleting a Record

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

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

The steps for adding, finding, editing, and deleting a record are the same as for Excel 2016, so follow the steps above. The appearance may be slightly different. In general, the steps are to select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu.

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

In general, the steps are to select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu. You can follow the steps for adding, finding, editing, and deleting a record in Excel 2016 above. Note that the interface may be slightly different.

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

Select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu. Follow the steps above for Excel 2016 to add, find, edit, or delete. The interface may be slightly different.

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

1. You can save a step by adding a Form command button to the toolbar. To do this, right-click a toolbar, and click Customize Toolbars and Menus…
2. Next, click Commands, click All Command, scroll to and click Form… Drag it to the toolbar of your choice.
3. After you’ve set up the table, select it, and then click the Forms command that you’ve added.

Adding a Drop-Down List to an Excel Data Entry Form

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

1. Add a new sheet to the workbook (you can also 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 change the name of the sheet to something like “data.” Double-click the new sheet’s title tab and type the new name.
   
2. Enter the list of options into a column in the new sheet. If more than one field in the form needs a dropdown list, repeat the process in another column. If you have more than one field with a dropdown list, the data column for each list can be on the same sheet or on different sheets, depending on your preference.
3. Return to the worksheet with the table and select the column that will have the dropdown list.
4. On the ribbon, click Data, click Validate, click arrow next to the Allow field and click List.
   
   Ensure the In-cell dropdown option is checked and put the cursor in the Source field.
5. Click on the tab for the sheet with the list (in our example, the Data sheet), highlight the list of items (the sheet and cell reference will be added to the source field), and click OK.
6. Show the data entry form based on the steps above. The field connected to that column will only allow entries from the list on the other sheet.
   

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

Other Types of Cell Validation

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

• Make a Column a Date or Time: Select Date or Time. If needed, enter start and end values to constrain the starting and ending date or times.
• Make a Column 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 end values to constrain the range.

How Do I Create a Submit Button in Excel?

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

There are two ways to create a button. One uses ActiveX, the other uses Visual Basic (VBA), so they are beyond the scope of this article. If you have experience with ActiveX or Visual Basic, Microsoft's support site offers instructions.

Excel To Web Form

How Do I Insert a UserForm in Excel?

A UserForm is an Excel tool that is similar to a data entry form. However, it is more flexible because it allows the use of checkboxes, radio buttons, multiple selections, and scrolling lists to enter data in addition to text boxes. Creating Userforms requires knowledge of Visual Basic. If you have experience with Visual Basic, a tutorial for creating UserForms can be found here or here.

How Do I Create a Data Entry Form with VBA?

A data entry form can be created with VBA in the same 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 adapt as those needs change.

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.

Forms In Excel Online