Microsoft Excel ~ All In One

Microsoft excel

Definition:

“This office tool is used for accounting calculation and also different daily usage software”.

Examples:
Bill, inventory, salary sheet, etc.

File extension: .xls

Note: combination of rows and columns.

Interface of MS Excel:

1). Title bar

2). Menu bar
3). Standard tool bar
4). Formatting bar
5). Formula bar

In this bar when user applies a formula, formula automatically appears in this bar, user can edit formula with the help of this bar.

Parts of formula bar:
Formula pane
Cell address box
No of columns in cover sheet (256)
No of rows in one sheet (25536)

Working area of MS Excel is known as work sheet.

Note:
By default 3 work sheets are visible when Excel is open.

To open MS Excel:

L.C on start menu
L.C on all programs
L.C on MS OFFICE
L.C on MS EXCEL

OR

Windows + R press keys
Type excel
Press enter

Note:
File menu, edit menu, view menu, and insert menu, are similar to MS Word.

Excel formulas:

Sum

(1) =cell + cell+……..
(2) Sum (range of cells)

Subtract

= cell – cell

Multiplication

= cell * cell

Divide

=cell / cell

Note:

In excel every formula starts from “= “ sign.
In cell numeric data have right alignment
Text has alignment

Percentage:
Total obtained marks *100 /total marks

Average:
Average (range of subjects)

If condition:

To bound some cells

To apple some restriction
=if (cell address > = 33, “pass”, “fail”)

If user wants to display data in alphabetic data then user must type data in double commas “

Grade scale:

Condition True value

= 90 ……………….A++
= 80…………………A+
= 70…………………A
= 60…………………B
= 50…………………C
= 40……………….D
= 33………………..E
F…………..false value

Nested if condition
To repeat if condition, in nested if condition repeat on seven times.

=if (per > = 90, “A++”, if (per > = 80, “A+”, if (per > = 70, “A”, if (per > = 60,”B”, if (per > = 50, “C”, if (per > = 40, “D”, if (per > = 33, “E”, “F”)

Remarks scale:

Condition
= A++…………………….outstanding
= A+………………………excellent
=A………………………..V. Good
= B………………………. Good
= C…………………………fair
= D………………………….Normal
= E………………………… Poor
Fail………………………..false value

= if (grade > = “A++”, “outstanding”, if (grade > = “A+”, ”Excellent”, if (grade > = “A”, “V good”, if ( grade > = “B”, “good”, if (grade > = “C”, “fair”, if (grade > = “D”, “normal”, if (grade > = “E”, “poor”, “fail”.)))))))

If and condition:

In this condition we bond cell two times or apply condition two times.
= if (and (per% cell address > = 33, cell address < =100),”pass”, “fail”)

Grade scale with nested If And:

Condition true value>90, < =100……………………………….A++

>80, < =90…………………………………A+
>70, < =80………………………..A
>60, < =70……………………….B
>50, < =60………………………C
>40, < =50………………………D
>33, < =40………………………E
False value……………………F

=if (and (per >90, per < =1 00), “A++”, if (and (per > 80, per < = 90), “A+”, if (and (per > 70, per < = 80), “A”, if (and (per > 60, per < = 70), “B”, if (and (per > 50, per < = 60), “C”, if (and (per > 40, per < = 50), “D”, if (and (per > 33, per < = 40), “E”, “F”,)))))))

If condition is logical. If and condition is technical.

Filtration functions:

Count:
To count numeric cells
= count (cell range)

Count A:
To count those cells having alphabetic and also numeric value.
= counta (cell range)

Count Blank:
To count blanks cells in database
= count blank (cell range)

Count if:
To count something with single criteria
= count if (cell range, criteria)

Sum if:
To take sum of numeric value with the help of criteria
=sumif (cell range, criteria, sum range)

Is blank:
To check cell if blank or not.

If cell is blank then answer is true otherwise false.

= is blank (cell)

Is test:
To check value in cell is alphabetic.
If cell contain alphabetic blank then answer will be true otherwise false.
= istext (cell)

Is number:
To check value of cell is numeric. If cell contain numeric value then answer will be true otherwise false.
= is number (cell)

Database functions:

Dcount:
To count something with different criteria
= dcount (database, field, criteria)

S sum:
To take sum of something or any person numeric value with different criteria.
=d sum (database, field, criteria)

D mine:
To check minimum value with the help of criteria.
= d min (database, field, criteria)

Tools protection:

1. Sheet protection

2. Book protection
3. File protection

Perform sheet protection
1. Pointer required sheet wants to protect

2. Tools menu
3. Protection
4. Sheet protection “L.C”
5. Type required password
6. Retype required password
7. Ok / save / tray to change

UN Protection:

1. Pointer required sheet wants to un protect

2. Tools menu
3. Protection
4. Un protect sheet “L.C”
5. Type required password
6. Ok / save / change required data

Perform selective UN Protection:

1. Select required data range wants to un protect

2. Ctrl + 1 “open format cell box”
3. L.C on lock tab
4. Locked “un check”
5. Ok
6. Repeat sheet protection steps

Selective protection:

1. Select all required sheet

2. Ctrl + 1 “format cell”
3. Protection tab “ L.C”
4. Locked “un check”
5. Ok
6. Select required range wants to protect
7. Ctrl + 1 “open format cell”
8. Protection tab “L.C”
9. Locked “check”
10. Repeat sheet protection steps

Book protection:
1. Pointer required book wants to protect
2. Tools menu
3. Protection
4. Book protection “L.C”
5. Type required password
6. Retype required password
7. Ok

File protection:
1. Pointer required file wants to protect
2. Tools menu
3. Option
4. Security tab “L.C”
5. Password to open “type required password”
6. Retype password
7. Ok

Macro:
“Save set of command / actions” Sheet to sheet move
1. Pointer required 1st sheet

2. Tools menu

3. Macro
4. Record new macro
5. Type required macro and key
6. Ok
7. Move required 2nd sheet
8. Stop macro “L.C”

Macro security level:
1. High level
2. Medium level
3. Low level

Change macro security level:
1. Pointer required book wants to change macro security level
2. Tools menu
3. Macro
4. Macro security “L.C”
5. Select required security level
6. Ok / save / close / open required book

Auto filter:

To filter any record with the help of criteria from drop down box.

1. Select required data base
2. L.C on data menu in menu bar
3. L.C on filter
4. L.C on auto filter
5. Select required criteria from drop down box

Advance filtration:
To check record from data base without formula.
1. L.C on data menu in menu bar
2. L.C on filter
3. L.C on advance filter
4. Select data range
5. Select criteria range
6. Select copy to another location cells
7. L.C on ok

Chart:

Graphical representation of rows and columns
1. L.C on insert menu
2. L.C on chart
3. Select chart design
4. Select data range and also select alignment of chart as row wise and column wise
5. L.C on next6. Apply chart formatting
7. L.C on next
8. Select chart as object or as new sheet
9. L.C on finish