Electronic Spreadsheet Notes – Class 10 IT 402 [CBSE Board Revision]

Don’t walk into your IT exam unprepared!
Simplify your preparation with these Electronic Spreadsheet notes of Class 10 IT 402. We break down the toughest topics into easy, step-by-step guides that anyone can follow. If you’re looking for all important topics of Class 10 Electronic Spreadsheet (Advanced) explanations or comprehensive notes, here it is—everything you need to ace your CBSE exams in one place.

Analyze Data Using Scenarios and Goal Seek

Consolidate Data

Consolidate is a feature used to combine data from multiple sheets into one sheet.
It helps in:

• Summarizing data
• Comparing information
• Finding trends and relationships

Things to Check Before Consolidating Data

Before using consolidate, make sure:

• Data types are same across all sheets
• Labels (row/column headings) match properly
• First column is set as the primary reference column

Steps to Consolidate Data

• Step 1: Open the spreadsheet containing data
• Step 2: Create a new sheet for consolidated data
• Step 3: Select Data → Consolidate. Consolidate dialog box opens.
• Step 4: In the dialog box, Select the required function (like Average, Count) from drop down list.(Note: By Default, ‘Sum’ function is selected in drop down list)
• Step 5: Select the source data range (Use named ranges OR manually select data)
• Step 6: Click ‘Add’ under ‘Consolidation Range’ to include the range
• Step 7: Repeat Steps 5-6 for other sheets
• Step 8: Select the target location (where result will appear)
  (by checking target range under ‘Copy result to’ or clicking the cell of target sheet)
• Step 9: Under ‘Consolidate by’ label, check ‘row label’ and ‘link to source data’. You can also check ‘column label’ if required
• Step 10: Click OK

Groups and Subtotals

Grouping:

Combines selected rows or columns into an outline for easy expand(-)/collapse(+), helping in better organization and quick viewing of large data.
To group follow these steps:

• Step1: Select the data to be grouped
• Step2: Click on Data > Group and Outline
• Step3: Select Rows to group data row-wise or Columns to group data column-wise

Subtotals

Automatically groups data by category and applies any summary functions like Sum or Average to generate subtotal for each group. It is used to summarize data, helping to analyze and extract specific information.
For Subtotal follow these steps:

• Step1: Open the worksheet where subtotal is to be appled
• Step2: Click on Data>Subtotals. Subtotal dialog box appears
• Step3: Select the column to group data by from the “Group By” list in the Subtotal dialog box
• Step4: Select the column by clicking the checkbox under ‘Calculate subtotal for’ to create subtotals
• Step5: Select the desired summary function (like sum, average) under Use function
• Step6: You can use the 2^nd group or 3^rd group tabs to group the data in further levels
• Step7: Click OK

Pre-requirement for Subtotals:
Data should have clear column labels and must be sorted based on the category for correct grouping and calculation.

What-if Scenario

• A What-if Scenario is a set of values saved with a name in a spreadsheet.
• Several scenarios can be created on the same sheet
• It is used to test and compare different outcomes by changing input values.

Steps to create Scenario

• Step1: Select the cells which contains values in the sheet that needs to be changed
• Step2: Choose Tools>Scenarios. Scenario dialog box opens.
• Step3: Enter a name for the new scenario and leave the other fields unchanged
• Step4: Click on OK button. This will create a new scenario which is activated automatically

What-if Analysis Tool

• Uses Data → Multiple Operations tools for What-if analysis.
• It is a planning tool to answer what-if questions.
• Output is shown using a list (array/drop-down), not in the same cells.
• Applies a formula to different input values to generate multiple results.

Steps for What-if analysis tool:

• Step1: Enter data in cells and calculate result using formula in other cell.
• Step2: Create an array of input values on the basis of which output is generated using formula
• Step3: Select the cell range of input array and output array
• Step4: click on Data > Multiple Operations. Multiple operation dialog box opens
• Step5: enter the cell address in the Formula box from the sheet which contain the formula
• Step 6: Now, enter the cell address of the cell which is a variable and is used in the formula in Column input cell box.
• Step 7: Click on OK will generate all the possible outputs based on the formula

