Bal-I (1-15) - Final

Download as xlsx, pdf, or txt
Download as xlsx, pdf, or txt
You are on page 1of 65

S.

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

CALL PER BONUS=C


HOUR=H ALL PER
OW HOUR*B
MANY ONUS
CALLS/H RATE
OW
MANY
HOURS

TOTAL 4439 4439


AVERAGE 443.9 443.9
HIGHEST 789 789
LOWEST 124 124
COUNT 10 10
789
S.NO SALES RATE% COMMISSION SALARY TOTAL SALARY
1 20000 2% 400 15000 15400
2 60000 2% 1200 15001 16201
3 11223 2% 224.46 15002 15226.46
4 3322 2% 66.44 15003 15069.44
5 334 2% 6.68 15004 15010.68
6 5514 2% 110.28 15005 15115.28
7 5545 2% 110.9 15006 15116.9
8 4744 2% 94.88 15007 15101.88
9 7874 2% 157.48 15008 15165.48
10 55000 2% 1100 15009 16109
11 40000 2% 800 15010 15810
12 60000 2% 1200 15011 16211

COMMISSION TOTAL
=SALES*RATE SALARY=SALARY+CO
% MMISSION

TOTAL 273556 24% 5471.12 180066 185537.12


AVG 22796.33 2% 455.9266667 15005.5 15461.4266666667
HIGHEST 60000 2% 1200 15011 16211
LOWEST 334 2% 6.68 15000 15010.68
COUNT 12 12 12 12 12
SNO CITY FUEL PURCHASE MATERIAL PURCHASE MEDICIAL PURCHASE ANNUAL PURCHASE
1 BANGALORE $3,000 $1,500.00 $5,800.00 $10,300.00
2 CHENAI $2,000 $9,800.00 $4,700.00 $16,500.00
3 COIMBATORE $7,000 $2,300.00 $6,900.00 $16,200.00
4 HYDERABAD $6,700 $4,500.00 $2,100.00 $13,300.00
5 CHATTISGARH $2,300 $6,400.00 $3,200.00 $11,900.00
6 DELHI $6,800 $7,800.00 $5,400.00 $20,000.00
7 PUNE $2,300 $9,500.00 $6,500.00 $18,300.00
8 AGRA $1,200 $4,500.00 $5,800.00 $11,500.00
9 LUCKNOW $7,800 $6,500.00 $9,500.00 $23,800.00
10 WEST BENGAL $3,400 $3,100.00 $1,500.00 $8,000.00

Currency & Decimal


Home-General,Decimal
$25,000

$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%

TOTAL PRICE =PRICE*QUANTITY D3*E3 Relative Cell Reference


TAX=TOTAL PRICE*TAX(20%) F3*$E$15 Absolute Cell Reference
BONUS=TOTAL PRICE*BONUS(20%) F3*$E$16 Absolute Cell Reference
NET TOTAL TOTAL PRICE+Bonus+ F3+H3+(F3*E$17)-G3 Mixed Cell Reference
(Total
Price*Commission)-
TAX
ABSOLUTE CELL MIXED CELL
BONUS NET TOTAL
5500 30250
10400 57200
11400 62700
4050 22275
6650 36575
14250 78375
2700 14850
14450 79475
12350 67925
5200 28600

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

Name Grade Total Percentage Percentage


Maximum Score 100 Relative Reference Absolute Reference
John 85 85.00% 85.00%
Arun 65 76.47% 65.00%
Bell 45 69.23% 45.00% Absolute Reference($B$9)
Ivan 55 122.22% 55.00% ERROR-Relative Reference
Rani 95 172.73% 95.00%
Tom 90 94.74% 90.00%
Ram 98 108.89% 98.00%
Raj 75 76.53% 75.00%

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

Count Only Count No(Not Count Text/Formula/Blank/Errors) 10


Counta Count non empty cell 11
Count Blank 1
count if 3

ARUN FG Trim remove unwanted space ARUN FG


Proper first latter of every word in cap Arun Fg
Upper ARUN FG
Lower arun fg
LEN including space 7

