GRADE –X

ICT REVISION NOTES

ELECTRONIC SPREADSHEET (Advanced)

CONSOLIDATING DATA

1. Definition

Consolidating Data in LibreOffice Calc means combining data from multiple ranges (in the same
sheet or different sheets) into a single summary table, usually by applying a function like Sum,
Average, Count, etc..

Example: If you have monthly sales data in separate sheets for January, February, and March, you
can consolidate them into one total report.

2. Data Required in Consolidate Dialog Box

When you open the Consolidate dialog box, you will need to provide:
 Field / Option Description
Source Data Ranges The ranges of data you want to combine (can be from multiple sheets).
Function The calculation type: Sum, Average, Count, Max, Min, etc.
Target Range The location where the combined result will be displayed.
Options: Row/Column
Check these if your data has headings for rows/columns.
Labels
Optional – keeps the consolidated table linked so it updates if the
Link to Source Data
source data changes.

3. Steps to Consolidate Data (Simplified)

Step 1: Open your Calc file with all data sheets ready.
Step 2: Click the cell where you want the result.
Step 3: Go to Menu → Data → Consolidate.
Step 4: In the Function drop-down, select the calculation type (e.g., Sum, Average, Count etc.).
Step 5: In Source Data Ranges, click Add, then select the first data range → click OK.
Step 6: Repeat Step 5 for all other ranges to be consolidated.
Step 7: In Target Range, ensure your desired output cell is shown.
Step 8: (Optional) Tick Row labels / Column labels if your data has headers.
Step 9: Click OK to generate the consolidated table.

SUBTOTALS

1. Definition

Subtotals in LibreOffice Calc are automatic summaries that group and total your data based on a
selected column.
It quickly shows sum, average, count, etc. for each group in a sorted list.

Example: If you have sales data by region, you can get totals for each region without making
separate formulas.

2. Data Required in Subtotals Dialog Box

Field / Option Description

Group by Column used for grouping (must be sorted first).

Calculate subtotals for The column(s) whose totals you want.

Use function Function to apply (Sum, Average, Count, etc.).

Sort before subtotaling Option to sort automatically before grouping.

Replace current subtotals Remove old subtotals before adding new ones.
3. Steps to Add Subtotals (Simplified)

1. Sort the data by the column you want to group.

2. Select the data range.
3. Go to Data → Subtotals.
4. Choose the column for Group by.
5. Select the column(s) to subtotal under Calculate subtotals for.
6. Pick the function (Sum, Average, etc.).
7. Click OK to see results.

“What-IF” SCENARIOS

1. Definition

A Scenario in LibreOffice Calc is a saved set of values for certain cells, which you can quickly
switch between to see how results change.
It’s useful for “what-if” analysis — testing different possibilities without changing your original data.

Example: You can create “Best Case” and “Worst Case” sales projections and swap between them
instantly.

2. Data Required in Scenario Dialog Box

Alt + T + E → Opens the Scenarios dialog box

 Field / Option Description

Name A unique name for the scenario (e.g., Best Case, Option 1).

Comment Notes about what the scenario represents.

Changing cells The cells whose values will be stored in the scenario.

Options Prevent changes, display border, copy back, etc.

3. Steps to Create a Scenario (Simplified)

1. Select the cells whose values will change in scenarios.

2. Go to Tools → Scenarios.
3. Type a name for the scenario.
4. Click OK → First scenario saved. Change the cell values, then repeat steps 2–6 for another
scenario.
MULTIPLE OPERATIONS

1. Definition

Multiple Operations in LibreOffice Calc let you see how changing one or two input values affects
the result of a formula — all at once.
It’s useful for “what-if” analysis, e.g., testing different interest rates or loan amounts in a table without
writing many formulas.

2. Data Required in Multiple Operations Dialog Box

Field / Option Description

Formula cell The cell containing the formula whose result you want to test.

Row input cell The cell where the row values will replace.

Column input cell The cell where the column values will replace.

3. Steps to Use Multiple Operations (Simplified)

For Two Variables:

1. Type the formula in a cell.

2. Create a table with different row values and column values.
3. Select the whole table including the formula cell.
4. Go to Data → Multiple Operations.
5. Enter Row input cell and Column input cell.
6. Click OK to fill the table with results.
GOAL SEEK

1. Definition

Goal Seek in LibreOffice Calc is used to find the input value needed to get a specific result in a
formula.
It changes one variable to make the formula give your desired output.

Example: If you know the final profit you want, Goal Seek can find the sales needed to reach it.

2. Data Required in Goal Seek Dialog Box

Field / Option Description

Formula cell The cell that contains the formula whose result you want.

Target value The result you want to achieve.

Variable cell The cell that will be changed to reach the target value.

3. Steps to Use Goal Seek (Simplified)

1. Type your formula in a cell.

2. Go to Tools → Goal Seek.
3. Select the formula cell in Formula cell.
4. Enter your desired result in Target value.
5. Select the cell to change in Variable cell.
6. Click OK → Calc finds the required value.
SOLVER

1. Definition

Solver in LibreOffice Calc is used to find the best solution for a formula by changing multiple input
cells, while following certain conditions or limits.
It’s like an advanced version of Goal Seek, but can work with more than one variable and with
constraints.

Example: Finding the maximum profit by adjusting both price and quantity while staying within
production limits.

2. Data Required in Solver Dialog Box

Field / Option Description

Target cell The cell with the formula whose value you want to optimize.

Optimize result to Choose Maximum, Minimum, or a specific value.

By changing cells The cells whose values Solver will adjust.

