How to Create a Data Input Form in Microsoft Excel

Forms help you input data into a spreadsheet more easily. We'll look at a data input form. We're going to see how helpful a data input form is, and how easy it is to create one. In fact, you won't be creating one at all - Excel does all the work for you. First, the problem.

Data Input Forms

Start a new spreadsheet. Enter January in cell A1. AutoFill the months up to October in column J. Then do the following:

• 
  
  Highlight the entire A column by clicking on the letter A at the top of the spreadsheet


• 
  
  Hold down your left mouse button on the letter A of the column


• 
  
  Keep it held down and drag to the right


• 
  
  Keep dragging until you have highlighted all the columns from A to J


• 
  
  Your highlighted spreadsheet will look like this:

• 
  
  Once you have the columns A to J highlighted, click on Format from the menu bar


• 
  
  From the drop down menu, select Column. A sub menu appears


• 
  
  From the sub menu, click on Width


• 
  
  The following little dialogue box pops up:

• 
  
  Change your Column width to 15 and then click OK

What should happen is that columns H to J disappear from the screen, and you can no longer see August to October. Now enter a number for each month. Any number will do.

What happened? You should find yourself having to scroll across the spreadsheet in order to enter number for the months not on the screen. When you got to October, only the months May to October are visible. If you want to enter some more numbers, starting with January again, you have to scroll back to the start.

Clearly this is a problem, if you have lots of data to enter. You don't really want to be scrolling backwards and forwards. Especially if you had over a hundred rows to fill. It would drive you nuts!

There is an easier way - use a Form for the data inputting.

To see how much more simple your life would be, do the following:

• 
  
  Click inside cell A3 of your spreadsheet


• 
  
  From the Excel menu bar, click on Data


• 
  
  From the drop down list, click Form


• 
  
  A form like the one below should pop up on top of your spreadsheet:

As you can see, the labels for the months are on the left. To the right of each month there is a text box. The numbers currently in them are the numbers inputted on the spreadsheet.

• 
  
  Click the New button at the top


• 
  
  The text boxes go blank


• 
  
  Click inside the January text box and enter a new number


• 
  
  Enter new number for the rest of the months


• 
  
  When you have finished, click the New button again

When you click the New button, Excel will enter the numbers into your spreadsheet. The text boxes will be blanked out, ready for some new data.

And that's it. No more scrolling! Easy, hey? The form even gives you button to set up some search criteria (Find and Criteria buttons). When you want to get back to your spreadsheet, just click the Close button.