Bal-I (1-15) - Final
Bal-I (1-15) - Final
Bal-I (1-15) - Final
NO EMPLOYEE NAME HOURLY RATE Working Hours GROSS PAY GROSS PAY 6 % NET PAY
1 ARUN 1000 12 12000 720 11280
2 BALA 400 10 4000 240 3760
3 CHITRA 200 9 1800 108 1692
4 DINESH 600 14 8400 504 7896
5 ELANGO 8 8 64 3.84 60.16
6 VARUN 600 5 3000 180 2820
7 LOGESH 500 8 4000 240 3760
8 JOHN 100 9 900 54 846
9 VIKRAM 800 7 5600 336 5264
10 GOKUL 800 10 0 0
GROSS PAY 6
GROSS %=GROSS NET
PAY=HOUR PAY*6% PAY=GRO
RATE * SS PAY-
WORKING GROSS
HOURS PAY 6 %
ARUN E0421036
S.NO HOW HOW CALL PER BONUS BONUS
MANY MANY HOUR RATE
1 CALLS 245 HOURS 1 245 2% 4.9
2 356 1 356 2% 7.12
3 789 1 789 2% 15.78
4 324 1 324 2% 6.48
5 456 1 456 2% 9.12
6 786 1 786 2% 15.72
7 124 1 124 2% 2.48
8 346 1 346 2% 6.92
9 764 1 764 2% 15.28
10 249 1 249 2% 4.98
COMMISSION TOTAL
=SALES*RATE SALARY=SALARY+CO
% MMISSION
$20,000
$15,000
FUEL PURCHASE
$10,000 MATERIAL PURCHASE
MEDICIAL PURCHASE
ANNUAL PURCHASE
$5,000
$0
E AI E D H HI E A W L
LOR EN TOR ABA GAR DEL PUN AGR NO NGA
A CH BA ER IS CK BE
A NG IM YD ATT LU EST
B CO H CH W
S.NO GENDER TOTAL NO OF CUSTOMER
1 male 800
2 female 400
900
800
700
600
500
male
400 female
300
200
100
0
male female
male
female
S.No Employee Name Age Gender Marital Status Occupation Mobile No City Working Hours Salary
1 Arun 23 Male Married Business 9842234554 Chennai 5 25,000 Working Hint
2 Vrun 25 Male Unmarried IT Employee 9842234554 Coimbatore 6 35000 1. Data
3 John 45 Male Married Doctor 9842234554 Erode 7 50000 2. Alignment
4 Eswar 34 Male Married Driver 9842234554 Pune 8 20000 3. Page Layout-Select Theme-Choose
5 Dev 35 Male Married IT Employee 9842234554 Goa 9 40000 4. Page Layout-Print Titles-select page setup-Margin,Orientation,Size,Print,Area Back Griund,Print Tiltles
6 Geetha 36 Female Married Bank Employee 9842234554 Surat 4 35000
7 Sita 24 Female Unmarried IT Employee 9842234554 Salem 5 45000
8 Rani 26 Female Married Government Employee 9842234554 Banglaore 6 34000
9 Velu 22 Female Unmarried Teacher 9842234554 Mangalore 7 28000
10 Sam 20 Male Unmarried Farmer 9842234554 Lucknow 6 20000
Total 332,000
S.No Name Register No City Mobile No
1 Das 7201203 Surat 978654321
2 Nirmala 7201204 Salem 978654325
3 Kamal 7201205 Banglaore 978654313
4 Raj 7201206 Mangalore 978654367
5 Arun 7201207 Lucknow 978654343
6 Ram 7201208 Chennai 978667389
7 Sri 7201209 Coimbatore 978654321
8 Govind 7201210 Erode 978654321
9 Meena 7201211 Pune 978654378
10 Tom 7201212 Goa 978654345
Working Hint
1.Review-New Command,show/hide,edit
2. Review-Protect sheet,workbook
S.No Name Register No Tamil Mark English Mark Science Mark Total Marks
1 Das 7201203 67 55 45 167
2 Nirmala 7201204 68 78 56 202
3 Kamal 7201205 69 90 77 236
4 Raj 7201206 56 88 88 232
5 Arun 7201207 78 99 98 275
6 Ram 7201208 79 67 78 224
7 Sri 7201209 90 56 55 201
8 Govind 7201210 56 67 66 189
9 Meena 7201211 66 78 77 221
10 Tom 7201212 77 99 88 264
11 Raj 7201213 67 55 45 167
12 Arun 7201214 68 78 56 202
13 Varun 7201215 69 90 77 236
14 Logesh 7201216 56 88 88 232
15 Narun 7201217 78 99 98 275
16 Das 7201218 79 67 78 224
17 Nirmala 7201219 90 56 55 201
18 Kamal 7201220 56 67 66 189
19 Raj 7201221 66 78 77 221
20 Arun 7201222 77 99 88 264
21 Ram 7201223 67 55 45 167
22 Sri 7201224 68 78 56 202
23 Govind 7201225 69 90 77 236
24 Meena 7201226 56 88 88 232
25 John 7201227 78 99 98 275
26 Akil 7201228 79 67 78 224
27 Balu 7201229 90 56 55 201
28 Murali 7201230 56 67 66 189
29 Mano 7201231 66 78 77 221
Working Hint
1. Enter Data
2. select table to be freeze
3.view-freeze pane
S.No Employee Name Age Gender Marital Status Occupation Mobile No City
1 Arun 23 Male Married Business 9842234554 Chennai
2 Vrun 25 Male Unmarried IT Employee 9842234554 Coimbatore
3 John 45 Male Married Doctor 9842234554 Erode
4 Eswar 34 Male Married Driver 9842234554 Pune
5 Dev 35 Male Married IT Employee 9842234554 Goa
6 Geetha 36 Female Married Bank Employee 9842234554 Surat
7 Sita 24 Female Unmarried IT Employee 9842234554 Salem
8 Rani 26 Female Married Government Empl9842234554 Banglaore
9 Velu 22 Female Unmarried Teacher 9842234554 Mangalore
10 Sam 20 Male Unmarried Farmer 9842234554 Lucknow
Working Hours Salary
5 25,000
6 35000
7 50000 Working Hint
8 20000 1. Enter Data
9 40000 2. Alignment
4 35000 3.view-window- split,Hide/Unhide
5 45000
6 34000
7 28000
6 20000
PARTICULARS RELATIVE CELL ABSOLUTE CELL
ITEM NO PRICE QUANTITY TOTAL PRICE TAX
1 500 55 27500 3300
2 800 65 52000 6240
3 600 95 57000 6840
4 450 45 20250 2430
5 950 35 33250 3990
6 750 95 71250 8550
7 300 45 13500 1620
8 850 85 72250 8670
9 950 65 61750 7410
10 650 40 26000 3120
TAX 12%
BONUS 20%
COMMISSION 2%
ve Cell Reference
ute Cell Reference
ute Cell Reference
d Cell Reference
S.No Month Total Revenue Total Expenses Net Income
1 Jan 10,000 7,000 3,000
2 Feb 20,000 8,000 12,000 Relative Reference
3 March 30,000 9,000 21,000
4 April 40,000 12,000 28,000
5 May 50,000 32,000 18,000
MULTIPICATION TABLE
COLUMN HEADERS
1 2 3 4 5
10 10 20 60 240 1200
20 200
R
O
W 30 6000
H
E
A
D
40 240000
E
R
S 50 12000000
MULTIPICATION TABLE
COLUMN HEADERS
1 2 3 4 5
R
10 10 20 30 40 50
O
W 20
H
E 30
A
D
E
40
R
S 50
MULTIPICATION TABLE
COLUMN HEADERS
1 2 3 4 5
R
10 20 30
O
W 20 40 60
H
E 30 60 90
A
D
E
40 80 120
R
S 50 100 150
MULTIPICATION TABLE
COLUMN HEADERS
1 2 3 4 5
R
10 10 20 30 40 50
O
W 20 20 40 60 80 100
H
E 30 30 60 90 120 150
A
D
E
40 40 80 120 160 200
R
S 50 50 100 150 200 250
MULTIPICATION TABLE
COLUMN HEADERS
1 2 3 4 5
R
10 10 20 30 40 50
O
W 20 20 40 60 80 100
H
E 30 30 60 90 120 150
A
D
E
40 40 80 120 160 200
R
S 50 50 100 150 200 250
Mixed Reference
ERROR
Mixed Reference
Row Locked-$B34
Mixed Reference
Column Locked-D$43
Mixed Reference
Row Locked-$B54
Mixed Reference
Column Locked-C$63
S,NO EMPLOYEE ID EMPLOYEE NAMEHOURS RATE HOURS WORKED GROSS PAY TAX(7%) NET PAY
1 100 ARUN 400 5 2000 140 1860
2 101 RAGU 500 7 3500 245 3255
3 102 VRUN 600 6 3600 252 3348
4 103 RAM 700 4 2800 196 2604
5 104 GOKUL 750 5 3750 262.5 3487.5
6 105 PRABHU 430 6 2580 180.6 2399.4
7 106 SAM 250 7 1750 122.5 1627.5
8 107 JOHN 200 6 1200 84 1116
9 108 TOM 350 8 2800 196 2604
10 109 ANGEL 400 7 2800 196 2604
11 110 GEETHA 460 5 2300 161 2139
12 111 SOUNDHAR 600 6 3600 252 3348
13 112 RAJU 700 6 4200 294 3906
14 113 NAVEEN 750 7 5250 367.5 4882.5
15 114 SELVI 430 5 2150 150.5 1999.5
16 115 KUTTAN 250 5 1250 87.5 1162.5
17 116 SHREE 200 6 1200 84 1116
18 117 DAS 350 8 2800 196 2604
19 118 JENI 400 7 2800 196 2604
20 119 ABI 460 5 2300 161 2139
GROSS PAY=HOURS RATE*HOURS WORKED
SS TAX(7%)=GROSS PAY*7%
NET PAY=GROSS PPAY-TAX
FONT SIZE
FONT STYLE
BOLD
ITLAIC
UNDERLINE
FONT COLOUR
MERGE & CENTER
ORIENTATION
WRAP TEXT
Conditional Formatting
ITEM ITEM NAME PRICE QUANTITY IN KG TOTAL PRICE
1 APPLE 100 1 100
2 MANGO 200 2 400
3 ORGANE 300 3 900
4 BANANA 400 4 1600
5 PAPAYA 500 5 2500
6 PINE APPLE 600 6 3600
7 KIWI 700 7 4900
8 GRAPES 800 8 6400
9 WATERMELON 900 9 8100
10 MUSK MELON 1000 10 10000
Naming Range
ITEM ITEM NAME PRICE QUANTITY IN KG TOTAL PRICE
1 APPLE 200 2 400
2 MANGO 300 3 900
3 ORGANE 400 4 1600
4 BANANA 100 4 400
5 PAPAYA 200 5 1000
6 PINE APPLE 300 3 900
7 KIWI 400 2 800
8 GRAPES 300 3 900
9 WATERMELON 600 3 1800
10 MUSK MELON 500 2 1000
Formula-Naming Manager,Define Name,Rename,Delete,Create from Selection
EXCEL FUNCTIONS
EMPLOYEE NO FRIST NAME SUB NAME GENDER Age JAN
1 John T MALE <23 1000
2 Arun F MALE <24 1500
3 Bell G MALE <35 2000
4 Ivan H MALE >36 2500
5 Rani J FEMALE <23 3000
6 Tom K MALE <22 3500
7 Ram L MALE <23 4000
8 Raj R MALE <24 4500
9 Abdul T MALE <25 5000
10 Mohammed R MALE >26 5500
Text Arun
11 Arun Count 10
John Maximum 5500
12 Vrun Minimum 1000
AGRICULTURE LEFT A
INDIAN RIGHT E
MID CULT
CONCATENATE INDIANAGRICULTURE
Answer as Formula
Boolean Expression True,False 1 85>35
1,0 1 if(B39>C39,1,0)
Pass,Fail Pass
PASS,FAIL PASS
nested formula upper(D38),UPPER(D39)
Yes,No YES
B39+C39,0 120
today(),Today+10 11/5/2024
FEB MAR APRIL MAY AVERAGE TOTAL
2000 3000 4000 5000 3000 15000
2600 3400 4200 5350 3410 17050
3200 3800 4400 5700 3820 19100
3800 4200 4600 6050 4230 21150
4400 4600 4800 6400 4640 23200
5000 5000 5000 6750 5050 25250
5600 5400 5200 7100 5460 27300
6200 5800 5400 7450 5870 29350
6800 6200 5600 7800 6280 31400
7400 6600 5800 8150 6690 33450
10 10 10 10 10 10
7400 6600 5800 8150 6690 33450
2000 3000 4000 5000 3000 15000
AGRI
CULTURE
NAGRICULTURE INDIAN AGRICULTURE
COPYING DATA
EMPLOYEE NO FRIST NAME SUB NAME GENDER JAN FEB
1 John T MALE 1000 2000
2 Arun F MALE 1500 2600
3 Bell G MALE 2000 3200
4 Ivan H MALE 2500 3800
5 Rani J FEMALE 3000 4400
6 Tom K MALE 3500 5000
7 Ram L MALE 4000 5600
8 Raj R MALE 4500 6200
9 Abdul T MALE 5000 6800
10 Mohammed R MALE 5500 7400
CTRL +C
CTRL+P
PASTE SPECIAL
MAR APRIL MAY AVERAGE TOTAL
3000 4000 5000 3000 15000
3400 4200 5350 3410 17050
3800 4400 5700 3820 19100
4200 4600 6050 4230 21150
4600 4800 6400 4640 23200
5000 5000 6750 5050 25250
5400 5200 7100 5460 27300
5800 5400 7450 5870 29350
6200 5600 7800 6280 31400
6600 5800 8150 6690 33450
EMPLOYEE NO FRIST NAME GENDER HOUSE RENT HOURS WORKED GROSS PAY
1 John MALE 100 5 500
2 Arun MALE 300 6 1800
3 Bell MALE 500 7 3500
4 Ivan MALE 700 8 5600
5 Rani FEMALE 900 9 8100
6 Tom MALE 1100 15 16500
7 Ram MALE 1300 16 20800
8 Raj MALE 1500 12 18000
9 Abdul MALE 1700 13 22100
10 Mohammed MALE 1900 10 19000
EMPLOYEE NO FRIST NAME GENDER HOUSE RENT HOURS WORKED GROSS PAY
1 John MALE 100 5 500
2 Arun MALE 300 6 1800
3 Bell MALE 500 7 3500
4 Ivan MALE 700 8 5600
5 Rani FEMALE 900 9 8100
6 Tom MALE 1100 15 16500
7 Ram MALE 1300 16 20800
8 Raj MALE 1500 12 18000
9 Abdul MALE 1700 13 22100
10 Mohammed MALE 1900 10 19000
EMPLOYEE NO FRIST NAME GENDER HOUSE RENT HOURS WORKED GROSS PAY
1 John MALE 100 5 500
2 Arun MALE 300 6 1800
3 Bell MALE 500 7 3500
4 Ivan MALE 700 8 5600
5 Rani FEMALE 900 9 8100
6 Tom MALE 1100 15 16500
7 Ram MALE 1300 16 20800
8 Raj MALE 1500 12 18000
9 Abdul MALE 1700 13 22100
10 Mohammed MALE 1900 10 19000
TAX NET PAY
100 400 SORT A-Z
200 1600 SORT Z-A
300 3200
400 5200
500 7600
600 15900
700 20100
800 17200
900 21200
1000 18000
Criteria 2
REGION SALES REP
WEST TOM
Criteria 3
COGS
<500
Advanced filter-
copying in another
sheet
-create another
sheet-select-
advanced filter-
another location-filter New Sheet/New Workbook same
list (existing sheet)- procedure but keep it as active
citeria(existing sheet) sheet
S.NO NAME AGE GENDER MARTIAL STATUS
1 ARUN 21 MALE UNMARRIED
2 ANU 22 FEMALE MARRIED
3 RIGZIN 24 MALE UNMARRIED
4 HASINI 23 FEMALE MARRIED
5 ABI 25 MALE UNMARRIED
6 MUGESH 22 MALE MARRIED
7 SEVALI 23 FEMALE UNMARRIED
8 SATHYA 24 FEMALE UNMARRIED
9 ABINESH 26 MALE MARRIED
10 VIGNESH 28 MALE UNMARRIED
DATE REGION
16/3/2023 MIDWEST
HOME-SORT& FILTER 11/3/2023 WEST
DATE FILTER 7/3/2023 SOUTHWEST
OLD-NEW 1/3/2023 NORTHEAST
NEW-OLD 27/2/2023 SOUTHWEST
24/2/2023 NORTH
30/1/2023 WEST
16/1/2023 1
13/1/2023 SOUTHWEST
DATE REGION
24/2/2023 NORTH
1/3/2023 NORTHEAST ADD LEVEL
27/2/2023 SOUTHWEST
16/3/2023 MIDWEST
DATA -SORT 11/3/2023 WEST
SORTBY,SORTON,ORDER 7/3/2023 SOUTHWEST
ADD LEVEL,DELETE LEVEL 30/1/2023 WEST
SAME FOR FONT COLOR 16/1/2023 WEST
13/1/2023 SOUTHWEST
Source
Racer Name Timing Racer Name
SORT -TEXT WITH NUMBER ARUN 34 RAM
Sort with racer,values,a-z BHARANI 12 URJITH
then by timing,values,small-large Racer Name Timing(Minutes) ARUN
RAM 23 BHARANI
URJITH 56
DATE REGION SALES REP COGS SALES
16/3/2023 MIDWEST JOHN 234.00 2670.00
24/2/2023 NORTH JOHNCENA 2456.00 8900.00
1/3/2023 NORTHEAST JACK 6754.00 778844.00
7/3/2023 SOUTHWEST JACOB 2934.00 3590.00
SORT -TEXT WITH TEXT 13/1/2023 SOUTHWEST JOHN CENA 2345.00 7896.00
REGION WITH SALES REP 27/2/2023 SOUTHWEST STEPHEN 3675.00 4500.00
Sort with region,values,a-z 30/1/2023 WEST CHERRY 4544.00 68229.00
then by sales rep,values,a-z 16/1/2023 WEST TOM 4356.00 6780.00
11/3/2023 WEST TOM 3426.00 9567.00
OCCUPATION SALARY
FINANCE 100000
TEACHER 50000 Data-Advanced
DOCTOR 120000 SORT & Filter,Filter
HR 43000
MANAGER 5000
DRIVER 4600
CLEANER 8000
TAILOR 9000
BANK MANAGER 75000
MARKETER 90000
OCCUPATION SALARY
MARKETER 90000
BANK MANAGER 75000
TAILOR 9000
CLEANER 8000
DRIVER 4600
MANAGER 5000
HR 43000
DOCTOR 120000
TEACHER 50000
FINANCE 100000
OCCUPATION SALARY
FINANCE 100000
TEACHER 50000
DOCTOR 120000
HR 43000
MANAGER 5000
DRIVER 4600
CLEANER 8000
TAILOR 9000
BANK MANAGER 75000
MARKETER 90000
Timing(Minutes)
23
56
34
12
DATE REGION SALES REP COGS SALES
13/1/2023 SOUTHWEST JOHN CENA 2345.00 7896.00
16/1/2023 WEST TOM 4356.00 6780.00
30/1/2023 WEST CHERRY 4544.00 68229.00
24/2/2023 NORTH JOHNCENA 2456.00 8900.00
27/2/2023 SOUTHWEST STEPHEN 3675.00 4500.00
1/3/2023 NORTHEAST JACK 6754.00 778844.00
7/3/2023 SOUTHWEST JACOB 2934.00 3590.00
11/3/2023 WEST TOM 3426.00 9567.00
16/3/2023 MIDWEST JOHN 234.00 2670.00
S.NO NAME AGE GENDER MARTIAL STATUS OCCUPATION SALARY
1 VARUN 24 MALE UNMARRIED FINANCE 100000
2 MANJU 22 FEMALE MARRIED TEACHER 50000
3 RAMESH 29 MALE UNMARRIED DOCTOR 120000
4 HARSHINI 30 FEMALE MARRIED HR 43000
5 ABI RAM 33 MALE UNMARRIED MANAGER 5000
6 MUTHU 22 MALE MARRIED DRIVER 4600
7 SUMATHI 23 FEMALE UNMARRIED CLEANER 8000
8 SATHYARAJ 32 FEMALE UNMARRIED TAILOR 9000
9 ARULRAJ 26 MALE MARRIED BANK MANAGER 75000
10 VIGNESH 28 MALE UNMARRIED MARKETER 90000
Insert-Chart
RELIANCE SALES
INSTORE SALES WEBSITE SALES MAIL SALES
TOYS 12456 45677 45689
SHOE 34567 67890 456723
LAPTOP 790 456 990
600000
500000
400000
LAPTOP
300000 SHOE
TOYS
200000
100000
0
INSTORE SALES WEBSITE SALES MAIL SALES
Insert Chart(ALT+F1)
Separate Sheet-F11
CHART COMPONENTS
VERTICAL & HORIZONTAL AXIS
PLOT AREA & CHART AREA
SERIES
Gridlines
450000
400000
350000
300000
TOYS
250000 SHOE
LAPTOP
200000
150000
100000
50000
0
INSTORE SALES WEBSITE SALES MAIL SALES
Pie Chart-One Coulmn of no
QUARTER SALES
Qtr 1 2500
Qtr 2 1000
Qtr 3 2600
Qtr 4 6000
Qtr 1
Qtr 2
Qtr 3
Qtr 4
2500
a Label,Format Data Series
2600
Selecting plot area individivally and dragging
2500 For not showing higher side -select slice and format data point,Format data
6000 no and slice hidden
2600
nd format data point,Format data labels
Line Chart
YEAR REVENUE EXPENSE
Qtr 1 100000 57890
Qtr 2 167800 78900
Qtr 3 323000 243500
Qtr 4 456000 345789
500000
450000
400000
350000
300000
250000 REVENUE
EXPENSE
200000
150000
100000
50000
0
Qtr 1 Qtr 2 Qtr 3 Qtr 4
Column Chart Default Chart
YEAR SALES 4500
4500
2015 1000
4000 4000
2016 2000
3500 2017 2358 3500
2018 2700
3000 3000
2019 3023
2500 2020 3456 2500
YEAR 2021 3780
2000 SALES 2000
2022 4000
1500 1500
1000 1000
500 500
0 0
1 2 3 4 5 6 7 8 2015 2016
Year is not there so select the chart ,design-select data-year,edit,select years in table-ok
2015
4500
4000 2016
3500 2017
3000 2018
2500
YEAR 2019
2000 SALES
2020
1500
2021
1000
2022
500
0 500 10
0
2015 2016 2017 2018 2019 2020 2021 2022
2021
1000
2022
500
0 500 10
0
2015 2016 2017 2018 2019 2020 2021 2022
2015
2016
2017
2018
2019
2020
2021
2022
0 500 10
4000
3500
3000
2500
YEAR
2000 SALES
1500
1000
500
0
2015 2016 2017 2018 2019 2020 2021 2022
Column to bar chart(Click chart area and change chart type)
2015
2016
2017
2018
SALES
2019 YEAR
2020
2021
2022
2022
Filter for comparing highest & lowest sales do by filtering the table and inseert chart
2015 2015
2016 2016
2017 2017
2018 2018
SALES
2019 2019 YEAR
2020 2020
2021 2021
2022 2022
4
SALES
5 YEAR
6
3
4
SALES
5 YEAR