Use a Form to Enter Data into a Table in Excel

Add to Favorites
Author:

You can enter data into a table in Excel using a form; here I'll show you how to do that.

This is a great feature that makes it more difficult for someone to mess-up entering data into a table.  However, it is not easily accessible.

Steps to use a Form to Enter Data into a Table

This feature is actually hidden in Excel, so first we need to access it and then we can begin.

  1. Go to the File menu, Office button for Excel 2007.  Then click Options. You should now see this window:
  2. Go to the Quick Access Toolbar option on the left.
  3. Under where it says Choose commands from select Commands Not in the Ribbon

  4. Select Form and then click the button Add. You should now see the Form option on the right side of the screen; when you do, hit OK.
  5. Now we should have a small Form button in the Quick Access Toolbar
  6. Select any cell within the table to which you want to add data and then click the Form button.
    You should see a window like this:
  7. To add a new record, simply click the New button.
  8. Fill out the fields.  Notice how you can't enter anything for the % of Goal field because that is a field with a formula in it; this way the form is helping to prevent you from messing-up formulas in the table.
  9. Hit New again if you want to add another piece of data or simply click Close and the data will be added to the table.

Notes

You can also edit, remove, and browse form data by using the Delete, Find Prev, and Find Next buttons.

The Form tool is a great too to use to enter data into a table and also to manage it.  I recommend using it if you are worried about potentially messing-up data in the table.

Download the sample workbook for this tutorial to try this in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Input Form to Get Data and Store it in Another Tab in Excel
Tutorial: How to make a user input form in Excel and have the data stored on another worksheet at th...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Get User Submitted Data from a Prompt in Excel using VBA Macros
Tutorial: How to prompt a user for their input in Excel. There is a simple way to do this using VBA ...
How to Enter Data into Excel
Tutorial: Entering data into Excel is exactly the same across all Excel versions and can be done in ...
How to Create and Manage a Chart in Excel
Tutorial: In this tutorial I am going to introduce you to creating and managing charts in Excel. Bef...
Put Data into a UserForm
Tutorial: How to take data from Excel and put it into a UserForm. This is useful when you use a form...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course