MS Excel is a spreadsheet tool, which is a part of MS Office. It is widely used application Software in industries. Excel supports may in-built functions that makes it easier to use. In this article top most useful Excel are covered that one must know while working with MS Excel :-

1. SUM

The SUM() function, as the name suggests, gives the total of the selected range of cell values. It performs addition. Lets see an example.

In this example, SUM function is written is G4 cell. This function, =SUM(B4:F4), add all the values entered in B4 to F4 range and gives result. 

: (Colon) sign is used to defined the range.

If there are only two or three numbers, or numbers are not in sequence, to add then comma(,) can be used as separated. i.e.

=SUM(B4,C4,D4,E4,F4)

2. AVERAGE 

AVERAGE function calculates the average of numbers provided as arguments. To calculate the average, Excel sums all numeric values and divides them by the count of numeric values. Lets see an example:

In this Function, =AVRAGE(B4:D4) is given  in E4. B4 to D4 is cell range given to this function. SO, Excel found average of these 3 cells.

Excel count 0 as a number,  but ignore black cell , in this example D6 cell is black , so in E6 cell there is an average of only 3 cells.

3. COUNT

The function COUNT() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric i.e. text, date etc. Lets see and example:

In this example, function =COUNT(B4:D4) is given in cell E4, which count all there cells and given answer as 3. Because, all of these three cells contain numeric data. But in D5 & D6 cell alphabet and black is given respectively, so it would be ignored by Excel.

4. POWER

The function “Power()” returns the result of a number raised to a certain power. Let’s check following example shown below:

In this example =POWER(B3,3) is entered into cell C3 and it gives result as 1000. It raises value 10 to power 3.

5. CONCATENATE

This function merges or joins multiple text strings into one text string An example is given below:

In this example, we have entered =CONCATENATE(B3," ",C3). And it joins B3 and C3 values and gives a concatenated string.

6. NOW()

The NOW() function in Excel gives the current system date and time. Where we want current date to be inserted, NOW() function may be used. The result of the NOW() function will change based on your system date and time.

7. IF

The IF() function is used to check a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.

=IF(C2>35,"Pass","Fail")

In the this example, we want to check if Student is Pass or Fail, if a student gets marks more than 35 , he/she Pass otherwise Fail. the value in cell C2 is greater than 35, it shows Pass as result, in C4 value is less than 35 it shows result Fail.

8. LEN 

This function of Excel is used to calculate the number of characters in a cell or text. For Example:

This Function shows the length of string given in Cell A1.

9. LEFT

The LEFT function is used to extract a specific number of characters or substring from a text string, such as a specific portion of a telephone number. The syntax of the LEFT function is:

 

=LEFT(text, [num_chars])

num_chars refer to the number of characters to be extracted counting from the left. If the num_chars argument is omitted, Excel returns the leftmost character only.

=LEFT(A3,5) function returned the left 5 characters of the string.

In cell B2, no argument is given in LEFT function, so only 1 Left character of argument is returned

10. RIGHT

The RIGHT function is used to extract a specific number of characters or substring from a text string from right side, such as a specific portion of a telephone number. The syntax of the RIGHT function is:

=RIGHT(text, [num_chars])

num_chars refer to the number of characters to be extracted counting from the left. If the num_chars argument is omitted, Excel returns the leftmost character only.

=RIGHT(A3,4) function returned the right 4 characters of the string.