Microsoft Excel (advanced)

Sub Total :-

Items Quantity Price

AC 6 56987
AC 5 52364
AC 9 89745
MOBILE 25 97875
MOBILE 30 86159
MOBILE 20 25568
TV 21 58624
TV 32 78634

STEP :- Table Select - Data - Sub total - Hlfl Sub total

window ¢V Quantity J Price Hl ( ) ¢Qq² ok.
Advanced Filter :-

Name Ben Eng Hist Geo Pol Total

A 89 68 73 73 88 Ben Total
B 65 78 84 66 79 >80 >400
C 91 46 63 59 82
D 75 95 55 67 87
STEP :- Data – Advanced - Advanced Filter Window ¢V open List Range
Hl j¡Ep select - criteria range Hl j¡Ep
Criteria Table select - Copy to Another Location Select Copy
to Hl j¡Ep স্থানে Result ¢V Beh click - ok

V-Lookup (Vertical Lookup) :-

HL¢V Table ¢e¢cÑø naÑ pq HL¢V S¡uN¡u Be¡l V-lookup
z - HL¢V School Hl Student Result Efl ¢i¢š
Total SeÉ V-lookup hÉhq¡l Ll¡ quz

NAME BEN ENG HIST GEO POL TOTAL

A 85 95 69 45 95 389
B 76 36 24 78 71 285
C 49 45 89 69 55 307
D 35 72 66 89 64 326
E 86 65 47 45 89 332 Name Total
C
STEP :- Name Hl cell H mouse -Data-Data validation-Allow(list)-
Source Hl box H mouse name select -ok. Hlfl V-
lookup Ll¡l SeÉ total Hl cell H mouse -Formulas-Lookup &
References-V-lookup-Lookup value Hl box H name Hl cell no
-Column index num(Name Total fkÑ¿¹ 7 ¢V Column a¡C
Hl 7 -Range lookup Hl box H False -ok.

H-Lookup(Horizontal Lookup) :- H-lookup Ll¡l SeÉ H¢V hÉhq¡l Ll¡ quz

Name A B C D Name Total

Ben 85 90 54 88 B
Eng 96 89 63 45
Hist 45 65 76 86
Geo 36 78 68 54
Pol 75 47 46 76
Total 337 369 307 349

STEP :- Name Hl cell H Data validation Ll¡l fl Total Hl cell H

mouse -Formulas-Lookup & References-H-lookup-Lookup value Hl
box H Name Hl cell no-Table array(Name Total fkÑ¿¹ select )-
Row index num (7)-Range lookup(False)-ok.

PMT-Function Hl hÉhq¡l :- hÉ¢š² hÉ¡ˆ fËi«¢a V¡L¡ d¡l fË¢a

a¡ ¢eeÑu Ll¡ qu PMT-Function Hl z
Q. 320000 V¡L¡u 25% Downpayment H HL¢V N¡¢s qmzh¡¢L V¡L¡ 4
8.36% h¡vp¢lL Payment zHl Jfl ¢i¢š Loan amount,
Monthly payment, Total payment ¢eeÑu z
=>
Purchase price 320000
Down payment rate 25%
Months 48
Annual interest rate 8.36%
Down payment amount
Loan amount
Monthly payment
Total payment
Formulas:-
Down payment amount :- =Purchase price * Down payment rate
=B1 * B2
Loan amount :- =Purchase price – Down payment amount
=B1 – B6
Monthly payment :- =PMT(Annual interest rate/12,Months,-Loan amount)
=PMT(B4/12,B3,-B7)
Total payment :- =monthly payment*months

Trial Balance :- Trial Balance H ¢h¢iæ Account Hl Debit Balance Hhw Credit
Balance Hl ¢eeÑu Ll¡ quz
Sl No. Head of Account Debit Balance Credit Balance
1 Cash account 5640
2 Purchase A/C 1560
3 Sales A/C 4200
4 Stationary A/C 500
5 Capital A/C 5000
6 Wages A/C 1500
7 Total 9200 9200

Debit Balance Hl Total Ll¡l SeÉ :- =Sum(Cash amount : Wages

amount)
=Sum(C2:C7)
Credit Balance Hl Total Ll¡l SeÉ :- =Sum(Cash amount : Wages
amount)
=Sum(C2:C7)

Advanced Salary Sheet :-

Emp Dept. Place Basic DA TA MA HRA Gross PF Tax Net designation

Name salary salary
A ACC KOL 18500
B PUR DEL 12652
C MKT MUM 14786
D SALE BHR 8412
E ACC RAJ 25785
F SALE MUM 10566
G MKT DEL 11742
H PUR MUM 14525
I ACC KOL 15542
Formulas :- 1. DA => pjÙ¹ LjÑQ¡l£l Dept. "Sale" Hhw Place "Kol" DA
qm Basic Hl 50%. Basic Hl 40%.
=If(And(B2="Sale",C2="Kol"),D2*50%,D2*40%)
2. TA => pjÙ¹ LjÑQ¡l£l Dept. "MKT" Abh¡ Basic 9000 h¡ a¡l
TA qm Basic Hl 20%. h¡¢Ll¡ TA e¡z
=If(Or(B2="MKT",D2>=9000),D2*20%,0)
3. MA => pjÙ¹ LjÑQ¡l£l Basic 12000 h¡ a¡l MA Basic Hl 10%,
Basic 8000 h¡ a¡l MA Basic Hl 5%, Basic 8000 Hl Lj
MA 200 V¡L¡z
=If(D2>=12000,D2*10%,If(D2>=8000,D2*5%,If(D2<8000,200)))
4. HRA => Basic Hl 5%
5. Gross Salary => Basic HRA Hl
=Sum(D2:H2)
6. PF => pjÙ¹ LjÑQ¡l£l Basic 6000 h¡ a¡l , Hhw
Dept. "ACC" & Place "Del" PF 1000 300.
=If(And(D2>=6000,B2="ACC",C2="Del"),1000,300)
7. Tax => pjÙ¹ LjÑQ¡l£l Basic 9000 V¡L¡l Abh¡
Dept. "PUR" Tax, Gross salary l 3%, 8%.
=If(Or(D2<9000,B2="PUR"),I2*3%,I2*8%)
8. Net Salary => Gross salary-(PF+Tax)
=I2-(J2+K2)
9. Designation => k¢c Basic salary 12000 V¡L¡ h¡ a¡l qu
"Officer",k¢c 8000 V¡L¡ h¡ a¡l qu "Manager",k¢c 8000 Hl qu
"Clerk".
=If(D2>=12000,"Officer",IF(D2>8000,"Manager","Clerk"))
Pivot Table :- H¢Vl HL¢V Table Hl (Summary) °al£ Ll¡ k¡uz
- Table ¢Vl à¡l¡ Pivot Table Hl Category, Amount, &
Country l Summary °al£ Llhz
Order Date Product Category Amount Country
11.02.18 Carrot Vegetables 8000 India
18.02.18 Apple Fruits 5000 USA
24.02.18 Mango Fruits 90000 Austerllia
26.02.18 Orange Fruits 75680 India
STEP :- Table Select Insert - Pivot Table - New Worksheet Hl
Click - ok - Hlfl ea¥e Worksheet ¢V X¡e Pivot Table
Field List ¢e¢cÑø Field …¢ml Click Report °al£ z

Wages ¢eeÑu :- HL¢V ¢LR¥ z fË¢a¢ce 8 hr Duty

zfË¢a O¾V¡ ¢fR¥ 30 V¡L¡ Hhw a¡l¡ k¢c Overtime fË¢a O¾V¡ ¢fR¥ 40
V¡L¡ z

Name Working Hours Overtime Wages

A 10
B 12
C 9
D 11
E 8
F 7
Formula :- Overtime =If(B2>8,B2-8,0)
Wages =If(C2>0,C2*40+8*30,B2*30)

Depreciation ¢eeÑu :- 80000 V¡L¡ HL¢V N¡¢s 8 hRl fl 28000 V¡L¡

N¡¢s¢V ¢hœ²u fË¢a hRl N¡¢s¢Vl SeÉ La V¡L¡ Loss a¡ ¢eeÑu Ll ?
=>
Initial Cost 80000
Salvage 28000
Years 8
Annual Depreciation =(B1-B2)/B3

Office Hl Stationary MlQ :- HL¢V Office H fË¢a Newspaper, Xerox, &

Courier h¡hc ¢LR¥ V¡L¡ MlQ quzHl Jfl ¢i¢š HL La V¡L¡ fË¢a MlQ qu
Hhw fË¢a ph La MlQ qu ¢qp¡h L¢lz
Month Items Cost
Jan Paper 465
Feb Xerox 759
Mar Courier 698
Apr Paper 893
Mar Paper 578
Apr Xerox 9685
Jan Xerox 974
Feb Courier 6397
Apr Paper 997
Mar Xerox 5978
Summary Hl Ešl Ll¡l SeÉ Sumif Function hÉhq¡l z

Items Summary
months Summary
Paper =sumif(B:B,E3,C:C)
Jan =sumif(A:A,E7,C:C)
Xerox
Feb
Courier
Mar