MS EXCEL

MS OFFICE EXCEL 

Excel is designed to help you create records and calculate data, and present it in a clear  and attractive manner. Excel  provides you with various chart and layout options to Enhance your spread sheets.

Spread application is desired primarily  to perform mathematical calculation. The spread sheet as the name suggest is like a large sheet containing several rows and columns to maintain record. Spread sheet application also comes with powerful graph utilities. These utilities help us in representing our data in a pictorial  format to active this task. These utilities make use of different charts and graph that has been given alone with the spread sheet application.

 

ADVANTAGES OF COMPUTERISED SPREAD SHEET :

  1. It is easier to maintain huge amount of records than paper spread sheet.
  2. The possibility of errors are greatly minimize.
  3. It is automatically changes or re-calculates the previous record incase of any changes made to the records.

 

Cells -Cells are areas in a worksheet in which you store data. You can enter text, values and formula. Each cell is defined by the intersection  of a row and a column. Cell location is called a cell reference or cell  address.

 

Work sheet – It is a collection of related data. You can use worksheets to  list and analyze data. Each work sheets contains 256 columns and 65,536 rows. Therefore each worksheets contains 16777216 cells (2003).In 2007 & upper version work sheets contains 1048576 rows and 16384 columns.

 

Work book – A work book may contain any combination of worksheets and chart sheets.

Ctrl+down Arrow -> Go to last row   Ctrl+right Arrow -> Go to last column.

 

You can insert new sheet, Delete sheet, Rename sheet, Hide & Unhide sheet using right key.

 

Columns are labeled A,B,C,D,……………….. While rows are labeled 1,2,3,4,……………….

Each cell can store up to 30,000 characters.

CREATING A SERIES :

Step 1- Enter the first value of any series in any cell.

2- Enter the 2nd value of the series just below or right of the previous cell

3- Select these two cell.

4- Place the mouse pointer at the right bottom corner of the selection.

5- When your mouse pointer become like(+), click the mouse and drag it to the  desire location.

CONDITIONAL FORMATTING:

Excel 2007’s conditional formatting lets you change the appearance of a cell based on its value or the value of another cell. You specify certain conditions, and when those conditions are met, Excel applies the formatting that you choose. You might use conditional formatting to locate dates that meet a certain criteria (such as falling on a Saturday or Sunday), to call out highest or lowest value in a range, or to indicate values that fall under or over a specified amount.

 

Delete Sheet -Select the sheets you want to delete. On the Edit menu, click Delete Sheet.

Delete cells, rows, or columns -Select the cells, rows, or columns you want to delete.On the Edit menu, click Delete. Surrounding cells shift to fill the space.Note Microsoft Excel keeps formulas up to date by adjusting absolute references to the shifted

cells to reflect their new locations.

INSERT: Cells, Rows, or Columns -You can insert blank cells, rows, and columns and fill them with data.

Insert blank cells – Select a range of existing cells where you want to insert the new blank cells. Select the same number of cells as you want to insert.On the Insert menu, click Cells.Click Shift cells right or Shift cells down.

Insert rows -To insert a single row, click a cell in the row immediately below where you want the new row. For example, to insert a new row above Row 5, click a cell in Row 5.

To insert multiple rows, select rows immediately below where you want the new rows. Select the same number of rows as you want to insert.  On the Insert menu, click Rows.

Insert columns – To insert a single column, click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of Column B, click a cell in Column B. To insert multiple columns, select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.  On the Insert menu, click Columns.

Worksheet – To add a single worksheet, click Worksheet on the Insert menu. To add multiple worksheets, hold down SHIFT, and then click the number of worksheet tabs you want to add in the open workbook. Then click Worksheet on the Insert menu.

Creating a chart – Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. For instance, rather than having to analyze several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.

You can create a chart on its own sheet or as an embedded object on a worksheet. You can also publish a chart on a Web page. To create a chart, you must first enter the data for the chart on the worksheet. Then select that data and use the Chart Wizard to step through the process of choosing the chart type and the various chart options.

