Excel Functions

Only available on StudyMode
  • Download(s) : 93
  • Published : February 12, 2013
Open Document
Text Preview
You are already familiar with Microsoft Excel software. You have learnt some interesting and useful things that can be done in MS-Excel. Microsoft Excel is an electronic spreadsheet software which allows you to enter, calculate, manipulate, organize and analyze data in the form of tabular form. Let us learn more about data tools provide in Microsoft Excel. * Data Tools

* Text to Columns
* Remove Duplicates
* Data Validation
* Consolidate

Data Tools

With the powerful data tools included within Microsoft Excel you have the ability to analyze large amounts of data that will influence decisions. Data Validation is a great way to limit what a user can enter into a worksheet cell. It may not stop a cell from being left empty, however. This tip explores why this is and how you can make sure that a cell contains a value. Microsoft Excel’s data consolidate capabilities allows you to summarize your data enhancing your ability to organize and structure your data. Remove Duplicates tool makes your data uniform by removing duplicate records.

Text To Column

If you copy data from another program and paste it into Microsoft Excel, Excel may summarize several columns of data to a single column. You can use the Text To Columns command on the Data menu to put each record in a separate column. The range that you convert may contain many rows, but you can only convert one column of data at a time.

Understanding with the help of example
To split a list of names into three columns, you don't have to do it manually.In this given worksheet, to split the students name into three columns the first name, middle name and the last name, follow the following steps:

1. Select the cells range B4:B7 where column B has full name of the students.

1

2. On the Data menu, click Text to Columns.

2

3. Of the Convert Text To Columns Wizard, make sure that the Delimited option is selected, and then click Next.

3

4. Of the Convert Text To Columns Wizard, click to select the Space check box under Delimiters, and then click Next. All other check boxes must be cleared.

4

5. Of the Convert Text To Columns Wizard, type $C$4 in the Destination box, and then click Finish.

5

6. A message box is appeared, “Do you want to replace contents of the destination cells?” Click OK.

6

Students’ name is separated into 3 columns
The result will appear as shown here:

Remove Duplicates

Microsoft Excel imports or combines many records for the purpose of analyzing data or making a report, it may has duplicate records. The redundant data that has duplicate records is not desirable. To filter unique records Remove Duplicates function can be used under Data Tools.

Understanding with the help of example
If you have sales records having three columns customers’ name, invoice number and amount.

1

To prepare a list of the names of customers to whom sales is made, follow these steps:

1. Select the cells range $A$4:$C$13
2. On the Data menu, click Remove Duplicates.

2

3. The Remove Duplicates dialog will give you a list of columns. Choose the columns which should be considered. You are trying to get a unique list of customers, so choose only the Customer field.

3

4

4. Click OK.

Excel will delete duplicate records from your dataset. It will report that numbers of duplicates were removed and numbers of records remain.

Unique Records

Data Validation

Data validation is the process to make sure that a program operates on clean, correct and useful data that check for correctness, meaningfulness, and security of data that are input to the system. For this purpose you can set criteria for inputs. Excel’s Data Validation tool enables you to prevent those records that don’t meet the specified criteria being entered into a cell.

Use of Data Validation
There are following uses of data validation:
* To control user’s input into a cell....
tracking img