📘 Detailed Notes – Electronic Spreadsheet (Advanced) (LibreOffice Calc)
---
Unit 1: Analyzing Data Using Advanced Tools
Sorting
Definition:
Sorting is the process of arranging data in a logical order, either ascending (smallest to
largest, A → Z) or descending (largest to smallest, Z → A). Sorting can be done on one field
(single-level sorting) or multiple fields (multi-level sorting).
Steps to Sort:
1. Select the range of data.
2. Go to Data → Sort.
3. Choose column(s) for sorting.
4. Select order (Ascending/Descending).
5. Click OK.
Advantages:
Organizes data systematically.
Makes it easier to analyze and compare.
Simplifies searching in large datasets.
---
Filtering
Definition:
Filtering means displaying only those records that meet certain conditions, hiding all other
data temporarily.
Types of Filters:
1. AutoFilter: Quick filter applied directly to column headers.
2. Standard Filter: Allows defining conditions like =, >, <, AND/OR.
3. Advanced Filter: Allows using complex conditions with criteria ranges.
Steps to Apply Filter (AutoFilter):
1. Select data.
2. Go to Data → AutoFilter.
3. Drop-down arrows appear in headers.
4. Select conditions.
Advantages:
Saves time by focusing only on required records.
Makes analysis simple.
Avoids manual searching.
---
Subtotals
Definition:
Subtotals are automatic calculations (sum, average, count, etc.) performed on grouped data.
Steps to Apply Subtotals:
1. Sort data based on grouping field.
2. Select data.
3. Go to Data → Subtotals.
4. Choose group field and calculation type (sum, avg, etc.).
5. Click OK.
Advantages:
Helps in summarizing grouped data.
Useful in financial, sales, and business reports.
Reduces manual calculations.
---
Data Consolidation
Definition:
Data Consolidation means combining data from multiple sheets or files into one summary
sheet.
Steps:
1. Go to Data → Consolidate.
2. Select function (Sum, Avg, Count, etc.).
3. Add ranges from different sheets/files.
4. Choose destination cell.
5. Click OK.
Advantages:
Saves effort in merging large datasets.
Provides a summarized view.
Useful when data is scattered across files.
---
Unit 2: Performing What-if Analysis
Goal Seek
Definition:
Goal Seek is a tool that helps in finding the input value required to produce a desired output
for a formula.
Steps:
1. Enter a formula with dependent cells.
2. Go to Tools → Goal Seek.
3. Set the “Formula cell” (output cell).
4. Enter desired target value.
5. Set “Variable cell” (input cell).
6. Click OK.
Example:
To find marks required in the final exam to achieve 80% average, set the average formula as
the formula cell and adjust the final exam cell as the variable.
Advantages:
Saves trial-and-error calculations.
Provides accurate required input.
Useful in planning budgets, targets, etc.
---
Scenarios
Definition:
A Scenario is a named set of values for certain cells that represents a possible situation. It
allows comparing multiple “what-if” cases easily.
Steps to Create Scenario:
1. Select the cells with variable values.
2. Go to Tools → Scenarios.
3. Enter Scenario name.
4. Enter values for the case.
5. Save Scenario.
6. Repeat for other scenarios.
Example:
Profit analysis under three cases – low sales, medium sales, high sales.
Advantages:
Allows storing multiple cases in one sheet.
Helps compare different situations.
Useful in forecasting, planning, and decision-making.
---
Unit 3: Advanced Features of Electronic Spreadsheet
Data Protection
Definition:
Data protection restricts access to cells, sheets, or entire documents with passwords to
prevent unauthorized changes.
Types:
1. Protect Sheet/Range: Only selected cells remain editable.
2. Protect Document: Entire spreadsheet is password-protected.
Steps:
To protect sheet: Tools → Protect Sheet.
To protect document: Tools → Protect Document → Select option.
Advantages:
Prevents accidental modification.
Protects sensitive information.
Ensures integrity of official records.
---
Sharing and Collaboration
Features:
1. Track Changes: Records all changes (who, what, when).
Steps: Edit → Track Changes → Record.
2. Comments: Add notes to cells for discussion.
Steps: Insert → Comment.
3. Merge Documents: Combines changes from multiple users.
Steps: Edit → Track Changes → Merge Document.
Advantages:
Facilitates teamwork.
Maintains record of edits.
Improves communication.
---
Linking and Importing Data
Linking: Refers to connecting data from external files/sheets. Updates automatically if source
changes.
Embedding: Copies full data inside current file (larger size, independent of source).
Importing: Bringing in data from other files, databases, or web sources.
Advantages of Linking: Smaller file, auto-updates.
Advantages of Embedding: File works independently of source.
---
Macros
Definition:
A Macro is a recorded sequence of steps and commands that can be saved and replayed to
automate repetitive tasks.
Steps to Record Macro:
1. Go to Tools → Macros → Record Macro.
2. Perform steps to be automated.
3. Stop recording.
4. Save with a name.
Advantages:
Saves time.
Reduces repetitive work.
Minimizes human error.
---
Pivot Tables (DataPilot)
Definition:
A Pivot Table (DataPilot in Calc) is a tool that summarizes large datasets, allowing grouping,
filtering, and aggregation.
Advantages:
Easy summarization.
Provides quick insights.
Supports interactive analysis.
---
Charts
Definition:
Charts are graphical representations of data such as bar, column, pie, line, scatter, area
charts.
Steps to Create Chart:
1. Select data.
2. Go to Insert → Chart.
3. Select chart type.
4. Customize chart elements (title, legend, axis).
5. Finish.
Advantages:
Makes data easy to understand.
Helps in decision-making.
Adds visual appeal to documents.