I.21 Spreadsheets

⬆️ Back to: P-I.I. Introduction to Computing

🧭

Page Outline — click a heading to jump.

🎯

Exam pattern (2016–2026): No direct question. Use it as one of the two tools in 2021 Q2(a) (productivity tools, see I.19).

---

CSS Computer Science (Optional) — Paper-I, Section-A, Topic I, p.42-44. Covers: Spread Sheets.

Definition

A spreadsheet is application software organized into a grid of rows and columns (cells), used for numerical data, calculations, and analysis (e.g., Microsoft Excel, Google Sheets).

Key Concepts

• Cell: the intersection of a row and column, referenced like A1, B2 — holds a value, text, or formula
• Formulas: user-written expressions that calculate values from other cells (e.g., =A1+B1)
• Functions: built-in predefined formulas for common operations. Common functions a candidate should be able to describe individually:
  • SUM() — adds up all numeric values in a specified range (e.g., =SUM(A1:A10) adds every value from A1 through A10). Used whenever a total is needed — total marks, total sales, total expenses — and is the most frequently used spreadsheet function.
  • AVERAGE() — calculates the arithmetic mean of a range (e.g., =AVERAGE(B1:B5) sums B1:B5 and divides by the count of cells). Used for finding average scores, average monthly expenditure, etc.; differs from SUM in that it also divides by the number of items.
  • IF() — a logical/conditional function that tests a condition and returns one value if true and another if false, e.g., =IF(A1>=50,"Pass","Fail"). It lets a spreadsheet make decisions automatically rather than requiring the user to inspect each cell manually; IF statements can be nested for multiple conditions.
  • VLOOKUP() — "Vertical Lookup": searches for a value in the first (leftmost) column of a table/range and returns a corresponding value from a specified column in the same row, e.g., =VLOOKUP(A1, D1:F10, 3, FALSE) looks up A1 in column D and returns the matching value from column F. Widely used to pull related data from a second table (e.g., looking up an employee's department from an ID) without manually searching; the FALSE argument requests an exact match rather than an approximate one.
  • COUNT() / MAX() / MIN() — COUNT() counts how many cells in a range contain numbers; MAX() and MIN() return the largest and smallest values in a range respectively. Useful for quick range statistics (e.g., highest and lowest score in a class).
• Charts/Graphs: a visual representation of spreadsheet data, generated directly from selected cells so that it updates automatically if the underlying data changes.
  • Bar chart — uses rectangular bars (horizontal or vertical) whose length is proportional to the value; best for comparing discrete categories against each other (e.g., sales by region).
  • Line chart — plots data points connected by a line; best for showing a trend or change over a continuous interval such as time (e.g., monthly revenue across a year).
  • Pie chart — a circle divided into proportional slices; best for showing each category's share of a whole (e.g., percentage market share of competing products) — not suitable for showing change over time.
• Sorting & Filtering: reorganizing or narrowing displayed data based on criteria
• Absolute vs Relative referencing: a relative reference (A1) shifts when copied to another cell; an absolute reference ($A$1) stays fixed regardless of where it's copied

Common Spreadsheet Software

• Microsoft Excel — the market-leading spreadsheet application, part of Microsoft Office/365. Offers the deepest formula library, pivot tables, macros (via VBA — Visual Basic for Applications) for automation, and is the de-facto standard in corporate/government offices, including Pakistan's public sector. Advantage: extremely powerful and widely taught; disadvantage: paid license required for the desktop suite and files can become large/slow with heavy formulas.
• Google Sheets — a free, browser-based (cloud) spreadsheet that is part of Google Workspace. Its defining feature is real-time multi-user collaboration — several people can edit the same sheet simultaneously and see each other's cursor and changes live, and every change auto-saves to the cloud. Advantage: free, accessible from any device with internet, easy sharing via a link; disadvantage: needs an internet connection for full functionality and has a slightly smaller function/macro library than Excel.
• LibreOffice Calc — a free, open-source spreadsheet application (part of the LibreOffice suite) that can open and save Excel-compatible files. Advantage: no licensing cost and works fully offline; disadvantage: occasional formatting/formula-compatibility issues when exchanging complex files with Excel.

Worked Example

If cell C1 contains =A1+B1 and is copied to C2, it automatically becomes =A2+B2 (relative reference shifting). But if C1 contains =$A$1+B1 and is copied to C2, it becomes =$A$1+B2 — the $A$1 part stays fixed while B1 still shifts to B2.

---

MCQ Traps

• Absolute references ($A$1) do not change when copied; relative references (A1) do — a frequently tested practical distinction.
• A function is a predefined formula; a formula can be any user-written expression, which may or may not use a function.
• VLOOKUP searches only the leftmost column of the given range and looks rightward for the return value — it cannot look to the left of its search column (a common practical exam trap).
• A pie chart is unsuitable for showing change over time — use a line chart for trends and a pie chart only for proportional share of a whole at one point in time.

Frequently Asked MCQs (Past Papers)

• Q: Every formula in Excel must begin with which symbol? — A: The equals sign (=).
• Q: What is the maximum number of rows in a modern Excel worksheet (2016 onward)? — A: 1,048,576 rows.
• Q: What is the maximum number of columns in a modern Excel worksheet, and what is the last column labeled? — A: 16,384 columns; the last one is labeled XFD.
• Q: Which cell reference type does NOT change when a formula is copied to another cell? — A: Absolute reference (e.g., $A$1).
• Q: What does VLOOKUP search, and from where does it return a value? — A: It searches the leftmost column of a range and returns a value from a specified column to the right, in the same row.
• Q: Which chart type best shows each category's share of a whole? — A: Pie chart.
• Q: Which chart type best shows a trend over a continuous period like months or years? — A: Line chart.
• Q: What is the default file extension for an Excel workbook saved in Excel 2007 or later? — A: .xlsx (a macro-enabled workbook is .xlsm).
• Q: Which function returns the arithmetic mean of a range of cells? — A: AVERAGE().
• Q: Which print orientation is generally best for a wide spreadsheet with many columns? — A: Landscape.
• Q: What term describes the intersection of a row and a column in a spreadsheet? — A: Cell.

✅ Self-Test Questions (with Answers)

Q1. What is the difference between a relative and an absolute cell reference?

Ans: The difference lies entirely in what happens when a formula is copied to another cell. A relative reference, written plainly as A1, records the target cell's position relative to the formula's own cell ("two columns left, same row"). When the formula is copied, that offset is re-applied from the new location, so the reference shifts automatically. An absolute reference, written with dollar signs as $A$1, is locked to one fixed cell address and does not change no matter where the formula is copied. The $ acts as an anchor: $ before the column letter fixes the column, $ before the row number fixes the row.

A third form, the mixed reference, locks only one dimension — $A1 fixes the column but lets the row shift, while A$1 fixes the row but lets the column shift. In Excel, pressing F4 while editing a reference cycles through A1 → $A$1 → A$1 → $A1.

Practical illustration: if F1 holds a sales-tax rate of 18%, then writing =B2*$F$1 in C2 and filling it down 500 rows multiplies every row's amount by that one rate. If it had been written =B2*F1, the second row would wrongly read F2, the third F3, and the results would be nonsense — the classic error this distinction exists to prevent.

Q2. If =A1*2 in cell B1 is copied to B2, what does the formula become?

Ans: It becomes =A2*2.

Working it through step by step:

• The formula sits in B1 and refers to A1. Since A1 has no $ signs, it is a relative reference, stored internally as an offset — "one column to the left, same row."
• Copying from B1 to B2 moves the formula down by one row (+1) and across by zero columns (0).
• The same offset is applied to the reference: the column stays as column A (0 change), and the row increases by 1, so A1 becomes A2.
• The literal constant 2 is not a cell reference, so it is unaffected.
• Result: =A2*2.

Compare the variants to fix the rule:

• =$A$1*2 copied to B2 → =$A$1*2 (fully absolute, no change at all).
• =A$1*2 copied to B2 → =A$1*2 (row locked by $, so the downward copy changes nothing).
• =$A1*2 copied to B2 → =$A2*2 (column locked, but the row still shifts).
• =A1*2 copied sideways to C1 → =B1*2 (column shifts by +1 instead).

Q3. Name two built-in spreadsheet functions and what they compute.

Ans: A function is a predefined, built-in formula supplied by the spreadsheet software, invoked by name with arguments in parentheses. Every formula must begin with the equals sign (=). Two of the most important are:

• SUM() — adds all the numeric values in the specified range and returns the total. Syntax =SUM(A1:A10) adds every value from A1 through A10 inclusive. It ignores text and blank cells. It is the most frequently used spreadsheet function of all, applied to total marks, total sales, total expenditure; Alt + = inserts it automatically.
• AVERAGE() — returns the arithmetic mean of a range: it sums the numeric values and divides by the count of numeric cells. Syntax =AVERAGE(B1:B5). The crucial distinction from SUM is that division by the count also occurs, and that blank cells are excluded from the count while cells containing a literal zero are included — a difference that changes the answer.

Other commonly examined functions:

• IF(condition, value_if_true, value_if_false) — tests a condition and returns one of two values; e.g. =IF(A1>=50,"Pass","Fail"). IFs can be nested for multiple bands.
• COUNT() counts cells containing numbers; COUNTA() counts all non-empty cells.
• MAX() and MIN() return the largest and smallest values in a range.
• VLOOKUP() retrieves a matching value from a lookup table.

Remember the definitional trap: a function is a predefined formula, whereas a formula is any user-written expression — =A1+B1 is a formula containing no function at all.

Q4. Describe what VLOOKUP does and give a real-world scenario where it would be used.

Ans: VLOOKUP stands for Vertical Lookup. It searches for a specified value in the leftmost (first) column of a given table range, moves down that column until it finds a match, and then returns the value from a nominated column to the right, in that same row. Its syntax has four arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value — what you are searching for (e.g. an employee ID).
• table_array — the range to search; its first column must contain the lookup values.
• col_index_num — which column of that range to return, counted from 1 at the leftmost column of the range.
• range_lookup — FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match, which requires the first column to be sorted ascending. In practice always use FALSE unless banding values.

Real-world scenario: an examination office holds a master sheet of 5,000 candidates with columns Roll No | Name | Centre | Marks, and a second sheet listing only the roll numbers shortlisted for interview. To pull each shortlisted candidate's name automatically, the formula =VLOOKUP(A2, Master!$A$2:$D$5001, 2, FALSE) searches the roll number in A2 down the master's first column and returns the name from column 2. Filling it down populates all names instantly, eliminating thousands of manual lookups and the transcription errors they cause. Equivalent uses: fetching an employee's department from an ID, a product's price from its catalogue code, or a district's population from its name.

Key limitations and traps: VLOOKUP cannot look leftward — the return column must be to the right of the search column. The table range should be made absolute ($A$2:$D$5001) so it does not drift when the formula is filled down. A failed match returns #N/A, best wrapped as =IFERROR(VLOOKUP(...),"Not found"). Inserting a column into the table silently breaks the hard-coded col_index_num. Modern Excel addresses all of these with XLOOKUP, and the older workaround is an INDEX/MATCH combination.

Q5. Which chart type would you use to compare sales across five regions, and which would you use to show revenue trend over 12 months? Justify your choice.

Ans: Use a bar (or column) chart for the five regions, and a line chart for the 12-month revenue trend. The choice follows from the nature of the variable on the horizontal axis.

Bar chart for five regions. Regions are discrete, unordered categories — Punjab, Sindh, KP, Balochistan, Gilgit-Baltistan have no inherent sequence and nothing continuous lies between them. A bar chart encodes each value as the length of a rectangle from a zero baseline, and length is the visual property the human eye compares most accurately, so ranking five regions and judging "how much bigger" is immediate. Five categories is also an ideal count — readable without crowding.

Line chart for 12 months of revenue. Time is a continuous, ordered variable, and the question of interest is the trajectory — growth, decline, seasonality, turning points. Connecting the points with a line makes the slope visible, and slope is precisely what "trend" means. It also handles 12 points comfortably, where 12 bars would begin to look cluttered, and multiple years can be overlaid as separate lines for comparison.

Common trap: a pie chart is wrong for both of these tasks. It cannot show change over time at all, and comparing slice angles is far less accurate than comparing bar lengths; it is appropriate only for proportional share of a whole at a single point in time, ideally with few categories. Equally, a bar chart forced onto 12 months buries the trend that a line makes obvious. Note also that in spreadsheets a chart is linked to its source cells, so editing the data updates the chart automatically.