Tööta LibreOffice Calciga

📊

Unit 1: Using the Application

ICDL Category 1 · Working with Spreadsheets (1.1) & Productivity (1.2)

1.1.1 Calci avamine ja sulgemine 1.1.1

LibreOffice Calc is a free, open-source spreadsheet application — the Alin Li alternative to Microsoft Excel! It organizes data in rows and columns of cells.

Avatud: Application Menu → search "LibreOffice Calc" → click to open. Or double-click any .ods / .xlsx file.

Close: Ctrl + W (sulgeb faili) või Ctrl + Q (sulgeb rakenduse). Klõpsa nuppu ❌.

1.1.2–1.1.4 Loomine ja salvestamine 1.1.2–1.1.4

Toiming	How	Shortcut
Uus mallist	File → New → Templates	—
Save	File → Save	`Ctrl` + `S`
Save As (new name/format)	File → Save As	`Ctrl` + `Shift` + `S`

File formats:

Vorming	Extension	When to Use
Open Documenti arvutustabel	`.ods`	Default — open standard. Kasuta alati!
Microsoft Excel	`.xlsx`	Sharing with Excel users
CSV (Comma Separated)	`.csv`	Lihtandmed — vorminguta, kasutatakse andmevahetuseks
PDF	`.pdf`	Read-only printed version
Lihttekst	`.txt`	Text only, tab-separated

⚠️ CSV warning: CSV files lose all formatting, formulas, charts, and multiple sheets! Only use CSV for simple data exchange.

1.1.5 Arvutustabelite vahel liikumine 1.1.5

Use Window menu to see all open spreadsheets, or Alt + Tab to switch windows.

1.2 Tootlikkuse suurendamine 1.2

Feature	How	Shortcut
Preferences	Tools → Options → LibreOffice → Paths / User Data	—
Help	Help → LibreOffice Help	`F1`
Zoom	Slider (bottom-right) or View → Zoom	`Ctrl` + kerimine
Show/hide toolbars	View → Toolbars → check/uncheck	—
Go to a cell	Click the Nimekast (valemiribast vasakul) → sisesta lahtri aadress → Enter	`Ctrl` + `G` või `F5`
Go to beginning	—	`Ctrl` + `Home`
Go to last used cell	—	`Ctrl` + `End`

📖 Allikad

🔗 LibreOffice Calci juhend (ametlik) 🔗 GCFGlobal — LibreOffice Calci õpetus

✏️

Quiz: Using the Application

6 questions · ICDL Category 1

📝

Unit 2: Cells — Enter, Select, Edit, Sort

ICDL Category 2 · Insert/Select (2.1), Edit/Sort (2.2), Copy/Move/Delete (2.3)

2.1.1–2.1.2 Lahtrireeglid ja head tavad 2.1.1–2.1.2

Rule #1: Each cell should contain only ÜKS andmeelement. Pane kogus ühte lahtrisse ja kirjeldus järgmisse. Ära sega neid kokku!

Rule #2 — Good lists: Avoid blank rows/columns in the middle of a data list. Ensure cells surrounding the list are blank. This helps Calc correctly detect the data range for sorting, filtering, and charts.

2.1.3 Andmete sisestamine 2.1.3

Click a cell and type. Calc recognises three data types:

Type	Joondus	Examples
Arvud	Right-aligned automatically	42, 3.14, -100, 1500
Text (Labels)	Automaatselt vasakjoondatud	Nimi, kokku, jaanuar
Dates	Right-aligned	15/03/2025, 2025-03-15

Press Enter to confirm and move down. Press Tab to confirm and move right.

📊 LibreOffice Calc — õpilaste hinded

	A	B	C	D
1	Student	Matemaatika	Science	Average
2	Sara	85	92	=AVERAGE(B2:C2)
3	Ahmed	78	88	=AVERAGE(B3:C3)
4	Lina	95	90	=AVERAGE(B4:C4)

2.1.4 Lahtrite valimine 2.1.4

Select	How
Single cell	Click it
Kõrvutine vahemik (nt A1:C5)	Click first cell → drag to last cell, OR click first → `Shift` + klõpsa viimasel
Mittekülgnevad lahtrid	Select first range → hold `Ctrl` → select additional ranges
Entire row	Click the row number (1, 2, 3...)
Entire column	Click the column letter (A, B, C...)
Entire worksheet	Click the box at the intersection of row/column headers, or `Ctrl` + `A`

