Advanced Features of

2. Spreadsheet Unit 2: Electronic Spreadsheet [Advanced]

Unsolved Exercise
Section A
A. 1. iv 2. iv 3. iii 4. iv 5. ii
B. 1. master worksheet 2. Goal seek 3. Solver 4. Sheet 1 5. Hyperlinks
Section B
A. 1. Scenario is a tool to test ‘what-if’ questions. Scenario is a set of values that spreadsheet
saves and can substitute automatically in cells on a worksheet. Each Scenario is named and
can be edited and formatted separately, and chosen from a drop-down list in the Navigator
and the title bar of the Scenario.
Step 1: Select the cells that contain the values which will change between scenarios. To
select multiple cells, hold the ‘Ctrl’ key and then click each cell.
Step 2: Click on ‘Tools Menu.
Step 3: Select ‘Scenarios’ option.
Step 4: Type a name for the new scenario. This name will be displayed on the title bar of
the scenario on the worksheet itself.
Step 5: Click on OK button. It is also optional to select or deselect the options in the ‘Settings’
section. Following options are there in ‘Settings’ section.
Step 6: Click ‘OK’ to close the dialog box. A new Scenario is automatically activated. Repeat
the steps to create another Scenario.
ultiple Operations is a planning tool for ‘what-if’ questions. Unlike Scenario. The Multiple
M
Operations tool creates a formula array which is a separate set of cells that give all the
alternative results for the formulas used.
Goal Seek is basically used when our output or target value is fixed and we have to make a
change in any one of the input cell values. Goal Seek option reverses the usual order of a
formula.
Solver is a more descriptive form of Goal Seek. Solver can deal with equations having multiple
unknown variables. In Solver, we can manipulate a set of cells after knowing the output
and can estimate the minimum or maximum value that can be entered into those cells. It is
specifically designed to minimize or maximize the result according to a set of limiting rules
defined by the user.
2. • Display border: It is used to highlight the Scenario with a border. The color for the border
is specified in the field to the right of this option. The border has a title bar displaying
the name of the last scenario.
• Copy back: It copies the values of cells that you change into the active Scenario. If you
do not select this option, the Scenario doesn’t change when you change cell values.
 • Copy entire sheet: It copies the entire sheet into an additional scenario sheet.
• Prevent changes: It is used to prevent changes to the active scenario.
3. • Formula Cell: In the formula cell we can enter the reference of the cell which contains
the formula. It contains the current cell reference. We can click another cell in the sheet
to apply its reference to the text box.
• Variable Cell: It specifies the reference for the cell that contains the value you want to
adjust in order to reach the target.
4. To create a scenario:
Step 1: Select the cells that contain the values which will change between scenarios. To
select multiple cells, hold the ‘Ctrl’ key and then click each cell.
Step 2: Click on ‘Tools Menu.
Step 3: Select ‘Scenarios’ option.
Step 4: Type a name for the new scenario. This name will be displayed on the title bar of
the scenario on the worksheet itself.
Step 5: Click on OK button. It is also optional to select or deselect the options in the ‘Settings’
section. Following options are there in ‘Settings’ section.
Step 6: Click ‘OK’ to close the dialog box. A new Scenario is automatically activated. Repeat
the steps to create another Scenario.
5. To provide results using multiple operations:
Step 1: To calculate the profit, enter the formula =B4*B1-B2)-B3 in cell ‘B5’
Step 2: In column D, enter expected ‘Annual Sale Figure’.
Step 3: Select the range ‘D2:E6’. This will select the values in column D and empty cells of
column E.
Step 4: Click on ‘Data’ menu and then select ‘Multiple Operations’ option.
Step 5: ‘Multiple operations’ dialog box appears on the screen. Click on the ‘Formulas’ field
of the ‘Multiple operations’ dialog box, then click the cell ‘B5’. This means that cell
B5 contains the result i.e. the formula for calculation of result.
Step 6: Place the cursor in the ‘Column input cell’ field and click cell ‘B4’. This means that
content of cell ‘B4’ is variable in the formula.
Step 7: Click on Ok button.
6. To perform GoalSeek in OO Calc:
Step 1: Click on ‘Tools’ menu.
Step 2: Select ‘Goal Seek’ option. Goal Seek’ dialog box appears on the screen.
Step 3: The cell address containing the formula is already entered in a ‘Formula cell’ field.
Step 4: Enter the desired result i.e. ‘80’.
Step 5: ‘Place the cursor in the ‘Variable cell’ field. In the sheet, click in the cell that contains
the value to be changed. In this example, ‘B7’ is the cell.
Step 6: Click on ‘OK’ button.
 Step 7: Click on ‘Yes’ button.
