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.

Question? Ask it in our Excel Forum

Downloadable Files: Excel File