Excel Worksheet Cell accepts values of any data type viz. Text, Number, Date/Time or expressions (result values can be any of these types). Worksheets are the best tools for What…If analysis and present numerical data in Graph Chart form. Besides all these we can create databases on worksheets and query information very quickly.
An Excel Database is like any other worksheet but created with few basic rules of databases in mind so that certain querying operations can be performed, like searching, filtering, lookup and so on. I will list few basic rules, that you should know about setting up an excel based database (table) structure, below.
- The topmost row columns of a table should have the data Field Names and all of them should be unique (no duplicates) and cannot be in merged cells.
- There should not be any column without column heading within the table area.
- Data type of each column should be of same type, like text or Number or Date or a formula that results in the same data type, not the mix of different data types in the same column.
- There should not be any blank rows within the table range, but some cells in a row can be empty, if needed.
These are some of the simple rules that goes for defining an excel based database. Image of a sample database is given below:
There are several built-in database functions in excel, like DSUM(), DAVERAGE(), DCOUNT() and others for analysis purposes, but the data must be reliable. We have to ensure that the data entered into the data fields keep up with the strict rules that we set for each field.
A single cell under the First Name field can accommodate thousands of characters. We don’t need that much text in the First Name field. But, we must be able to enter enough text (say a maximum of 25 characters) in the First Name field and about that much in the Last Name field as well, not more than that.
A list of validation rules applicable for each data field is given below. We will use these rules to perform validation checks on keyed-in data (result of expressions are not validated) before they are being accepted into the data field.
| Serial | Field Name | Data Type | Length | Validation Rule |
|---|---|---|---|---|
| 1. | First Name | Text | 25 | Text Data, maximum length 25 characters |
| 2. | Last Name | Text | 25 | Text Data, maximum length 25 characters |
| 3. | Date Of Birth | Date | Future Date and Date earlier than 100 Years not Valid | |
| 4. | Age | Number | Range 1 - 100 |
Now, let us try to implement the above rules in an excel Table area and experiment with it to see how these rules actually works while entering data into each column.
- Open a blank Excel Workbook.
- Enter the Table headings in Range A1 to D1 as shown in the image given above.
- Select the first cell below the column heading First Name.
- Select Data Validation . . . from the Data Tools Toolbar buttons group under the Data Menu. The Validation settings dialog control opens up like the image shown below:
- Select the Settings Tab.
- Select Text Length from the Allow: control’s drop-down list.
- Select Less than or equal to from the Data: control’s drop-down list.
- Type 25 into the Maximum control. The cell with this settings will allow only 25 characters of input into the cell.
- Select the Input Message control.
When the User selects the Cell to key in data the settings on the Input Message control will appear as a comment box attached to the cell, to guide the user to enter a valid value, or with whatever message you intended to convey.
- Put check mark in the ‘Show input message when cell is selected’ control. If check mark is not present then the message we set up in the next two steps will not appear on the worksheet.
- Type First Name in the Title control.
- Type Enter First Name (25 characters) in the Input Message control.
- Select Error Alert Tab. This is where we have to set up the real validation check to perform and alert the user when invalid data is entered into the cell.
- Put a check mark in the ‘Show Error Alert after invalid data is entered’. If this is not enabled then the user will not be warned on entry of invalid data.
- Select the Icon style (Stop, Warning or Information) of Alert message box, default stop will be just fine.
- Type First Name in the Title control.
- Type Maximum 25 characters.
- Click OK to close the dialog box.
- Select the first cell below Last Name field.
- Repeat Step-4 to Step-18 replacing First Name field name with Last Name, wherever applicable.
- Select the Cell beneath the Date Of Birth field.
We will prevent this field from accepting a future date for Date of Birth.
- Select Data Validation option to open up the dialog control.
- Select Date from the drop-down list in the Allow: control.
- Select Less than or equal to in the Data: control.
- Type =TODAY() in the End Date: control.
As you can see we can enter a Function or expression in this control rather than a constant value, when needed.
- Select the cell below the Age field.
- Enter the following expression to calculate the Age, based on the Date of Birth field value:
>=INT(IF(C2>0,(TODAY()-C2)/365.25,0))
We will leave this field from validation checks. Validation checks can be performed only on cells where manual keying of data is done.
- Highlight Cell Range A2:D2 and Right-Click on the highlighted range.
- Select Copy from the shortcut menu.
- Highlight Range A3:A20 Right-Click on the highlighted range.
- Select Paste Special from the shortcut menu.
- Select the Radio Button with the label Validation under the Paste options and click the OK button to apply the Validation rules we have copied from the source range A2:D2 into the target area A3:D20.
- Highlight Cell D2, right-click and select Copy from the shortcut menu to copy the Formula from cell D2.
- Highlight Range D3:D20, right-click and select paste from the short-cut menu.
Tip: You may press Ctrl+C to copy the source cell and press Ctrl+V to paste the formula after highlighting the cell ranges.
- Save the Workbook file with a name.
Now, let us test our database’s validation checks.
- Type a name with more than 25 characters in length into Cell A2. When the Error Alert pops up click Cancel and enter a name with 25 characters or less.
- Type something similar in Cell B2 with more than 25 characters.
- Type tomorrow’s date into Cell C2.
Key Words:Validation,Input Message,Error Alert
No comments:
Post a Comment