MICROSOFT EXCEL

by Mr.(Sumit)

Introduction

Formula bar

Display of all contains current cell will be the active cell.

Row header

There are 1048576 rows, numbered as 1,2,3.......1048756. First row is numbered 1 and last row cell by pressing.

End + (down direction key)

And return to the first cell

End + (up direction key)

Column header

There are 16384 column numbered as A,B,C,.........X F D. First column name is A and last column name is X F D.

To go last column

End + (right direction key)

To go first column

End + (left direction key)

Sheet tabs

Display the sheet name. Each work sheet named as sheet 1,2,3 etc.

Worksheets Within Workbook

Excel document are known as worksheet contains three worksheet by default adding or deleting the sheet can change the no of sheet. Each sheet is named. Unitarily like sheet - 1 , sheet - 2 etc. Which is displayed in the sheet tabs. when excel is located it automatically opens a new worksheet of a workbook. The main part of excel screen is the worksheet area. A quire of rows and columns.

Cell

This intersection of column and row is called as a cell. Each cell has a name or a cell address consist of the column letter and a row number for example the first cell in the first column and first row 1.

Enter and Editing data

Any entry can be made in the active cell. Enteries can be of four different types.

Text

Text in a cell can include any combination of letters numbered any keyboard symbols. A cell contain 3200 characters. If column with presents a text string fitting usually in a cell. The display extended over the successive cell.

Numbers

Include digits from 0 to 9 and some special character like $ , % , + , - , / , [ , { . ( data, and time storage as numbers. 8/15 is entered in a cell. Excel displays it as 15 - Aug. When an unformatted number does not dit in a cell. It is displayed in scientific nation.

When a formatted number does't fit in a cell ###### is displayed.

Logical value

Logical entries like true or false can be entered in the cell.

Formula

Formula is entered into the cell to performed calculations. A formula begins with an= sign will be completing the formula entry the result of formula will be displayed in the cell and formula bar. If the formula entered is wrong, error value are stored in the cell.

To Edit the cell content

Make the cell active, select the cell and use any one the following method.

Press F2 key or double click on the cell make the correction in the cell content and press enter.

Click on the formula bar to delete the cell content click on the cell and press delete.

Range of cell

Range of a group of cells (also called block) selected generally to make cell formation to specify a range of the cell using the mouse click then drog from middle of cell (the mouse pointer should turn to doctor's symbol).

In the following example the cell E4 to E7 written as E4 to E7 (use a dot symbol) or (:) or (;) between first and last cell address in a range.

Entries and Copying of Formula

A formula begins with = sign for example " To make the calculation 7% of 2400 types the formula = 2400*7% then press enter key. The formula result appears "

Cell Reference

A formula represents contain cell relationship, which generates a result. When typing the cell address in the formula three types of cell reference can be used. A cell reference specifies when the formula is copied to the other cell. It's a column or row number is to change or not.

Cell Formatting

Cell formatting includes setting the font type, font size, bold, italic, underline, diagrams, cell border, cell colour, text colour, decimals the icons to perform them operating are generally available in the formatting tool bar.

For example : the center the heading blocks the column heading and click on merge and center icon in the formatting tool bar.

Importance of Function

Function shorten a formula to add the value of cells C2 to C10. The formula is = C1 + C2 + ....... + C10 . If the function is used, we need to type only

= sum (C1: C10)

Formula

Formula for finding minimum value

= min (A1 : A10) (example)

For finding maximum value

= max (A1 : A10) (example)

For finding average value

= average (A1 : A10) (example)

For counting number of items

= count if (range, criteria)

= count if (A1 : A10, A1)

For sum if

= sum if (A1 : A10, A1, B1)

For knowing date

=Ctrl + ; (current date)

For knowing time

= Ctrl + ; (current time)

For leaving specific character from left side

= left (string, number of characters)

For proper (sentence case)

= proper ("text")

For repeating

= REPT ("text", number of times)

Conditional formatting for filter

alt + DFF

= If (criteria condition, value for tree, value for false)

Uppercase

=upper (cell address)

Mathematical Functions

ABS()

Return the absolute value of the number

Sum()

Adds all the numbers in the range of cells.

MOD()

Returns the result of a number is divided by

POWER()

Returns the result of a number raised to a power

Round()

Round a number to a specified numbers of digits syntax:= round (number, number of digits

Fact()

Returns the factorial of a number

ASIN()

Returns the arsine, or inverse sine, of a number.

Logical Function

If()

Returns one value if a condition you specify evaluates to TRUE and another value if the evaluated to FALSE. Use if () conditional tests on values and formula.

Syntax : = if 9logical test, value if true, value if false)

not()

Reverse the value of its argument. Use NOI when you want to make sure value is not equal to one particular value.

Syntax : = not (logical test)

Or()

Returns TRUE if any argument is TRUE; returns false if all argument are FALSE.

Syntax : = or (logical test 1, logical test 2)

And()

Return TRUE if all arguments are TRUE; returns FALSE if one or more arguments are false.

false()

Return logical value false.

MS excel in explane Menu, Tab and command