-->

Microsoft-Excel 2007 Study Material Day-Twenty Eight

Data Tools Group :



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
Remove Duplicates:
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
    Example 4 :  Date : 
    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
    Example 4 :  Custom  : 
    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

    No comments: