Use a Form to Enter Data into a Table in Excel

Add to Favorites

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.


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

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...