Step 8: Estimated marks of Computer subject is ‘75’ to get ‘80%’ aggregate.
7. To use solver in OO Calc:
Step 1: Place the cursor in the formula cell (i.e. ‘B8’).
Step 2: Click on ‘Tools’ menu.
Step 3: Select ‘Solver’ option. ‘‘Solver’ dialog box appears on the screen.
Step 4: Set the result to 80.
Step 5: Select the cells whose value can be changed. (Here, B6 and B7).
Step 6: ‘Set desired Limiting Condition. Here, we have set the rule that the marks of Computer
subject must be greater than marks of Science subject.
Step 7: ‘Click on ‘Solve’ button. ‘Solving Result’ dialog box appears.
Step 8: Click on ‘Keep Result’ button to keep the updated values in the cell.
8. An absolute hyperlink contains a full address, the protocol and domain name for URLs, and
the entire path and file name for documents where as a relative hyperlink contains a partial
address.
B. 1. i. 137.50 ii. 97.50 iii. 123.38
2. File Æ Rename

Previous Years’ Questions

1. SUM
2. rowwise, columnwise
3. (a) =SUM(B3 : E3)
(b) Multiple operations
(c) =MAX(B5 : E5)
(d) AVERAGE(B3 : B6)
(e) = C5 – C4
4. Charts
5. File
6. Freeze Panes is spreadsheet means to keep specific rows or columns visible when the user
scrolls in the worksheet. It is generally used when the initial rows or columns of the worksheets
contain labels.
7. (a) Autosum
(b) Right click on sheet tab Æ Rename
(c) Freeze Panes
8. (a) F2 = C2 + D2
F3 = C3 + D3
F4 = C4 + D4
 F5 = C5 + D5
F6 = C6 + D6
(b) Sort Descending
(c) =MAX(F2 : F6)
(d) =AVERAGE(C2 : C6)
(e) View Æ orientation Æ Portrait
9. rowwise, columnwise
10. Window, View
11. (b)
12. Types of Charts:
1. Line Chart: It is used to show trends over a period of time. It is similar to plotting a graph
on a graph paper with its values on X and Y axis. It uses connecting dots to display trends
over a period of time.

2. Column Chart: It is usually used to display the data in the form of vertical bars. It is used
to show the changes in data over a period of time or comparison among the different
data items. The categories are represented on the horizontal axis and the values are
represented on the vertical axis.

3. Pie Chart: It is a circular chart divided into sectors where each sector shows the relative
size of each value. It always shows only one data series. It is useful when you want to
emphasis on a significant element.
 4. Bar Chart: The bar chart displays the data in the form of long rectangular rods also
called bars. These bars can be placed horizontally on the chart area. It illustrates the
comparisons amongst the individual items. In this chart, categories are represented on
the vertical axis and values are represented on the horizontal axis.

5. Area Chart: It is used to display the quantitative magnitude of the data graphically.
These charts are based on the features of the line chart. They basically emphasise the
area between the line and the axis with the help of the colours, textures, pictures, etc.

13. (a) =B2 * D2

(b) =(10 * E2)/100
(c) =E2 + F2