CBSE Class 10 Computer Science Electronic Spreadsheet Advanced

ANALYZE DATA USING SCENARIOS AND GOAL SEEK

Data Consolidation
In OpenOffice, data consolidation refers to the combining of data from various sources or sheets into a single area for easy analysis and manipulation. It helps when working with data that is spread across multiple files or sheets and enhances productivity, accuracy, and decision-making abilities.'

Advantages of using Consolidation Data –
• Saves time by combining information from various sources into one document.
• reduces the possibility of errors that could happen when manually copying data, which increases accuracy.
• allows you to examine and interact with the combined data in one place, which improves efficiency.
• improves decision-making by giving a thorough perspective of the data.
• Having all the data in one location makes it simpler to share it with other people.

Consolidation Data Window
To use the Consolidate tool in OpenOffice, follow these steps:
1. Open a new or existing spreadsheet in OpenOffice.
2. Select the cell where you want the consolidated data to appear.
3. Click on Data > Consolidate from the menu bar.
4. In the Consolidate dialog box, select the range of cells or sheets you want to consolidate.
5. Choose the function you want to use to consolidate the data (such as sum or average).
6. Specify any additional settings, such as whether to include labels or empty cells.
7. Click OK to consolidate the data.

Creating Subtotal in Electronic Spreadsheet
Subtotal in OpenOffice electronic spreadsheet refers to a function that calculates the subtotal of a range of values within a list or table. This function can be used to summarize data by category or group, allowing you to quickly see the total for each group.

Advantages of using Subtotal in OpenOffice electronic spreadsheet:
• Easy data analysis by grouping and summarizing data based on criteria.
• Improved accuracy by automating subtotal calculations and reducing errors.
• Increased efficiency by saving time and effort.
• Enhanced organization by grouping data into categories and subcategories.
• Customizable grouping and calculation criteria for flexibility in data analysis.

SubTotal Window
To use the Subtotal function in OpenOffice, follow these steps:
1. Open a new or existing spreadsheet in OpenOffice.
2. Select the range of data you want to subtotal.
3. Click on Data > Subtotals from the menu bar.
4. In the Subtotals dialog box, select the column you want to group by and the function you want to use (such as sum or count).
5. Click OK to apply the subtotals.

Using “What-if” Scenarios in Electronic Spreadsheet
Scenarios in OpenOffice electronic spreadsheet refer to a feature that allows you to create and compare different sets of data to explore different outcomes or possibilities. Scenarios are useful when you want to explore how changes in data will affect your calculations, such as in financial modeling or forecasting.

What is use of What-if in scenario
“what-if” analysis in scenarios, you can test different possibilities and explore the potential impact of changes to your data, helping you make more informed decisions.

Advantages of using Scenarios in OpenOffice electronic spreadsheet:
• Allows you to explore different outcomes or possibilities based on changes to your data.
• Helps you to better understand your data and make more informed decisions.
• Particularly useful in financial modeling, forecasting, and other areas where you need to explore the impact of different variables on your data.
• Provides a way to compare and contrast different sets of data and their potential impact.
• Can save time by allowing you to easily create and test different scenarios.

To use Scenarios in OpenOffice, follow these steps:
1. Open a new or existing spreadsheet in OpenOffice.
2. Enter the original data that you want to use as the basis for your scenarios.
3. Click on Tools > Scenarios from the menu bar.
4. In the Scenarios dialog box, click on Add to create a new scenario.
5. Give your scenario a name and select the cells you want to vary in your scenario.
6. Enter the new values for each cell in your scenario.
7. Click OK to save your scenario.
8. Repeat steps 4-7 to create additional scenarios.
9. To compare scenarios, click on Tools > Scenarios and select the scenarios you want to compare.'

Goal Seek in Electronic Spreadsheet
Goal Seek in electronic spreadsheet refers to a feature that allows you to find the input value needed to achieve a specific output value. This can be useful when you know the desired result of a
calculation, but you are not sure what input value is needed to achieve that result.
For example, you might use Goal Seek to determine what interest rate is needed to pay off a loan in a specific amount of time, or what sales volume is needed to achieve a certain profit margin.

To use Goal Seek in an electronic spreadsheet, follow these steps:
1. Enter the formula that you want to use in the cell where you want the output value to appear.
2. Enter an initial value for the input cell that you want to change.
3. Click on Tools > Goal Seek from the menu bar.
4. In the Goal Seek dialog box, enter the output value you want to achieve and select the cell containing the input value you want to change.
5. Click OK to run the Goal Seek analysis.
6. The Goal Seek feature will calculate the value needed to achieve the desired output and update the input cell with the new value.