Goal Seek

Goal Seek is another ‘what-if’ analysis tool that helps to obtain the required input value by setting a desired output(goal). It tries different values in a formula to determine which input will produce the expected result.

Steps to Goal Seek

Step1: Enter the values in the worksheet
Step2: Write the formula in the cell where the calculation has to be used
Step3: Place the cursor in the formula cell, choose Tools > Goal Seek. The Goal seek dialog box appears and Formula cell box will have the correct formula.
Step4: Place the cursor on the Variable cell box and click on the cell that contains the value to be changed
Step5: Enter the desired result in the Target value box
Step6: Click on OK button.

Using Macros in Spreadsheet

What is Macro?

• Macros are small programs that allows you to record a set of actions that you perform repeatedly in a spreadsheet.
• It helps automate repetitive tasks by allowing the same actions to be reused multiple times
• You can run macro as many times as you want.

Important Note:
Macro recording is disabled by default in LibreOffice; to enable it, go to Tools → Options → LibreOffice → Advanced and check “Enable macro recording” under Optional Features.

Limitations of Macro Recording

• Does not record opening of windows
• Does not record actions in other windows or window switching
• Ignores actions not related to spreadsheet content (e.g., Options, customization)
• Records selections only via keyboard, not mouse

Recording a Macro (Creating Macro)

• Step 1: Click on Tools → Macros, and then select the Record Macro option.
• Step 2: Start performing the actions that you want to record.
• Step 3: Once you click on the Record Macro option, recording begins and a small alert is displayed. Click on the “Stop Recording” button to stop recording the actions.
• Step 4: This will open the Basic Macros dialog box to save and run the created macro.
• Step 5: To save the macro, first select the location where you want to store it in the “Save Macro to” list box.
• Step 6: By default, the macro is named Main and is saved in the Standard Library under Module1. You can rename the macro if required.
• Step 7: Click on the Save button.

Rules for Naming a Macro, Module, or Library

While naming a Macro, Module, or Library, the name should:

• Begin with a letter
• Not contain spaces
• Not include special characters, except the underscore (_)

Steps to Run a Macro

• Step 1: Type the heading in cell A1.
• Step 2: Go to Tools → Macros → Run Macro to open the Macro Selector dialog box.
• Step 3: In the dialog box, you will see different libraries:
  LibreOffice Macros: Contains pre-recorded macros provided by LibreOffice (should not be modified).
  • My Macros: Contains macros created or added by the user.
  • Untitled1: Represents the current worksheet (it changes to the file name once saved).
• Step 4: Select the required library and module from the list.
• Step 5: Choose the macro from the Macro Name list.
• Step 6: Click on Run to execute the macro.

Creating and Organising a Simple Macro

• A recorded macro is stored as programming instructions that run when the macro is executed.
• While creating a macro, you can either create a new library/module or edit an existing one.

Steps to Organise a Macro

• Step 1: Go to Tools → Macros → Organize Macros → LibreOffice Basic to open the dialog box.
• Step 2: Click on Organizer to open the Basic Macro Organizer dialog box.
• Step 3: Go to the Libraries tab and click on New to create a library.
• Step 4: Select the Modules tab, choose a module, or click on New to create a module.
• Step 5: Open the module and write or edit the macro code.
• Step 6: Click OK to save and organize the macro.

Macro as a Function

• Used when repetitive calculations are required and no predefined function is available.
• Helps save time and effort by avoiding repeated typing of formulas.
• Created using Function and End Function instead of Sub and End Sub.
• Can accept arguments (inputs), perform calculations, and return a result.

Linking Spreadsheet Data

Setting up Multiple Sheets

• A spreadsheet can contain multiple sheets (e.g., Term1 for storing marks)
• To add a new sheet,
  • click the ( + ) icon at the bottom left of the spreadsheet OR
  • right-click on a sheet tab and select Insert Sheet OR
  • You can also use Sheet → Insert Sheet from the menu bar.

