Data Tools Group :
Text to columns:
It converts text format to table format .
Steps :
Delete Duplicate rows from a sheet.you can specify which columns should be checked from duplicate information.
Data Validation :
You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list.
Setting : Setting option is used to set the condition to the required cells.
Validation Criteria Types :
1) Any Value : It Accept the any Format.
2) Whole Number: Limit entries to whole numbers.
3) Decimal :Limit entries to decimal (percentage) values.
4) List : It list of Defined name .
5) Date : Limit entries to dates within a range.
6) Time :Limit entries between a time frame.
7) Text Length :Limit entries to a certain number of characters.
8) Custom :Limit entries to unique values only (no repeated entries)
Error Alert :
Error alert message will be displayed whenever the validation rule is not satisfied.
Example Table :
Example 1 : Whole Number :
Steps :
Text to columns:
It converts text format to table format .
Steps :
- Select the Cell
- Click on Text to Column
- Choose the file type best describes your data
- Delimited (or) Fixed Width
- Check the Delimiters (Tab,Semicolon,Comma,Space,Other)
- Click on Next Button
- Click on Finish Button
Delete Duplicate rows from a sheet.you can specify which columns should be checked from duplicate information.
Data Validation :
You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list.
Setting : Setting option is used to set the condition to the required cells.
Validation Criteria Types :
1) Any Value : It Accept the any Format.
2) Whole Number: Limit entries to whole numbers.
3) Decimal :Limit entries to decimal (percentage) values.
4) List : It list of Defined name .
5) Date : Limit entries to dates within a range.
6) Time :Limit entries between a time frame.
7) Text Length :Limit entries to a certain number of characters.
8) Custom :Limit entries to unique values only (no repeated entries)
Error Alert :
Error alert message will be displayed whenever the validation rule is not satisfied.
Example Table :
Example 1 : Whole Number :
Steps :
- Select EMPNO column
- Click on Data Validation
- Select Settings option
- Select Whole Number from Allow Box
- Select Between from Data Box
- Type the Minimum : 100001
- Type the Maximum : 100020
- Select Input Message Tab
- Title : PLEASE ENTER VALID RANGE
- Input Message : Employee Id Should be in the (100001 to 100020)
- Select Error Alert Tab
- Select the Style type :(STOP,WARNING,INFORMATION)
- Title : PLEASE ENTER VALID RANGE
- Input Message : Employee Id Should be in the (100001 to 100020)
- Click on OK Button
Example 2 : Text Length:
Steps :- Select ENAME column
- Click on Data Validation
- Select Settings option
- Select Text Length from Allow Box
- Select Between from Data Box
- Type the Minimum : 1
- Type the Maximum : 15
- Select Input Message Tab
- Title : Please enter valid Name
- Input Message : Employee Name Should be in the (1-15 Characters)
- Select Error Alert Tab
- Select the Style type :(STOP,WARNING,INFORMATION)
- Title : Please enter valid Name
- Input Message :Employee Name Should be in the (1-15 Characters)
- Click on OK Button
Example 3 : List :
Step 1 :
- First Create Define Name
- Type the Text : Male and Female
- Select the Both Cells
- Click on Formula Tab
- Click on Define Name
- Type the Name : Gender
- Click on Ok Button
Step 2 :
- Select GENDER column
- Click on Data Validation
- Select Settings option
- Select List from Allow Box
- Type Source Location : =Gender
- Select Input Message Tab
- Title : Please select the Drop down List
- Input Message : This cell Accept the Only Male and Female Text .
- Select Error Alert Tab
- Select the Style type :(STOP,WARNING,INFORMATION)
- Title : Please select the Drop down List
- Input Message :This cell Accept the Only Male and Female Text .
- Click on OK Button
Steps :
- Select BIRTHDATE column
- Click on Data Validation
- Select Settings option
- Select Date from Allow Box
- Select Between from Data Box
- Type the Start Date =DATE(1988,6,20)
- Type the End Date =DATE(2001,6,20)
- Select Input Message Tab
- Title : Please Enter Valid Date
- Input Message : The Accept dates between 1988,6,20 to 2001,6,20.
- Select Error Alert Tab
- Select the Style type :(STOP,WARNING,INFORMATION)
- Title : Please Enter Valid Date
- Input Message :The Accept dates between 1988,6,20 to 2001,6,20.
- Click on OK Button
Example 4 : Time :
Steps :
- Select WORK HOURS column
- Click on Data Validation
- Select Settings option
- Select Time from Allow Box
- Select Between to from Data Box
- Start Time =TIME(1,00,00)
- End Time =TIME(8,00,00)
- Select Input Message Tab
- Title : Out of Office Timings
- Input Message : Please enter the valid timings (1 to 8 Hours).
- Select Error Alert Tab
- Select the Style type :(STOP,WARNING,INFORMATION)
- Title :Out of Office Timings
- Input Message :Please enter the valid timings (1 to 8 Hours).
- Click on OK Button
Steps :
- Select Email Id column
- Click on Data Validation
- Select Settings option
- Select Custom from Allow Box
- Type Formula :=ISNUMBER(FIND("@",F2:F10))
- Select Input Message Tab
- Title : Enter Valid Email Id
- Input Message : Please Enter the Valid Email Id.
- Select Error Alert Tab
- Select the Style type :(STOP,WARNING,INFORMATION)
- Title :Enter Valid Email Id
- Input Message :Please Enter the Valid Email Id.
- Click on OK Button
Follow @Sarvavasi.com