2.2 Muutmine ja sortimine 2.2

Edit a cell: Double-click it (or press F2), et minna redigeerimisrežiimi. Muuda sisu → vajuta Enter.

Undo / Redo: Ctrl + Z / Ctrl + Y

Find & Replace: Ctrl + H — works the same as in Writer.

Sort data: Select your data range → Data → Sort → choose column to sort by → ascending (A→Z, 1→9) or descending (Z→A, 9→1).

💡 Tip: Kaasa valikusse alati päiserida ja märgi „Vahemik sisaldab veerusilte”, et päised jääksid üles!

2.3 Kopeerimine, teisaldamine, kustutamine ja automaattäitmine 2.3

Toiming	How
Copy cells	Select → `Ctrl` + `C` → click destination → `Ctrl` + `V`
Teisalda lahtrid	Select → `Ctrl` + `X` → click destination → `Ctrl` + `V`
Delete contents	Select → press `Delete` key
Copy between worksheets	Copy → click target sheet tab → paste
Copy between files	Copy → switch to other spreadsheet window → paste

2.3.2 Automaattäitmise pide 2.3.2

The AutoFill handle is the small square at the bottom-right corner of a selected cell. Drag it to:

📋

Copy Data

Type a value → drag the handle → it copies to all cells. Works for text and numbers.

Tap

🔢

Increment Numbers

Type 1, 2 in two cells → select both → drag handle → continues 3, 4, 5, 6... Works for any pattern!

Tap

📅

Fill Dates

Type a date → drag → fills in the next days automatically. Also works for months (Jan, Feb, Mar...).

Tap

📐

Copy Formulas

Write a formula in one cell → drag the handle down → the formula copies and adjusts cell references automatically!

Tap

📖 Allikad

🔗 GCFGlobal — lahtrite mõistmine 🔗 GCFGlobal — andmete sortimine

✏️

Quiz: Cells

8 questions · ICDL Category 2

📑

Unit 3: Managing Worksheets

ICDL Category 3 · Rows/Columns (3.1) & Worksheets (3.2)

3.1.1–3.1.2 Ridade ja veergude valimine 3.1

Select	How
Üks rida	Click the row number
Kõrvuti read	Click first row number → `Shift` + klõpsa viimasel
Mittekülgnevad read	Click first row → `Ctrl` + klõpsa teistel ridadel
Üks veerg	Click the column letter
Kõrvuti veerud	Click first column → `Shift` + klõpsa viimasel
Mittekülgnevad veerud	Click first column → `Ctrl` + klõpsa teistel

3.1.3–3.1.5 Toimingud ridade ja veergudega 3.1.3–3.1.5

Toiming	How
Insert row/column	Right-click row/column header → Insert Rows Above/Below or Columns Left/Right
Delete row/column	Right-click → Delete Rows / Delete Columns
Change column width	Drag the column border in the header, or right-click → Column Width → enter value
Optimaalne laius	Double-click the column border — auto-fits to content!
Change row height	Drag row border, or right-click → Row Height
Freeze rows/columns	Click below & right of what to freeze → View → Freeze Rows and Columns

💡 Freeze: Freezing keeps header rows/columns visible when you scroll through long spreadsheets. Click cell B2 → View → Freeze to freeze Row 1 and Column A.

3.2 Töölehtedega töötamine 3.2

Arvutustabelifail võib sisaldada mitut worksheets (töölehed) — kuvatakse allservas vahekaartidena.