Cell Reference

A cell reference is the address of a cell or group of cells in the current worksheet or another worksheet, used to obtain values for calculations.

Creating Reference to Other Sheets by Using Keyboard and Mouse

• Step 1: Copy all required data and headings to the target sheet.
• Step 2: Select the cell in the target sheet where the result should be displayed and type =.
• Step 3: Enter the required formula (for example, SUM()).
• Step 4: Click on the sheet from which data is to be referenced, and select the required cell or range using the mouse.
• Step 5: Type a comma (,) and then again go to another sheet and select the corresponding cell or range.
• Step 6: Repeat Steps 4 and 5 if you need to include data from more sheets, and complete the formula in the target sheet accordingly.
• Step 7: Press Enter to get the final result.

Cell Reference Using Keyboard

To type the reference manually, it is important to understand how referencing works across different sheets in the same spreadsheet document.

To refer to a cell in another sheet, we use a specific format. First, a $ sign is used, followed by the sheet name enclosed in single quotes (‘ ‘). After that, a dot (.) is added, and then the cell address is written.
For example, to refer to cell C4 of a sheet named Term 1, we write:
$’Term 1′.C4

Note: Single quotes are necessary because there is a space in the sheet name (“Term 1”).

Creating Reference to Other Documents by Using Keyboard and Mouse

• Step 1: Open both documents (source document and target document) in the spreadsheet application.
• Step 2: In the target document, select the cell where the result should be displayed and type =.
• Step 3: Enter the required formula (for example, SUM() or any other function).
• Step 4 (Using Mouse):
• Switch to the source document and click on the required cell or select the required range. The reference will be automatically added to the formula.
• Step 5: If required, repeat Step 4 to include data from multiple documents by separating references with a comma (,).
• Step 6: Complete the formula in the target document.
• Step 7: Press Enter to get the result.

Cell reference to another document using keyboard

To type the reference manually,It is important to understand how referencing works between different spreadsheet files.

To refer to a cell in a different spreadsheet file, we write the full file path in single quotes. This is followed by #$, then the sheet name, a dot (.), and finally the cell address.
Format:
‘file path’# $SheetName.CellAddress

Example:
To refer to cell C4 from a sheet named Result in a file stored at a given location, we write:
‘file:///C:/Users/ADMIN/Documents/X-A.ods’#$Result.C4

Hyperlink

A hyperlink is a text or graphic that appears in a different color and is usually underlined, which you can click to open a file, a specific location within a file, or a web page.

Types of Hyperlinks

1. Absolute Hyperlink
2. Relative Hyperlink

Absolute Hyperlink

An absolute hyperlink is a link that contains the complete and exact path of a file from the root location. It directly points to a fixed address, so if the file is removed or shifted from that location, the link will stop working.
Example: D:\SchoolWork\Projects\report.ods

Relative Hyperlink

A relative hyperlink stores the path of a file based on the current file’s location instead of the full address. It depends on the active file location, so it works even if the whole folder is moved together; however, if the linked file itself is removed, the link will not open it.
Example: Projects\report.ods

Creating Hyperlink

• Step 1: Open the spreadsheet.
• Step 2: Click on Insert > Hyperlink. The Hyperlink dialog box appears.
• Step 3: Select Document, then choose Spreadsheet and browse to select the required file.
• Step 4: Click on Target and select the specific sheet or cell to be linked.
• Step 5: Enter the display text in the Text box for the hyperlink.
• Step 6: Click Apply and then Close.

Editing a Hyperlink

• Place the cursor on the hyperlink and right-click it.
• Select Edit Hyperlink from the context menu to open the dialog box and make required changes.

Removing a Hyperlink:

• Right-click on the hyperlink.
• Choose Remove Hyperlink to delete the link and disconnect it from the target location.

Linking to External Data