Solver in Electronic Spreadsheet
The Solver is a more advanced version of Goal Seek. While Goal Seek is designed to find a single variable solution, Solver can handle equations with multiple unknown variables. It is used to find the best possible outcome that meets a set of defined conditions, such as minimizing or maximizing a particular result based on a set of rules or constraints that you define.

Solver example
Consider investing $10,000 over the course of a year in two mutual funds. Fund X is a lower risk fund with an interest rate of 8%, whereas Fund Y has a greater risk interest rate of 12%. How much should be put into each fund in order to generate a total interest of $1,000?

LINK DATA AND SPREADSHEETS
You may also link cells from several worksheets and spreadsheets to summarise data from numerous sources using spreadsheets. This allows you to build formulas that combine data from several sources and perform computations using both linked and local data.

Inserting New Sheet
When you open a new spreadsheet, it usually contains three sheet named Sheet1, Sheet2 and Sheet3.
If you want to insert a new sheet into the spreadsheet, there are different methods you can use.

Steps for Creating New Sheet –
1. Choose Insert > Sheet from the menu bar,
2. Insert the sheet by right-clicking on the tab,
3. Click in the empty area at the end of the sheet tabs.

Renaming Sheets
• Step 1 : Double-click on the name of a current worksheet.
• Step 2 : Use the context menu that appears when you right-click on the name of an existing worksheet to select Rename.
• Step 3 : Click on the worksheet tab to choose the worksheet you want to rename, and then choose the Sheet option from the Format menu. The Rename option should be chosen from the submenu that is displayed when you do this.

Cell Reference
Cell referencing in a spreadsheet is the process of referring to a specific cell or range of cells within a worksheet by using a unique identifier. This unique identifier is called a cell reference and it is made up of a combination of a column letter and a row number that identifies the location of a particular cell.

There are two ways to reference cells in other sheets –
1. Creating The Reference With The Mouse
2. Creating The Reference With The Keyboard.

Creating The Reference With The Mouse
Both spreadsheets must be open in order to construct the reference with the mouse. Choose the cell where the formula will be entered.

Creating The Reference With The Keyboard
Referencing It’s easy to enter a cell reference using the keyboard in a formula. The reference consists of three parts:
• Path and file name
• Sheet name
• Cell

Example – =’file:///Path &File Name’#$SheetName.CellName.
Working with Hyperlinks In Calc, you can utilise hyperlinks to navigate between spreadsheet cells and to other areas of the same or different files, websites, or even other spreadsheets.
Hyperlinks can be stored within your file as either relative or absolute.

Relative And Absolute Hyperlinks
Relative and absolute hyperlinks are used in spreadsheet applications, such as OpenOffice Calc, to create links between cells or other resources within the same spreadsheet or to external files and web pages.

a. Relative Hyperlinks – Relative hyperlinks are links that are specified in relation to the current
cell’s location. They typically use a path that is relative to the current file’s location. For example, if you are currently on cell A1 and you want to link to cell B1 in the same sheet, you can use the relative hyperlink “B1”.

b. Absolute Hyperlinks – Absolute hyperlinks, on the other hand, specify the full path or URL of the resource being linked to. For example, to link to a web page located at “https://cbseskilleducation.com/information-technology-class-10-notes/”, you can use the absolute hyperlink “https://cbseskilleducation.com/information-technology-class-10-notes/”.

Linking to External Data
To insert external data, such as tables from HTML documents or named ranges from other spreadsheets, into OpenOffice Calc, you can use the External Data dialog or the Navigator.
1. Using the External Data dialog: Go to “Data” > “External Data”, select the source of the data, and choose the specific data to insert. You can link to the external data or copy it into your spreadsheet.
2. Using the Navigator: Click on the “Navigator” icon or press F5, expand the “Data Sources” section, select the source of the data, and drag and drop the table or named range into your spreadsheet.

SHARING WORKSHEET DATA
Spreadsheet software gives users the option to share and store workbooks in a network location so that numerous users can view the same workbook at once. This function helps users collaborate more effectively by increasing the efficiency of data entering.

Setting up a Spreadsheet for Sharing
You can create a spreadsheet at any moment and share it with others. Open the spreadsheet document and select Tools > Share Document to enable the document’s collaborative features. You can choose whether to activate or disable sharing from a dialogue that appears.

Record Changes
You may track changes made to a spreadsheet by yourself or other users using the “Record Changes” option in OpenOffice Calc. When working on a spreadsheet with others, reviewing and editing data,and keeping track of multiple people’s edits, this capability comes in handy.
Any changes you make to the spreadsheet are noted in the document and recorded when the “Record Changes” option is turned on in OpenOffice Calc.
Turned on Record Changes featue ON
• Open the Spreadsheet
• Click on Edit > Changes > Record
• Now, You can start Editing the worksheet