There are two type of Chart are there –  1. Standard Type  2. Custom Type

 FRORMULA  :

  1. A formula is an instruction to calculate numbers. Formula in Ms-Excel follow a specific syntex or order, that includes an equal (=) sign followed by the elements to be calculated, which are separated calaculations operators.
  2. OPERATORS :

 ARITHMETIC OPERATORS –
+                    PLUS SIGN                                                    ADDITION

–                     MINUS SIGN                                                SUBSTRACTION

*                    ASTERISK                                                     MULTIPLICATION

/                      FORWARD SLASH                         DIVISION

%                   PERCENTAGE SIGN                                   PERCENT

^                     CARET                                                           EXPONENTIATION

  1. COMPARISION OPERATORS –

=                     EQUAL SIGN                                                EQUAL TO

>                     GREATER THAN SIGN                              GREATER THAN

<                     LESS THAN SIGN                                        LESS THAN

>=                  GREATER THAN EQUAL TO                   GREATER THAN EQUAL TO
<=                  LESS THAN EQUAL TO                             LESS THAN EQUAL TO

<>                  NOT EQUAL TO                                          NOT EQUAL TO

 

  1. FORMULA EXAMPLE :
  2. = A1+ B1 -> Add the content of cell A1 with the content of B1.
  3. = A1- B1 -> Substract the content of cell A1 with the content of B1.
  4. = A1* B1 -> Multiplay  the content of cell A1 with the content of B1.
  5. = A1/ B1 -> Divide  the content of cell A1 with the content of B1.
  6. = A1^ B1 -> Find out A1  to the power B1.
  7. = 10%*A1 -> Find out 10 percentage of the content of cell A1.
  8. (A1+B1)/2 -> Add the content of A1 and B1 and divided it by 2.
  9. A1&” “&A10 -> Combine the the text in cell A1 and A10.

About using functions to calculate values –

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. If the function starts a formula, type an equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you.

Entering  Function –

1.Function begin with an equal (=) sign.

2.The arguments are enclosed in a brackets.

3.The arguments are separated by comms (,).

4.If any arguments contains a text value that includes a space, enclose the value in quotes (“ “).

DATE AND TIME FUNCTION :

1. DATE (Year, Month, Day)

Syn – =Date(2020,01,15)- It will display the date.

2.DAYS360(“Starting date”, “Ending date”) –

Syn – =Days360(“01/02/2000”,”01/02/2006”) – It will display the gap value in days between the two date.

3.MONTH(NUMBER)

Syn – =Month(“10-June”) – It will display the numeric value of the month june.

  • NOW( )
    Syn – =Now( ) – It will display the current date and time.

LOGICAL FUNCTION :

  1. AND(EXPR1, EXPR2)
    Syn – =And(A1>15, B1>20) – If both condition are True then it will display True.

If one is True and other is False then it will display False.

  1. OR(EXPR1, EXPR2)
    Syn – =Or(A1>15, B1>20) – If both condition are True then it will display True.

If one is True and other is False then it will display True.

If both are False then it will display False.

  1. IF(A1>10,”C-NET”,”COMPUTER”)

Syn – =If A1 value is greater than 10 then it will display C-NET otherwise it will display COMPUTER.

  1. NOT(LOGICAL)
    Syn – =Not(A1=90) – If A1 value is 90 then it will display False otherwise True.

MATHEMATICAL FUNCTION :

  1. ABS(NUMBER)
    Syn – =Abs(-10) It will display the +ve value. Ie, 10.
  2. COUNTIF(RANGE, CRITERIA)

Syn – =Countif(A1:A10, 25) – It will display how many 25 are there in range A1 to A10.

  1. INT(NUMBER)

Syn – =Int(25.4567) – It will display only the integer part i.e. 25.

  1. MOD(NUMBER, DIVISION)

Syn – =Mod (5,2) – It will display the remainder part. i.e. 1.

  1. ROUND(NUMBER, NUMBER OF DIGIT)

