ms excel theory

                               smmcc gonda

ms excel theory for ccc 

what is ms excel?

MS EXCEL IS A MS OFFICE PROGRAME .BY THIS PROGRAME WE CAN MAKE A DATA SHEET AND PERFORM THE CALCULATION..

HOME MENU:-ALIGNMENT:::-IF YOU WANT TEXT TO APPEAR ON MULTIPLE LINES IN A

 CELL, YOU CAN FORMAT THE CELL SO THAT THE TEXT WRAPS AUTOMATICALLY, OR

 YOU CAN ENTER A MANUAL LINE BREAK.

MERGE AND CENTER:-BY THIS OPTION WE CAN JOIN MANY CELLS IN TO A LARGER

 CELL AND SET THE TEXT AT CENTER.

NUMBER:-BY THIS OPTION WE CAN CHANGE THE NUMBER FORMAT.

STYLE:-BY THIS COMMAND WE CAN SET THE FORMAT ACOURDING CONDITION.

CELLS :- BY THIS COMMAND WE CAN INSERT AND DELETE THE CELL ,ROW ,COLUMN ETC.

WE CAN ALSO CHANGE THE ROW COLUMN HEIGHT AND WIDTH AND PROTECT THE SHEET.

EDITING:-THIS OPTION IS USED TO SUM DATA AND DELETE THE FORMAT AND SORT AND FILTER THE DATA.

INSERT MENU :-TABLE:BY THIS OPTION WE CAN INSERT THE TABLE AND GET THE SUMMARY OF SELECTED DATA FOR ANOTHER PLACE.

PAGE LAYOUT :-PAGE SET UP:-BY THIS OPTION WE CAN SET AND CLEAR THE PRINT AREA.

SCALE TO FIT:- BY THIS OPTION WE CAN STRETCH OR SHRINK THE PRINTED OUT PUT PAGE.

SHEET OPTION:-BY THIS OPTION WE CAN SHOW AND HIDE GRID LINES AND HEADINGS.

FORMULAS MENU:-

 FUNCTION LIBRARY:-

FINANCIAL:-

DB:-

TO RETURN THE DEPRECIATION OF AN ASSET FOR A SPECIFIED PERIOD USING THE FIXED-DECLINING BALANCE METHOD.

SYNTAX

DB(COST,SALVAGE,LIFE,PERIOD,MONTH)

COST IS THE INITIAL COST OF THE ASSET.

SALVAGE IS THE VALUE AT THE END OF THE DEPRECIATION (SOMETIMES CALLED THE SALVAGE VALUE OF THE ASSET)

.
LIFE IS THE NUMBER OF PERIODS OVER WHICH THE ASSET IS BEING

 DEPRECIATED (SOMETIMES CALLED THE USEFUL LIFE OF THE ASSET).

PERIOD IS THE PERIOD FOR WHICH YOU WANT TO CALCULATE THE

 DEPRECIATION. PERIOD MUST USE THE SAME UNITS AS LIFE.

MONTH IS THE NUMBER OF MONTHS IN THE FIRST YEAR. IF MONTH IS

 OMITTED, IT IS ASSUMED TO BE 12.

PMT
To Calculate the payment for a loan based on constant payments and a constant interest rate.

Syntax
PMT(rate,nper,pv,fv,type)

For a more complete description of the arguments in PMT, see the PV function.
Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to If payments are due 0 or omitted At the end of the period 1 At the beginning of the periodms 

Concatenate := by this command we can join several number s of text.

Left:-LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Right :- RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

LOWER:-TO Convert all uppercase letters in a text string to lowercase.

UPPER :- TO CONVERT ALL LOWER CASE LETTERS IN A TEXT STRING TO UPPER CASE.

PROPER:-Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Date and time :-date :-to make a date and time code of any date.

Look up:- hlook up:-Searches for a value in the top row of a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Math and trig function :-ABS :- TO GET THE NUMBER WITHOUT SIGN.

EVEN :- TOGET THE EVEN NUMBER OF ANY ODD NUMBER

ODD:-TO GET THE ODD NUMBER OF ANY EVEN NUMBER.



FACT:- TO GET FACTORIAL NUMBER OF ANY NUMBER .


LCM:- TO GET THE LOWEST COMMON NUMBER OF A GROUP OF A NUMBER.


MOD :-TO GET THE REMAINDER OF ANY DIVISION.


POWER :-TO RAISED A POWER .


PRODUCT:-TO MULTIPLY ALL NUMBERS.


SQRT:-TO GET THE SQUARE ROOT OF ANY NUMBER.


DEFINED NAME :-BY THIS OPTION WE CAN ADD THE NAME


INTO THE NAME BOX.


FORMULA AUDITING:- BY THIS OPTION WE


CAN INSERT ARROW AND ALSO DELETE IT. AND SHOW


THE FORMULA AND ALSO CHECK IT.


CALCULATION :BY THIS OPTION WE CAN CALCULATE THE


VALUE MANUALLY OR AUTOMATICALLY.


DATA MENU :- GET EXTERNAL DATA :-BY THIS OPTION WE


CAN GET EXTERNAL DATA FROM ACCESS, TEXT, WEB AND


OTHER SERVER.


CONNECTION :-BY THIS OPTION WE CAN REFREASH ALL CONNECTION ,


SORT & FITLTER :- BY THIS OPTION WE CAN COPY THE DATA WITHOUT FORMULA .


DATA TOOLS:=TEXT TO COLUMN:- BY THIS OPTION WE


CAN DISTRIBUTE THE TEXT IN TO SEPARATE COLUMAN ,


REMOVE DUPLICATE :- TO REMOVE OR DELETE DUPLICATE


VALUE FROM THE SHEET.


DATA VALIDATION:- BY THIS OPTION WE CAN


PREVANT TO INSERT INVALID DATA INTO VALIDATED


AREA .


CONSOLIDATE:- TO SUM OR OTHER FORMULA INPUT IN DIFFERENT PLACES VALUE.

What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.

Goal seek
On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek. In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4. In the To value box, type the formula result that you want.
Out line :By this option we can group or ungroup the data in the sheet.
REVIEW MENU:-


CHANGES: PROTECT SHEET :BY THIS OPTION WE CAN MAKE A FILE READ ONLY BY GIVING THE PASSWORD.


ALLOW USERS TO EDIT RANGES :
Lock or unlock specific areas of a protected worksheet

By default, protecting a worksheet locks all cells so none of them are editable. To enable some cell editing, while leaving other cells locked, it's possible to unlock all the cells. You can lock only specific cells and ranges before you protect the worksheet and, optionally, enable specific users to edit only in specific ranges of a protected sheet.

VIEW MENU:

FREEZE PANE :- BY THIS OPTION WE CAN KEEP A PORTION OF THE SHEET VISIBLE WHILE THE REST OF THE SHEET SCROLL.