Courses / Application of ICT (GEN-103) – BS 1st Semester

Week 10 Lab 10 – MS Excel: Interface, AutoFill, Formulas and Built-in Functions

Week 10 – Data Communication and Transmission Media · 100 min read · 29 MCQs to check yourself

Lab objectives

Open Excel and identify the interface; enter and fill data with AutoFill and series; write formulas for total, percentage and average; merge/split cells, add borders, wrap text; use SUM, PRODUCT, AVERAGE, MAX, MIN, POWER, SQRT and COUNT.

Microsoft Excel is spreadsheet software used to organise data in rows and columns, perform calculations, analyse data and create charts. An Excel file is a workbook (.xlsx) containing one or more worksheets (sheets).

Opening Excel

Start → type Excel → Enter → Blank workbook (Ctrl + N).

The Excel interface

Part Description
Title bar Workbook name (Book1)
Quick Access Toolbar Save, Undo, Redo
Ribbon Tabs: Home, Insert, Page Layout, Formulas, Data, Review, View
Name Box Shows the address of the active cell (e.g. C5) or a selected range; can be used to jump to a cell
Formula Bar Shows/edits the content or formula of the active cell; fx button inserts a function
Column headings Letters A, B, C … XFD (16,384 columns)
Row headings Numbers 1 … 1,048,576
Cell Intersection of a row and a column – the basic unit for data
Cell address (reference) Column letter + row number, e.g. B3
Active cell The selected cell with a thick green border; its address is the active cell address in the Name Box
Range Group of cells written with a colon: A1:D10
Worksheet window / grid Area of cells
Sheet tabs Sheet1, Sheet2… at the bottom; + adds a sheet
Status bar Ready/Enter/Edit mode, quick Sum, Average, Count of selected cells, view buttons, zoom

Data types: labels/text (left-aligned), numbers/values (right-aligned), dates, formulas (start with =).

Task 1 – Filling columns and rows; AutoFill

Task 2 – Using formulas

A formula always begins with = and may use cell references, numbers and operators:

Operator Meaning Example
+ Add =B2+C2
- Subtract =B2-C2
* Multiply =B2*C2
/ Divide =B2/C2
^ Power =B2^2
% Percent =20%

Order of operations (BODMAS / PEMDAS): brackets → ^ → * and / → + and -. =2+3*4 gives 14, =(2+3)*4 gives 20.

Example – marks sheet (Total, Percentage, Average):

A B C D E F G
1 Name ICT English Maths Total Percentage Average
2 Ali 78 65 88 =B2+C2+D2 =E2/300*100 =E2/3
3 Sara 90 80 76 =SUM(B3:D3) =E3/300 (format as %) =AVERAGE(B3:D3)

Results for Ali: Total 231, Percentage 77, Average 77. Press Enter to calculate; edit with F2 or in the formula bar. Show formulas: Ctrl + ` (grave accent).

Task 3 – Merge and split cells, borders, wrap text

Task 4 – Built-in functions

A function is a predefined formula: =FUNCTION(arguments). Insert with fx or Formulas tab → Function Library, or AutoSum (Alt + =).

Function Purpose Example Result (if B2:B6 = 10, 20, 30, 40, 50)
SUM Adds numbers =SUM(B2:B6) 150
PRODUCT Multiplies numbers =PRODUCT(2,3,4) 24
AVERAGE Arithmetic mean =AVERAGE(B2:B6) 30
MAX Largest value =MAX(B2:B6) 50
MIN Smallest value =MIN(B2:B6) 10
POWER Number raised to a power =POWER(5,3) 125
SQRT Square root =SQRT(81) 9
COUNT Counts cells containing numbers =COUNT(B2:B6) 5
COUNTA Counts non-empty cells (text too) =COUNTA(A2:A6)
ROUND Rounds to digits =ROUND(77.456,1) 77.5
IF Logical test =IF(F2>=50,"Pass","Fail")

Colon (:) = range (B2 to B6); comma (,) = separate arguments: =SUM(B2:B6,D2).

Common errors: #DIV/0! (division by zero), #NAME? (misspelt function), #VALUE! (wrong data type), #REF! (invalid reference), ###### (column too narrow).

Lab exercise (submit)

Create a result sheet for 10 students with marks in 5 subjects (out of 100). Use AutoFill for roll numbers 101–110 (Fill Series, step 1). Calculate Total (SUM), Percentage, Average, Highest (MAX) and Lowest (MIN) in each subject, COUNT the students, show =POWER(2,10) and =SQRT(144), =PRODUCT of two cells, merge & centre the title, wrap long headings, apply All Borders and save as RollNo_Lab10.xlsx.

Excel Tutorial for Beginners (Kevin Stratvert)

Excel Formulas and Functions – Full Course (Kevin Stratvert)

Short and long questions

1. Define cell, cell address, active cell and range.

Model answer

A cell is the intersection of a row and a column. A cell address is the column letter plus row number (e.g. B4). The active cell is the currently selected cell (shown in the Name Box). A range is a group of adjacent cells written with a colon (e.g. A1:D10).

2. What are the Name Box and the Formula Bar?

Model answer

The Name Box shows the address of the active cell or selected range and can be used to jump to a cell. The Formula Bar shows and lets you edit the data or formula of the active cell.

3. What is AutoFill? Give two examples.

Model answer

AutoFill uses the fill handle to copy data or continue a series automatically. Examples: typing Jan and dragging gives Feb, Mar…; typing 1 and 2, selecting both and dragging gives 3, 4, 5…; dragging a formula copies it with adjusted references.

4. Explain formulas and built-in functions in Excel. Prepare formulas for Total, Percentage and Average of a student's marks in three subjects (B2:D2, each out of 100).

Model answer

A formula is an expression that starts with = and calculates a value using cell references, numbers and operators (+, -, *, /, ^); Excel follows the order: brackets, power, multiplication/division, addition/subtraction. A function is a predefined formula with arguments, e.g. SUM, AVERAGE, MAX, MIN, PRODUCT, POWER, SQRT, COUNT, inserted with fx or AutoSum (Alt+=). Formulas: Total in E2: =B2+C2+D2 or =SUM(B2:D2); Percentage in F2: =E2/300*100 (or =E2/300 formatted as %); Average in G2: =AVERAGE(B2:D2) or =E2/3. Drag the fill handle down to copy the formulas for other students. Highest marks: =MAX(B2:B11); lowest: =MIN(B2:B11); number of students: =COUNT(B2:B11).

5. Write the use of SUM, AVERAGE, MAX, MIN, POWER, SQRT and COUNT with examples.

Model answer

SUM adds (=SUM(A1:A5)); AVERAGE gives mean (=AVERAGE(A1:A5)); MAX largest (=MAX(A1:A5)); MIN smallest (=MIN(A1:A5)); POWER raises a number to a power (=POWER(2,5) = 32); SQRT square root (=SQRT(49) = 7); COUNT counts numeric cells (=COUNT(A1:A5)).

This lesson ends with a quiz of 29 MCQs. Open the lesson on PrepOasis to take it and save your progress.

Previous: Week 10 Theory – Data Communications and Transmission Media · Next: Week 11 Theory – Transmission Modes, Protocols, Topologies and Network Types

All lessons in Application of ICT (GEN-103) – BS 1st Semester