Cookies in Use Read more: Cookie Button:Get a free "Cookies in Use" button for your website | Attacat of Edinburgh



 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy

If any of my FREE Excel Templates, tutorials or Excel VBA resources have helped you, please spare 2 seconds to click the sponsored ad on this page. Although the revenue generated is no way near the cost of tipping me a beer, the pennies certainly help to pay for the web hosting and to keep the Excel content updated.

Adding Data using Excel’s DataForm

Not many users realise that Excel has an in-built Data Entry Form for adding and working with data that is set up correctly. Basically the DataForm is the same in all current versions of Excel. However, accessing it changed with the introduction of Excel 2007. In earlier versions of Excel  the DataForm was displayed simply by opening the Data menu then clicking Form. In later versions the command has to be added manually before you can display the DataForm.

Note: The following steps only apply to Excel 2007 and later versions of Excel

Adding the Dataform to the QAT

To add the DataForm, click on the Office Button in the top left of Excel, for 2007 users. From the Office button menu, click on Excel Options. For Excel 2010 and 2013 users, click the File tab in the top left, from the File menu, click Options.         

You could add the Form to the Data Tab, but a good place would be the Quick Access Tab or QAT. Click the Customization item on the left in Excel 2007, in Excel 2010 and 2013 there is a Quick Access Toolbar item, click that instead of Customization. The idea is that you can place any items you like on the Quick Access toolbar at the top of the Excel window. You pick one from the list, and then click the Add button in the middle. This means exactly what it’s name implies - you have quick access to frequently used commands.

To add the DataForm option to the Quick Access Toolbar, click the drop down list where it says Choose Commands From and scroll down to select Commands Not in the Ribbon.   

The list will change, allowing you to select the item that you require. Choose Form then click the Add button in the middle and Form will be added to the right hand list.

You can add other items  to the Quick Access Toolbar. When you are ready, click OK and you will see the QAT looking something like this.

Now whenever you want to add data to a database in Excel you can click this button and the Excel DataForm will open customised to your data headings.

Without the use of VBA the dataform must be displayed on the sheet containing the data and a cell within the data must be selected to allow it to be displayed. Using VBA you can build your own UserForm which can be customised to simply data input with Controls such as ComboBoxes, Calendars etc. Alternatively you can format a different sheet to act as an input form. These alternatives are discussed in separate articles.

Using the DataForm

You can use a DataForm to add, find, change, and delete rows in a your Excel Database.  To add a new row of data, in the DataForm, click New. Type the data for the new row, use Tab to move to the next field in the row. To move to the previous field, press SHIFT+TAB. Once the entry is entered to the DataForm  press ENTER to add the row to the bottom of the database.

Tip:  Before you press ENTER, you can undo any changes by clicking Restore. Any data that you have typed in the fields is discarded.

The Delete button is used to delete records from the database.

The Restore button - This button can be used to undo changes to a record that is being edited. Occasionally, we make the wrong changes to a record or even edit the wrong record all together. If so, the restore button can be used to undo those changes.

Tip: the restore button only works as long as a record is present in the form. As soon as you access another record or close the form, the restore button becomes inactive.

The Criteria button allows you to search the database for records based on specific criteria, such as name, age, or gender. An example of using the Criteria button is included in the next step of the tutorial.

Searching for Records Using One Field Name

  1. Click on the Criteria button in the form.
  2. Clicking on the Criteria button clears all the form fields but does not remove any data from the database.
  3. Click on the any field and type the word that you want to search for.
  4. Click on the Find Next button. The first record matching that word will be displayed.
  5. Click on the Find Next button to display the next matching record..

Searching for Records Using Multiple Field Names

  1. It’s possible to search for records using multiple fields. Only those records that match all the criteria should be displayed in the form.
  2. Click on the Criteria button in the form.
  3. Click on each field that you want to use in the search and type
  4. Click on the Find Next button. The first record matching the criteria should appear in the form.
  5. Click on the Find Next button a second time the next should be displayed.
  6. Repeat this to check the whole database

Related articles

Use a Worksheet as a Data Entry Form

Build a Data Entry Form

Click to view full sized image