Spreadsheets: Class 10 Computer Science Notes, Short Questions and Numericals

Spreadsheet software such as MS Excel, its cells, rows and columns, data entry, formulas and common functions, cell references, formatting, sorting, filtering and charts, with worked calculation examples.

Key points

A spreadsheet is an application program that arranges data in rows and columns and can perform calculations on it automatically. Examples are Microsoft Excel, LibreOffice Calc and Google Sheets. Spreadsheets are used for marks sheets, budgets, accounts, bills and graphs.

Basic parts of a spreadsheet:

Workbook: the complete file, which contains one or more worksheets.
Worksheet: one page of the workbook, made of rows and columns.
Column: runs vertically and is named with letters A, B, C and so on.
Row: runs horizontally and is numbered 1, 2, 3 and so on.
Cell: the box where a row and column meet. Its address is the column letter followed by the row number, for example B3.
Active cell: the cell that is currently selected.
Range: a group of cells, written with a colon, for example A1:A10.

Other screen parts include:

Formula bar: shows the content or formula of the active cell.
Name box: shows the address of the active cell.
Sheet tabs: at the bottom, used to move between worksheets.
Ribbon: holds the tabs and commands such as Home, Insert and Formulas.

Types of data that can be typed in a cell:

Text (label): letters or a mix, aligned left by default.
Number (value): digits, aligned right by default.
Date and time.
Formula: starts with an equals sign (=) and gives a calculated result.

A formula always begins with the = sign. For example, =A1+B1 adds the contents of A1 and B1. The cell shows the result, while the formula bar shows the formula. When a cell value changes, the result updates automatically, which is the main strength of a spreadsheet.

Arithmetic operators used in formulas:

+ for addition, - for subtraction.
* for multiplication, / for division.
^ for power, for example =2^3 gives 8.
Brackets change the order. Spreadsheets follow BODMAS, so =2+3*4 gives 14 but =(2+3)*4 gives 20.

A function is a ready-made formula. Common functions are:

=SUM(B2:B6): adds all values in the range.
=AVERAGE(B2:B6): finds the mean.
=MAX(B2:B6): finds the largest value.
=MIN(B2:B6): finds the smallest value.
=COUNT(B2:B6): counts the cells that contain numbers.
=IF(B2>=40,"Pass","Fail"): gives one result if the test is true and another if false.
=ROUND(B2,1): rounds a value to one decimal place.

Cell references tell a formula where to find its data:

Relative reference (A1): changes when the formula is copied to another cell. If =A1+B1 in C1 is copied down to C2, it becomes =A2+B2.
Absolute reference ($A$1): stays fixed when copied. It is made by pressing F4 or typing the $ signs.
Mixed reference ($A1 or A$1): fixes only the column or only the row.

Copying a formula by dragging the fill handle (the small square at the corner of the cell) is a quick way to repeat a calculation down a column. It saves time and avoids mistakes.

Formatting makes a sheet clear and neat:

Change font, size, colour and bold or italic style.
Align text left, centre or right.
Merge and centre cells for titles.
Apply borders and shading.
Number formats: currency, percentage, date, number of decimal places.
Adjust column width and row height.

Data tools:

Sorting arranges rows in ascending (A to Z, small to large) or descending order.
Filtering shows only the rows that meet a condition and hides the rest, without deleting them.
Find and replace, freeze panes and data validation (allowing only valid entries) help with large sheets.

Charts show data as a picture. Choose the chart to fit the data:

Column or bar chart: compares values of different items.
Line chart: shows change over time.
Pie chart: shows parts of a whole, as percentages.
Every chart should have a title, labelled axes and a legend where needed.

Study tip: Practise on a real sheet, such as a marks list with total, average, percentage and grade columns. In the exam, write the cell addresses carefully and always start formulas with =. For reference questions, show how the formula changes after copying, step by step.

Definitions and flashcards

Say the meaning first, then open the card to check.