AGRICULTURE LEFT A
INDIAN RIGHT E
MID CULT
CONCATENATE INDIANAGRICULTURE

Logical Function IF Function

Sales 1 sales 2 pass


85 35 fail

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

TAX NET PAY Filter-text filter


100 400
200 1600
300 3200
400 5200
500 7600
600 15900
700 20100
800 17200
900 21200
1000 18000

TAX NET PAY Filter-Number filter


100 400
200 1600
300 3200
400 5200
500 7600
600 15900
700 20100
800 17200
900 21200
1000 18000
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
Source 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

DATE REGION SALES REP COGS SALES


Output after filter 16/1/2023 WEST TOM 4356.00 6780.00
Criteria 1 30/1/2023 WEST CHERRY 4544.00 68229.00
11/3/2023 WEST TOM 3426.00 9567.00

Output after filter DATE REGION SALES REP COGS SALES


Criteria 2 16/1/2023 WEST TOM 4356.00 6780.00
11/3/2023 WEST TOM 3426.00 9567.00

Output after filter DATE REGION SALES REP COGS SALES


Criteria 3 16/3/2023 MIDWEST JOHN 234.00 2670.00

Output after filter DATE REGION SALES REP COGS SALES


Criteria 4 11/3/2023 WEST TOM 3426.00 9567.00
16/3/2023 MIDWEST JOHN 234.00 2670.00

Output after filter DATE REGION SALES REP COGS SALES


Criteria 5 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
Criteria 1 Criteria 4
REGION DATE
WEST >07-03-2023

Criteria 2
REGION SALES REP
WEST TOM

Criteria 3
COGS
<500

Uses: basic filter -filter it on the


place,copy & paste in different
page but in advanced filter copy & Data-Advanced-
paste in same sheet Filter-list range-
criteria range-
copy in another
location
Criteria 5
DATE
<=24-02-2023
DATE REGION SALES REP COGS
16/1/2023 WEST TOM 4356.00
30/1/2023 WEST CHERRY 4544.00
11/3/2023 WEST TOM 3426.00
SALES
6780.00 Active Sheet
68229.00
9567.00

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

S.NO NAME AGE GENDER MARTIAL STATUS


10 VIGNESH 28 MALE UNMARRIED
9 ABINESH 26 MALE MARRIED
8 SATHYA 24 FEMALE UNMARRIED
Z-A 7 SEVALI 23 FEMALE UNMARRIED
6 MUGESH 22 MALE MARRIED
5 ABI 25 MALE UNMARRIED
4 HASINI 23 FEMALE MARRIED
3 RIGZIN 24 MALE UNMARRIED
2 ANU 22 FEMALE MARRIED
1 ARUN 21 MALE UNMARRIED

S.NO NAME AGE GENDER MARTIAL STATUS


1 ARUN 21 MALE UNMARRIED
2 ANU 22 FEMALE MARRIED
Filter 3 RIGZIN 24 MALE UNMARRIED
Filter 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

FORMAT-DOUBLE CLICK/LEFT CLICK


Format Chart Area
Format Plot Area
LAPTOP Format Axis
SHOE
TOYS
500000

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

Click Plot Area-Add Data Label,Format Data Series

Qtr 1
Qtr 2
Qtr 3
Qtr 4
2500
a Label,Format Data Series

6000 1000 Qtr 1


Qtr 2
Qtr 3
Qtr 4

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

6000 1000 Qtr 1


Qtr 2
Qtr 3
Qtr 4

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

paste special transpose


YEAR 2015 2016 2017
SALES 1000 2000 2358
Bar Chart(More Sensible)
4500

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

0 500 1000 1500 2000 2500 3000 3500 4000 4500


2021

2022

0 500 1000 1500 2000 2500 3000 3500 4000 4500

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

0 500 1000 1500 2000 2500 3000 3500 4000 4500

2018 2019 2020 2021 2022


2700 3023 3456 3780 4000

4
SALES
5 YEAR

6
3

4
SALES
5 YEAR

0 500 1000 1500 2000 2500 3000 3500 4000 4500

You might also like