Electronic Spreadsheet (Advanced)
Complete CBSE Class 10 IT (Subject Code 402) textbook coverage for LibreOffice Calc: Data Consolidation, Subtotals, What-If Scenarios, Goal Seek, Solver, LibreOffice BASIC Macros, Multi-Sheet Linking, and Real-Time Collaborative Spreadsheets.
Browse Dedicated Chapter Notes & Solved MCQs
Scenarios & Goal Seek
Consolidate Data, Subtotals, What-If Scenarios, Scenario Manager & Solver.
Using Macros in Calc
Macro Recorder, LibreOffice BASIC, Custom Functions, and Security Settings.
Linking Spreadsheet Data
Cross-Sheet Cell References, Hyperlinks, and Registered External Data Sources.
Share & Review Spreadsheet
Collaborative Sharing, Record Cell Changes, and Merge & Compare Workbooks.
Key Concepts At A Glance (Unit 2 Cheat Sheet)
Everything tested in CBSE Class 10 IT Board Examinations regarding LibreOffice Calc 7.x tools, menus, shortcuts, and functions:
Path: Data > Consolidate. Merges data from multiple ranges using mathematical functions (SUM, AVG, MAX). Check 'Link to source data' for dynamic live updates.
Scenarios: Tools > Scenarios (multiple cases in single sheet).
Goal Seek: Tools > Goal Seek (single input reverse solve).
Solver: Tools > Solver (multiple variables with <=, >= constraints).
Automates repetitive tasks. Path: Tools > Macros. Custom functions: Function Name(arg) ... End Function. Stored in Standard > Module1.
Reference: =$'SheetName'.Cell. Data Sources: F4. Share: Tools > Share Spreadsheet. Track changes with red border borders.
Analyse Data Using Scenarios and Goal Seek
In advanced spreadsheet modeling, decision-makers test multiple business hypotheses, combine distributed data sets, and work backwards from target financial outcomes using What-If analysis tools.
1Consolidating Data (Data > Consolidate)
Consolidation takes data from two or more cell ranges or sheets and applies an aggregate mathematical function to populate a target master table.
Supported Consolidation Functions
In the Consolidate dialog, you choose an aggregate function:
- Sum: Adds values from all source ranges (default).
- Count: Counts the number of numerical entries.
- Average: Computes the arithmetic mean.
- Max & Min: Returns highest or lowest value among sheets.
- Product & StDev: Calculates product or sample standard deviation.
Crucial Exam Note: Link to Source Data
Expanding 'More' reveals the Link to source data checkbox.
When Unchecked: The master sheet holds static values copied at the moment of consolidation.
2Creating Subtotals (Data > Subtotals)
The Subtotals tool automatically categorizes rows by group labels and creates summary rows (Subtotal and Grand Total) with collapsible outline controls (levels 1, 2, 3) on the left margin.
Step-by-Step Procedure:
- Highlight the table range including column headers.
- Ensure data is sorted by the grouping column, or let Subtotals sort it automatically via the Options tab.
- Click Data > Subtotals.
- In the 1st Group tab: select the category column in 'Group by', check columns to calculate under 'Calculate subtotals for', and pick the aggregate function (Sum/Average).
- Up to 3 Group levels can be defined for nested hierarchical analysis.
3What-If Analysis: Scenarios vs Multiple Operations vs Goal Seek vs Solver
Chapter 4 categorizes What-If tools based on whether they evaluate forward hypotheses or backward calculations:
| Feature | Scenarios | Multiple Operations | Goal Seek | Solver |
|---|---|---|---|---|
| Menu Path | Tools > Scenarios | Data > Multiple Operations | Tools > Goal Seek | Tools > Solver |
| Direction | Forward (What-if input X changes?) | Forward (Side-by-side comparative table) | Backward (Reverse engineering single input) | Optimization (Target, Min, Max with constraints) |
| Number of Variables | Multiple cell values per scenario | One or Two input variables | Strictly ONE variable cell | Multiple variable cells |
| Constraints Support | None | None | None | Yes (<=, >=, =, Integer, Binary) |
| Real-World Example | Toggling Best Case (+20% sales) vs Worst Case (-15% sales) | Mortgage interest rate table from 7% to 11% across 15, 20, 30 years | "What marks do I need in Final Exam to score 90% overall?" | "Maximize factory profits under labor <= 500 hrs & budget <= $50k" |
Unit 2 Solved MCQs (10 High-Yield Questions)
Directly curated from curriculum chapter-end self-assessment exercises and CBSE sample papers.
Which feature of LibreOffice Calc allows combining information from multiple separate worksheets into a single summary sheet?
What happens when you enable the 'Link to source data' checkbox in the Consolidate dialog in LibreOffice Calc?
Which What-If analysis tool in Calc is used to find an unknown input value required to produce a specific desired result in a formula?
How does the 'Solver' tool differ fundamentally from 'Goal Seek' in LibreOffice Calc?
What is a Macro in LibreOffice Calc?
Which of the following actions CANNOT be recorded by the built-in Macro Recorder in LibreOffice Calc?
Which Macro Security level in LibreOffice prompts the user for confirmation before executing unsigned macros from untrusted origins?
How do you correctly reference cell B4 located in a sheet named 'Quarter 1' within the same LibreOffice Calc workbook?
Which function key opens the 'Data Sources' browser in LibreOffice Calc to link tables or queries from a registered database?
When a spreadsheet document is set to 'Shared' mode (Tools > Share Spreadsheet), which of the following operations is disabled?
Frequently Asked Board Questions (Calc Unit 2)
Detailed model answers strictly formulated according to official CBSE marking criteria: