Revision notes

Spreadsheet: Foundation

The key points, rules to remember, tables and diagrams from all 5 lessons, on one page for quick revision. To keep a copy, use your browser's Print and choose "Save as PDF".

Lesson 1

Spreadsheets from zero: workbooks, sheets, rows, columns and cells

File extensions to recognise
ProgramIts own file formatTemplate format
LibreOffice Calc.ods (OpenDocument Spreadsheet).ots
Microsoft Excel (2007 and later).xlsx.xltx
Older Microsoft Excel.xls.xlt

Inside a workbook

  • Worksheets
    • Tabs such as Sheet1
    • Add, rename, move or delete
  • Columns
    • Run up and down
    • Labelled A, B, C ... then AA, AB
  • Rows
    • Run across
    • Numbered 1, 2, 3 ...
  • Cells
    • Where a column and a row meet
    • Address: column letter + row number, such as C5

Address = column letter + row number (C5). Range = first cell : last cell (B2:D5). Cells in a range = columns × rows.

Key takeaways: a workbook holds sheets; a sheet is a grid of columns (letters) and rows (numbers); each cell has an address such as C5; a range such as B2:D5 is a block whose size is columns times rows. Calc saves as .ods and Excel as .xlsx, and each can read the other.

Read the full lesson

Lesson 2

Entering data, selecting ranges, AutoFill and number formats

How a cell treats what you type
You typeStored asDefault alignment
KolkataText (a label)Left
2500NumberRight
15/08/2024 (in a day-first setting)Date (kept internally as a number)Right
'007Text that shows 007Left

Text or number?

  • Text
    • Lines up on the left
    • Example: Kolkata
    • '007 keeps its leading zero as text
    • A total skips a number stored as text
  • Number
    • Lines up on the right
    • Example: 2500
    • Dates are stored as serial numbers
    • Can be used in calculations
Same values, different looks
Stored valueFormatShows as
1234.5Currency, 2 decimal places₹1,234.50
0.18Percentage, 0 decimal places18%
0.125Percentage, 1 decimal place12.5%
3.14159Number, 2 decimal places3.14
a dateLong date15 August 2024 (for example)

Text aligns left, numbers align right. A format changes the look, never the stored value. Two values selected + fill handle = a series with the same step.

Key takeaways: let the program recognise numbers and dates, use an apostrophe for codes with leading zeros, edit with F2, use the fill handle for series and copies, and use number formats (Ctrl+1) for currency, percentages and dates. ##### only means the column is too narrow.

Read the full lesson

Lesson 3

Formulas: operators, order of operations, relative and absolute references

Operators in Calc and Excel
OperatorMeaningExampleResult
+Add=7+310
-Subtract=7-34
*Multiply=7*321
/Divide=21/37
^Power=2^38
%Per cent=200*10%20
&Join text="Sik"&"hora"Sikhora
=, <>, >, <, >=, <=Compare (equal, not equal, greater ...)=5>3TRUE

Order of operations

  1. Brackets
  2. Powers (^)
  3. × and ÷ Left to right.
  4. + and − Left to right.

Three kinds of reference

  • Relative (A2)
    • Changes when copied
    • Moves by the same rows and columns
  • Absolute ($E$1)
    • Stays fixed when copied
    • For a cell every copy must share
  • Mixed ($E1 or E$1)
    • Fixes only one part
    • $E1 keeps column E
    • E$1 keeps row 1
A2 changes row by row; $E$1 never moves
CellFormula after copyingPriceGST
B2=A2*$E$120036
B3=A3*$E$145081
B4=A4*$E$11200216

Brackets → powers → × and ÷ (left to right) → + and − (left to right). $ fixes what follows it: $A$1 fixed, $A1 column fixed, A$1 row fixed.

Key takeaways: start every formula with =, refer to cells rather than typing numbers, use brackets to control the order, and use $ for any cell that every copy must share. F4 (Excel) and Shift+F4 (Calc) switch between relative, absolute and mixed forms.

