Notifications

Log in to see your notifications.

Spreadsheets

14 min read

What is a spreadsheet?

A spreadsheet is a program that arranges data in a grid of rows (numbers) and columns (letters). A box where a row and a column meet is a cell, named by its cell reference such as B2. A group of cells is a range, for example B2:B10. A file is a workbook and may contain several worksheets. Examples: Microsoft Excel, Google Sheets, LibreOffice Calc.

A spreadsheetA grid of columns lettered A, B, C and D and rows numbered 1, 2, 3 and 4. The place where a column and a row meet is a cell, such as B2. A formula begins with an equals sign. =B2*C2 multiplies the price by the quantity, and =SUM(D2:D3) adds the cells from D2 to D3.ABCD1ItemPriceQtyTotal2Rice2 5004=B2*C23Beans3 0003=B3*C34Total=SUM(D2:D3)

Creating, formatting, sorting and filtering

Type text (labels), numbers or dates into cells. Format with number types (currency, percentage, date), bold, colours, borders, merging, wrapping and column width. Sorting puts data in order (A–Z, smallest to largest). Filtering shows only rows that meet a condition (for example, Form = 2). Charts show data visually.

Basic calculations and formulas

A formula always starts with =. It can use operators + − * / ^ and functions. A cell reference in a formula makes it update when the data changes. Relative references (B2) change when copied; absolute references (2) stay fixed.

FunctionDoesExample
SUMAdds=SUM(B2:B10)
AVERAGEMean=AVERAGE(B2:B10)
MAX / MINLargest / smallest=MAX(B2:B10)
COUNTCounts numbers=COUNT(B2:B10)
IFTests a condition=IF(B2>=50,"Pass","Fail")
ROUNDRounds a number=ROUND(B2,1)
Worked exampleA shopping list

In D2, rice costs TSh 2 500 per kg (B2) and 4 kg are bought (C2). Write the formulas to find the cost and the total of D2 to D5.

  1. Cost of the row.
  2. Total.

Answer: Copy the formula in D2 down to D5 (relative references update).

Data validation and conditional formatting

Data validation restricts what can be typed in a cell (for example, marks between 0 and 100, or a list of choices), which reduces errors. Conditional formatting changes the look of a cell depending on its value, for example marking marks below 50 in red.

Import, export, page setup and printing

Data can be imported from CSV or text files and exported to PDF or CSV. Before printing set the print area, orientation, margins, paper size, and repeat the titles on each page; use print preview.

Try itSpreadsheet functions

Choose four marks. See the results of SUM, AVERAGE, MAX and MIN, and an IF test.

Check yourself A formula in a spreadsheet starts with
Check yourself The cell where column C meets row 5 is
Check yourself =SUM(A1:A4) adds

Key terms

Cell
A box in a spreadsheet where a row and column meet.
Formula
An instruction that calculates a value.
Function
A built-in formula such as SUM.
Range
A group of cells.

Summary

  • A spreadsheet is a grid of cells in rows and columns.
  • Formulas start with = and use cell references.
  • Use functions SUM, AVERAGE, MAX, MIN and IF.
  • Validation and conditional formatting improve data quality.
Databases