Data Entry and Keyboarding Skills Class 9 Notes

1. Analyze Data Using Scenarios and Goal Seek

Consolidate Data

Purpose:

• Combine data from multiple sheets into one sheet.
• Used for:
  • Summarizing data
  • Comparing information
  • Identifying trends

Requirements:

• Same data types across sheets
• Matching row/column labels
• Proper reference columns

Process Overview:

1. Open spreadsheet.
2. Create destination sheet.
3. Use Data → Consolidate.
4. Select function (Sum, Average, etc.).
5. Add source ranges.
6. Choose target location.
7. Configure row/column labels.
8. Confirm to generate consolidated results.

Groups and Subtotals

Grouping

• Organizes rows or columns.
• Allows expand/collapse view.
• Improves navigation of large datasets.

Subtotals

• Groups records automatically.
• Calculates:
  • Sum
  • Average
  • Other summary functions

Requirement:

• Data should be sorted before creating subtotals.

What-if Scenario

Definition:

• A named set of values stored in a spreadsheet.
• Multiple scenarios can exist in the same worksheet.

Use:

• Compare possible outcomes.
• Test different assumptions.

What-if Analysis Tool

Purpose:

• Perform planning and forecasting.
• Generate outputs for multiple input values.

Features:

• Uses Multiple Operations.
• Produces result arrays/lists.
• Useful for decision-making.

Goal Seek

Definition:

• Determines the input value required to achieve a desired result.

Use Cases:

• Finding required marks
• Required sales amount
• Target profit calculations

Basic Workflow:

1. Enter formula.
2. Open Goal Seek.
3. Select variable cell.
4. Enter target value.
5. Run Goal Seek.

---

2. Using Macros in Spreadsheet

What is a Macro?

A macro:

• Records repeated actions.
• Automates repetitive work.
• Can be executed multiple times.

Macro Recording Notes

By default:

• Macro recording is disabled.
• Must be enabled through LibreOffice options.

Limitations

Macro recorder does not:

• Record opening windows.
• Record switching windows.
• Capture customization activities.
• Record mouse-based selections properly.

Recording a Macro

General steps:

1. Open Macro Recorder.
2. Perform actions.
3. Stop recording.
4. Save macro.
5. Choose storage location.
6. Name the macro appropriately.

Naming Rules

Names should:

• Start with a letter.
• Contain no spaces.
• Avoid special characters except underscore (_).

Running a Macro

Process:

1. Open Macro Selector.
2. Choose library.
3. Select module.
4. Select macro.
5. Run macro.

Organizing Macros

Possible actions:

• Create libraries.
• Create modules.
• Edit macro code.
• Manage macro structure.

Macro as a Function

Used when:

• No built-in function exists.
• Repetitive calculations are required.

Features:

• Accepts arguments.
• Returns values.
• Works like spreadsheet functions.

---

3. Linking Spreadsheet Data

Setting Up Multiple Sheets

Methods to add sheets:

• Plus (+) button
• Insert Sheet option
• Sheet menu commands

Cell Reference

A cell reference:

• Identifies a cell address.
• Can refer to current or external sheets.

Referencing Other Sheets

Methods:

• Mouse-based referencing
• Keyboard-based referencing

Example format:

$'Sheet Name'.CellAddress

Used when referencing data from another sheet.

Referencing Other Documents

Allows formulas to use data from separate spreadsheet files.

General format:

'file path'#$SheetName.CellAddress

Useful for combining information across documents.

Hyperlinks

Definition

A hyperlink:

• Opens files
• Opens websites
• Navigates within documents

Types

1. Absolute Hyperlink
2. Relative Hyperlink

Absolute Hyperlink

• Uses complete file path.
• Breaks if file moves.

Relative Hyperlink

• Based on current file location.
• Continues working if folders move together.

Creating Hyperlinks

Typical process:

1. Insert Hyperlink.
2. Select target file.
3. Choose target location.
4. Enter display text.
5. Apply changes.

Editing and Removing Hyperlinks

Options include:

• Edit hyperlink
• Remove hyperlink
• Change destination
• Modify display text.

Linking External Data

Purpose:

• Import information from external sources.

General process:

1. Choose destination cell.
2. Select Link to External Data.
3. Provide source URL.
4. Import data.

Linking Registered Data Sources

Allows spreadsheets to connect with registered databases.

Main steps:

• Register database.
• Assign database name.
• Use database within spreadsheet.

---

4. Share and Review a Spreadsheet

Shared Spreadsheet

A shared spreadsheet:

• Supports multiple users.
• Enables simultaneous editing.
• Facilitates collaboration.

Sharing a Spreadsheet

Basic procedure:

1. Save spreadsheet.
2. Open Share Spreadsheet option.
3. Enable sharing.
4. Save in shared mode.

Opening and Saving Shared Files

Opening

• Warning message may appear.
• Some features become unavailable.

Saving

• Handles multi-user updates.
• Conflict resolution may be required.

Recording Changes

Purpose:

• Track modifications.
• Identify who changed data.
• Review edits later.

Features:

• Highlighted changes.
• Change details available.

Comments in Calc

Users can:

• Add comments
• Edit comments
• Delete comments
• Show/Hide comments

Comments help explain spreadsheet data and modifications.

Reviewing Changes

Options:

• View changes
• Accept changes
• Reject changes
• Accept all changes
• Reject all changes

Used before finalizing documents.

Merging Documents

Purpose:

• Combine changes from multiple spreadsheets.

General process:

1. Open spreadsheets.
2. Merge documents.
3. Review changes.
4. Accept updates.

Comparing Documents

Purpose:

• Identify differences between spreadsheets.

Workflow:

1. Select comparison file.
2. Review detected changes.
3. Accept or reject modifications.