Read the full lesson

Lesson 4

Functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, IF, ROUND and text functions

Marks in B2:B6 - one entry is text
ItemAB
1NameMarks
2Asha72
3Bilal45
4Charu88
5DevAbsent
6Esha60
Text such as "Absent" is skipped by SUM, AVERAGE, MIN, MAX and COUNT
FormulaWhat it doesResult
=SUM(B2:B6)Adds the numbers265
=AVERAGE(B2:B6)Sum of numbers ÷ how many numbers66.25
=MIN(B2:B6)Smallest number45
=MAX(B2:B6)Largest number88
=COUNT(B2:B6)Counts cells holding numbers4
=COUNTA(B2:B6)Counts cells that are not empty5

COUNT or COUNTA?

  • COUNT
    • Counts cells holding numbers
    • Skips the text Absent
    • Result on the marksheet: 4
  • COUNTA
    • Counts cells that are not empty
    • Includes text
    • Result on the marksheet: 5

Number format or ROUND?

  • Number format
    • Changes how the value looks
    • Calculations use the stored value
  • ROUND
    • Changes the value itself
    • Later formulas use the rounded value
    • =ROUND(66.25,1) gives 66.3
What the messages mean (Calc may also show codes such as Err:502 for some other problems)
ShowsUsual causeExample
#DIV/0!Dividing by zero or by an empty cell=A1/B1 when B1 is empty
#NAME?A name the program does not know: misspelt function, or text without quotes=SUMM(A1:A3); =IF(A1>40,Pass,Fail)
#VALUE!The wrong kind of data, such as text in arithmetic="ten"+5
#REF!A referenced cell no longer exists, usually because it was deletedDeleting column B used by =B2*2
#N/AA lookup did not find the valuea lookup for a missing roll number
#####Not an error: the column is too narrowa long number in a narrow column

SUM, AVERAGE, MIN, MAX and COUNT use numbers only; COUNTA counts every non-empty cell. IF(test, if true, if false) - text in quotes.

Key takeaways: use functions instead of long chains of +; remember that text and blank cells are skipped by AVERAGE and COUNT; put text results of IF in quotes; ROUND changes the value; and read an error value as a clue to the cause.

Read the full lesson

Lesson 5

Sorting, filtering, freeze panes, charts and printing a sheet

Before (as typed)After sorting Marks descending
Asha 72Esha 91
Bilal 45Charu 88
Charu 88Asha 72
Dev 60Dev 60
Esha 91Bilal 45

Sort or filter?

  • Sort
    • Data > Sort
    • Puts rows in ascending or descending order
    • Sort the whole table, never one column
  • Filter
    • Excel: Data > Filter; Calc: Data > AutoFilter
    • Shows only rows that meet a condition
    • Hides the others, does not delete them
ChartBest forExample
Column (vertical bars)Comparing amounts across a few categoriesSales of five branches
Bar (horizontal bars)Comparing categories with long names, or many of themVotes for twelve candidates
LineA trend over timeTemperature for twelve months
PieParts of one whole, a few slicesHow one month's budget is shared

Which chart?

  • Line chart
    • A trend over time
    • Monthly sales from April to March
  • Pie chart
    • Parts of one whole
    • Share of sales from each product group
  • Column chart
    • Comparing amounts across categories
    • Sales of six branches side by side
  • Bar chart
    • Categories with long names

Sort the whole table, never one column. A filter hides rows, it does not delete. Freeze at the cell below and right of what must stay. Line = trend over time; pie = parts of a whole; column/bar = compare categories.

Key takeaways: Data > Sort for order, Data > Filter (Excel) or Data > AutoFilter (Calc) to show only matching rows, freeze panes for headings, the chart type chosen by the question, and print preview plus print area, orientation and repeated headings for neat pages.

Read the full lesson

Privacy · Terms · Refunds · Delivery