• Step 1: Open the spreadsheet where the external data needs to be inserted.
• Step 2: Select the cell where the first value of the external data should appear.
• Step 3: Go to Sheet > Link to External Data and the External Data dialog box will open.
• Step 4: Enter the URL of the source document or select it from the drop-down list and press Enter.
• Step 5: Choose the import language (select Automatic to display data in the same language as the source)

Linking to Registered Data Sources

• Step 1: Select Tools > Options > LibreOffice Base > Databases. The Options – LibreOffice Base-Databases dialog box appears.
• Step 2: Click the New button to open the Create Database Link dialog box.
• Step 3: Enter the location of the database file, or click Browse to select the database file.
• Step 4: Type a name to register the database and click OK. The database is added to the list of registered databases.

Note: The OK button becomes active only when both required fields are filled

Share and Review a Spreadsheet

• A shared spreadsheet is a single sheet that can be accessed by multiple users at the same time.
• It allows users to make changes simultaneously and work together on the same file.
• It avoids multiple copies of the same spreadsheet and supports collaboration.

Sharing Spreadsheet

• Step 1: Open a new spreadsheet and save it with a name.
• Step 2: Go to Tools > Share Spreadsheet from the main menu bar. The Share Document dialog box will open.
• Step 3: Tick the checkbox “Share this spreadsheet with other users” and click OK. A confirmation dialog box will appear to save the file in shared mode.
• Step 4: Click Yes to continue.
• Step 5: After saving, the title bar will show the spreadsheet name with (shared) indicating shared mode is active

Opening and Saving a Shared Spreadsheet

Opening a Shared Spreadsheet:

• A message appears while opening, indicating the spreadsheet is in shared mode and some features are restricted.
• Click OK to open it in shared mode.
• Optionally, select “Do not show warning again” to disable the message.
• Some features become unavailable after opening.
• The Edit menu shows limited options in shared mode.

Saving a Shared Spreadsheet:

• Save the spreadsheet after making changes before closing it.
• If changes don’t conflict, updates from all users are saved successfully.
• If conflicts occur, a Resolve Conflict window appears.
• Only one user can resolve conflicts at a time.
• If another user tries to save during conflict resolution, the file becomes locked.

Recording Changes

• To record changes, first disable the shared mode of the spreadsheet.
• Open the spreadsheet and go to Tools > Share Spreadsheet, then uncheck the sharing option and click OK.
• Confirm by clicking Yes; the “(shared)” label will be removed from the title bar.
• Enable tracking by going to Edit > Track Changes > Record.
• Make changes in the sheet; modified cells are highlighted (e.g., red border) and show change details on hover.
• This helps users identify what changes were made and by whom

Adding, Editing and Formatting Comments in Calc:

• Comments can be added automatically or manually by the user in a spreadsheet.
• To add a comment via track changes, go to Edit > Track Changes > Comment, then enter and save the text.
• You can also insert a comment using Insert > Comment; a note box appears where you type the message.
• A small indicator appears on the cell showing that a comment is attached, and hovering displays it.
• Right-click the cell to edit, delete, show, or hide the comment as needed.

Reviewing Changes (View, Accept or Reject):

• This step is done at the final stage to check all edits before submitting the spreadsheet.
• Go to Edit > Track Changes > Show to open the Show Changes dialog and decide which changes to display.
• Then select Edit > Track Changes > Manage to open the Manage Changes window.
• From here, you can Accept, Accept All, or Reject All changes after reviewing them.
• Click Close after completing the review process

Merging Documents:

• Open the spreadsheet files that need to be merged.
• Go to Edit > Track Changes > Merge Document.
• Select the first spreadsheet file from the Merge With dialog box and click Open.
• The Manage Changes dialog box opens; click Accept All to merge changes.

Comparing Documents:

• Open the spreadsheet file that needs to be compared.
• Go to Edit > Track Changes > Compare Document and select another file to compare, then click Open.
• The Manage Changes dialog box appears; review and click Accept for changes.
• Click Close to exit the Manage Changes window.