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
- Type data and press Enter (moves down) or Tab (moves right).
- Fill handle – the small square at the bottom-right of the active cell. Drag it to AutoFill:
- Copies the same value (
Lahore→Lahore,Lahore…). - Continues a series:
Mon→ Tue, Wed…;January→ February…;1, 2(select both) → 3, 4, 5…;Week 1→ Week 2…; dates. - Copies formulas with adjusted references.
- Copies the same value (
- Auto Fill Options button (after dragging): Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, Fill Days/Weekdays/Months.
- Double-click the fill handle to fill a formula down to the end of adjacent data.
- Fill → Series (Home → Editing → Fill → Series): choose Rows/Columns, Type (Linear, Growth, Date, AutoFill), Step value (e.g. 5) and Stop value (e.g. 100) → produces 5, 10, 15 … 100.
- Ctrl + D fills down, Ctrl + R fills right; Flash Fill (Ctrl + E) fills patterns.
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
- Merge & Center (Home → Alignment): select
A1:G1→ Merge & Center for a title. Options: Merge Across, Merge Cells, Unmerge Cells (Excel "split" = unmerge a merged cell). - Borders (Home → Font → Borders ▼): All Borders, Outside Borders, Thick Box Border, Bottom Double Border, More Borders (line style/colour).
- Wrap Text (Home → Alignment): shows long text on multiple lines inside the same cell; Alt + Enter inserts a manual line break in a cell.
- Alignment (left/centre/right, top/middle/bottom), Orientation (angled text), Number formats (General, Number, Currency, Percentage %, Date), Fill colour, Increase/Decrease Decimal.
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