Electronic Spreadsheet Class 10 Notes

1. Consolidation Data

Sometimes we want to combine data from multiple sheets for analyzing purposes, this you can
do with consolidation of data, consolidation of the data helps to run multiple raw data to
summarize data easily using functions like sum, count, average, max, min, product etc.

Definition: Consolidate is a function which is used to combine data from the multiple sheets
into one place to summarize the information.

Steps to consolidate the data are as follows –

 Step 1: Open Spreadsheet

 Step 2: Choose Data > Consolidate dialog box and select the function

 Step 3: Click on Source data area

 Step 4: Select the source range or type the cell address

 Step 5: Click on Add button

 Step 6: Select additional range and click Add

 Step 7: Specify where you want to save the result using Copy results to box

 Step 8: Press Ok

2. Groups and Subtotals

Groups in LibreOffice – Group and Outline data is used when we want to group rows or
columns together so that one can collapse (-) to hide it or expand (+) it using a single click on it.
To select the data to be grouped Data > Group and Outline

The subtotal tool in Calc creates a group of data automatically and applies common functions
like sum, average on the grouped data. The subtotal will display the data in the form of sorting
in ascending or descending order.

Steps how to use subtotal –

 Step 1: Click on Data > Subtotals from menu bar

 Step 2: Select Column in which you want to use subtotal

 Step 3: Select functions like sum, average, count, max, min etc.

 Step 4: Click Ok.

3. Scenarios

When we ask about the possible results of an activity then we use What-if, What-if helps to
explore and compare various alternatives depending on changing conditions. It is used to
optimize the output and helps to predict the output while changing the inputs.

Definition: The What-if scenario is a powerful technique, scenario is a set of cell values which is
used within the calculation.

Use following steps to create scenarios in LibreOffice Calc-

 Step 1: Select the cells where you want to apply scenarios

 Step 2: Click Tools > Scenarios

 Step 3: Enter name of Scenarios

 Step 4: Select the Border color if desired

 Step 5: Click Ok Button.

4. Multiple Operations

What-if tool is a planning tool for possible outcomes, you can open Multiple Operations from
Data > Multiple Operations. Scenarios use a drop-down list to display the result based on the
input rather than displaying the output in the same cells as the input but Multiple Operations
tools creates a formula array to display the list of results.

Use following steps to create What-if analysis tool –

 Step 1: Enter the data in the cells

 Step 2: Create an array of input values on the basis of output what you generage

 Step 3: Select the cell range of input array and output array

 Step 4: Click on Data > Multiple Operations

 Step 5: Enter the cell address in formula box and Column input cell

 Step 6: Click on Ok
5. Goal Seek

Goal Seek comes under What-if Analysis tools. Goal seek helps to find specific output from the
given input. Goal seek can find the correct input when only the output is known.

Example – Suppose, you require a loan. You are aware of the amount you wish to borrow, you
also know the interest rate of the Bank, and how much you can afford to pay each month. To
find out what time you’ll need to get in order to reach your loan target, use Goal Seek.

User following steps to create goal seek –

 Step 1: Enter Principal amount, time, rate of interest in the worksheet

 Step 2: Write a formula for Simple interest

 Step 3: Click on Tools > Goal Seek

 Step 4: Place the cursor on the Variable cell box and click on the cell

Chapter 5: Using Macros in Spreadsheet

1. Macro in LibreOffice Calc

Sometimes we perform the same task multiple times such as using formulas in different places,
sometimes we type the same information multiple times like school Name, address, contact
number etc, Micro is a tool which helps the user to do the task easily.

Definition: A Macros is a set of commands for keystrokes that we can store for later use.

a. Recording a Macro

Macro is a sequence of actions such as keystrokes and clicks that can be recorded and then run
as per the requirement. Macro recording can be enabled using the tools option on the main
menu bar by selection Tools > Option > LibreOffice > Advanced.

Note: There are two different options which are not check marked, before starting Macro you
have to enable these two options.

In Macro you can record many things but there are some actions which will not be recorded –

 Opening of windows

 Transferring action from one window to another window.

 Window switching

 In Spreadsheet if actions are not related to content

 This macro you can use only in Calc, Writer and Excel

Follow the steps given below to record a macro –

 Step 1: Click on Tools > Macros > Record Macro

 Step 2: Start doing actions which you want to record

 Step 3: After completing your actions Click on Save Macro

 Step 4: Change the name of Macro

 Stet 5: Click on Save button

Rules for naming a Macro –

 Begin with a letter

 Not contain spaces

 Not contain special characters except for underscore (_)

b. Running a Macro

To run the macro in LibreOffice Calc, you can click on Tools > Macros > Run Macro.

Advantages of using Macro in LibreOffice Calc

 The macro can use multiple time

 The macro reduce time and improve the work speed

 The macro can interact more quickly as compare to function

 The macro saves the effort of remembering and typing the formulas.
 Linking Spreadsheet Data
In this chapter we are going to learn how to create multiple sheets and we are also going to
understand how to link the data in multiple sheets and how to organize the data properly.

1. Setting up Multiple Sheets (Insert New Sheets)

When you open LibreOffice Calc, you will find the default sheet “Sheet1”. There are multiple
ways to insert a new sheet.

 Select Insert > Sheet from menu bar

 Right click on tab and select Insert Sheet

 Click on the Add Sheet by clicking on the (+) sign located on the left side.

