HELLO Guys, In This article I discuss about Ms Excel
Simple Short AND Easy :-Ms Excel Notes
INTRODUCTION:Ø MS EXCEL IS PART OF MS OFFICE
Ø MS EXCEL IS A CONCEPT OF WORKSHEET OR SPREAD SHEET
Ø THE EXTENSION PART OF THE FILE IS .XLSX
CONCEPT OF MS EXCEL 2007:
Ø A SHEET CONTAIN ROW & COLUMN IS CALLED WORKSHEET OR SPREAD SHEET.
ROW
COLUMN
Ø THE INTERSECTION POSITION OF ROW AND COLUMN IS CALLED CELL.
CELL
Ø CELL CAN BE DEFINED BY AN ADDRESS.
§ ROW ARE DENOTES NUMERICALLY
1, 2, 3, 4 ……………, 1048576
IN A SHEET MAXIMUM 1048576 ROWS ARE AVAILABLE
§ COLUMN ARE DENOTES ALPHABATICALLY
A, B, C, D ……………., XFD
IN A SHEET MAXIMUM 16,384 COLUMNS ARE AVAILABLE
Ø A COLUMN HAS A STANDARD WIDTH 8.43 (64 Pixels).
WE CAN ALSO INCREASE OR DECREASE THE WIDTH OF THE COLUMN.
Ø A ROW HAS A STANDARD HEIGHT 15.00 (20 Pixels).
WE CAN ALSO INCREASE OR DECREASE THE HEIGHT OF THE ROW.
WE CAN SAY THE CELL CAN BE DEFINED BY AN ADDRESS.
WHEN WE SAY THE CELL ADDRESS, IST SAY THE COLUMN AND THEN SAY ROW.
v CELL ADDRESS:
A
B
C
D
E
1
2
E2
3
C3
4
D4
5
8965
4563
Ø AT THE TIME OF CALCULATION THE CELL VALUE
WILL IGNORE, WE CAN CALCULATE USING THE CELL ADDRESS.
Ø AN EXCEL FILE IS CALLED WORK BOOK.
IN AN EXCEL FILE 3 SHEETS ARE AVAILABLE GENERALLY.
Ø WE CAN ALSO ADD 254 SHEETS MAXIMUM.
v CREATE THE FOLLOWING SALARY SHEET:
NAME OF THE EMPLOYEES
BASIC
DA
TA
HRA
GROSS
MR. D. RAY
2560
MR. C. DAS
2478
MR. S. BASU
2630
MR. M. SARDAR
2760
MR. B. NATH
2820
CONDITIONS:
1. DA WILL BE CALCULATED 10% ON BASIC
2. TA WILL BE CALCULATED 5% ON BASIC
3. HRA WILL BE CALCULATED 10% ON BASIC +DA
4. GROSS WILL BE CALCULATED BASIC+DA+TA+HRA
v A sheet can be rename:
- Home menu
- Format
- Rename sheet
- Input the name of the sheet
or
- RIGHT MOUSE CLICK ON THE SHEET
- RENAME
- INPUT THE NAME OF THE SHEET
v A sheet can be moved or copied:
- Home menu
- Format
- Move or copy sheet
- Select the sheet
- Create copy
Or
- SELECT THE DOCUMENT IN YOUR SHEET
- RIGHT MOUSE CLICK ON THE SELECTED AREA
- COPY OR CUT
- OPEN ANOTHER SHEET
- RIGHT MOUSE CLICK ON THE SHEET
- PASTE
v Insert work sheet
- Home
- Insert
- Insert sheet
(or short cut :: shift+f11)
v To delete a sheet:
- Home menu
- Delete
- Delete sheet
v To insert a row:
- Select the entire row
- Right mouse click over it
- Insert
Or
- SELECT THE ENTIRE ROW
- HOME MENU
- INSERT
- INSERT ROW
v To insert a column:
- Select the entire column
- Right mouse click over it
- Insert
Or
- SELECT THE ENTIRE ROW
- HOME MENU
- INSERT
- INSERT COLUMN
v To delete row:
- Select the entire row
- Right mouse click over it
- Delete
Or
- SELECT THE ENTIRE ROW
- HOME MENU
- DELETE
- DELETE ROW
v To delete a column:
- Select the entire row
- Right mouse click over it
- Delete
or
- SELECT THE ENTIRE COLUMN
- HOME MENU
- DELETE
- DELETE COLUMN
v Merge cell:
- Select the two or more cell
- Home menu
- Merge cell
v Hide row:
- Select the entire row
- Right mouse click over the row
- Hide
or
- SELECT THE ENTIRE ROW
- HOME MENU
- FORMAT
- HIDE / UNHIDE
- HIDE ROW
v Unhide row:
- Select the entire work sheet
- Home menu
- Format
- Hide / unhide
- Unhide row
v Hide column:
- Select the entire column
- Right mouse click over the column
- Hide
or
- SELECT THE ENTIRE COLUMN
- HOME MENU
- FORMAT
- HIDE / UNHIDE
- HIDE COLUMN
v Unhide column:
- Select the entire work sheet
- Home menu
- Format
- Hide / unhide
- Unhide column
v Background change of the sheet:
- Page layout menu
- Background
- Select the location
- Select the picture
- Ok
v To delete background of the sheet:
- Page layout menu
- Background
v Sheet can be hide:
- Home tab
- Format
- Hide / unhide
- Hide sheet
v Sheet can be unhide:
- Home tab
- Format
- Hide / unhide
- Unhide sheet
v Data can be sorting by ascending or decending order:
- Select the entire data base
- Data
- Sort
- Ascending or decending
- Select the field, on which sorting will be made.
Or
- HOME
- SORT & FILTER
v Wrap text:
- Select the cell
- Home menu
- Alignment
- Wrap text
v Shrink to fit:
- Select the cell
- Home menu
- Alignment
- Shrink to fit
v Text allignment orientation of degrees:
- Select the cell
- Home
- Allignment
- Orientation
v Filtering the data, by condition:
- Select the entire data base
- Data
- Filter
- A botton will appear on each field
- Click this botton
- Number filter
- Input the conditions.
or
- HOME
- SORT & FILTER
- FILTER
v Remove filtering the data:
- Data
- Filter
v AUTO FILL
- SELECT THE CELL, WHERE NEUMERIC VALUE IS AVAILABLE
- HOME MENU
- FILL
- SERIES
- SELECT COLUMN OR ROW
OR
- SELECT THE CELL, WHERE NEUMERIC VALUE IS AVAILABLE
- DRAG THE BOX
v INPUT HEADER AND FOOTER
- INSERT
- HEADER/FOOTER
- INPUT THE NAME OF THE HEADER / FOOTER
v READY MADE FORMAT YOUR TEXT
- SELECT THE PARTICULAR TEXT
- HOME
- FORAMT AS TABLE
- SELECT THE TYPE OF FORMAT
- OK
v TO WRITE ANY COMMENT ANY TEXT
- SELECT THE PARTICULAR TEXT
- REVIEW
- NEW COMMENT
- INPUT THE COMMENT
FOR DISPLAY COMMENT:
SELECT THE PARTICULAR CELL, WHERE COMMENT ARE EXIT, CLICK THE RIGHT MOUSE
OTHER PROPERTIES OF COMMENT:
1. COMMENT SHOULD BE HIDE
2. COMMENT SHOULD BE UNHIDE
3. COMMENT SHOULD BE SHOW
4. COMMENT SHOULD BE EDIT
5. COMMENT SHOULD BE DELETE
v ADVANCE FILTER
FILTERING THE DATA BY GIVEN CONDITION BY USER AND COPY THE FILTERING DATA TO THE ANOTHER LOCATION.
STEP 1:
- DATA
- ADVANCE FILTER
STEP 2:
Ø LIST RANGE
1ST SELECT THE ENTIRE DATA BASE (AOTUMATICALLY).
Ø CRITERIA RANGE
2ND SELECT THE CRITERIA DATA RANGE
(CONDITION DATA).
Ø COPY TO
3RD SELECT THE LOCATION, WHERE YOU WANT TO COPY.
v WHAT IS THE DIFFERENT BETWEEN AUTO FILTER AND ADVANCE FILTER?
AUTO FILTER
ADVANCE FILTER
1. IN CASE OF AUTO FILTER, DATA WILL BE FILTERED WITHIN THE DATA BASE.
1. IN CASE OF ADVANCE FILTER DATA WILL BE FILTERED, ON CONDITION AND COPY TO ANOTHER LOCATION
2. IN AUTO FILTER FUNCTION IS COPIED.
2. BUT IN ADVANCE FILTER THE FUNCTION DOES NOT COPIED.
3. IN CASE FILTER THE DATA BASE DOES NOT COPIED.
3. IN ADVANCE FILTER DATA BASE IS COPIED.
FUNCTIONS
1. SUM:
CALCULATE THE SUM VALUE OF A NEUMERIC VALUE.
=SUM (C2:E2)
2. AVERAGE:
AVERAGE FUNCTION IS USE FOR CALCULATED THE AVERAGE VALUE FOR NEUMERIC EXPRESSION.
=AVERAGE (C2:C6)
3. MIN:
CALCULATE THE MINIMUM VALUE OF A NEUMERIC EXPRESSION.
=MIN (C2:E2)
4. MAX:
CALCULATE THE MAXMIUM VALUE OF A NEUMERIC EXPRESSION
=MAX (C2:E2)
5. MOD:
CALCULATE THE REMIENDER OF A NEUMERIC EXPESSION
=MOD (582, 3)
582 WILL BE DIVIDED BY 3
6. INT:
CALCULATE THE INTEGRE VALUE OF NEUMERIC EXPRESSION
=INT (585965.3652)
7. SQRT:
CALCULATE THE SQURE ROOT OF A NUMERIC EXPRESSION
=SQRT (225)
8. ABS:
CALCULATE THE ABSOLUTE VALUE OF NEUMERIC EXPRESSION.
=ABS (C2-D2)
C2 = 582 AND D2 = 987
9. COUNT:
HOW MANY NUMBERS IN THE LIST OF RANGE
=COUNT (D2:D10)
10. POWER:
RETURN THE VALUE OF POWER NUMBER
EXAMPLE: 96
=POWER (9, 6)
11. PRODUCT:
CALCULATE THE PRODUCT VALUE OF A NEUMERIC EXPRESSION.
=PRODUCT (D2:E2)
12. LEFT:
DISPLAY THE CHARACTER FROM LEFT
=LEFT (D5, 10)
DISPLAY THE 10 CHARACTER FROM THE LEFT
13. RIGHT:
DISPLAY THE CHARACTER FROM THE RIGHT
=RIGHT (D5, 10)
DISPLAY THE 10 CHARACTER FROM THE RIGHT
14. MID:
DISPLAY THE CHRACTER YOU SPECIFIED FOR TEXT,
MENTION THE STARTING POSITION.
=MID (E11, 5, 5)
E11= SHIBRAMPUR YOUTH COMPUTE CENTRE
15. LEN:
DISPLAY THE LENGTH OF THE CHARACTER.
=LEN (E11)
16. UPPER:
CONVERT FROM UPPER CASE.
=UPPER (E11)
17. LOWER :
CONVERT FROM A LOWER CASE
=LOWER (E11)
18. PROPER :
CONVERT IN A PROPER CASE
=PROPER (E11)
19. ROUND:
CALCULATE THE ROUND VALUE OF A NEUMERIC EXPRESSION.
E11 = 85685.33325
=ROUND(E11,2)
E11=85685.33
=ROUND(E11,0)
85685.00
20. NOW:
DISPLAY THE CURRENT DATE AND TIME.
=NOW ( )
21. REPEAT:
DISPLAY ONE ALPHEBATE OR SYMBOL REPETEDLY.
=REPT (“$”, 10)
22. SEARCH:
DISPLAY THE PLACE NUMBER OF ANY SINGLE
CHARECTER.
=SEARCH (“H”, E11)
E11= SHIBRAMPUR YOUTH COMPUTE CENTRE
=LEFT (A1, SEARCH (“ ”, A1))
=RIGHT (A1, LEN (A1)-SEARCH (“ ”, A1))
IF CONDITION
IF IS THE MOST POWERFUL FUNCTION, IT IS USE FOR CONDITIONAL SITUATION.
SYNTEX:
=IF (CONDITION, “TRUE”, “FALSE”)
EXAMPLE:
1. DA WILL BE CALCULATED 10% ON BASIC, WHO ARE
WORKING IN ACCOUNTS DEPARTMENT (ACC), OTHERWISE 0.
=IF (B2= “ACC”, C2*10%, 0)
2. DA WILL BE CALCULATED 10% ON BASIC, WHO ARE
WORKING IN ACCOUNTS DEPARTMENT (ACC), 20% ON BASIC, WHO ARE WORKING IN PRODUCTION DEPARTMENT (PDN), OTHERWISE 0.
=IF (B2= “ACC”, C2*10%, IF (B2= “PDN”, C2*20%, 0))
3. DA WILL BE CALCULATED 10% ON BASIC, WHO ARE
WORKING IN ACCOUNTS DEPARTMENT, 20% ON BASIC, WHO ARE WORKING IN PRODUCTION DEPARTMENT AND 30% ON BASIC WHO ARE WORKING IN MARKETING DEPARTMENT
=IF (B2= “ACC”, C2*10%, IF (B2= “PDN”, C2*20%, IF (B2= “MKT”, C2*30%)))
4. HRA WILL BE CALCULATED 10% ON BASIC BUT RESTRICTED TO RS. 300
=IF (C2*10%>=300, 300, C2*10%)
LOGIC OPERATOR
WE CAN ALSO USE LOGIC OPERATOR (AND, OR) WITH IF CONDITIONS.
SYNTEX:
=AND (STATEMENT, STATEMENT)
=OR (STATEMENT, STATEMENT)
EXAMPLE
5. SPECIAL ALLOWANCE WILL BE GIVEN Rs. 500 FOR THOSE EMPLOYEES, WHO ARE WORKING IN MARKETING DEPARTEMNT AND WHOSE BASIC IS GREATER THAN 3000/-
=IF (AND (B2= “MKT”, C2>=3000), 500, 0)
6. SPECIAL ALLOWANCE WILL BE GIVEN Rs. 500 FOR THOSE EMPLOYEES, WHO ARE WORKING IN EITHER ACCOUNT DEPARTEMNT OR MARKETING DEPARTMENT.
=IF (OR (B2= “ACC”, B2= “MKT”), 500, 0)
7. SPECIAL ALLOWANCE WILL BE GIVEN Rs. 500 FOR THOSE EMPLOYEES, WHO ARE WORKING IN EITHER ACCOUNT DEPARTEMNT OR MARKETING DEPARTEMNT AND WHOSE BASIC IS GREATER THAN 3000/-
EXAMPLE:
1. REMARKS WILL BE CALCULATED PASS IF THE MARKS IS GREATER THAN 40 AND FAIL IF THE MARKS IS LESS THAN 40.
2. REMARKS WILL BE CALCULATED PASS IF THE MARKS IS GREATER THAN 40 AND THE SUBJECT ‘ENVS’ MARKS IS GREATER THAN 40 ELSE FAIL IF THE MARKS IS LESS THAN 40 OR THE SUBJECT ‘ENVS’ MARKS IS LESS THAN 40.
=IF (AND (OR (B2= “MKT”, B2= “ACC”), C2>=3000), 500, 0)
EXAMPLE:
1. REMARKS WILL BE CALCULATED PASS IF THE MARKS IS GREATER THAN 40 AND FAIL IF THE MARKS IS LESS THAN 40.
2. REMARKS WILL BE CALCULATED PASS IF THE MARKS IS GREATER THAN 40 AND THE SUBJECT ‘ENVS’ MARKS IS GREATER THAN 40 ELSE FAIL IF THE MARKS IS LESS THAN 40 OR THE SUBJECT ‘ENVS’ MARKS IS LESS THAN 40.
SUBTOTAL:
v HOW TO MAKE SUBTOTAL:
1. SORTING THE PARTICULAR FIELD ON WHICH SUBTOTAL IS MADE.
2. FOR MAKING SUBTOTAL
- SELECT THE ENTIRE DATA BASE
- DATA
- SUBTOTAL
- SELECT THE FIELD OF SUBTOTAL,
- SELECT THE FUNCTION
- OK
DELETE THE SUB TOTAL:
SELECT THE ENTIRE DATA BASE
- DATA
- SUBTOTAL
- REMOVE ALL
VALIDATION:
FIELD VALUE WILL BE RESTRICTED AT THE TIME OF INPUT VALUES.
SUPPOSE, INPUT THE BASIC AMOUNT FROM RANGE RS. 2000 TO RS. 5000
- SELECT THE RANGE OF CELL, WHERE THE VALUES ARE INSERTED.
- DATA
- DATA VALIDATION
Ø SETTING:
- WHOLE NUMBER
- INPUT THE RANGE OF VALUES,
MINIMUM -2000
MAXIMUM - 5000
- INPUT MASSAGE
INPUT BASIC RS.2000 TO RS.5000
- ERROR ALERT
- WRONG
INPUT INVALID DATA TRY AGAIN
GROUP & OUTLINE:
SCROLLING DATA BASE FROM GROUP & OUTLINE
SELECT THE COLUMN BASIC, DA, TA, HRA
AND GOTO DATA – GROUP AND UNGROUP
AUDITING:
- FORMULA
- FORMULA AUDITING
A) TRACE PRECEDENTS
B) TRACE DEPENDENCE
Ø REMOVE THE ARROWS
- FORMULA
- REMOVE ALL ARROWS
PROTECT A SHEET OR WORK BOOK:
- REVIEW
- PROTECT SHEET
OR
- PROTECT WORK BOOK
- INPUT THE PASSWORD
- REENTER THE PASSWORD
TO REMOVE THE PASSWARD:
- REVIEW
- UNPROTECT DOCUMENTS
- INPUT THE PASS WORD
TABLE:
CREATE A SERIES OF DATA BY TABLE OPTIONS.
DATA – WHAT IF ANALYSIS___ DATA TABLE
P
100000
R
12%
Y
15
180000
125000
225000
168000
302400
200000
360000
300000
540000
450000
810000
GOAL SEEK:
CALCULATE THE DESIRE VALUE BY CHANGING COROSPONDANCE CELL.
- DATA
- WHAT IF ANALYSIS
- GOAL SEEK
SCENARIOS:
CREATE A SET OF DATA, BY CHANGING FORMULA.
- DATA
- WHAT IF ANALYSIS
- SCENARIOS MANAGER
- ADD
- INPUT THE NAME OF THE SCENARIOS
- SELECT THE CHANGING CELL
(THAT MAY BE YEAR, RATE, PRINCIPAL)
- INPUT THE CHENGING VALUE
Thank You ....
Hope It Is Helpful To You...
){kind=link}
0 Comments
Don't Spam/Scam ....Otherwise Thanks For Comment...