MAKING EXCEL WORK FOR YOU
© Roy Cox 2005 to 2013
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
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.
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
Searching for Records Using Multiple Field Names
Use a Worksheet as a Data Entry Form
Build a Data Entry Form
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|