How to create a data entry form in Excel

A spreadsheet is a great place to enter tables of data, such as price lists, but to enter records a row at a time from left to right isn’t always user friendly. Did you know that there’s a handy form feature so you can create a data entry form, making it much more intuitive to add information manually to your spreadsheet?

A data entry form can be really useful when you have a table that has more columns of data than can be viewed on the screen as you’ll see them in a handy list.

First you need to convert your column names into a Table, by clicking on one of the headings then selecting Insert> Table

When the pop up box appears, you should see all the columns have been found. Make sure my table has headers is also checked.

Now go to File > Options then choose Customize Ribbon.

On the left hand side of the page,In the Choose commands from box, Select Commands Not in the Ribbon and then Form.

On the right hand side of the page, in Customise the Ribbon, find a convenient place to add the new Form button. I’ve put my under Insert by clicking on the word insert then select New Group at the bottom of the list of options. When the group appears right-click on the name and rename it Forms.

Alternatively you could just easily use New Tab feature to create an all new menu item on the ribbon.

Click Add, then click OK

Back in your worksheet, click inside your table, and on the Insert menu, in your new Forms group, select Form.

A new Form box will pop up.

Now you can input your data into each box, just pressing the tab key or clicking in the next one to enter something.

Clicking New will save the records you’re doing to the table underneath and you can repeat this for all the records you want to add.

Click Close to get out of this screen and see the data in your Excel Table.

You can open the form whenever you want to add some more data in future.

You can also use the same form to filter through your data once it’s been entered. For example clicking Criteria will allow you to enter values in fields and then you’ll be shown only the records that match those multiple criteria, which is very handy when looking for something quickly.