Constraints Conditions the solution must satisfy (e.g., B1 <= 100).

3. Steps to Use Solver (Simplified)

1. Type your formula in the target cell.

2. Go to Tools → Solver.
3. Select the Target cell.
4. Choose Max, Min, or a specific value.
5. Select the cells to change.
6. Add any constraints if needed. 7. Click Solve to get the result.
LibreOffice Calc – Data Analysis Tools Summary Chart
Feature Definition Data Required in Dialog Box Steps (Simplified)

Combines data from multiple ranges 1. Click target cell. 2. Data → Consolidate.
Source data ranges, Function (Sum, Avg, etc.),
Consolidate into one summary table using a 3. Choose function. 4. Add ranges.
Target range, Row/Column labels, Link to source.
function. 5. Set target & labels.

1. Sort data. 2. Select range.

Creates automatic totals for grouped Group by column, Calculate subtotals for, 3. Data → Subtotals. 4. Choose group
Subtotals
and sorted data. Function, Sort option, Replace old subtotals. column.
5. Pick column & function.

1. Select changing cells.

2. Tools → Scenarios.
Saves and switches between different Name, Comment, Changing cells, Options (Prevent
Scenarios 3. Enter name & comment.
sets of values for selected cells. changes, Border, etc.).
4. Set options. 5. OK.
6. Repeat for more scenarios.

One variable: Select list & result → Data →

Multiple Operations → Set row/column cell →
Multiple Tests how changing one/two inputs
Formula cell, Row input cell, Column input cell. OK.
Operations affects a formula result.
Two variables: Select table & result → Set row &
column cells → OK.

1. Make formula.
2. Tools → Goal Seek.
Finds the input value needed to get a
Goal Seek Formula cell, Target value, Variable cell. 3. Select formula cell.
desired formula result.
4. Enter target value.
5. Select variable cell.

1. Make formula.
2. Tools → Solver.
3. Select target cell.
Finds the best solution for a formula by Target cell, Optimize to (Max/Min/Value),
Solver 4. Choose Max/Min/Value.
changing multiple cells with conditions. Changing cells, Constraints.
5. Select changing cells.
6. Add constraints.
7. Solve.
MACRO

1. Definition

A Macro in LibreOffice Calc is a recorded set of actions or commands that can be played back
to automate repetitive tasks.
Example: If you need to format a sheet the same way every time, you can record a macro to do it
instantly.

2. Data Required in Macro Dialog Box

When recording or running a macro, the dialog box requires:

Field / Option Description

Macro name A unique name for your macro.

Location where the macro is stored (Current Document or My

Save in
Macros).

Existing macros list Shows macros already saved.

Assign shortcut
Key combination to run the macro quickly.
(optional)

3. Steps to Record and Run a Macro (Simplified)

To Record a Macro:

1. Go to Tools → Macros → Record Macro.

2. Perform the actions you want to automate.
3. Click Stop Recording.
4. Give the macro a name and choose where to save it.
5. Click Save.

To Run a Macro:

1. Go to Tools → Macros → Run Macro.

2. Select the macro name from the list.
3. Click Run.

CREATING MACRO AS A FUNCTION

Example: Make a function AddTwoNumbers to add two numbers.

1. Open Macro Editor

o Tools → Macros → Organize Macros → LibreOffice Basic.
2. Pick Where to Save
o Select My Macros or current document → Click New (module).
3. Type the Function
 Function AddTwoNumbers(a, b)
AddTwoNumbers = a + b
End Function

4. Save and Close

o Ctrl + S → Close editor.
5. Use in Calc
o In a cell, type:
=AddTwoNumbers(10,20) → Press Enter → 30 appears.

LINKING SPREADSHEETS

1. Setting up Multiple Sheets in LibreOffice Calc

Notes:

 You can have many sheets in one Calc file.

 Use for separating months, subjects, categories, etc.

Steps:

1. Click the + icon at the bottom to add a sheet.

2. Rename: Right-click sheet tab → Rename Sheet.
3. Move: Drag sheet tab to reorder.
4. Delete: Right-click → Delete Sheet.

2. Creating Reference to Other Sheets

Notes:

 Lets you use data from another sheet in formulas.

Steps:

1. Type = in the cell.

2. Go to the other sheet → Click the cell you want to link.
3. Press Enter.
o Example: =Sheet2.A1 → takes value from cell A1 in Sheet2.

3. Creating Reference to Another Document

Notes:

 Pulls data from another Calc file.

Steps:

1. Type = in the cell.

2. Go to File → Open the other Calc file.
3. Click the cell you want to link → Press Enter.
o Example: ='[Link]
4. Using Hyperlinks – Relative & Absolute

Notes:

 Relative: Link path changes if file is moved with the linked file.
 Absolute: Full path; does not change if file is moved.

Steps to Insert Hyperlink:

1. Select the cell or text.

2. Press Ctrl + K or Insert → Hyperlink.
3. Choose link type (Web, File, etc.).
4. Enter the path (choose relative or absolute).
5. Click Apply or OK.

5. Four Categories in Hyperlink Dialog Box

1. Internet – Web (HTTP/HTTPS) or FTP links.

2. Mail & News – Email addresses or newsgroups.
3. Document – Links to another file or location inside a document.
4. New Document – Creates a new file and links to it.

6. Linking to External Data Source

Notes: Brings data from a webpage or other external file into Calc.

Steps:

1. Go to Sheet → Link to External Data.

2. Enter the URL or browse for file.
3. Select the data table to import.
4. Set update interval if you want auto-refresh.
5. Click OK.