1. ilter option in your Table. (Name / Total).

2. Click on Name Filter / Number Filter.

3. Click on Custom Filter / Top 10.
4. Write down the name in the first box / Set the Top Value
5. Click on OK.

25)How to Remove Filter in your Table?

1. Select the Table.
2. Click on Data tab.
3. Click on Filter from Sort and Filter Group.
OR
CTRL + SHIFT + L Key from Key board

26) How to Apply Data Validation in Cell / Cells /

Sheet / Table?
1. Select on cell/cells/sheet/table.
2. Click on Data tab.
3. Click on Data validation from Data tools box.
4. Click on Allow combo box.
5. Click on Whole number.
6. Write down Minimum and Maximum value.
7. Click on ok.
27)How to Remove Data Validation?
1. Select on Cell / Cells / Sheet / Table.
2. Click on Data tab.
3. Click on Data validation from Data tools box.
 4. Click on Clear all.
5. Click on OK.
28)How to Apply Subtotal in your Table?

A B C D

1 Product Name Purchase Purchase Rate Total Purchase

Quantity Rate
2 BOOK 5 100

3 BOOK 10 50

4 Key board 10 450

5 Key board 15 300

6 PEN 10 25

7 PEN 15 15

 Formula:-
Total Purchase Rate: - = (Purchase Quantity * Purchase Rate)
1. Select the table.
2. Click on Data tab.
3. Click on subtotal from outlines box.
4. Mark the subtotal field name.(Total purchase Rate, Purchase
Quantity and Purchase Rate)
5. Click on OK.

29)How to Remove Subtotal?

1. Select the table.
2. Click on Data tab.
3. Click on subtotal from outlines box.
4. Click on remove all
 30)How to Apply Goal Seek?
1. At first create a table.

A B C D E

1 Product Name Cost Price Sales Price Quantity Profit

2 Pen 10 15 10

3 Key Board 100 150 20

4 Monitor 5000 6000 5

 Formula:-
Profit: - = (Sales Price – Cost Price) * Quantity 
2. Select the any(1st) Profit Value. (single)
3. Click on Data tab.
4. Click on What-if Analysis.
5. Click on Goal Seek.
6. Click on ‘To value’ text box and write down any new value.
7. Click on ‘by changing cell’ box and select the Sales Price Value.
8. Click on ok

31) How to Calculate Purchase and Sales?

 TOTAL
PRODUCT PURCHASE PURCHASE PURCHASE SALES SALES TOTAL PROFIR
NAME QUANTITY RATE RATE QUANTITY RATE SALES RATE / LOSS STOCK
PEN 10 20 8 30
KEYBOARD 15 200 12 250
BOOK 20 560 15 600
MOUSE 10 200 8 500

 Formula:-
1. Total Purchase Rate :- = (Purchase Rate * Purchase Quantity)
2. Total Sales Rate :- =(Sales Rate * Sales Quantity)
3. Profit :- = (Total Sales Rate – Total Purchase Rate)
4. Stock :- = (Purchase Quantity- Sales Quantity)
32)How to Create Mark sheet?
MAXI MINI AVER PERCEN PASS /
NAME BEN ENG MATH P SC L SC GEO HIST TOTAL COUNT DIVISION GRADE RANK
MUM MUM AGE TAGE FAIL
SUMON 85 98 85 69 100 18 84
AKASH 8 75 96 58 45 10 63
RAHUL 69 78 26 45 25 65 58
DEEP 51 87 45 96 88 74 75
ZINNA 85 98 85 69 100 18 84

 FORMULA:
1. Total: - =Sum (Give the subject Cell range)
2. Minimum: - =Min (Give the sub. Cell range)
3. Maximum: - =Max (Give the sub. Cell range)
4. Average: - =Average (Give the sub. Cell range)
a. Select the Average Cell
 b. Click on Home Tab
c. Select the Number from general combo box in Number
group.
d. Click on Outside.
5. Count: - =Count (Give the subject Cell range)
6. Percentage :- =(Total number cell address/Count cell
address/100)
a. Select the cell.
b. Click on Home tab.
c. Click on Percentage (%) symbol from number box.
d. Click on Outside.
7. Division :- =If(Give the total number cell address>700,“Out
of Range”,If (Give the total no cell
address>=525,“Star”,If(Give the total no cell
address>=420,“1st Div”,If(Give the total no cell
address>=315,“2nd Div”,If (Give the total no cell
address>=175,“3rd Div”,“Fail”)))))

8. Total Grade :- =If(Give the total no cell address>700,“Out of

Range”,If(Give the total no cell address>=525,“A+”,If(Give the
total no cell address>=420,“A”,If(Give the total no cell
address>=315,“B”,If(Give the total no cell address>=175,“C”,
“D”)))))

9. Pass / Fail :- =IF(MIN(SELECT THE SUBJECT CELL

RANGE)>=25,“PASS”,“FAIL”) 
10. RANK :- =RANK(SELECT THE FIRST TOTAL CELL ADDRESS ,
SELECT ALL TOTAL CELL ADDRESS (F4 KEY FOR LOCK),0) ENTER KEY

11. Subject Grade: - = If(Give the subject no cell address>100,

“Out of Range”,If(Give the subject no cell address>=90,“AA”,If
(Give the subject no cell address>=80,“A+”,If(Give the subject no
cell address>=60,“A”,If(Give the subject no cell
address>=45,”B+”,If(Give the subject no cell
address>=34,”B”,if(Give the subject no cell
address>=25,”C”,”D”)))))))

