How To Create A Form From Excel Spreadsheet

Entering data on Excel is a daunting task, especially if you have a lot of columns and tons of entries. It can also be confusing to other users, especially if they aren't familiar with your spreadsheet.

But did you know you can create forms to make this task easier? Microsoft lets you or anybody else add data to your spreadsheet via a form in a new window. That way, there's less confusion on what info they should put in the Excel file.

Read on and learn how to create a form for your own Excel spreadsheet.

Preparing Your Data

Before you start building your form, you must first prepare the data you're going to need. For example, if you're creating a student attendance list, you must write down the information you will require from them in columns first.

For example, you need to have their first name, last name, student number, email address, and course; then, you should list these along the top row. These items would signify the columns where the data your students enter would be compiled under.

If you're going to ask your students to enter their details manually, they will have to scroll down to the bottom of the Excel file to find an empty row. And if you're asking for a lot of information, say for a yearbook, it would be easy for any of the students to skip a cell, leading to incomplete data.

Inserting a Simple Form

Once you've prepared all your column titles, you can now start creating a form. To do so, select one of the column headers. Go to the Search Bar at the topmost section of the Excel window and type Form. In the search results, under Actions, click on Form.

A Microsoft Excel warning window will pop up, confirming if you want to use your selected row as the form labels. If you've already done so, click on OK. Otherwise, click Cancel, select the column headers you want to use as the form labels, and repeat the steps outlined in the previous paragraph.

After pressing OK, a form window will appear, and that's it! You're now free to start entering data.

If you're going to use this tool frequently, you can add a shortcut to this feature in your Quick Access Toolbar. Return to the Search Bar and type in Form again. But instead of left-clicking on the Form result, right-click on it instead to bring up the context menu.

Select Add to Quick Access Toolbar, and you will see the Form icon appear in it. So the next time you need to pull up the form tool, all you need is to click on the shortcut button.

Adding New Data

With the open form window, you'll see the information you need to enter into your database. Fill out all the empty fields, and once done, click on New. The data you typed will automatically populate the bottom-most part of your table, and you'll get a new blank form ready to go.

In case you accidentally saved the wrong data, you can go back and change it. Browse to the entry you wish to change by pressing the Find Prev or Find Next buttons. When you've reached the item you want to edit, simply change the information on the relevant field.

Once you've finished your corrections, click New or press Enter on your keyboard to commit the changes. But if you've made a typographical error and wish to restore the previous entry, click Restore instead. However, note that you cannot undo the changes with Restore once you've committed the changes.

Browsing Entries With the Form Window

Now that you have several entries into your spreadsheet, you can use the form window itself to browse your data. Near the top right corner of the window, you'll see the current data's serial number.

If you want to see the entries before or after the current selection, you can use the previously mentioned Find Prev and Find Next buttons. Alternatively, you can use the scroll bar to jump through multiple entries quickly.

To find specific results, you can use the form window's search function. To access it, click on the Criteria button right below, Find Next. Type in the information you want to search in the relevant field, then press Enter on your keyboard.

The window will then show you the next row with the pertinent information. To browse through other results, click on either Find Prev or Find Next.

You can also use formulas and modifiers for a more advanced search. You can use the wildcard asterisk to search for a part of record. Likewise, you can also use greater than or less than signs to search for numerical or date values.

For example, if you want to look for students taking up a BA course, you can input BA* in the appropriate field. The form window will only show students from your spreadsheet that are taking up a BA degree.

Avoiding Blank Entries

You can also prevent accidentally blank entries by adding using the Data Validation function. To do this, you first need to select the table where you're going to input data. You should then go to Data > Data Validation.

The Data Validation window will then open. In the Settings tab, under the Allow dropdown menu, select Text length. Then under the Data dropdown menu, pick greater than. Finally, under the Minimum field, input 1. Press OK.

This function prevents users from entering just one character into any field, ensuring that your data will be fully populated.

A Quick and Easy Way to Populate Large Databases

If you frequently miss information on your spreadsheet or put in the wrong data in columns, then it's high time that you make a form for yourself. This quick and easy solution will ensure that you get an accurate database.

Although the example above only shows five entries, if you're working on many entries, like a company employee database, you will appreciate this feature on Excel. By giving you an independent window to input information, you can avoid getting cross-eyed with hundreds, if not thousands, of entries while ensuring accurate and precise data inputs.

How to Merge Microsoft Excel Files and Sheets

Learn everything about how to merge sheets in Excel, plus how to combine multiple Excel files into one.