Microsoft Excel

Microsoft Excel

Features of Excel

  • It is used to create and perform "What If" analysis of complex, interrelated columnar reports.
  • Easy to manipulate data in grid format
  • Formulas can be applied and copied to other areas easily.
  • Function wizard is used to apply formulas easily to the cells. Complex problems can be solved using function wizard.
  • A chart Wizard helps to convert cell data into pie charts, line graphs, bar charts, three-dimensional charts, scatter charts and other visual aids.
  • Data can be summarized using subtotal, pivot table, filter, consolidate etc.
  • Any part of worksheet can be viewed, modified and printed

Moving Cell pointer : Cell pointer is a rectangular box that represents the current cell position. Current cell address will be displayed in formula bar. Cell pointer can be moved to desired cell as follows

Right arrow -> Next column cell

Left arrow -> Previous column cell

Up arrow -> Previous row cell

Down arrow -> Next row cell

Home -> Column A of current row

Ctrl Home -> First cell (A1)

Selecting cells

Single cell - point to cell and click mouse left button

Multiple adjacent cells - Shift arrow key or mouse dragging

Multiple non adjacent - Ctrl + Click on the cells

Entire row - Click on row heading. Ctrl+Click on the row

heading for selecting more than one rows.

Entire column - Click on column heading. Ctrl+Click on the

column heading for selecting more than one rows.

Entire worksheet - Click the intersection of the column and row

heading.

Label prefixes Characters : In excel, a sequence of text is referred to as a label. The alignment of a label within a cell can be controlled by label prefix characters. Label prefix characters are followed by the text.

‘(single quotes) - Left alignment

“(double quotes) - right alignment

^ (caret) - centre alignment

Typing numbers : A number can be typed in a cell without giving any label prefix characters. A number will be right aligned in a cell. The format of a number can be changed as follows.

  1. Select the cell or cells where the number is to be typed
  2. Choose Cells option in Format menu or press Ctrl 1 and select Numbering tab.
  3. Select the desired format of the number such as currency, date, time, percentage, general etc.

Now the numbers typed in that cells will be automatically converted to the selected format.

Subtotal : Subtotal is used to summarise the sorted data. We can apply functions such as sum, count, minimum, maximum etc in subtotal.

  1. Select the data range and sort the data
  2. After sorting, again select the data
  3. Choose Subtotal option in Data menu
  4. Verify the data range, choose the function which is to be applied in subtotal. Also choose when the subtotal is to be calculated. (Every change in code no etc)
  5. Select Ok. The duplicate data are summarised into a single data and at end grand total will be placed.

Functions : Functions are typed in a cell in following format=Function name(argument) Argument may be in any one of the following format.

  1. Numeric value
  2. Cell address
  3. Range ( Starting cell address : Ending cell address)
  4. Range name
  5. Cell addresses (Address1, address2, .....)

Functions are classified into several functions such as

  1. Math & Trig function
  2. Text function
  3. Logical function
  4. Statistics function
  5. Lookup and reference function
  6. Date & Time function
  7. Financial function
  8. Information function
  9. Data base function

Statistical functions

  1. Count() - count number of numeric values in a list. Other type data will be included (1 to 30 arguments)
  2. CountA() - count the nonempty cells in a range
  3. Min() - find the minimum value in a range 
  4. Max() - find the maximum value in a range 
  5. Var() - find variance of the range
  6. Stdev() - find standard deviation of the range 
  7. Mode() - find the most common value in a range
  8. Median() - find the median of the range
Last modified: Tuesday, 8 November 2011, 6:05 AM