Lab Exercise: 5
1) Create the following table and solve the followings:
Name Age City Mobile Company Connection Discount
Number Name Charges
Joydeep 25 Kolkata 9830124587
Sandip 33 Howrah 9732568487
Anjan 62 Habra 9433201458
Bimal 68 Kolkata 9434025147
Subrata 45 Murshidabad 9831852146
Tapas 56 Siliguri 9231521478
2) The company name is treated as follows:
Mobile Number Company Name Company Connection
(Four characters Charges
from the left) BSNL 300
“9830” OR “9732” VODAFONE VODAFONE 500
“9831” OR “9900” AIRTEL AIRTEL 500
“9433” OR “9434” BSNL TATA 400
Rest TATA
The AIRTEL and VODAFONE Company offer discount @ 10% on Connection charges for those
who belong to Kolkata City and age is more than 60.
3) Now use Conditional formatting as follows for the ‘Company name’
Mobile Number Company Name Company Format
(Four characters (Fore Color)
from the left) BSNL RED
“9830” OR “9732” VODAFONE VODAFONE BLUE
“9831” OR “9900” AIRTEL
AIRTEL PINK
“9433” OR “9434” BSNL
TATA GREEN
Rest TATA
1
Lab Exercise: 4
1) Create the following table:
NAME DEPT DESIG CITY BASIC DA HRA TA PF GROSS NET
Sandip Acc Accountant Kolkata 10500
Sumit Pur Admin Durgapur 8500
Rana Sales Clerk Midnapur 8400
Firoz Acc Clerk Kolkata 9000
Mili Mkt Officer Basirhat 10500
Imran Com Clerk Nadia 8300
a) Calculation DA as 60% of BASIC for all employees.
b) Calculation HRA 40% for those who belong to “Acc” dept and working as an
“Accountant” or “Clerk”
c) Calculate TA as fixed RS. 750 for those who comes from other that “Kolkata” and
working as an “Officer” and Rs. 500 for those who comes from other than “Kolkata”
and Desig is other than “Officer”
d) Calculate PF as follows:
Desig PF
Officer 25% of Basic
Admin 20% of Basic
Clerk 15% of Basic
e) Calculate “Gross” salary
f) Calculate “Net” salary [Gross-Pf]
g) Sort the “Dept” field in Ascending order and “Desig” field in Descending order.
h) Display those records whose Dept is “Acc” and working as an “Accountant” or
“Officer”. [Using Auto and Advance Filter].
i) Display those records having the Basic>=8000 and Basic<10000.
j) Display the records whose names are started with ‘S’ and “Net” salary more than
17000.
k) Display the first two employees whose Net salaries are maximum.
l) Add two records with the using “Form” option.
m) Search the records who’s “Dept” are “Acc”. [Using “Form” option].
n) Delete the last two records that you have append [Using “Form” option].
o) Calculation the grand total of “Net” salary according to “Dept” field [Using
“Subtotal” option.]
p) Display the Maximum “Net” salary in “Dept” wise. [Using “Subtotal” option]
q) Save the file as Paysheet under ‘D:\user\YCTC’ folder.
2
Lab Exercise: 3
1) Prepare the following table in a new workbook and do the necessary operations to give
resulted figures under the appropriate column head as per given instructions:
Numbers Integer Return Square Root Rounded Figure
23.55
85.42
96.32
56.32
85.75
Round off all the square root figures up to 2 decimal places. And also find out the summation of all
the rounded figures at the bottom of Rounded Figure Column,
2) Prepare the following table in new worksheet and use necessary Functions as required.:
Title First Name Last Name Full Name Lower Upper Title Case
Case Case
Ms. Santu Ghosh
Mr. Anirban Sinha
Mr. Kalam Hossain
Ms. Anuradha Sing
3) Create the following tables within a Workbook in different Worksheet and prepared their
Consolidated report in different Worksheet:
Sales of January, 2008 Sales of February, 2008
Salesman Product1 Product2 Salesman Product1 Product2
Arup 52 56 Arup 46 54
Biplab 98 54 Biplab 85 72
Kaushik 75 65 Kaushik 71 63
4) Create the Table and solve the following:
[Link]. Name Course Fees Duration Sem-I Sem-II Total Grand % of Grade
TH PR TH PR TH PR Total Marks
1 Raja DFAS 5000 1 Year 120 42 86 92
2 Kapil DITA 5000 1 Year 115 43 86 88
3 Raju DDTP 5000 1 Year 118 42 82 86
a) Give Data validation on Sem-I and Sem-II, Theory marks within 150 and 100 and practical
marks of Sem-I and Sem-II is with 50 and 100.
b) Calculation the total theory and total practical marks.
c) Calculate Grand Total.
d) Calculate the % of Marks.
e) Insert a new field namely “Grade” after “% of Marks” field and calculate the Grand as
follows:
3
% of Marks Grade
>90 “A+”
80-90 “A”
70-79 “B+”
60-69 “B”
50-56 “C”
40-49 “D”
<40 “Not Applicable”
f) Create a Line Chart on Name and Grand Total Columns.
g) Use ‘Goal Seek’ to adjust the value of cell L3 to 340 by changing the cell amount in cell
H3.
h) Using Scenario Manager saved the values of grand total, % of marks and grade columns.
4
Lab Exercise: 2
1) Create the table as following in new Workbook.
Interest Calculation
Amount Rate of Interest
8.5% 9.75% 10.25% 11.75%
10000
11000
12000
13000
14000
2) Calculate the interest amount using formula (cell reference) and also using Data Table
option.
3) Change the Sheet name as interest.
4) Prepare the following table in a new worksheet and renamed the sheet as “Library”
Trans Book Book Memb. Memb Issue Date Received Due Over Fine
No No Name No Name Date Date Due
Days
T001 B001 M001 05/10/2007 05/22/2007
T002 B006 M004 05/12/2007 07/28/2007
T003 B004 M005 05/15/2007 05/28/2007
T004 B005 M002 05/18/2007 07/05/2007
T005 B003 M006 05/24/2007 05/29/2007
T006 B002 M003 05/29/2007 06/12/2007
5) Due Date will be after 15 days of Issue Date.
6) Calculate Over Due Days as (Received Date-Due Date). If it is negative show, it is as o (Zero).
7) Calculate Fine Amount with Rs. 5 per over Due Day.
5
Lab Exercise: 1
1) Using Mouse, Tab and Shortcuts perform the following tasks.
a. Go to the last row.
b. Go to the last Column.
c. Go to the Last Cell.
d. Back to the first cell of the worksheet.
2) Generate the following series
a. 1,2,3,4….20
b. 1,3,5,7 …. 50
c. Sun, Mon …
d. 100,95,90 … 45
e. 3,9,27,81 … 729
f. 01/01/2007, 01/04/2007, 01/07/2007, 01/10/2007, 01/01/2008
g. 01/04/2008, 05/04/2008, 09/04/2008, 13/04/2008, 17/04/2008
3) Create a custom series the contains a list of the names of your 10 friends.
4) Make the following Calendar.
July November
Sun Mon Tue Wed Thu Fri Sat Sun 7 14 21 28
1 2 3 4 5 Mon 1 8 15 22 29
6 7 8 9 10 11 12 Tue 2 9 16 23 30
13 14 15 16 17 18 19 Wed 3 10 17 24 31
20 21 22 23 24 25 26
Thu 4 11 18 25
27 28 29 30 31
Fri 5 12 19 26
Sat 6 13 20 27
5) Go to a new worksheet and prepare the table as follows:
Meter Opening Closing Metered Rate/Unit Total Rental Bill
No Reading Reading Units Amt. Charge Amt.
DE/023 4568 5528 3.20 87.50
AY/153 3520 3685 3.20 87.50
WE/085 1250 1420 3.20 87.50
RT/850 1850 2580 3.20 87.50
6) Naming the sheet as “Electricity”.
7) Create a new folder with your name and save the current workbook as [Link].
8) Best fit the columns if required.
9) Allow two decimal places for Rate/Unit to ‘Bill Amt’ date.
10) Allow Centre alignment only for Rate/Unit and Rental Charges columns in one shot.
11) Apply Currency format from Rate/Unit to Bill Amt columns with two decimal places.
12) Select entire table, apply Border and Pattern as per your choice.
13) Prepare a style “Mystyle” with the following specifications:
a. Font Face: Arial
b. Font Size 12 Points
6
c. Font Color: Any
d. Pattern: Any light color
e. Border: Double-line Dark Red colored border for outline and inside
both
14) Apply the above style on student table and see the effect.
15) Prepare the following series using tab in a new worksheet and name as “Series”.
16) Save and close the workbook.
17) Type the name of your five friends in the Custom List and apply if in the Worksheet.
18) Type five marks in A1 to A5 Cell and display the Sum of Marks.
19) Make the following:
Sl. Roll Name Beng Eng Math Additional Tatal Average
1 1001 Raja 78 85 95 45
Ghosh
2 1002 Imran Ali 75 76 90 20
3 1004 Arunava 76 69 80 92
Sarkar
Maximum Marks
Minimum Marks
a) Calculate the Total (If additional is More the 34 rest of marks add with Total), Average
Marks, Maximum and Minimum Marks.
b) Create new row before 1004 Roll No and type a student details like Sl. No. -3 Roll No.-
1003 Name- Deep Roy, Beng-70, Eng. – 60, Math-80.
c) Select the entire table, apply boarder and pattern as per your choice.
d) Make the Header as “YCTC” and Page Number as in the footer.