Comsats University Islamabad, Lahore Campus
Lab Manual
Applications of Information & Communication Technology
Course Instructor Muhammad Nasir
Table of Contents
Objectives...................................................................................................................................................... 3
Task-1 Auto complete and basic formatting.................................................................................................. 3
Task-2 Formula Calculation ........................................................................................................................... 3
Task-3 Conditional Formatting ...................................................................................................................... 4
Task-4 Create Chart ....................................................................................................................................... 4
Task-5 Create a pie chart. .............................................................................................................................. 5
Task-6 Background and picture Insertion. ...................................................................................................... 5
Task-7 Formula Calculation and data validation............................................................................................ 5
Task-8 Look up formula. ................................................................................................................................ 6
Task-9 IF function .......................................................................................................................................... 6
Task-10 Conditional formatting ..................................................................................................................... 6
Task-11 Creating a drop down list. ................................................................................................................ 7
Objectives
After performing this lab, students shall be able to:
▪ Use the auto complete function.
▪ Basic formatting.
▪ Using graph and pie charts.
▪ Background insertion.
▪ Formula calculation and data validation.
▪ Look up and IF formula.
▪ Conditional Formatting.
▪ Drop down lists.
▪ Pivot table and chart
Task-1 Auto complete and basic formatting
Create the table as per the given information and auto complete the first column. Also the
format the table as shown below. Also format the columns for dollar currency.
MONTH Sales Expense Profit
January $ 50,000.00 $25,000.00
February $ 65,000.00 $ 23,000.00
March $ 80,360.00 $ 79,200.00
April $ 75,230.00 $ 80,500.00
May $ 89,560.00 $ 85,000.00
June $ 45,320.00 $ 25,300.00
July $ 23,000.00 $ 45,000.00
August $ 89,230.00 $ 55,261.00
September $ 78,230.00 $ 58,321.00
October $ 45,256.00 $ 50,356.00
November $ 23,789.00 $ 45,783.00
December $500,236.00 $ 45,289.00
Total
Task-2 Formula Calculation
Calculate the profit column by using the formula. Profit=Expense-Sales
Also calculate the total row by adding the values above. Make sure that the negative
values are shown in this format -$5,270 not in brackets.
Task-3 Conditional Formatting
For all the negative values in the profit column apply the blue shading. Do not do this step
manually. See the sample below.
MONTH Sales Expense Profit
January $ 50,000.00 $25,000.00 $25,000
February $ 65,000.00 $ 23,000.00
March $ 80,360.00 $ 79,200.00
April $ 75,230.00 $ 80,500.00 -$5,270
May $ 89,560.00 $ 85,000.00
June $ 45,320.00 $ 25,300.00
July $ 23,000.00 $ 45,000.00
August $ 89,230.00 $ 55,261.00
September $ 78,230.00 $ 58,321.00
October $ 45,256.00 $ 50,356.00
November $ 23,789.00 $ 45,783.00
December $500,236.00 $ 45,289.00
Total $1,165,211 $618,010 $547,201
Task-4 Create Chart
Create the chart of the sales, expenses and profit column excluding the total row. Show the
axis, axis titles, chat title, gridlines and legend.
Task-5 Create a pie chart.
Task-6 Background and picture Insertion.
Add a background image of the Comsats logo to your workbook. Add the following data and
table heading. Useauto complete function for the first two columns.
S.NO Reg NO Student Name Theory Lab exam Result
50 50 100
1 F16-23 Abdullah 50 39
2 F16-24 Yumna 23 25
3 F16-25 Amna 45 50
4 F16-26 David 15 10
5 F16-27 Albert 32 45
6 F16-28 Rani 35 42
7 F16-29 Harry 45 50
8 F16-30 Tahira 29 30
9 F16-31 Danyal 45 49
10 F16-32 Hina 49 50
Task-7 Formula Calculation and data validation
Task-8 Look up formula.
Now add a column naming grade and auto populate it using the lookup formula.
HINT:
Use a table for the grade range as given.
0 F
50 D
65 C
80 B
90 A
The first column should contain the value and the second column contains the grade also
make sure that the data is in ascending order. Then use the formula LOOKUP (Lookup
value, lookup vector, result vector).
Task-9 IF function
Add another column in the table with the heading Remarks and using the IF function fill
the column usingthe criteria for total marks less than 50 write fail and for above 50 write
pass.
S.NO Reg NO Student Name Theory 50 Lab exam 50 Result Grade Remarks
100
1 F16-23 Abdullah 50 39 89 B Pass
2 F16-24 Yumna 23 25 48 F fail
3 F16-25 Amna 45 50 95 A Pass
4 F16-26 David 15 10 25 F fail
5 F16-27 Albert 32 45 77 C Pass
6 F16-28 Rani 35 42 77 C Pass
7 F16-29 Harry 45 50 95 A Pass
8 F16-30 Tahira 29 30 59 D Pass
9 F16-31 Danyal 45 49 94 A Pass
10 F16-32 Hina 49 50 99 A Pass
Task-10 Conditional formatting
Add another column name attendance in the table. Use percentage formatting for the entire
column with nodecimal places. Also format the Result column for three colors red, green
and yellow such that red indicatesthe lowest mark, yellow indicating the medium range and
green indicating the highest marks. Also apply conditional icons on the attendance table
such that attendance below 80 gets a different icon then the attendance above 80. Also
format the entire table. Lastly sort the result column from the smallest to largestvalue.
Task-11 Creating a drop down list.
Add another column “Major” and fill it using the drop down list. You can find the option
under data validation option. Use the following options for the major list BSSE, BSIT, BSCS.
Also program the errormessage to appear as shown below. Also add a gender column using
the same list function.
