Excel

  • Microsoft Excel is used to store and retrieve numerical data in a grid format of columns and rows. 
  • The excel program can turn a spreadsheet of numbers into a pie char, bar diagram or a variety of other visual diagrams.
  • Excel can also be used to store text-based data such as client lists, employee data and mailing lists.
Excel sheet looks like below

  • The selected area is called as cell
Formulas:
  • Formulas are used to perform a specific operations on data.
  • Just remember that all the formulas in excel begin with an equal sign(=).
1.SUM
The sum function total the values in one or more cells or range.
Shortcut Key: Alt + =
Formula: =Sum(number 1,number 2)
  • We can type the number or select the cell it places cell reference. eg: A1,B2,C5 and so on..
  • Numbers separated by ','
  • In one formula we can pass up to 252 cells.

    • Press Enter



    Formula: =Sum(range 1)


    • Press Enter


    Formula: =Sum(Range 1, range 2)
    • Select Range 1
    • Then put ','
    • Select Range 2
    • Press enter

    2.Difference
    • This function is used to calculate the difference between two or more cells.
    • There is no separate formula for subtraction we just put '-' symbol in "sum" formula.
    Formula: =sum(number 1- number 2)

    • Press Enter

    Formula: =Sum(number 1- number 2.......-number n)
    • Press Enter

    3.Multiplication and Division
    Multiplication and division in excel is easy, but you need to create a simple formula to do it.
    • Click ' = ' sign on any blank cell.
    • Then enter list of numbers or select the cells on which you want to perform operation separate by ' * ' for multiplication ' / ' for division.

    • press enter.

    Multiplication by selecting cells



    Division

    •  Press Enter

    4.Average
    • Used to calculate the Arithmetic mean of numbers or ranges.
    Formula: =Average(number 1, number 2)

    • Press Enter

    Formula: =Average(range 1)


    • Press Enter
    • We can do it for multiple ranges also
    5.Count
    Used to count the number of cells that contain numbers in a range.
    Formula: =Count(range 1)



    Formula: =Count(range 1, range 2, ....)



    6.Counta
    Used to find the number of cells in a range that are not empty.
    Formula: =Counta(number 1, number 2)



    Formula: =Counta(range 1)



    Formula: =Counta(range 1, range 2, ...)



     7.Minimum
    Used to find the minimum value

    Formula: =Min(range)



    Formula: =Min(range 1, range 2,....)



    8.Maximum
    Used to find the Maximum value.
    Formula: =Max(range 1, range 2...)



    9.Concatenate
    Used to join several text strings into one text string.
    Formula: =Concatenate(text 1, text 2)


    • If we want some space between them add space in the formula.
    Formula: =Concatenate(text 1," ",text 2)


    • If we want comma between them place the comma in between " ".
    Formula: =Concatenate(text 1,",",text 2)


    How to create our own formula
    In excel we don't have formulas for some operations like Multiplication, Division. In this case we can create our own formula.
    Steps:

    1.Open excel
    2.Press Alt + F11

    The following window will open


    3.Choose Insert
    4.Click on module (new window opens)


    5.Type the formula code shown below


    6.Save the formula
    7.Return to excel (press F11)
    8.Use the Formula

     Note:
    This function will multiply only two numbers because i have passed two arguments.

     Vlookup:
    Vlookup function performs a vertical lookup by searching for a value in the first column of the table.
    Formula:
    Vlookup(lookup_value, table_array, column_index_num, range_lookup)

     lookup_value: The value to search for in the first column of the table.
    table_array : Two or more columns of data. 
    column_index : The column number in a table from which the matching value must be returned. The first column is 1
    range_lookup : False - exact match True- Appropriate match. if this is omitted True is the default value.

     Example:




    • Press Enter


    • If we change any name the value also change automatically.

    Hlookup:
    Hlookup function performs a horizontal lookup by searching for a value in the top row of the table.
    Formula:
    Hlookup(lookup_value, table_array, row_index_num, range_lookup)

     lookup_value: The value to search for in the first row of the table.
    table_array : Two or more rows of data.
    row_index : The row number in a table from which the matching value must be returned. The first row is 1.
    range_lookup : False - exact match True- Appropriate match. if this is omitted True is the default value.

     Example:




    •  Press Enter