Viewing Changes
Calc gives the control over the spread sheet to view the changes you or other have done in
Worksheet. To change the available filters, select Edit > Changes > Show. The following dialog
opens.

In OpenOffice Calc, you can view changes made to a spreadsheet using the “Record Changes” feature.
• Date filter: Shows only the changes made within a specified time range.
• Author filter: Displays changes made by a specific person, which is useful when multiple reviewers are working on the worksheet.
• Range filter: Shows only changes made within a specific range of cells, helpful for large spreadsheets.
• Comment filter: Displays changes that have comments matching the search criteria.
• Show accepted changes: Displays only the changes that you have accepted.
• Show rejected changes: Displays only the changes that you have rejected.

Accepting or Rejecting Changes
You have the choice to accept or reject the changes once you’ve done editing the document. When changes are accepted, they are made permanent parts of the document; when changes are rejected, the document returns to the way it was before the changes were made.
1. Go to the “Edit” menu and select “Changes” followed by “Accept or Reject.”
2. In the Accept or Reject Changes dialog box, you can either accept or reject changes one-by-one or all at once.

Merging Worksheets
Merging worksheets in OpenOffice Calc is a feature that allows you to combine two or more worksheets into a single worksheet. This is useful when you have data spread across multiple worksheets that you want to consolidate into a single worksheet for analysis or reporting purposes.
• Open the Worksheet
• Select Edit > Changes > Merge Document
• Select the file from selection dialog box
• Use Accept or Reject changes

Comparing Document
You can compare two or more spreadsheets in OpenOffice Calc using a feature called “Document Comparison” to find discrepancies between them. This can be helpful when you want to evaluate edits made to a spreadsheet by another person or when you want to compare two spreadsheet versions for inconsistencies or errors.
• Open worksheet
• Select Edit > Compare Document
• Select the original worksheet
• Now, You can find the changes

CREATE AND USE MICROS IN SPREADSHEET
Using Micors
A macro in OpenOffice is a set of commands or keystrokes that are stored for later usage. Macro might be simple, like inputting an address automatically, or sophisticated, like automating a sequence of calculations. Because the OpenOffice macro language is so flexible, users can automate a variety of processes. When repetitive actions need to be carried out repeatedly in the same manner, macros are especially helpful.

Advantages of Micro
• The repetitive and routine processes are automated by macros.
• Macros save your time

Creating Macro in Spreadsheet
• Open Worksheet
• Click on Tools > Macros > Record Macro
• Click on Start/Stop recording

Objective Questions

Question. ______________ allows you to combine data from multiple worksheets into a single master worksheet.
a. Data Consolidation
b. Multiple Data
c. Scenarios
d. Goal Seek
Answer : A

Question. Scenarios are a tool to test questions.
a. Auto
b. Goal Seek
c. What-if
d. Drop Down
Answer : C

Question. Which of the following reflects the importance of Goal Seek?
a. Subtotal
b. Scenario
c. Solver
d. Consolidate
Answer : C

Question. The intersection point between a row and column is called _______.
a. Row
b. Column
c. Table
d. Cell
Answer : D

Question. The Consolidated option is available under _________ menu.
a. View
b. Tool
c. Insert
d. Data
Answer : D

Question. _________ means combining data in a spreadsheet from different worksheets into master worksheet
a. Hyperlinks
b. Consolidating
c. Linking
d. Filter
Answer : B

Question. An __________ link will stop working only if the target is moved while a__________ link will stop working only if the start and target locations change relative to each other.
a. absolute, fixed
b. Relative, Absolute
c. absolute, relative
d. permanent, absolute
Answer : C

Question. Subtotals are available in __________ menu.
a. Tools
b. Format
c. Insert
d. Data
Answer : D

Question. Record Changes feature of Calc help
a. Authors and other reviewers to know which cells were edited.
b. to record the screen
c. to make changes permanent
d. None of the above
Answer : A

Question. Scenario option is available in ___________________ menu.
a. Data
b. Tools
c. Insert
d. Windows
Answer : B

Question. Hema holds the title of chartered accountant. On a daily basis, she used to keep track of the accounts on a spreadsheet. There are a number of steps that she must follow at all times. Can you recommend a spreadsheet feature that will allow her to complete these activities quickly and without having to repeat the processes each time?
a. Record Changes
b. Track Changes
c. Goal Seek
d. Using Macros
Answer : D

Question. _______________series of worksheets or workbooks summarizes it into a single worksheet.
a. Multiple Data
b. Data Consolidation
c. Scenarios
d. Goal Seek
Answer : B

Question. We can consolidate data by ________
a. Row Label
b. Column Label
c. Both of the above
d. None of the above
Answer : C

Question. Formulas in a Spreadsheet must begin with a __________ sign.
a. $
b. @
c. #
d. =
Answer : D

