Work with LibreOffice Calc

📊

Unit 1: Using the Application

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

1.1.1 Opening & Closing Calc 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.

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

Close: Ctrl + W (close file) or Ctrl + Q (close app). Click ❌ button.

1.1.2–1.1.4 Creating & Saving 1.1.2–1.1.4

Action	How	Shortcut
New from template	File → New → Templates	—
Save	File → Save	`Ctrl` + `S`
Save As (new name/format)	File → Save As	`Ctrl` + `Shift` + `S`

File formats:

Format	Extension	When to Use
Open Document Spreadsheet	`.ods`	Default — open standard. Always use!
Microsoft Excel	`.xlsx`	Sharing with Excel users
CSV (Comma Separated)	`.csv`	Plain data — no formatting, used for data exchange
PDF	`.pdf`	Read-only printed version
Plain Text	`.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 Switching Between Spreadsheets 1.1.5

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

1.2 Enhancing Productivity 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` + Scroll
Show/hide toolbars	View → Toolbars → check/uncheck	—
Go to a cell	Click the Name Box (left of formula bar) → type cell address → Enter	`Ctrl` + `G` or `F5`
Go to beginning	—	`Ctrl` + `Home`
Go to last used cell	—	`Ctrl` + `End`

📖 References

🔗 LibreOffice Calc Guide (Official) 🔗 GCFGlobal — LibreOffice Calc Tutorial

✏️

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 Cell Rules & Good Practice 2.1.1–2.1.2

Rule #1: Each cell should contain only ONE element of data. Put quantity in one cell, description in the next. Don't mix them!

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 Entering Data 2.1.3

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

Type	Alignment	Examples
Numbers	Right-aligned automatically	42, 3.14, -100, 1500
Text (Labels)	Left-aligned automatically	Name, Total, January
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 — Student Grades

	A	B	C	D
1	Student	Math	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 Selecting Cells 2.1.4

Select	How
Single cell	Click it
Adjacent range (e.g., A1:C5)	Click first cell → drag to last cell, OR click first → `Shift` + click last
Non-adjacent cells	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 Editing & Sorting 2.2

Edit a cell: Double-click it (or press F2) to enter edit mode. Change content → press 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: Always include your header row in the selection, then check "Range contains column labels" so headers stay at the top!

2.3 Copy, Move, Delete & AutoFill 2.3

Action	How
Copy cells	Select → `Ctrl` + `C` → click destination → `Ctrl` + `V`
Move cells	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 The AutoFill Handle 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

📖 References

🔗 GCFGlobal — Understanding Cells 🔗 GCFGlobal — Sorting Data

✏️

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 Selecting Rows & Columns 3.1

Select	How
One row	Click the row number
Adjacent rows	Click first row number → `Shift` + click last
Non-adjacent rows	Click first row → `Ctrl` + click other rows
One column	Click the column letter
Adjacent columns	Click first column → `Shift` + click last
Non-adjacent columns	Click first column → `Ctrl` + click others

3.1.3–3.1.5 Rows & Columns Operations 3.1.3–3.1.5

Action	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
Optimal width	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 Working with Worksheets 3.2

A spreadsheet file can contain multiple worksheets (sheets) — shown as tabs at the bottom.

Action	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
Move/copy sheet	Right-click tab → Move/Copy Sheet → choose destination

Good practice (3.2.3): Always rename sheets with meaningful names! "Student Grades" is much better than "Sheet1". This helps you and others find data quickly.

📖 References

🔗 GCFGlobal — Rows, Columns, Cells 🔗 GCFGlobal — Multiple Worksheets

✏️

Quiz: Managing Worksheets

6 questions · ICDL Category 3

🧮

Unit 4: Formulas & Functions

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

4.1.1 Good Practice: Use Cell References! 4.1.1

GOLDEN RULE: Always use 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 Arithmetic Formulas 4.1.2

Every formula starts with = (equals sign). Then use cell references and operators:

Operator	Meaning	Example	Result (if A1=10, B1=3)
`+`	Addition	`=A1+B1`	13
`-`	Subtraction	`=A1-B1`	7
`*`	Multiplication	`=A1*B1`	30
`/`	Division	`=A1/B1`	3.33

📊 Formula Examples

	A	B	C
1	Item	Price	Qty
2	Notebook	5.50	3
3	Pen	1.20	10
4	Total Cost:	=B2C2+B3C3 → 28.50

4.1.3 Common Error Values 4.1.3

