How to create a form for Data Entry in Excel

Have you ever created a Workbook in Excel as part of a business process and defined how you wanted people to enter the data just to find that they disregard your method and just put the information anywhere. This can be frustrating but will involve you being the one who has to reformat the data. This article explains how to create a form for data entry in excel

In this scenario I find it makes sense to control how people enter the data. If I give people a form to enter the data I can validate it and control where it gets added. The following is a very simple example of a form (and yes I am using an example from the online course):

The above is a good example of the advantages of using forms. The lists are being populated from other sheets that could also be hidden. You should never have to manually look up a code in another sheet when you can pick it from a friendly list.

Once the data is entered we can then add it to the correct sheet in the correct location so no more having to reformat the data. Now with Excel 2010 and 2013 we can add our own tabs to the Ribbon and have a button that brings up the form or even have an auto open macro that opens the form when the Workbook is opened or a specific Worksheet is selected.

If you want to try creating your own forms you will need to open the Microsoft Visual Basic for Applications editor using either the Developer tab on the Ribbon or the shortcut ALT+F11. You can then right click over VBAProject and select Insert + UserForm.

This should create a new blank form and also should display the Toolbox:

This allows you to add Buttons, Lists, Text Boxes and other input controls to present to the user. If you want to test your form you can press the F5 key and the form should be displayed over the currently active sheet in Excel.

There really is no limit to the things you can do using Macros and Excel VBA all you need is time and practice to master this advanced topic and then the imagination to put into place your own custom functionality to make using Excel for your needs easier.

[et_social_follow icon_style="slide" icon_shape="rectangle" icons_location="top" col_number="auto" outer_color="dark"]

Be Brilliant at Excel. Save Hours each week and add Professional Certification to Your Resume

Even Microsoft use us to teach their employees Excel

Get access to The Ultimate Excel Training Course Bundle