Question. As a school project, Krish and Kritika conducted a survey of their neighborhood’s literacy rates by age, which they recorded in a spreadsheet. They both want to work on it at the same time to finish it on time. Which option should they use to access the same Spreadsheet in order to save time?
a. Consolidate Worksheet
b. Shared Worksheet
c. Link Worksheet
d. Lock Worksheet
Answer : B

Question. You can use ________ to combine data from multiple worksheets into a master worksheet.
a. Data Consolidation
b. Goal Seek
c. Solver
d. Total data
Answer : A

Question. In Subtotals we can select up to _______ groups of arrays.
a. 3
b. 2
c. 4
d. Infinite
Answer : A

Question. Which feature of Calc helps to see the changes made in the shared worksheet?
a. Record Changes
b. Solver
c. Subtotal
d. None of the above
Answer : A

Question. In the consolidate window, which of the following functions are available?
a. Max
b. Min
c. Count
d. All of the above
Answer : D

Question. Raj has produced a worksheet in which he has entered all of his employees’ details. He wants each employee to look over the spreadsheet and, if necessary, change their address and phone number. He’d also like to know what modifications his employees have made. Which aspect of the spreadsheet should he enable so that he can view the changes that his staff have made?
a. Macro
b. Link Workbook
c. Change Worksheet
d. Track Changes
Answer : D

Question. When making a watermark or putting an image in the backdrop of a document, __________ comes in useful.
a. Transparency
b. Fill
c. Filters
d. Crop
Answer : A

Question. Solver option is available under _________ menu
a. Tools
b. Format
c. Edit
d. Insert
Answer : A

Question. Which function cannot be performed through Subtotal in a Spreadsheet?
a. Sum
b. Product
c. Average
d. Percentage
Answer : D

Question. ___________ is a tool to test “what-if” questions.
a. Scenario
b. Solver
c. Macro
d. Average
Answer : A 

Question. Which option is suitable to calculate the effect of different interest rates on an investment.
a. Scenario
b. Subtotal
c. Consolidate
d. None of the above
Answer : A

Question. _______is a spreadsheet function that is designed to minimize or maximize a result based on a set of rules that you describe.
a. Goal Seek
b. Scenario
c. Solver
d. Subtotal
Answer : C

Question. Which option is used to name a range of cells?
a. Range name
b. Cell Range
c. Define Range
d. Select Range
Answer : C

Question. A ______ hyperlink contains the full address of the destination file or web page.
a. Relative
b. Absolute
c. Mixed
d. Address
Answer : B

Question. Sunita is working on a spreadsheet project. Her friend taught her how to rename a spreadsheet, which will make it easier for her to identify the worksheet data. She was excited and told her brother about it. A worksheet in spreadsheets can be renamed in _____different ways, according to her brother.
a. 2
b. 3
c. 4
d. 1
Answer : B

Question. Which option should we use to define the target range? (Where we want the result to be displayed)
a. Source data range
b. Copy results to
c. Need result at
d. target range
Answer : B

Question. To select a random multiple cell hold down ________ key as you click on each cell.
a. Alt
b. Shift
c. Ctrl
d. All of the above
Answer : C

Question. Goal Seek Option available in __ menu.
a. Tools
b. Format
c. Data
d. Insert
Answer : A

Question. __________, totals/adds data arranged in an array—that is, a group of cells with labels for columns and/or rows Which step one must follow before using the Subtotal option?
a. Consolidate
b. Rename Data
c. Filter Data
d. Subtotal
Answer : D

Question. It refers to a cell or a range of cells on a worksheet and can be used to find the values or data that you want a formula to calculate.
a. Row
b. Column
c. Autosum
d. Cell Reference
Answer : D

Question. We can shift from one scenario to another by _____________
a. Navigator
b. Find and Replace
c. Data Source
d. None of the above
Answer : A

Question. Rohit received a 25 out of 30 grade in English and a 22 out of 30 grade in Math. In order to calculate the score in IT, he needs to acquire an overall score of 85 percent. Provide him with the best selection from the list below.
a. Macro
b. Solver
c. Goal Seek
d. Sub Total
Answer : C

Question. ____________ is a more elaborate form of Goal Seek.
a. Scenario
b. Subtotal
c. Solver
d. All of the above
Answer : C

Question. Define range option is available under the ________ menu.
a. Insert
b. View
c. Developer
d. Data
Answer : D

Question. SUBTOTALS _________ data arranged in an array (that is, a group of cells )
a. Add
b. Average
c. Find
d. Clear
Answer : A 

Question. Default name of first scenario created in Sheet1 of Calc is ________
a. Sheet1_Scenario1
b. Sheet1_Scenario_1
c. Sheet_1_Scenario1
d. Sheet_1_Scenario_1
Answer : B