Error	Meaning	Common Cause
`#NAME?`	Calc doesn't recognise the name	Misspelled function name (e.g., SUMM instead of SUM)
`#DIV/0!`	Division by zero	A formula divides by a cell that contains 0 or is empty
`#REF!`	Invalid cell reference	A referenced cell was deleted
`#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): Changes when you copy the formula. If you copy =A1+B1 down one row, it becomes =A2+B2. This is the default and usually what you want!

Absolute reference (e.g., $A$1): Does NOT change when copied. The $ locks the row and/or column. Use when referring to a fixed cell like a tax rate.

Mixed: $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()`	Adds all numbers	`=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)`	Lowest value
`=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: = (equals), > (greater than), < (less than), >= (greater or equal), <= (less or equal), <> (not equal).

🎮 Activity: Match the Function!

📖 References

🔗 GCFGlobal — Formulas in Calc 🔗 GCFGlobal — Functions in Calc 🔗 LibreOffice Wiki — Calc Functions List

✏️

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):

Format	Example Input	Displayed As
Decimal places (2)	3.5	3.50
Thousands separator	1500000	1,500,000
Currency	25	$25.00 or 25.00 QAR
Percentage	0.75	75%
Date	45365	15/03/2024 (or other date style)

⚠️ 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

Format	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	Long text wraps inside the cell instead of overflowing	Format → Cells → Alignment → Wrap text ✅
Horizontal align	Left, Centre, Right	Formatting toolbar alignment buttons
Vertical align	Top, Middle, Bottom	Format → Cells → Alignment
Orientation	Rotate text to any angle (e.g., 45° for headers)	Format → Cells → Alignment → degrees
Merge cells	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	Add lines around cells (style, width, colour)	Format → Cells → Borders tab, or Borders dropdown on toolbar

📖 References

🔗 GCFGlobal — Formatting Cells 🔗 LibreOffice Calc Guide — Formatting

✏️

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

📈

Line Chart

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

Tap

🥧

Pie Chart

A circle divided into slices. Shows proportions of a whole (percentages). Best with 3–7 categories. One data series only!

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)
Adjust data range and series if needed
Add a title → click Finish

Select chart: Click on it (handles appear). Move: 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
Add/edit chart title	Insert → Title, or click existing title to edit
Add/remove legend	Insert → Legend, or right-click legend → Delete
Add data labels (values/percentages)	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.

📖 References

🔗 GCFGlobal — Creating Charts 🔗 LibreOffice Calc Guide — Charts

✏️

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	Options
Margins	Page tab → Margins	Top, Bottom, Left, Right (in cm)
Orientation	Page tab	Portrait / Landscape
Paper size	Page tab → Format	A4, Letter, etc.
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

Action	How
Spell check	Tools → Spelling (`F7`) — works like 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`
Number of copies	Set in Print dialog

💡 Tip: Use Print Ranges (Format → Print Ranges → Define) to mark which area of a large spreadsheet should be printed. Only that area will print!

📖 References

🔗 GCFGlobal — Page Layout & Printing 🔗 LibreOffice Calc Guide — Printing

✏️

Quiz: Prepare Outputs

6 questions · ICDL Category 7

🏆

Final Exam: Spreadsheets with LibreOffice Calc

20 questions · All 7 ICDL Categories · Score 70%+ to pass!

🏆

Alin Li ICDL Academy — Spreadsheets with LibreOffice Calc

Spreadsheets with LibreOffice Calc

ICDL Spreadsheets Module — Complete Interactive Course 📊

1.1.1 Opening & Closing Calc 1.1.1

1.1.2–1.1.4 Creating & Saving 1.1.2–1.1.4

1.1.5 Switching Between Spreadsheets 1.1.5

1.2 Enhancing Productivity 1.2

📖 References

2.1.1–2.1.2 Cell Rules & Good Practice 2.1.1–2.1.2

2.1.3 Entering Data 2.1.3

2.1.4 Selecting Cells 2.1.4

2.2 Editing & Sorting 2.2

2.3 Copy, Move, Delete & AutoFill 2.3

2.3.2 The AutoFill Handle 2.3.2

📖 References

3.1.1–3.1.2 Selecting Rows & Columns 3.1

3.1.3–3.1.5 Rows & Columns Operations 3.1.3–3.1.5

3.2 Working with Worksheets 3.2

📖 References

4.1.1 Good Practice: Use Cell References! 4.1.1

4.1.2 Arithmetic Formulas 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!

📖 References

5.1 Number & Date Formatting 5.1

5.2 Text & Cell Formatting 5.2

5.3 Alignment, Wrapping, Merge & Borders 5.3

📖 References

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

📖 References

7.1 Page Setup 7.1

7.2 Check & Print 7.2

📖 References