Class - X
Subject - Information Technology
Chapter- Electronic Spreadsheet (Advanced)
[ Question & Answers]
Session - 2020-21
Q1. What is Consolidating data?
Ans: Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet. In other words, the Data Consolidation function takes data from a series of worksheets or workbooks and summaries it into a single worksheet that you can update easily.
Q2. What is the use of Macros in a Spreadsheet?
ANS: Macros help in saving time in cases when the same set of tasks are to be done repeatedly like formatting or applying a similar formula in a similar range of data. It can be used to name and record a set of actions or set of actions.
Q3. Define the following:
a) Subtotals
b) Consolidating Data
ANS: a) Subtotals Subtotal adds data arranged in an array—that is, a group of cells with labels for columns and/or rows. It is available in Data Tab, using the Subtotals dialog, you can select arrays, and then choose a statistical function such as Count, Min, Max etc. to be applied on arrays. For efficiency, one must choose up to three groups of arrays to which to apply a function.
b) Consolidating Data Consolidate provides a graphical interface for copying data from one range of cells to another, then running one of a dozen functions on the data. During consolidation, the contents of cells from several sheets can be combined in one place.
Q4. Explain Conditional formatting with a help of suitable examples.
Ans: Conditional formatting allows to change the formatting (font color, border, shading) of the cells based on the values in it. One or more cells can be selected, and create rules (conditions) for when and how those cells are formatted. The conditions can be, based on the selected cell's contents, or based on the contents of another cell. For example Highlight the marks of the students who got marks greater than 80 with green colored text.
Q5. Elaborate the Three types of Views available in a spreadsheet.
Ans: Normal View: The default view of the spreadsheet application is the Normal. It‟s a collection of cells arranged in the work area.
Page Layout: It is to quickly fine tune a worksheet that contains many charts or huge amounts of data and achieve professional looking results. Page Break Preview: This option is similar to the Page Layout option except you can set the area that is to be set as a page after inserting page break.
Custom Views: To view selected areas of a document the custom view option can be used. Full Screen: Selecting this option makes the workbook cover the entire screen. All tabs are hidden from view.
Q6. What do you mean by Subtotal? Give example.
Ans: SUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of its usefulness, the function has a graphical interface.
SUBTOTAL, totals/adds data arranged in an array—that is, a group of cells with labels for columns and/or rows. Using the Subtotals dialog, you can select arrays, and then choose a statistical function to apply to them. For efficiency, you can choose up to three groups of arrays to which to apply a function. When you click OK, Calc adds subtotals and grand totals to the selected arrays,using the Result and Result2 cell styles for them.
Q7. What do you mean by what if and scenario?
ANS: Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. When you print the spreadsheet, only the content of the currently active scenario is printed.
A scenario is essentially a saved set of cell values for your calculations. You can easily switch between these sets using the Navigator or a drop-down list which can be shown beside the changing cells.
Q8. What do you mean by goal seek?
ANS: Usually, you run a formula to calculate a result based upon existing values. By contrast, using Goal Seek option under Tools menu, you can discover what values will produce the result that you want.
Q9. What do you mean by Solver?
ANS: Solver option under Tools menu amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables. It is specifically designed to minimize or maximize the result according to a set of rules that you define.
Q10. How can you insert new sheet?
ANS: Select Insert > Sheet from the menu bar, or Right-click on the tab and select Insert Sheet, or Click in an empty space at the end of the line of sheet tabs.
Q11. How can you insert a sheet from different spreadsheet can you insert new sheet?
ANS: • Insert menu – sheet option
• Choose from file radio button.
• Browse another worksheet.
• Choose a sheet which you want to add.
Q12. How can you rename a worksheet?
ANS: • Right click on sheet
• Choose Rename Sheet option.
Q13. Differentiate between absolute and relative hyperlink?
ANS: An absolute link will stop working only if the target is moved. A relative link will stop working only if the start and target locations change relative to each other. For instance, if you have two spreadsheets in the same folder linked to each other and you move the entire folder to a new location, a relative hyperlink will not break. To change the way that OOo saves the hyperlinks in your file, select Tools > Options > Load/Save > General and choose if you want URLs saved relatively when referencing the File System, or the Internet, or both. You can insert and modify links using the Hyperlink dialog. To display the dialog, click the Hyperlink icon on the Standard toolbar or choose Insert > Hyperlink from the menu bar. To turn existing text into a link, highlight it before opening the Hyperlink dialog.
Q14. How to link to external data?
Or
List the procedure involved in Linking HTML Tables to Calc Worksheet.
Ans: 1. Open the Calc worksheet where the external data is to be inserted. This is the target worksheet.
2. Select the cell where the upper left-hand cell of the external data is to be inserted.
3. Choose Insert -> Link to External Data.
4. On the External Data dialog, type the URL of the source worksheet or click the [...] button to open a file selection dialog. Press Enter to get Calc to load the list of available tables.
5. In the Available tables/range list, select the named ranges or tables you want to insert. You can also specify that the ranges or tables are updated every (number of) seconds.
6. Click OK to close this dialog and insert the linked data.
Q15. How to link to a registered data source?
ANS: 1. Choose Tools -> Options -> OpenOffice.org Base -> Databases.
2. Click the New button (below the list of registered databases) to open the Create Database Link dialog.
3. Enter the location of the database file, or click Browse to open a file browser and select the database file.
4. Type a name to use as the registered name for the database and click OK. The database is added to the list of registered databases. The OK button is enabled only when both fields are filled in.
Q16. How can you set up a spreadsheet for sharing?
ANS: At any time, you can set up a spreadsheet for sharing with others. With the spreadsheet document open, choose Tools > Share Document to activate the collaboration features for this worksheet. A dialog opens where you can choose to enable or disable sharing.
To enable sharing, select the box at the top of the dialog, and then click OK. A message appears stating that you must save the worksheet to activate shared mode. Click Yes to continue. The word (shared) is then shown on the title bar after the worksheet's title.
Q17. Explain the saving of a shared spreadsheet.
ANS: When you save a shared spreadsheet, one of several situations may occur:
● If the worksheet was not modified and saved by another user since you opened it, the worksheet is saved.
● If the worksheet was modified and saved by another user since you opened it, one of the
following events will occur:
● If the changes do not conflict, the worksheet is saved, the dialog below appears, and any cells modified by the other user are shown with a red border.
● If the changes conflict, the Resolve Conflicts dialog is shown. You must decide for each conflict which version to keep, yours or the other person’s. When all conflicts are resolved, the worksheet is saved. While you are resolving the conflicts, no other user can save the shared worksheet.
● If the changes conflict, the Resolve Conflicts dialog is shown. You must decide for each conflict which version to keep, yours or the other person’s. When all conflicts are resolved, the worksheet is saved. While you are resolving the conflicts, no other user can save the shared worksheet.
Q18. What do you mean by record changes?
ANS: Calc has the feature to track what data was changed, when the change was made, who made the change and in which cell the change has occurred.
If you are the sponsor of a youth baseball team. The coach has submitted a budget to you for the season and you need to edit the costs and return it to her. You are concerned that if you just make the changes, then the coach won't see the changes you made. You decide to use Calc with the record changes feature turned on, so that the coach can easily see the changes you have made.
Looking Over the Values, Viewing Changes, Adding Comments to Changes, To add a comment to a change:
Q19. What is the purpose of adding comments?
ANS: Calc automatically adds to any recorded change a comment describing what was changed (for example, Cell B4 changed from ‘9’ to ‘4’). Reviewers and authors can add their comments to explain their changes.
Q20. How can we add comments to the changes made?
ANS: 1. Make the change to the spreadsheet.
2. Select the cell with the change.
3. Choose Edit > Changes > Comments.
The dialog shown below appears. The automatically-added comment provided by Calc appears in the title bar of this dialog and cannot be edited.
4. Type your own comment and click OK.
After you have added a comment to a changed cell, you can see it by hovering the mouse pointer over the cell.
The comment also appears in the dialog when you are accepting and rejecting changes.
Q21. What do you mean by accepting and rejecting change?
ANS: When you receive a worksheet back with changes, the beauty of the recording changes system becomes evident. Now, as the original author, you can step through each change and decide how to proceed. To begin this process:
1. Open the edited worksheet.
2. Select Edit > Changes > Accept or Reject. The dialog shown below opens.
3. Calc steps through the changes one at a time. You can choose to accept or reject each change as you go through.
Q22. What do you mean by merging worksheet?
ANS: Sometimes, multiple reviewers return edited versions of a worksheet at the same time. In this case, it may be quicker to review all of these changes at once, rather than one review at a time.
For this purpose, Calc provides the feature of merging worksheets. To merge worksheets, all of the edited worksheets need to have recorded changes in them.
1. Open the original worksheet.
2. Select Edit > Changes > Merge Document.
3. A file selection dialog opens. Select a file you want to merge and click OK.
4. After the worksheets merge, the Accept or Reject Changes dialog opens as shown below,
showing changes by more than one reviewer. If you want to merge more worksheets, close the dialog and then repeat steps 2 and 3.
Q23. What are the steps to compare a document?
ANS: When sharing worksheets reviewers may forget to record the changes they make. This is not a problem with Calc because Calc can find the changes by comparing worksheets. In order to compare worksheets, you need to have the original worksheet and the one that is edited. To compare them:
1. Open the edited worksheet that you want to compare with the original worksheet.
2. Select Edit > Compare Document.
3. An open worksheet dialog appears. Select the original worksheet and click Insert. Calc finds and marks the changes as follows:
● All data that occurs in the edited worksheet but not in the original is identified as inserted.
● All data that is in your original worksheet but is not in the edited worksheet is identified
as deleted.
● All data that is changed, is marked as changed.
Q24. What do you mean by Macro?
ANS: A macro is a saved sequence of commands or keystrokes that are stored for later use. An example of a simple macro is one that “types” your address. The OpenOffice.org (OOo) macro language is very flexible, allowing automation of both simple and complex tasks. Macros are especially useful to repeat a task the same way over and over again.
Q25. How can we record a Macro?
ANS: Following steps create a macro that performs paste special with multiply.
1. Open a new spreadsheet.
2. Enter numbers into a sheet.
3. Select cell A3, which contains the number 3, and copy the value to the clipboard.
4. Select the range A1:C3.
5. Use Tools > Macros > Record Macro to start the macro recorder. The Record Macro dialog is displayed with a stop recording button.
Q26. What do you mean by sorting?
Ans: Sorting data can be automated in Open Office by creating a Macro in Calc. Data can be sorted on a single column or more than one column. Each time the Macro runs the data gets sorted. Such macros can be written using code in Open Office.
Q27. Differentiate between Relative and Absolute Hyperlinks with the help of an example.
ANS: An absolute hyperlink will stop working only if the target is moved. A relative hyperlink will stop working only if the source and target locations change relative to each other. Suppose, if you have two spreadsheets in the same folder linked to each other and you move the entire folder to a new location, a relative hyperlink will not break a link.
Q28. Explain the different types of Charts.
Ans. The different types of Charts are following:
Column Chat
Line Chart
Pie Chart
Bar Chart
Area Chart
Scatter Chart
Surface Chart
Bubble Chart.