1. Pivot Table and Pivot Chart:
· After creating a salary or marksheet
· Click on a blank cell
· Insert > Pivot table
· Check different 2 fields
· Pivot table will be created
· Click on pivot chart from option tab
· Select any chart
· Pivot chart will be created.
2. Future Value:
· Future amount / Maturity Amount (Ex. 50000)
· Installment (Ex. 24)
· Interest (Ex. 8%)
· Payable amount
i. Formulas > Financial > PMT
ii. Rate : Interest Cell /12
iii. NPER : Installment Cell
iv. PV : 0
v. FV : Future Amount
vi. Type ABS before PMT in Formula Bar
vii. Change Data Type into Number or General.
3. Data Table :
· We want to make a Data Table on PMT (future amount) calculation.
· Click on a blank cell > Press =, and click on a payable amount cell.
· Type different interest in row beside the payable amount cell
· Type different installment in column bottom of the payable amount cell
· Select the entire table
· Data > what if analysis > Data Table >
i. Row Input Cell : Select the interest cell
ii. Column Input Cell : Select the installment cell
iii. Ok
4. Sumif
· To calculate total net amount of a particular product
· Click on a blank cell
· Formulas > Math & Trig > Sumif
i. Range : Select the entire table
ii. Criteria: Type any product name
iii. Sum_Range: Selct the column of Net amount
5. Sumifs
· To find out total amount of lux and rate is 10.
|
item |
Rate |
qty |
amount |
|
lux |
10 |
2 |
20 |
|
dove |
20 |
3 |
60 |
|
lux |
10 |
6 |
60 |
|
sunsilk |
30 |
2 |
60 |
|
dove |
40 |
4 |
160 |
|
axe |
120 |
2 |
240 |
|
lux |
20 |
3 |
60 |
=SUMIFS(D2:D8,A2:A8,"lux",B2:B8,10)
Formula>math & Trig> Sumifs
Sum_range=select the amount column
Criteria_range1=product column
Criteria1=type any product name
Criteria_range2=rate column
Criteria2=type any rate
6. Count
· To find out total number of products
|
sl no |
Item |
|
501 |
ponds |
|
502 |
dove |
|
503 |
Lux |
|
504 |
sunsilk |
|
505 |
Oil |
|
506 |
Axe |
|
507 |
cinthol |
=COUNT(A1:A8)
Formula > more function > statistical > count
7. Countif
· To find out total number of a particular product.
|
item |
|
ponds |
|
dove |
|
lux |
|
sunsilk |
|
oil |
|
axe |
|
lux |
· =COUNTIF(A1:A8,"lux")
· Result is : 2
· Formula > more function > statistical > countif
· Range : select the product column
· Criteria : type any product name.
8. Countifs
· To find out total number of ponds and rate is 10.
|
item |
Rate |
qty |
amount |
|
ponds |
10 |
2 |
20 |
|
dove |
20 |
3 |
60 |
|
lux |
10 |
6 |
60 |
|
ponds |
30 |
2 |
60 |
|
oil |
40 |
4 |
160 |
|
ponds |
10 |
2 |
20 |
|
lux |
20 |
3 |
60 |
=COUNTIFS(A1:A8,"ponds",B1:B8,10)
9. MAX and MIN
· To find out maximum number and minimum number
· =MAX(Select the range of number)
· =MIN(Select the range of number)
10. Date & Time Function
· To calculate Date of Retirement(DOR)
|
DOB |
DOJ |
DOR |
|
10/9/1982 |
12/2/2010 |
10/9/2042 |
· *D2=DOB
· =DATE(YEAR(D2)+60,MONTH(D2),DAY(D2))
11. Logical function (if, or, and)
|
name |
sec |
per |
remarks |
|
sumi |
a |
25 |
not allowed |
|
rajesh |
a |
36 |
not allowed |
|
payle |
b |
32 |
not allowed |
|
sandip |
c |
52 |
not allowed |
|
tanmoy |
a |
63 |
allowed |
|
sumon |
b |
85 |
allowed |
|
puja |
c |
69 |
not allowed |
12. =IF(OR(B2="a",B2="b")*AND(C2>50),"allowed","not allowed")
13. Vlookup (Vertical Lookup)
· To find out or search name by entering roll from a mark sheet
|
Roll No. |
Student Name |
Bengali |
English |
Math |
History |
Geography |
Scince |
Total |
|
1 |
Surajit Das |
98 |
99 |
87 |
98 |
85 |
95 |
562 |
|
2 |
Sandipan Dey |
10 |
20 |
25 |
15 |
32 |
20 |
122 |
|
3 |
Sudipta Nag |
98 |
89 |
85 |
83 |
84 |
82 |
521 |
|
4 |
Soma Dhar |
20 |
25 |
30 |
45 |
20 |
10 |
150 |
|
5 |
Sanjiv Das |
60 |
65 |
56 |
68 |
62 |
70 |
381 |
Make a searching place as following
|
Roll No. |
Student Name |
|
|
|
Click on Student Name Cell Bellow
Formulas > Lookup & Reference > VLookup >
Lookup_Value: Click on Roll No. Cell Bellow
Table _Array: Select the entire mark sheet table
Col_index_num: type 2
Range_Lookup: false
Type any roll no. in Roll No. cell bellow and press enter.
Name will be shown.
|
Roll No. |
Student Name |
|
3 |
Sudipta Nag |
14. HLookup:
Click on a blank cell > Formulas > Lookup & Reference > HLookup >
Lookup_Value: Click on any field of marksheet
Table _Array: Select the entire mark sheet table
row_index_num: type 2 or 3 as a row number
Range_Lookup: false
15. MATCH function
- To find out row number of a particular item
|
item |
Rate |
qty |
amount |
|
ponds |
10 |
2 |
20 |
|
dove |
20 |
3 |
60 |
|
oil |
10 |
6 |
60 |
|
sunsilk |
30 |
2 |
60 |
|
oil |
40 |
4 |
160 |
|
axe |
120 |
2 |
240 |
|
cinthol |
20 |
3 |
60 |
· =MATCH("axe",A2:A8,0)
· Result is : 6
16. INDEX function
· To find out sl. No. of a particular item
|
sl no |
Item |
rate |
qty |
amount |
|
501 |
ponds |
10 |
2 |
20 |
|
502 |
dove |
20 |
3 |
60 |
|
503 |
Lux |
10 |
6 |
60 |
|
504 |
sunsilk |
30 |
2 |
60 |
|
505 |
Oil |
40 |
4 |
160 |
|
506 |
Axe |
120 |
2 |
240 |
|
507 |
cinthol |
20 |
3 |
60 |
· =INDEX(A2:A8,MATCH("lux",B2:B8,0))
· Result is : 503
· To find out rate of lux
· =INDEX(A1:E8,4,3) [Array,row_num,col_num]
· Result is : 10
17. Text functions
· CONCATENATE
|
sl no |
first name |
last name |
full name |
|
1 |
sujay |
das |
sujay das |
· =CONCATENATE(B2," ",C2)
· LEFT, LEN, UPPER
|
full name |
left |
len |
upper |
|
sujay das |
suj |
9 |
SUJAY DAS |
· *D2= FULL NAME
· =LEFT(D2,3)
· =LEN(D2)
· =UPPER(D2)
18. How to create auto fill list?
File > Option > Advanced > Edit Custom List >Type some name one by one in List Entries box > Add >ok.
19. Hide / Unhide Row & Columns
To hide any row or column – Right click on row number or column heading > hide.
To unhide row and column – select two row numbers or columns headings > unhide.
20. How to calculate working time?
Field names are In Time | Out Time | Total Working Time
Select the cell where you will enter value
Home > Format > Format Cell
Number > Category: Time > Type: 1:30PM
21. Select Working Time cell
Number > Category: Custom > Type: h:mm
Formula : = out time – in time
In time: 10:00 (10:00 AM)
out time: 19:00 (7:00 PM)
Total working Time: 9
22. Protect Sheet
Home > Format > Protect Sheet > Type any password > Reenter password> ok.
23. Freeze Panes
To fix the top row or first column, goto view tab> Freeze to row / Freeze first column.
No comments:
Post a Comment