Saturday, September 18, 2021

Advanced Excel 2010 Notes

 

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

Microsoft Access 2010 Notes

  1.        What is Ms Access? ·          Microsoft Access is a  database management system  (DBMS) from  Microsoft  that combines the...