Page 210 - TP_IT_V1.0_C10_flipbpookl
P. 210
UNIT Electronic Spreadsheet
2 (Advanced) using
LibreOffice Calc
Topics Covered 95%
Data Consolidation Groups and Subtotals
Using What If Scenarios Using What If Analysis
Using Goal Seek Using Solver
What are Macros? Linking Data and Sheet
Sharing Spreadsheet Record Changes in a Spreadsheet
Comments in Spreadsheet
Data analysis plays a pivotal role in modern organisations for numerous reasons. Data analysis helps organisations
in various tasks such as informed decision-making, problem-solving, understanding customer behaviour, improving
efficiency, performance evaluation, and risk management. It is required to study the trends of products required in
the local and global market. It is an important part of many organisations for planning and taking important decisions
for the progress of the company.
The LibreOffice Calc offers several built-in tools for data analysis, which are essential for performing various analytical
tasks directly within the spreadsheet software. These tools include Data Consolidation, What-if Analysis, Goal Seek,
solver and many more. In this unit we will study about these important data analysis tools.
The LibreOffice Calc default extension is OpenDocument spreadsheet (.ods).
DATA CONSOLIDATION
In LibreOffice Calc, data consolidation refers to the process of combining data from multiple sheets of the spreadsheet
into a single sheet by using different built-in functions like sum(), max(), min(), average(), etc. Data consolidation is
used to summarise the information for analysing or reporting purposes. The data of similar type from different sheets
in the same spreadsheet is picked up, consolidated and saved for further data analysis.
Before consolidating data, you need to check and ensure the following points:
• Look at each sheet in the spreadsheet and make sure the types of data are the same as what you want to
consolidate.
• Make sure the labels on each sheet are the same that you used for consolidation.
• Specify the first column as the main column based on which the data will be combined.
Let us take an example of adding the marks of Unit Test (out of 20), Term 1 Exam (out of 40) and Term 2 Exam
(out of 40) stored in three different sheets in the same spreadsheet so that the consolidated marksheet of a student
can be generated.
208 Trackpad Information Technology (Ver. 1.0)-X