33)How to Insert Comment in any cell?

1. Select on any cell.
2. Click on Review tab.
3. Click on new comment.
4. Write down comment word.
5. Click on outside.

OR

1. Right Click on your cell.

2. Click on insert comment.
3. Write down comment word.
4. Click on outside.

OR
Press SHIFT + F2 Function Key from Keyboard.

34)How to modify any comment?

1. Select on comment cell.
2. Click on Review tab.
3. Click on edit comment.
4. Modify your comment.
5. Click on outside.
35)How to Show and Hide comment?
1. Select on comment word.
2. Click on Review tab.
3. Click on show/hide comment.
4. Click on outside.
36)How to Delete Comment?
1. Select on comment cell.
2. Click on Review tab.
3. Click on delete.
4. Click on outside.
Or
1) Right click on target Comment Cell
2) Click on Delete Comment.
37)How to Protect your worksheet?
1. Click on Review tab.
2. Click on protect sheet.
3. Enter your password.
4. Click on ok.
5. Reenter confirm password.
6. Click on ok.
 38)How to Unprotect your worksheet?
1. Click on Review tab.
2. Click on unprotect sheet.
3. Enter your password.
4. Click on ok.
39) How to Protect your workbook?
1. Click on Review tab.
2. Click on Protect Workbook from Changes group.
3. Click on Protect Structure and Window.
4. Enter the password.
5. Click on ok.
6. Reenter the Same Password in Reenter Password Box.
7. Click on Ok.
40)How to Unprotect your Work Book?
1) Click on Review tab.
2) Click on Protect Workbook from Changes group.
3) Click on Protect Structure and Window.
4) Enter the password.
5) Click on Ok.
41)How to Use Freeze Pane?
1. Select on particular row/column.
2. Click on View tab.
3. Click on freeze panes from window box.
4. Click on freeze panes.
42)How to Remove Freeze Panes in your sheet?
1. Click on View tab.
2. Click on freeze panes from window box.
3. Click on unfreeze panes.
 43) How to Show / Hide Gridlines, Heading, and
Formula bar?
1) Click on View tab.
2) Click on Check Box – Gridlines / Headings / Formula bar (MARK /

UNMARK)

44)How to select particular column without mouse?

1. Place the cursor in your cell.
2. Press the CTRL + SPACE KEY
i) Insert Column – CTRL + + key
ii) Delete Column – CTRL + -
45)How to select particular row without mouse?
1. Place the cursor in your cell
2. Press SHIFT + SPACE KEY.
i) Insert Row – CTRL + + key
ii) Delete Row – CTRL + -

46)FORMULA:
1. MOD FUNCTION:
=MOD (25, 4)
OUTPUT =1

2. SQRT FUNCTION:
=SQRT (81)
OUTPUT=9

3. POWER FUCTION:
=POWER (5, 2)
OUTPUT =25
 4. ROMAN FUNCTION:
=ROMAN (67)
OUTPUT= LXVII
47)HOW TO CALCULATE LOAN AMOUNT?
LOAN AMOUNT
RATE OF DURATIONS TOTAL INSTALLMENT
SL NO CUSTOMER NAME LOAN AMOUNT INTEREST
INTEREST OF (YEARS) AMOUNT (MONTHLY)
1 DR. RAJESH 50,000 5% 4
2 MR YUSUB AMINA 780,000 4% 5
3 MS AJIMA BEGAM 45000 7.50% 1
4 MR ABBASUDDIN 1200 1% 2
5 BISWAJIT GUI 100 0.50% 1
6 SUKHENA ROY 15000 80% 6
7 DR. SANTRA 500000 2.20% 3
9

FORMULA
INTEREST :- =(SELECT LOAN AMOUNT CELL * SELECT RATE OF
INTEREST *SELECT DURATION)
TOTAL AMOUNT:- =(SELECT LOAN AMOUNT CELL + SELECT
INTEREST CELL)
INSTALLMENT (MONTHLY):- =(SELECT TOTAL AMOUNT CELL /
SELECT DURATION /12)

98) How to Print the Excel Document?

1. At First Create A Document.
2. Select The Document. (CTRL + A)
3. Click On Page Layout Tab.
4. Click On Print Area From Page Setup Box.
5. Click On Set Print Area.
6. Click On Office Button.
7. Touch The Mouse Point In Print Option.
8. Click On Print Preview. (CTRL + F2 KEY)
9. Click On Page Setup Box From Print Box.
10. Click On Page Menu.
11. Click On Fit To Radio Button.
12. Set the Page – Width Tall
13. Click On Ok.
 14. Close The Print Preview Windows.
15. Click on Page Layout Tab. (Below the options are use for
Many pags)
16. Click On Dialog Box From Page Setup Box.
17. Click On Sheet Menu.
18. Place The Cursor In Rows To Repeat At Top Box And Select
The Particular Row.
19. Click on Ok
20. Press Ctrl+ F2 Key
21. Click On Next Page. (For Show)
22. Click on Print
23. Click On Ok.

99) How to save Excel document with password?

1) Create the Document first
2) Click on Office Button.
3) Click on Save as
4) Choose the Location (Desktop)
5) Write down the File name in file name box.
6) Click on Tools Combo
7) Click on General options
8) Write down the Password in Password box
9) Click on Ok
10) Reenter the Same password in Reenter password box
11) Click on Ok
12) Click on Save Button
13) Then Close the window.