2. Cell Reference

A worksheet cell or range of cells that may be used in a formula for calculating values is referred
to as a cell reference. We can link the data in multiple sheets using cell reference. This can be
done using two different way –

 Creating reference to other sheets/documents by using keyboard and mouse

 Linking external data.

Creating Reference to Other Sheets by Using Keyboard and Mouse

When we want to add a cell reference from one sheet to another sheet then you have to click
the cell where the formula is to be entered, then input the formula with the = (equal sign) and
select the cell from the difference sheet. For example, to refer a cell C4 of sheet named Term1
we will type: $‛Term 1’.C4

Note: Single quotes (‘ ’) are used as there is a space between Term and 1 in the sheet name.

Creating Reference to Other Documents by Using Keyboard and Mouse

It is possible to create the referencing across different spreadsheet files using LibreOffice Calc.
This type of referencing is used to find the summary of the report using hyperlinks.

To refer to a cell in a different spreadsheet we write in single quotes the path of the file followed
by #$ then the name of the sheet followed by a . (dot) and then the cell address. For example:
‘[Link]

Note : The path of a file has three forward slash ///. If filenames have space inside the name
then single quotes (‘ ‘) are used.
3. Hyperlinks to the Sheet

Hyperlinks are used to jump to a document stored at a different location from within a
document. . Hyperlinks help to jump from a sheet in the same spreadsheet, different
spreadsheet or a website by creating a hyperlink.

a. Relative and Absolute Hyperlinks

There are two different types of hyperlinks –

a. Absolute Hyperlink – An absolute hyperlink keeps track of the entire location where the file is
kept. Absolute hyperlinks will not work if the file is removed from the specified location. C:\
Users\ADMIN\Downloads\try

b. Relative Hyperlink – The only way a relative link will break is if the start and target locations
move in relation to one another. For example: Admin\Downloads\ [Link] is a relative hyperlink
as it is dependent on the current location and thus the folder admin is searched where the
active spreadsheet is being stored.

4. Linking to External Data

Internet web pages are a rich source of information, this information is stored in a Table in
tabular format. LibreOffice Calc helps to import tables from HTML pages into Calc using the
External Data Dialog box.

Steps to Inserting data from External Data Dialog box –

 Step 1: Open Spreadsheet where you want to insert external data

 Step 2: Select Sheet > External links

 Step 3: Browse or Drop Down the External Data Source

 Step 4: Press Ok.

a. Linking to Registered Data Sources

We may connect spreadsheet documents to databases and other data sources using LibreOffice
Calc. It is necessary to register the data source with LibreOffice.
Data source which you can use to import data in LibreOffice are –

 LibreOffice Base

 ODF Spreadsheet

 HTML Documents

 Notepad File

 XML

Chapter 7: Share and Review Spreadsheet

LibreOffice Calc allow to share spreadsheet to more than one user at a time. The shared
spreadsheet can be edited and accessed by multiple people and allow them to make changes
simultaneously on it.

1. Sharing Spreadsheet

You can share the spreadsheet to multiple users to work in collaboration with the people. To
share the spreadsheet you can click on Tools > Share Spreadsheet from the menu bar. This will
open the share document dialog box and you can enable or disable sharing option.

a. Saving a Shared Spreadsheet

When you save a shared spreadsheet, from the following any one situations may occur –

 If two or more users are working simultaneously and their changes do not clash. A
notification indicating that the spreadsheet has been updated with modifications.

 The resolve conflict dialog window will pop up if there are any conflicts with the
changes.

 If any issue is there, the shared spreadsheet cannot be saved by another user without
resolving issues.

 If another user is trying to resolve disputes, then you will receive notification that the file
is locked.

2. Recording Changes (Track Change)

Calculus has the ability to trace which data was modified, when it was modified, by whom, and
in which cell it is modified using record change. Record change helps to find the usual changes
made by the other user, such as text additions, text alterations, deletions and usual formatting.
Steps for starting recording changes option

 Step 1: To enable the Record changes, first disable the shared mode

 Step 2: Click on Edit > Track Changes > Record

 Step 3: Start editing the worksheet

Reviewing Changes – View, Accept or Reject Changes

Once the spreadsheet is edited and saved by the users, now you will find accept or reject
options in the Edit > Track Changes > Manage in menu bar to prepare the final spreadsheet.

3. Merging Worksheet

Users apply the copy and paste method to combine the sheet data, however merging the sheets
is a smarter way to take their data together into a single sheet in Calc.

Follow the following steps to merge document –

 Step 1: Open Spreadsheet

 Step 2: Click on Edit > Track Changes > Merge Document

 Step 3: Select spreadsheet file which you want to merge

 Step 4: Click on Accept All

Note: The worksheet’s modifications from various authors are indicated by different colors.

4. Comparing Documents

To compare two workbooks and track the differences between them line by line, use
Spreadsheet Compare. LibreOffice Calc provides a facility to compare two documents.

Steps to comparing document in LibreOffice Calc

 Step 1: Click Edit > Track Changes > Compare Document

 Step 2: Open the Compare to dialog window, and select the .ods file

 Step 3: Manage the Changes based on accept/reject the changes

 Step 4: Click on Close button