Useful Commands of Advance Excel for its Optimum & Efficient Use

Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display.

Here are some useful commands to use excel more efficiently with less time consuming work

A] Commands for Excel Text Functions:

1] Len() : Returns the number of characters of a string.

    Syntax: =len(String). Here string means the position of cell

2] Rept() : Repeat given characters (S) specified number of times
    Syntax: =rept(string, number of times). Here number of times means the Repeat value of Character.

3] Concatenate() : Join multiple strings in one string 

    Syntax: =concatenate ( String1, String2, String3,.......)

B]  Commands for Excel Mathematical Functions : 

1] Sum() : Adds all the numbers given as an argument

    Syntax: =sum(numerical argument).

2] Product() : Multiply all the numbers given as an argument.

    Syntax: =product( numerical argument)

3] SQRT() : Return Square root of a number.

    Syntax: =sqrt(number).

4] MOD() : Returns Reminder

    Syntax: =mod(number,divisor)

5] Fact() : Returns the factorial value of a number.

    Syntax: =fact(number)

C] Commands for Excel Statistical Functions :

1] MIN() : Returns the smallest value from a list of supplied numbers.

    Syntax: =min(number1,number2,.......)

2] Max() :  Returns the largest value from a list of supplied numbers.

    Syntax: = max(number1,number2,.......)

3] Large(): Returns the kth largest value from a list of supplied numbers for given value k.

    Syntax: =large(datatable,k)

4] Small(): Returns the kth smallest value from a list of supplied numbers for given value k.

    Syntax: =small(datatable,k)

5] Count() : Returns the number of numerical value in a supplied set of cells or values

    Syntax: =count(value1, value2,.....)

6] CountA() : Returns the number of blank cells in a supplied set of values

    Syntax: =countA(value1, value2,.....)

7] Count Blank() : Returns the number of cells that satisfy a given criteria.

   Syntax:  =Countblank(data range)

8] Count If() : Returns the number of cells that satisfy a given criteria

    Syntax: =countif(criteria range, criteria).

9] Countifs() : Returns the number of cells that satisfy a set of given criteria 

    Syntax: =countifs(criteria range1, criteria, criteria range2, criteria.......).

10] Average () : Returns the avg of list of supplied numbers

      Syntax: =Average(number1,number2....)

Thus to use excel in effective manner we should go through the advance excel course.