Worksheet

A single page of rows and columns in a spreadsheet file.

Cell address

The column letter and row number that name a cell, such as C5.

Range

A block of cells such as B2:B10.

Formula

An expression starting with = that calculates a result.

Function

A built-in formula such as SUM or AVERAGE.

Relative reference

A cell reference that changes when copied.

Absolute reference

A cell reference with $ that does not change when copied.

Filter

A tool that displays only rows that match a condition.

Fill handle

The small square used to copy a formula or series by dragging.

Formula sheet

Name	Formula	What it means
Add a range	=SUM(B2:B6)	Total of all numbers in B2 to B6
Mean	=AVERAGE(B2:B6)	Sum divided by the count of numbers
Conditional result	=IF(B2>=40,"Pass","Fail")	Shows Pass if B2 is 40 or more, otherwise Fail
Percentage	=B2/C2*100	Part divided by whole, times 100

Short questions with model answers

These are the kind of questions asked for 2, 3 and 5 marks. Try each one before opening the answer.

What is a spreadsheet? Name two spreadsheet programs. (2 marks)

A spreadsheet is a program that stores data in rows and columns and performs calculations using formulas. Examples are Microsoft Excel and LibreOffice Calc.

Differentiate between a workbook and a worksheet. (2 marks)

A workbook is the complete spreadsheet file and may contain many sheets. A worksheet is a single sheet inside the workbook, made up of rows and columns.

Explain relative and absolute cell references with an example. (3 marks)

A relative reference such as A1 changes automatically when the formula is copied to another cell. For example, =A1*2 copied one row down becomes =A2*2. An absolute reference such as $A$1 stays the same when copied, so it is used when every formula must use one fixed cell, for example a tax rate stored in $A$1.

Write the formula to find the largest and smallest marks in cells B2 to B11. (2 marks)

The largest mark is found with =MAX(B2:B11). The smallest mark is found with =MIN(B2:B11).

Describe how the IF function works. Give an example. (3 marks)

The IF function checks a condition. If the condition is true it returns the second argument, and if it is false it returns the third. Its form is =IF(condition, value_if_true, value_if_false). For example, =IF(C2>=33,"Pass","Fail") shows Pass when the marks in C2 are 33 or more and Fail otherwise.

Name three types of chart and state when each is used. (5 marks)

A column chart compares values of different items, such as marks of students. A line chart shows how a value changes over time, such as monthly sales. A pie chart shows how a whole is divided into parts, such as a spending budget.

Numericals with worked steps

Cells B2 to B5 contain 20, 35, 45 and 60. Find the results of =SUM(B2:B5) and =AVERAGE(B2:B5).

SUM = 20 + 35 + 45 + 60 = 160
There are 4 numbers
AVERAGE = 160 ÷ 4 = 40

Answer: SUM = 160 and AVERAGE = 40

What is the result of =2+3*4^2 in a spreadsheet?

Power first: 4^2 = 16
Then multiplication: 3 × 16 = 48
Then addition: 2 + 48 = 50

Answer: 50

In cell C2 the formula is =A2*B2. It is copied to C4. What formula appears in C4?

Copying moves two rows down
Relative references move two rows down
A2 becomes A4 and B2 becomes B4

Answer: =A4*B4

A student scores 72 out of 90. Write and evaluate a formula for percentage.

Formula: =72/90*100
72 ÷ 90 = 0.8
0.8 × 100 = 80

Answer: 80 per cent

Common mistakes

Forgetting the = sign at the start, so the spreadsheet treats the formula as plain text.
Typing a formula result by hand instead of using a formula, so it does not update when data changes.
Using a relative reference where an absolute reference ($) is needed, which gives wrong results when copying.
Writing a range with a dash or comma instead of a colon, such as B2-B6.
Choosing a wrong chart, such as a pie chart for changes over time.

Practise this chapter

Practice questions and timed test Printable worksheet