Syn -= Round(25.5678,2) – It will round the decimal part up to 2 degit i.e. 25.57

  1. SQRT(NUMBER)

Syn – =Sqrt(4) – It will calculate the square root of 4. i.e. 2

  1. SUM (NUM1,NUM2,NUM3,NUM4)

Syn – =Sum(2,10,13) – It will calaculate the summation of digits. i.e. 25.

  1. POWER (NUMBER, DIGITS)

Syn – =Power(4,2) – Return the result of a computer of a number raised to a computer. i.e. 16.

  1. FACT(NUMBER)
    Syn – =Fact(5) – Returns the factorial of a number i.e. 120
  2. PRODUCT(NUM1,NUM2,NUM3,NUM4)

Syn – =Product(2,3,2,5) – It will return the multiplies values of given numbers. i.e. 60

  1. ODD(NUMBER)

Syn – =Odd(6) – It receives any number and display  its nearest odd number.

  1. EVEN(NUMBER)

Syn -= Even(6) – It receives any number and display  its nearest even number.

 STATISTICAL FUNCTION :

1.AVERAGE(NUM1,NUM2,NUM3)

Syn – =Average(10,20,5) – It will find out the average of numbers. i.e. 11.66

2.COUNT(A1:A10)

Syn – =Count(A1:A10) –  It is used to get the number of entries in a range of cell.

3.MAX( A1:A10)

Syn – =MAX(5,12,6) It will return the largest value in a set of values.

  1. MIN( A1:A10)

Syn – – =MAX(5,12,6) It will return the smallest value in a set of values.

TEXT FUNCTION :

1.CHAR(NUMBER)

Syn – =Char(66) – It will display the ASCII value of B. Returns the character specified by the code number from the character set for your computer.

  1. CONCATENATE(TEXT1,TEXT2)
    Syn – =Concatenate(“HELLO”,”C-NET”) – Join several text strings into one text string.
    3.LEN(TEXT) –

Syn – =Len(“Computer”) – Returns the number of characters in a text.

  1. EXACT(TEXT1,TEXT2)

Syn – =Exact(“C-NET”, “C-NET”) – Compares two text and returns True if they are exactly the, False otherwise.

  1. LEFT(TEXT,NUMBER)
    Syn – =Left(“Baripada”,2) – It will display first 2 letter from left. i.e.Ba

6.RIGHT(TEXT,NUMBER)
Syn – =Right(“Baripada”,2) – It will display 2 letter from right. i.e.da

  1. MID(TEXT,START NUMBER,NO OF LETTER)

Syn – =Mid(“Baripada”, 3,2) – Returns the specific number of characters from a text at the position you specified.

8.LOWER(TEXT)
Syn – =Lower(“C-NET”) – Convert all uppercase letters in a text to lower case. i.e.c-net.

9.UPPER(TEXT)
Syn – =Upper(“c-net”) – Convert all lower case letters in a text to uppr case. i.e. C-NET.

GOAL SEEK –  When you  know the desired result of a single formula but not the input value the  formula needs to determine the result, you can use the Goal Seek feature.

A                                       B

1 Loan Amount                     50,000

2 Interest                                12%

3 Period                                 36

4 Installment                        = (Loan+(Loan*Int*Period)/12)/Period

We can change B1,B2 and B3 cell directly according to our need. When we want to change B4 Cell according to our need than we have to use the Goal Seek command.

SUBTOTAL  : Microsoft Excel can automatically summarize data by calculating subtotal and grand total values in a list. To use automatic subtotals, your list must contain labeled columns and the list must be sorted on the columns for which you want subtotals.

FILTER: Filtering is a quick and easy way to find and work with a subset of data in a list.

SORT -Click a cell in the column you would like to sort by.Click Sort Ascending . Click a cell in the column you would like to sort by. Click Sort Descending

PIVOT TABLE :A pivot table report is an interactive table that you can use to quickly summarize large amount of data you can rotate its row and columans to see different summarizes of the source data filter the data by displaying pages or displays the details for areas of interest.