Toiming	How
Switch sheets	Click the sheet tab at the bottom
Insert new sheet	Right-click a tab → Insert Sheet, or click the + button
Delete sheet	Right-click tab → Delete Sheet (⚠️ can't undo!)
Rename sheet	Double-click the tab → type new name → Enter
Teisalda/kopeeri leht	Right-click tab → Move/Copy Sheet → choose destination

Good practice (3.2.3): Nimeta töölehed alati sisukalt! „Õpilaste hinded” on palju parem kui „Sheet1”. See aitab sinul ja teistel andmeid kiiresti leida.

📖 Allikad

🔗 GCFGlobal — read, veerud ja lahtrid 🔗 GCFGlobal — mitu töölehte

✏️

Quiz: Managing Worksheets

6 questions · ICDL Category 3

🧮

Unit 4: Formulas & Functions

ICDL Category 4 · Arithmetic (4.1) & Functions (4.2)

4.1.1 Hea tava: kasuta lahtriviiteid! 4.1.1

GOLDEN RULE: Kasuta alati cell references in formulas, NOT typed numbers!

✅ =A1*B1 — if A1 or B1 changes, the formula updates automatically!
❌ =5*10 — if data changes, you must manually re-type the formula.

4.1.2 Aritmeetilised valemid 4.1.2

Every formula starts with = (võrdusmärk). Seejärel kasuta lahtriviiteid ja tehtemärke:

Operaator	Tähendus	Example	Result (if A1=10, B1=3)
`+`	Liitmine	`=A1+B1`	13
`-`	Subtraction	`=A1-B1`	7
`*`	Korrutamine	`=A1*B1`	30
`/`	Division	`=A1/B1`	3.33

📊 Valemite näited

	A	B	C
1	Item	Price	Qty
2	Märkmik	5.50	3
3	Pliiats	1.20	10
4	Total Cost:	=B2C2+B3C3 → 28.50

4.1.3 Common Error Values 4.1.3

Error	Tähendus	Common Cause
`#NAME?`	Calc doesn't recognise the name	Valesti kirjutatud funktsiooninimi (nt SUMM, mitte SUM)
`#DIV/0!`	Division by zero	Valem jagab lahtriga, mis sisaldab 0 või on tühi
`#REF!`	Invalid cell reference	Viidatud lahter kustutati
`#VALUE!`	Wrong type of value	Using text where a number is expected (e.g., =5+"hello")

4.1.4 Relative vs. Absolute References 4.1.4

Relative reference (e.g., A1): muutub valemi kopeerimisel. Kui kopeerid =A1+B1 down one row, it becomes =A2+B2. See on vaikevalik ja tavaliselt just see, mida vajad!

Absoluutviide (e.g., $A$1): EI muutu kopeerimisel. Märk $ lukustab rea ja/või veeru. Kasuta seda kindlale lahtrile, näiteks maksumäärale, viitamisel.

Segatud: $A1 locks column A only. A$1 locks row 1 only.

💡 Shortcut: Press F4 while editing a cell reference to cycle through: A1 → $A$1 → A$1 → $A1 → A1.

4.2.1 Essential Functions 4.2.1

Function	What It Does	Example	Result
`=SUM()`	Liidab kõik arvud	`=SUM(A1:A5)`	Total of A1 through A5
`=AVERAGE()`	Calculates the mean	`=AVERAGE(B1:B10)`	Average of the 10 values
`=MIN()`	Finds the smallest number	`=MIN(C1:C20)`	Väikseim väärtus
`=MAX()`	Finds the largest number	`=MAX(C1:C20)`	Highest value
`=COUNT()`	Counts cells with numbers	`=COUNT(A1:A100)`	How many numbers
`=COUNTA()`	Counts cells that aren't empty	`=COUNTA(A1:A100)`	How many non-empty cells
`=ROUND()`	Rounds to specified decimals	`=ROUND(3.14159, 2)`	3.14

4.2.2 The IF Function 4.2.2

The IF function makes decisions! It tests a condition and returns one value if TRUE, another if FALSE:

=IF(condition, value_if_true, value_if_false)

Example	What It Does
`=IF(A1>=50, "Pass", "Fail")`	If A1 is 50 or more → "Pass", otherwise → "Fail"
`=IF(B2>100, B2*0.9, B2)`	If B2 > 100 → apply 10% discount, otherwise keep original
`=IF(C3=0, "Empty", C3)`	If C3 is 0 → show "Empty", otherwise show the value

Comparison operators: = (võrdub), > (suurem kui), < (väiksem kui), >= (suurem või võrdne), <= (väiksem või võrdne), <> (ei võrdu).

🎮 Activity: Match the Function!

📖 Allikad

🔗 GCFGlobal — valemid Calcis 🔗 GCFGlobal — funktsioonid Calcis 🔗 LibreOffice'i viki — Calci funktsioonide loend

✏️

Quiz: Formulas & Functions

8 questions · ICDL Category 4

🎨

Unit 5: Formatting

ICDL Category 5 · Numbers/Dates (5.1), Contents (5.2), Alignment/Borders (5.3)

5.1 Number & Date Formatting 5.1

Select cells → Format → Cells → Numbers tab (or Ctrl + 1):

Vorming	Example Input	Displayed As
Decimal places (2)	3.5	3.50
Thousands separator	1500000	1,500,000
Currency	25	$25.00 või 25.00 QAR
Protsent	0.75	75%
Date	45365	15/03/2024 (või muu kuupäevavorming)

⚠️ Important: Formatting changes how a number LOOKS, not its actual value. The cell still contains the original number for calculations!

5.2 Text & Cell Formatting 5.2

Vorming	How	Shortcut
Font size / type	Formatting toolbar dropdowns	—
Bold	Toolbar button	`Ctrl` + `B`
Italic	Toolbar button	`Ctrl` + `I`
Underline	Toolbar button	`Ctrl` + `U`
Double underline	Format → Cells → Font Effects	—
Font colour	Font Color dropdown on toolbar	—
Cell background colour	Highlighting dropdown on toolbar	—
AutoFormat / Table style	Select range → Format → AutoFormat Styles	—
Copy formatting	Clone Formatting button (paintbrush 🖌️)	—

5.3 Alignment, Wrapping, Merge & Borders 5.3

Feature	What It Does	Where
Text wrapping	Pikk tekst murtakse lahtris mitmele reale ega ulatu üle serva	Format → Cells → Alignment → Wrap text ✅
Horizontal align	Vasakul, keskel, paremal	Formatting toolbar alignment buttons
Vertical align	Top, Middle, Bottom	Format → Cells → Alignment
Suund	Rotate text to any angle (e.g., 45° for headers)	Format → Cells → Alignment → degrees
Ühenda lahtrid	Combine multiple cells into one big cell (for titles)	Format → Merge Cells → Merge and Center
Unmerge	Split a merged cell back	Format → Merge Cells → Unmerge
Borders	Lisa lahtrite ümber jooned (stiil, laius, värv)	Format → Cells → Borders tab, or Borders dropdown on toolbar

📖 Allikad

🔗 GCFGlobal — lahtrite vormindamine 🔗 LibreOffice Calci juhend — vormindamine

✏️

Quiz: Formatting

8 questions · ICDL Category 5

📈

Unit 6: Charts

ICDL Category 6 · Create (6.1) & Edit (6.2)

6.1.1 Chart Types 6.1.1

📊

Column Chart

Vertical bars. Best for comparing values across categories (e.g., sales per month). Each bar represents one data point.

Tap

📊

Bar Chart

Horizontal bars. Like a column chart turned sideways. Good when category names are long or you have many categories.

Tap

📈

Joondiagramm

Connected dots showing trends over time. Perfect for tracking changes — temperature over a week, grades over a semester.

Tap

🥧

Sektordiagramm

Viiludeks jagatud ring. Näitab terviku osade suhteid (protsente). Sobib kõige paremini 3–7 kategooriale. Ainult üks andmesari!

Tap

6.1.2–6.1.5 Creating & Managing Charts 6.1.2–6.1.5

Select your data (including headers)
Go to Insert → Chart
Choose chart type (Column, Bar, Line, Pie)
Muuda vajadusel andmevahemikku ja andmesarju
Lisa pealkiri → klõpsa Finish

Select chart: Click on it (handles appear). Teisaldamine: Drag. Resize: Drag corner handles. Delete: Select → Delete. Change type: Double-click chart → right-click → Chart Type.

6.2 Editing Charts 6.2

Double-click the chart to enter edit mode, then right-click for options:

Edit	How
Lisa või muuda diagrammi pealkirja	Insert → Title, or click existing title to edit
Lisa või eemalda legend	Insert → Legend, or right-click legend → Delete
Lisa andmesildid (väärtused/protsendid)	Click data series → Insert → Data Labels → choose Values or Percentages
Change background colour	Right-click chart area → Format Chart Area → Area tab
Change legend colour	Right-click legend → Format Legend → Area tab
Change bar/line/slice colours	Click a bar/slice → right-click → Format Data Point → Area tab
Change font size/colour of title, axes, legend	Click the element → right-click → Format → Character tab

The first pie chart was created by William Playfair in 1801! He also invented the bar chart and line chart. Data visualization has been helping people understand numbers for over 200 years.

📖 Allikad

🔗 GCFGlobal — diagrammide loomine 🔗 LibreOffice Calci juhend — diagrammid

✏️

Quiz: Charts

8 questions · ICDL Category 6

🖨️

Unit 7: Prepare Outputs

ICDL Category 7 · Setup (7.1) & Check/Print (7.2)

7.1 Page Setup 7.1

Go to Format → Page Style:

Setting	Where	Valikud
Veerised	Lehekülje vahekaart → veerised	Top, Bottom, Left, Right (in cm)
Suund	Lehekülje vahekaart	Püstine / rõhtne
Paberi suurus	Lehekülje vahekaart → vorming	A4, Letter jne.
Scale to fit pages	Sheet tab → Scale → Fit to number of pages	Shrink/stretch to fit X pages wide by Y tall
Headers/Footers	Header/Footer tabs	Enable, add text, page numbers, date, file name, sheet name

7.2 Check & Print 7.2

Toiming	How
Spell check	Tools → Spelling (`F7`) — töötab nagu Writer
Show/hide gridlines for print	Format → Page Style → Sheet tab → Print → Grid ✅/❌
Show/hide row & column headers	Same Sheet tab → Print → Column & Row headers ✅/❌
Print title rows on every page	Format → Print Ranges → Edit → Rows to repeat → select your header row (e.g., $1)
Print Preview	File → Print Preview (`Ctrl` + `Shift` + `J`)
Print worksheet	`Ctrl` + `P` → choose All sheets / Active sheets / Selection
Print selected cells only	Select range → File → Print → Selection option
Print a chart only	Select the chart → `Ctrl` + `P`
Eksemplaride arv	Set in Print dialog

💡 Tip: Use Print Ranges (Vormindus → Trükialad → Määra), et märkida, milline osa suurest arvutustabelist tuleb printida. Prinditakse ainult see ala!

📖 Allikad

🔗 GCFGlobal — lehekülje paigutus ja printimine 🔗 LibreOffice Calci juhend — printimine

✏️

Quiz: Prepare Outputs

6 questions · ICDL Category 7

🏆

Final Exam: Spreadsheets with LibreOffice Calc

20 küsimust · Kõik 7 ICDL-i kategooriat · Läbimiseks vaja 70%+!

🏆

Alin Li ICDL akadeemia — Spreadsheets with LibreOffice Calc

Arvutustabelid LibreOffice Calciga

ICDL-i arvutustabelite moodul — täielik interaktiivne kursus 📊

1.1.1 Calci avamine ja sulgemine 1.1.1

1.1.2–1.1.4 Loomine ja salvestamine 1.1.2–1.1.4

1.1.5 Arvutustabelite vahel liikumine 1.1.5

1.2 Tootlikkuse suurendamine 1.2

📖 Allikad

2.1.1–2.1.2 Lahtrireeglid ja head tavad 2.1.1–2.1.2

2.1.3 Andmete sisestamine 2.1.3

2.1.4 Lahtrite valimine 2.1.4

2.2 Muutmine ja sortimine 2.2

2.3 Kopeerimine, teisaldamine, kustutamine ja automaattäitmine 2.3

2.3.2 Automaattäitmise pide 2.3.2

📖 Allikad

3.1.1–3.1.2 Ridade ja veergude valimine 3.1

3.1.3–3.1.5 Toimingud ridade ja veergudega 3.1.3–3.1.5

3.2 Töölehtedega töötamine 3.2

📖 Allikad

4.1.1 Hea tava: kasuta lahtriviiteid! 4.1.1

4.1.2 Aritmeetilised valemid 4.1.2

4.1.3 Common Error Values 4.1.3

4.1.4 Relative vs. Absolute References 4.1.4

4.2.1 Essential Functions 4.2.1

4.2.2 The IF Function 4.2.2

🎮 Activity: Match the Function!

📖 Allikad

5.1 Number & Date Formatting 5.1

5.2 Text & Cell Formatting 5.2

5.3 Alignment, Wrapping, Merge & Borders 5.3

📖 Allikad

6.1.1 Chart Types 6.1.1

6.1.2–6.1.5 Creating & Managing Charts 6.1.2–6.1.5

6.2 Editing Charts 6.2

📖 Allikad

7.1 Page Setup 7.1

7.2 Check & Print 7.2

📖 Allikad