Entering the Data
In Excel Databases we can fill, correct and format the data same as any normal Worksheet or cell. We can also take the benefit of the Data Form in the Excel.
You can fill the data in the any form, but keep some precautions.
Firstly, in excel there no facility of field type, therefore we can fill any text, number or date in any field. Sometimes it can be a problem. If you want to write any number which starts with a zero, in Excel Databases that zero will not shown.
For example: All the STD codes starts with zero. To show them correctly we have to use the Apostrophe.
Secondly, If you mix the numbers text or date in any of the field, then Excel will not provide you any type of warnings. If you see carefully, you will find out the mistakes. Basically in Excel the numbers are shown on the right side and the text are shown on the left side in a cell. Therefore if you have not formatted the cells together then you can find out the mistakes.
Adding Records
Basically you add the new record in the databases after the last record. This record is done as a norml worksheet or with the help of the Data Form, which add the new record below the old records.
But if you want to add any new record, then you can add the new row there. All the other records send down.
If your databases are under your pre-defined data range, then you never any problems while doing these activities. But if you have filled the cells below the data range, then it will be inconvenient to add new records and we can extend the data base. In this case we have to extend our data range.
Using Data Forms
In any Excel Databases to fill any data and to find & see the record, databases are convenient. These are the Dialog boxes which have name of fields, boxes to fill text data and some buttons. Select a cell in the data range, then go to the Access toolbar and click on the Form icon. Data form creates and show on your screen.
May be the icon of the Form not available on the Quick Access Toolbar, but you can easily add this to your toolbar.
Adding and Editing Records with the Data Forms
To add the new records with the help of the Data Forms, click on the new button or scroll down to the last record, which will blank. Now you can fill the data you desired or if you can leave it blank.
To correct any record by scrolling or any other method put that record in the data form, and edit them with the normal techniques. The fields whose calculations are depended on the other field’s values, you can not edit them. In the same way you can not format the fields in the data form. This work can be done only in the Worksheet.
The changes made by the data form in the Record will not be shown in the Worksheet, unless you jump to the next record or you close the data form. All the changes will be saved to the Computer RAM, since you save the Worksheet. Therefore to save the data, you should save the Worksheet.
Deleting Records with the Data Form
To delete a record, first bring it in the data form and then click the delete button, before doing so you will be confirmed to delete the record, then it will be deleted. You cannot undo the task of deleting records, this is done only in the worksheet.
Searching Records
If you want to use the data form to find the records that meet a condition and view or edit it one by one. First click on the ”criteria” button in the data form. In response to this your data form will be blank and the ”criteria” will take the form of the dialog box. You can fill your conditions in this dialog box. You can use the following Comparison Operators for Numbers and Dates:-
= Equal to
> Greater Than
< Less Than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
Using Filters
To find records in the Excel Database, Filter is very useful. The filter is available in two forms- Filter and Advance Filter.
When you click Filter in the sort and filter section in the data tab, MS Excel finds different types of entries in each field or column of your database and lists them. When you click on the Sort & filter button in the editing part of the Home tab, a pull-down menu opens. Clicking the Sort and Filter button in the editing section of the Home tab has the same effect as selecting the filter option in the open pull-down menu.
When you open the arrow by clicking the drop down list of a column, there is another option with all the different entries in the list. Moving the mouse pointer over the text pointer opens a menu to the right, which also has other options if the column has a digit in place of the text.
