Excel – Data Tab Assignment Exercises
Get External Data from Text
1. Open the notepad window and type the information in this format and save the file
EmployeeName,Designation
Harish,Designer
Gowtham,HR
Sundhar,Associate
Jegan,Developer
Kamesh,Engineer
Kumar,Admin
Indra,HR
2. In the excel data tab used Get External data command to import files from the text document
and refresh the data when the new data is added in the text document.
Output
EmployeeName Designation
Harish Designer
Gowtham HR
Sundhar Associate
Jegan Developer
Kamesh Engineer
Kumar Admin
Indra HR
Sorting
Create the table in the format below
Employee Pay Rate Weeks Total Pay
udaya 400 35 14000
gowtham 300 20 6000
sathish 345 30 10350
sundhar 450 24 10800
jegan 300 22 6600
indra 230 34 7820
lavanya 345 23 7935
Renuga 450 44 19800
Lakshmi 350 33 11550
Saranya 400 22 8800
Total 365 129755
Sort the table by Total pay as smallest to largest
Total
Employee Pay Rate Weeks Pay
gowtham 300 20 6000
jegan 300 22 6600
indra 230 34 7820
lavanya 345 23 7935
Saranya 400 22 8800
sathish 345 30 10350
sundhar 450 24 10800
Lakshmi 350 33 11550
udaya 400 35 14000
Renuga 450 44 19800
Total 287 103655
Advance Filter
Exercise 1:
List Range
Flight no Total Seats Seats sold Status Type
47 210 195 space available Inter
95 210 212 overbooked Domestic
291 350 312 space available Domestic
204 150 165 overbooked Domestic
140 210 210 flight full Domestic
301 150 145 space available Inter
38 350 350 flight full Inter
717 90 98 overbooked Domestic
724 90 89 space available Domestic
454 90 90 flight full Inter
936 350 339 space available Domestic
644 210 215 overbooked Inter
51 150 132 space available Domestic
320 90 45 space available Inter
347 150 112 space available Domestic
Criteria Range
Flight Total Seats
no Seats sold Status Type
space
<200 available Inter
Output
Flight no Total Seats Seats sold Status Type
301 150 145 space available Inter
320 90 45 space available Inter
Exercise 2:
Criteria Range
Flight Total Seats
no Seats sold Status Type
space
<>150 available Inter
Output
Flight no Total Seats Seats sold Status Type
47 210 195 space available Inter
320 90 45 space available Inter
Exercise 3:
Criteria Range
Flight Total Seats
no Seats sold Status Type
>150 overbooked Domestic
Output
Flight Total Seats
no Seats sold Status Type
95 210 212 overbooked Domestic
Exercise 4:
Criteria Range
Flight Total Seats
no Seats sold Status Type
>150 overbooked
space
available
Output
Flight no Total Seats Seats sold Status Type
47 210 195 space available Inter
95 210 212 overbooked Domestic
291 350 312 space available Domestic
301 150 145 space available Inter
724 90 89 space available Domestic
936 350 339 space available Domestic
644 210 215 overbooked Inter
51 150 132 space available Domestic
320 90 45 space available Inter
347 150 112 space available Domestic
Text to columns
Candidate name
Udayakumar, Umapathy
Gowtham, Vasudevan
Meganathan, Duraivel
Karthickeyan, Raja
Murugan, Annamalai
Harish, Ragavendra
Separate First name and Last by using this command
Output
Udayakumar Umapathy
Gowtham Vasudevan
Meganathan Duraivel
Karthickeyan Raja
Murugan Annamalai
Harish Ragavendra
Note:
Use Delimeters Option and select space and comma option
Subtotals
Table:
Date Salesman name Product Units Price Sales value
7/12/2016 Karthick Iphone 40 35000 1400000
7/13/2016 Suresh Blackberry 28 33000 924000
7/14/2016 Sathish HTC 46 28000 1288000
7/15/2016 Alex Iphone 35 35000 1225000
7/16/2016 Gowtham Blackberry 56 33000 1848000
7/17/2016 Mega HTC 44 28000 1232000
7/18/2016 Udaya Iphone 32 35000 1120000
7/19/2016 Karthick Blackberry 11 33000 363000
7/20/2016 Suresh HTC 23 28000 644000
7/21/2016 Sathish Iphone 23 35000 805000
7/22/2016 Alex Blackberry 43 33000 1419000
7/23/2016 Gowtham HTC 54 28000 1512000
7/24/2016 Mega Iphone 22 35000 770000
7/25/2016 Udaya Blackberry 44 33000 1452000
7/26/2016 Karthick HTC 23 28000 644000
7/27/2016 Suresh Iphone 22 35000 770000
7/28/2016 Sathish Blackberry 17 33000 561000
7/29/2016 Gowtham HTC 34 28000 952000
7/30/2016 Karthick Iphone 45 35000 1575000
7/31/2016 Mega Blackberry 33 33000 1089000
8/1/2016 Udaya HTC 24 28000 672000
8/2/2016 Karthick Iphone 22 35000 770000
8/3/2016 Gowtham Blackberry 12 33000 396000
8/4/2016 Sathish HTC 13 28000 364000
8/5/2016 Suresh Iphone 44 35000 1540000
8/6/2016 Mega Blackberry 34 33000 1122000
8/7/2016 Laxman HTC 23 28000 644000
8/8/2016 Laxman Iphone 33 35000 1155000
8/9/2016 Gowtham Blackberry 23 33000 759000
8/10/2016 Karthick HTC 12 28000 336000
Use subtotals option and select salesman name in at change in option
Use function (sum) select total sales in add subtotal
And also show the maximum unit sold by the salesman and select use function(max) and select units
in add subtotal
Output
Date Salesman name Product Units Price Sales value
7/15/2016 Alex Iphone 35 35000 1225000
7/22/2016 Alex Blackberry 43 33000 1419000
Alex Max 43 1419000
Alex Total 2644000
7/16/2016 Gowtham Blackberry 56 33000 1848000
7/23/2016 Gowtham HTC 54 28000 1512000
7/29/2016 Gowtham HTC 34 28000 952000
8/3/2016 Gowtham Blackberry 12 33000 396000
8/9/2016 Gowtham Blackberry 23 33000 759000
Gowtham Max 56 1848000
Gowtham Total 5467000
7/12/2016 Karthick Iphone 40 35000 1400000
7/19/2016 Karthick Blackberry 11 33000 363000
7/26/2016 Karthick HTC 23 28000 644000
7/30/2016 Karthick Iphone 45 35000 1575000
8/2/2016 Karthick Iphone 22 35000 770000
8/10/2016 Karthick HTC 12 28000 336000
Karthick Max 45 1575000
Karthick Total 5088000
8/7/2016 Laxman HTC 23 28000 644000
8/8/2016 Laxman Iphone 33 35000 1155000
Laxman Max 33 1155000
Laxman Total 1799000
7/17/2016 Mega HTC 44 28000 1232000
7/24/2016 Mega Iphone 22 35000 770000
7/31/2016 Mega Blackberry 33 33000 1089000
8/6/2016 Mega Blackberry 34 33000 1122000
Mega Max 44 1232000
Mega Total 4213000
7/14/2016 Sathish HTC 46 28000 1288000
7/21/2016 Sathish Iphone 23 35000 805000
7/28/2016 Sathish Blackberry 17 33000 561000
8/4/2016 Sathish HTC 13 28000 364000
Sathish Max 46 1288000
Sathish Total 3018000
7/13/2016 Suresh Blackberry 28 33000 924000
7/20/2016 Suresh HTC 23 28000 644000
7/27/2016 Suresh Iphone 22 35000 770000
8/5/2016 Suresh Iphone 44 35000 1540000
Suresh Max 44 1540000
Suresh Total 3878000
7/18/2016 Udaya Iphone 32 35000 1120000
7/25/2016 Udaya Blackberry 44 33000 1452000
8/1/2016 Udaya HTC 24 28000 672000
Udaya Max 44 1452000
Udaya Total 3244000
Grand Max 56 1848000
Grand Total 29351000
Data Validation
Table
Empid Empname Dept Salary
17026 udaya Sales 7500
17027 gowtham HR 8500
17028 sathish Medical 9500
17029 sundhar Sales 4500
17030 kamesh Comp 5000
17031 kishore Admin 6500
17032 kathir Associate 7000
17033 fahath Associate 7500
17034 madhan Comp 8000
17035 monish HR 8500
Use List option to add team name:
Comp
Sales
HR
Medical
Admin
Associate
Set the validation to whol number minimum (6000) and maximum (9000) and show the circular
invalid data in the output
Empid Empname Dept Salary
17026 udaya Sales 7500
17027 gowtham HR 8500
17028 sathish Medical 9500
17029 sundhar Sales 4500
17030 kamesh Comp 5000
17031 kishore Admin 6500
17032 kathir Associate 7000
17033 fahath Associate 7500
17034 madhan Comp 8000
17035 monish HR 8500
And also show the validation dialog box in the output
Consolidation
Table 1:
India
Month Product 1 Product 2 Product 3
Jan 33 12 23
Feb 12 32 22
Mar 33 32 10
Apr 23 45 22
May 43 31 3
Jun 54 22 32
Jul 65 29 6
Aug 7 20 54
Sep 55 7 44
Oct 44 8 33
Nov 3 45 2
Dec 33 22 22
Table 2:
Australia
Month Product 1 Product 2 Product 3
Jan 2 12 23
Feb 12 21 22
Mar 33 32 8
Apr 23 34 11
May 22 31 3
Jun 54 22 6
Jul 23 22 6
Aug 7 20 21
Sep 43 7 22
Oct 44 8 13
Nov 3 22 2
Dec 2 12 24
Table 3:
Belgium
Month Product 1 Product 2 Product 3
Jan 2 6 23
Feb 12 21 22
Mar 3 7 8
Apr 23 8 11
May 22 9 3
Jun 54 12 6
Jul 23 2 6
Aug 7 18 21
Sep 13 7 22
Oct 24 8 13
Nov 3 22 2
Dec 2 12 24
Output
Product 1 Product 2 Product 3
Jan 37 30 69
Feb 36 74 66
Mar 69 71 26
Apr 69 87 44
May 87 71 9
Jun 162 56 44
Jul 111 53 18
Aug 21 58 96
Sep 111 21 88
Oct 112 24 59
Nov 9 89 6
Dec 37 46 70
Pivot Tables
Table
Division Product Units Sales
North Hamam 10 12
South Hamam 2 2
East Hamam 12 3
West Hamam 12 4
North Lux 14 44
South Lux 23 3
East Lux 22 22
West Lux 14 3
North Peers 2 5
South Peers 22 4
East Peers 14 33
West Peers 16 4
North Fog 2 6
South Fog 3 5
East Fog 4 3
West Fog 5 6
North Park Avenue 4 2
South Park Avenue 3 1
East Park Avenue 2 4
West Park Avenue 4 2
Task to be performed,
1. Total Sales for each division
2. Compare sales of products across division
3. Examine sales of individuals product
Total Sales for each division
Sum of
Row Labels Sales
East 65
North 69
South 15
West 19
Grand Total 168
Set Row Labels as Division and column values as sales
Compare sales of products across division
Sum of
Row Labels Sales
East 65
Fog 3
Hamam 3
Lux 22
Park Avenue 4
Peers 33
North 69
Fog 6
Hamam 12
Lux 44
Park Avenue 2
Peers 5
South 15
Fog 5
Hamam 2
Lux 3
Park Avenue 1
Peers 4
West 19
Fog 6
Hamam 4
Lux 3
Park Avenue 2
Peers 4
Grand Total 168
Set Row Labels as Division, product and column values as sales
Examine sales of individuals product
Product Hamam
Sum of
Row Labels Sales
East 3
North 12
South 2
West 4
Grand Total 21
Set Row Labels as Division and column values as sales and report filter as product
Pivot Chart
Table
Division Product Units Sales
North Hamam 10 12
South Hamam 2 2
East Hamam 12 3
West Hamam 12 4
North Lux 14 44
South Lux 23 3
East Lux 22 22
West Lux 14 3
North Peers 2 5
South Peers 22 4
East Peers 14 33
West Peers 16 4
North Fog 2 6
South Fog 3 5
East Fog 4 3
West Fog 5 6
North Park Avenue 4 2
South Park Avenue 3 1
East Park Avenue 2 4
West Park Avenue 4 2
Set Legend Fields as Product
Set Axis Fields as Division
Set Values Fields as Sales
Output
Sum of Column
Sales Labels
Row Park Grand
Labels Fog Hamam Lux Avenue Peers Total
East 3 3 22 4 33 65
North 6 12 44 2 5 69
South 5 2 3 1 4 15
West 6 4 3 2 4 19
Grand
Total 20 21 72 9 46 168
Output Full View
What if Analysis
Goal Seek
Revenue % Contribution Contribution
Product A 150000 20% 30000
Product B 240000 25% 60000
Product C 15% 0
Total 390000 90000
Target 150000
Set the total contribution value : 150000 and product change the Revenue for product C
Output
%
Revenue Contribution Contribution
Product A 150000 20% 30000
Product B 240000 25% 60000
Product C 400000 15% 60000
Total 790000 150000
Target 150000
Scenario Manager
Monthly Income 3000
Rent 700
Travel 170
Food 250
Shipping 100
Going Out 400
Electricity 20
Phone 35
Gas 20
Water 15
Total expenses 1710
Amount Left Over 1290
Create a summary for July, August, September
Scenario Summary
Current
Values: July August September
Changing Cells:
monthlyincome 3000 2000 2500 3000
Rent 700 700 700 700
Travel 170 70 70 170
Food 250 250 250 250
Shipping 100 200 100 100
Going_Out 400 400 400 400
Electricity 20 20 20 20
Phone 35 35 35 35
Gas 20 20 20 20
Water 15 15 15 15
Result Cells:
$B$13 1710 1710 1610 1710
$B$14 1290 290 890 1290
Data Table
Table
Quantity 40000
Unitprice 6.5
Fixed costs 38400
Unit cost 3.78
Revenue 260000
Fixed costs -38400
Variable
cost -151200
Net profit 70400
Output
70400 5000 10000 15000 20000 25000 30000 35000 40000
5.00 (32300.00) (26200.00) (20100.00) (14000.00) (7900.00) (1800.00) 4300.00 10400.00
5.50 (29800.00) (21200.00) (12600.00) (4000.00) 4600.00 13200.00 21800.00 30400.00
6.00 (27300.00) (16200.00) (5100.00) 6000.00 17100.00 28200.00 39300.00 50400.00
6.50 (24800.00) (11200.00) 2400.00 16000.00 29600.00 43200.00 56800.00 70400.00
7.00 (22300.00) (6200.00) 9900.00 26000.00 42100.00 58200.00 74300.00 90400.00
7.50 (19800.00) (1200.00) 17400.00 36000.00 54600.00 73200.00 91800.00 110400.00
8.00 (17300.00) 3800.00 24900.00 46000.00 67100.00 88200.00 109300.00 130400.00
8.50 (14800.00) 8800.00 32400.00 56000.00 79600.00 103200.00 126800.00 150400.00
9.00 (12300.00) 13800.00 39900.00 66000.00 92100.00 118200.00 144300.00 170400.00
9.50 (9800.00) 18800.00 47400.00 76000.00 104600.00 133200.00 161800.00 190400.00
10.00 (7300.00) 23800.00 54900.00 86000.00 117100.00 148200.00 179300.00 210400.00