UNIT-2: ELECTRONIC SPREADSHEET (ADVANCED)

SESSION 1: ANALYSE DATA USING SCENARIOS AND GOAL SEEK

SESSION 2: LINK DATA AND SPREADSHEETS

SESSION 3: SHARE AND REVIEW A SPREADSHEET

SESSION 4: CREATE AND USE MACROS IN SPREADSHEET

60
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK

Consolidating data
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.
1) Open the worksheet that contains the cell ranges to be consolidated.
2) Choose the Consolidate option under the Data menu as shown in Figure2.1. The
Consolidate dialog box is shown in Figure 2.2.

Figure 2.1: Consolidate option under Data Menu

Figure 2.2: Consolidate Dialog box defining the data to be consolidated

61
 3) If the Source data range list contains named ranges, you can select a source cell
range to consolidate with other areas.
If the source range is not named, click in the field to the right and either type a
reference for the first source data range or use the mouse to select the range on the
sheet. (You may need to move the Consolidate dialog to reach the required cells.)
4) Click Add. The selected range now appears on the Consolidation ranges list.
5) Select additional ranges and click Add after each selection.
6) Specify where you want to display the result by selecting a target range from the Copy
results to box.
If the target range is not named, click in the field next to Copy results to and enter
the reference of the target range or select the range using the mouse or position the
cursor in the top left cell of the target range.
7) Select a function from the Function list. The function specifies how the values of the
consolidation ranges are linked. The Sum function is the default setting.
Most of the available functions are statistical (such as AVERAGE, MIN, MAX,
STDEV), and the tool is most useful when you are working with the same data over
and over.
8) Optionally click More in the Consolidate dialog to display additional settings.
• Select Link to source data to insert the formulas. This generates the results in the
target range instead of the actual results. If you link the data, any values modified in
the source range are automatically updated in the target range. The corresponding cell
references in the target range are inserted in consecutive rows, which are
automatically ordered and then hidden from view. Only the final result, based on the
selected function, is displayed.
• Under Consolidate by setting, select either Row labels or Column labels, if the cells
of the source data range are not to be consolidated corresponding to the identical
position of the cell in the range, but instead according to a matching row label or
column label. To consolidate by row labels or column labels, the label must be
contained in the selected source ranges. The text in the labels must be identical, so
that rows or columns can be accurately matched. If the row or column label does not
match any that exist in the target range, it will be appended as a new row or column.
9) Click OK to consolidate the ranges.
10) If you are continually working with the same range, then you probably want to use
Data > Define Range to give it a name. Define Range option is available under the
Data Menu.

62
 The data from the consolidation ranges and target range are saved when you save the
worksheet. If you later open a worksheet in which consolidation has been defined,
this data will again be available.

Creating Subtotals

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. It is accessible from Data menu as shown in Figure2.3.

Figure 2.3: Subtotal option under Data Menu

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.
Steps to insert subtotal values into a sheet:
1) Ensure that the columns have labels.
2) Select the range of cells that you want to calculate subtotals for, and then choose Data
-> Subtotals.
3) In the Subtotals dialog (Figure 2.4), in the Group by box, select the column that you
want to add the subtotals to. If the contents of the selected column change, the
subtotals are automatically recalculated.
4) In the Calculate subtotals for box, select the columns containing the values that you

63
 want to subtotal.
5) In the Use function box, select the function that you want to use to calculate the
subtotals.
6) Click OK.

Figure 2.4: Setting up subtotals

If you use more than one group, then you can also arrange the subtotals according to
choices made on the dialog‘s Options page (Figure 2.5), including ascending and
descending order or using one of the predefined custom sorts defined under Tools menu
as Tools-> Options-> OpenOffice.org Calc-> Sort Lists.

Figure 2.5: Choosing options for subtotals

64
Using “What If” Scenarios
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. For example, if you wanted to calculate the effect of different
interest rates on an investment, you could add a scenario for each interest rate, and quickly
view the results. Formulas that rely on the values changed by your scenario are updated
when the scenario is opened. If all your sources of income used scenarios, you could
efficiently build a complex model of your possible income.

Creating Scenarios
Use Scenarios option under Tools menu to enter variable contents—scenarios—in the
same cell. To create a scenario:
1) Select the cells that contain the values that will change between scenarios. To select
multiple cells, hold down the Ctrl key as you click each cell.
2) Choose Tools > Scenarios.
3) On the Create Scenario dialog (Figure 2.6), enter a name for the new scenario. It‘s
best to use a name that clearly identifies the scenario, not the default name as shown
in the illustration. This name is displayed in the Navigator and on the title bar of the
scenario on the sheet itself.

Figure 2.6: Creating a scenario

65
4) Optionally add some information to the Comment box. The example shows the
default comment. This information is displayed in the Navigator when you click the
Scenarios icon and select the desired scenario.
5) Optionally select or deselect the options in the Settings section. See below for more
information about these options.
6) Click OK to close the dialog. The new scenario is automatically activated.
You can create several scenarios for any given range of cells.

Using Goal Seek

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.

To take a simple example, imagine that the Chief Financial Officer of a company is
developing sales projections for each quarter of the forthcoming year. She knows what
the company’s total income must be for the year to satisfy stockholders. She also has a
good idea of the company’s income in the first three quarters, because of the contracts
that are already signed. For the fourth quarter, however, no definite income is available.
So how much must the company earn in Q4 to reach its goal? The CFO can enter the
projected earnings for each of the other three quarters along with a formula that totals all
four quarters. Then she runs a goal seek on the empty cell for Q4 sales, and receives her
answer.

Other uses of goal seek may be more complicated, but the method remains the same. Only
one argument can be altered in a single goal seek.

Goal Seek example

To calculate annual interest (I), create a table with the values for the capital (C), number
of years (n), and interest rate (r). The formula is I = C*n*r.
Let us assume that the interest rate r of 7.5% and the number of years n (1) will remain
constant. However, you want to know how much the investment capital C would have to
be modified in order to attain a particular return I. For this example, calculate how much
capital C would be required if you want an annual return of $15,000.
Enter each of the values mentioned above into adjacent cells (for Capital C, an arbitrary
value like $100,000 or it can be left blank; for number of years n, 1; for interest rate r,
7.5%). Enter the formula to calculate the interest I in another cell. Instead of C, n, and r

66
use the reference to the cell with the corresponding value. In our example, this would be
=B1*B2*B3.
1. Place the cursor in the formula cell (B4), and choose Tools > Goal Seek.
2. On the Goal Seek dialog, the correct cell is already entered in the Formula cell field.
3. 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 it is B1.
4. Enter the desired result of the formula in the Target value field. In this example, the
value is 15000. The figure below shows the cells and fields.

Figure 2.7: Example setup for goal seek

5. Click OK. A dialog appears informing you that the Goal Seek was successful. Click
Yes to enter the result in the cell with the variable value. The result is shown below.

Figure2.8: Result of goal seek operation

Using the Solver

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

67
specifically designed to minimize or maximize the result according to a set of rules that
you define.
Each of these rules sets up whether an argument in the formula should be greater than,
lesser than, or equal to the value you enter. If you want the argument to remain unchanged,
you enter a rule that the cell that contains it should be equal to its current entry. For
arguments that you would like to change, you need to add two rules to define a range of
possible values: the limiting conditions. For example, you can set the constraint that one
of the variables or cells must not be bigger than another variable, or not bigger than a
given value. You can also define the constraint that one or more variables must be integers
(values without decimals), or binary values (where only 0 and 1 are allowed).
Once you have finished setting up the rules, you can adjust the argument and the results
by clicking the Solve button.

Solver example

Let's say you have $10,000 that you want to invest in two mutual funds for one year. Fund
X is a low risk fund with 8% interest rate and Fund Y is a higher risk fund with 12%
interest rate. How much money should be invested in each fund to earn a total interest of
$1000?

To find the answer using Solver:

1. Enter labels and data:
● Row labels: Fund X, Fund Y, and total, in cells A2 thru A4.
● Column labels: interest earned, amount invested, interest rate, and time period, in
cells B1 thru E1.
● Interest rates: 8 and 12, in cells D2 and D3.
● Time period: 1, in cells E2 and E3.
● Total amount invested: 10000, in cell C4.
2. Enter an arbitrary value (0 or leave blank) in cell C2 as amount invested in Fund X.
3. Enter the formulae given below:
● In cell C3, enter the formula C4-C2 (total amount - amount invested in Fund X)
as the amount invested in Fund Y.
● In cells B2 and B3, enter the formula for calculating the interest earned (see
below).
● In cell B4, enter the formula B2+B3 as the total interest earned.

68
 Figure2.9: Example setup for solver

4. Choose Tools -> Solver. The solver dialog opens as shown in Figure 2.10.

Figure2.10: The Solver dialog

5. Click in the Target cell field. In the sheet, click in the cell that contains the target
value. In this example it is cell B4 containing total interest value.
6. Select Value of and enter 1000 in the field next to it. In this example, the target cell
value is 1000 because your target is a total interest earned of $1000. Select Maximum
or Minimum if the target cell value needs to be one of those extremes.
7. Click in the By changing cells field and click on cell C2 in the sheet. In this example,
you need to find the amount invested in Fund X (cell C2).
8. Enter limiting conditions for the variables by selecting the Cell reference, Operator

69
 and Value fields. In this example, the amount invested in Fund X (cell C2) should not
be greater than the total amount available (cell C4) and should not be less than 0.
9. Click OK. A dialog appears informing you that the Solving successfully finished.
Click Keep Result to enter the result in the cell with the variable value. The result is
shown below.

Figure2.11: Result of Solver operation

ACTIVITY/ QUESTIONS:
1. A student is planning her goals about the marks she should attain in the forthcoming
Semester 4 examinations in order to achieve a distinction (75%). Assuming that
examination of each subject is for 100 marks, her marks of the previous semesters are
given as under.
Subject 1 Subject 2 Subjec3 Subject 4
Semester 1 82 67 53 87
Semester 2 88 78 76 69
Semester 3 89 85 91 67

Find out how many marks should she obtain in 4th semester to secure distinction.
2. A business owner wants to decide if he should try to increase the sales a product or
price of an existing product in order to increase the profit by 10%.
Current Sales 82
Cost per Unit 75
Profit per unit 12

70
 The owner believes that he can either increase sales by 5 units without incurring
additional costs while the price can be increased by Rs 8 without affecting the sales.

3. The current profit situation of a business owner is as follows.

Current Sales 82
Cost per Unit 75
Profit per unit 12

Using the scenario manager, find the effect of in the new profit in case of the following
situations.
a. Sales = 70 and cost = 80
b. Sales = 90 and cost = 72
c. Sales = 85 and cost = 80
d. Sales = 65 and cost = 80

SESSION 2: LINK DATA AND SPREADSHEETS

USING MULTIPLE WORKBOOKS AND LINKING CELLS

Relevant Knowledge
Spreadsheet also allows you to link the cells from various worksheets and from various
other spreadsheets to summarize data from several sources. In this manner, you can create
formulas that span different sources and make calculations using a combination of local
and linked information. Multiple sheets help keep information organized

SETTING UP MULTIPLE SHEETS

Identifying sheets
When you open a new spreadsheet, by default, it has a sheet named Sheet1 which is
managed using tabs at the bottom of the spreadsheet, as shown below.

71
 Figure 2.12 Identifying Sheets

Inserting new sheets

There are several ways to insert a new sheet. The first step, in all cases, is to select the
sheet that will be next to the new sheet. Then do any of the following:

 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.

Figure 2.13 Inserting New Sheets

Figure2.13 Creating a new sheet

Each method opens the Insert Sheet dialog. Here you can choose to put the new sheet
before or after the selected sheet and how many sheets to insert.

72
 Figure2.14 Insert Sheet dialog
We need 6 sheets, one for each of the 5 accounts and one as a summary sheet so we will
add 3 more. We also want to name each of these sheets for the account they represent:
Summary, Checking Account, Savings Account, Credit Card 1, Credit Card 2, and Car
Loan.
We have two choices: insert 3 new sheets and rename all 6 sheets afterwards; or rename
the existing sheets, then insert the three new sheets 1 at a time, renaming each new sheet
during the insert step.
To insert sheets and rename afterwards:

1. In the Insert Sheet dialog, choose the position for the new sheets (in this example, we
use After current sheet).
2. Choose New sheet and 3 as the No. of sheets. (Three sheets are already provided by
default.) Because you are inserting more than one sheet, the Name box is not
available.
3. Click OK to insert the sheets.
4. For the next steps, go to “Renaming sheets” below.
To insert sheets and name them at the same time:

73
1. Rename the existing sheets Summary, Checking Account, and Savings Account, as
described in “Renaming sheets” below.
2. In the Insert Sheet dialog, choose the position for the first new sheet.
3. Choose New sheet and 1 as the No. of sheets. The Name box is now available.
4. In the Name box, type a name for this new sheet, for example Credit Card 1.
5. Click OK to insert the sheet.
6. Repeat steps 1–4 for each new sheet, giving them the names Credit Card 2 and Car
Loan.
On the Insert Sheet dialog, you can also add a sheet from a different spreadsheet file (for
example, another Calc or Excel spreadsheet), by choosing the From file option.
Click Browse and select the file; a list of the available sheets appears in the box. Select
the sheet to import. If, after you select the file, no sheets appear you probably selected an
invalid file type (not a spreadsheet, for example).
Note: For a shortcut to inserting a sheet from another file, choose Insert > Sheet from
file from the menu bar. The Insert Sheet dialog opens with the From file option
preselected, and then the Insert dialog opens on top of it.

Inserting sheets from a different spreadsheet

If you prefer, select the Link option to insert the external sheet as a link instead as a copy.
This is one of several ways to include “live” data from another spreadsheet. The links can
be updated manually to show the current contents of the external file; or, depending on
the options you have selected in Tools > Options > OpenOffice.org Calc > General >
Updating, whenever the file is opened.

Renaming Worksheets
At the bottom of each worksheet window is a small tab that indicates the name of the
worksheets in the workbook. These names (Sheet1, Sheet2, Sheet3, and so on) are not
very descriptive; you might want to rename your worksheets to reflect what they contain.
For instance, if your workbook contains Students Marks in individual Subject then you
may want to rename the worksheets as Subject names such as English, Mathematics and
Social Science etc.
There are three ways you can rename a worksheet, and the only difference between them
is the way in which you start the renaming process. You can do any of the following:

74
• Double-click on one of the existing worksheet names.
• Right-click on an existing worksheet name, then choose Rename from the resulting
Context menu.
• Select the worksheet you want to rename (click on the worksheet tab) and then select
the Sheet option from the Format menu. This displays a submenu from which you
should select the Rename option.

Create Or Change A Cell Reference

A cell reference 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 formula to calculate.

In one or several formulas, you can use a cell reference to refer to:
• Data from one or more contiguous cells on the worksheet.
• Data contained in different areas of a worksheet.
• Data on other worksheets in the same workbook.

Referencing Other Sheets

There are two ways to reference cells in other sheets: by entering the formula directly
using the keyboard or by using the mouse. We will look at the mouse method first.

Creating The Reference With The Mouse

Look at the example below in Figure 2.14 which shows an account summary sheet with
a blank Balance column. On the Summary sheet, set up a place for all five account
balances, so we know where to put the cell reference. We want to place the reference for
the checking account balance in cell B3.

Figure 2.14 Blank summa

To make the cell reference in cell B3, select the cell and follow these steps.

75
1. Click on the = icon next to the formula bar. The icons change and an equals sign
appears in the formula bars shown below.

Figure 2.15 Equal sign in formula bar

2. Now, click on the sheet tab for the sheet containing the cell to be referenced. In this
case, that is the Checking Account sheet as shown below.

Figure 2.16 the checking account tab

3. Click on cell F3 (where the balance is) in the Checking Account sheet. The phrase
‘Checking Account’.F3 should appear in the formula bar as shown below.

Figure 2.17 Cell reference selected

4. Click the green checkmark in the formula bar to finish.

5. The Summary sheet should now look like the figure below.

76
 Figure 2.18. Finished checking account reference

Creating The Reference With The Keyboard

From the figure above, you can deduce how the cell reference is constructed. The
reference has two parts: the sheet name (’Checking Account’) and the cell reference (F3).
Notice that they are separated by a period.
Note:The sheet will be in single quotes because it contains a space, and the mandatory
period (.) always falls outside any quotes.
So, you can fill in the Savings Account cell reference by just typing it in. Assuming that
the balance is in the same cell in the Savings Account sheet, F3, the cell reference should
be =’Savings Account’.F3.

Figure2.19 Savings account reference

77
Referencing Other Worksheets

Calc can link different files together. The process is the same as described for different
sheets in a single spreadsheet, but we add one more parameter to indicate which file the
sheet is in.

Creating The Reference With The Mouse

To create the reference with the mouse, both spreadsheets need to be open. Select the cell
in which the formula is going to be entered.

1. Click the = icon next to the formula bar.

2. Switch to the other spreadsheet (the process to do this will vary depending on which
operating system you are using).
3. Select the sheet (Savings account) and then the reference cell (F3).

Figure 2.20 Selecting the savings account reference cell

4. Switch back to the original spreadsheet.

5. Click on the green check mark on the formula bar.
Your spreadsheet should now resemble the figure below.

Figure 2.21 Linked files

78
 You will get a good feel for the format of the reference if you look closely at the formula
bar. Based on this line you can create the reference using the keyboard.

Creating The Reference With The Keyboard

Typing the reference is simple once you know the format the reference takes. The
reference has three parts to it:

 Path and file name

 Sheet name
 Cell
Looking at the figure above, you can see the general format for the reference is
=’file:///Path &File Name’#$SheetName.CellName.

Working with Hyperlinks

Hyperlinks can be used in Calc to jump to a different location from within a spreadsheet
and can lead to other parts of the current file, to different files or even to web sites.

Relative And Absolute Hyperlinks

Hyperlinks can be stored within your file as either relative or absolute.
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.

79
 Figure 2.22 Hyperlink dialog showing details for Internet links

On the left hand side, select one of the four types of hyperlinks:

 Internet: the hyperlink points to a web address, normally starting with http://
 Mail & News: the hyperlink opens an email message that is pre-addressed to a particular
recipient.
 Document: the hyperlink points to a place in either the current worksheet or another
existing worksheet.
 New document: the hyperlink creates a new worksheet.
The top right part of the dialog changes according to the choice made for the hyperlink
category from the left panel. A full description of all the choices, and their interactions,
is beyond the scope of this chapter. Here is a summary of the most common choices used
in presentations.
For an Internet hyperlink, choose the type of hyperlink (choose between Web, FTP or
Telnet), and enter the required web address (URL).
For a Mail and News hyperlink, specify whether it is a mail or news link, the receiver
address and for email, also the subject.

80
 For a Document hyperlink, specify the worksheet path (the Open File button opens a file
browser); leave this blank if you want to link to a target in the same spreadsheet.
Optionally specify the target in the worksheet (for example a specific sheet). Click on
the Target icon to open the Navigator where you can select the target, or if you know the
name of the target, you can type it into the box.
For a New Document type hyperlink, specify whether to edit the newly created worksheet
immediately (Edit now) or just create it (Edit later) and enter the file name and the type
of worksheet to create (text, spreadsheet, etc.). The Select path button opens a directory
picker dialog.
The Further settings section in the bottom right part of the dialog is common to all the
hyperlink types, although some choices are more relevant to some types of links.

 Set the value of Frame to determine how the hyperlink will open. This applies to
worksheets that open in a Web browser.
 Form specifies if the link is to be presented as text or as a button. The figure below shows
a link formatted as a button.

Figure 2.23 Authors hyperlink as button

 Text specifies the text that will be visible to the user. If you do not enter anything here,
Calc will use the full URL or path as the link text. Note that if the link is relative and you
move the file, this text will not change, though the target will.
 Name is applicable to HTML documents. It specifies text that will be added as a NAME
attribute in the HTML code behind the hyperlink.

81
 Linking To External Data

You can insert tables from HTML documents, and data located within named ranges from
an OpenOffice.org Calc or Microsoft Excel spreadsheet, into a Calc spreadsheet

You can do this in two ways: using the External Data dialog or using the Navigator.

Using the External Data dialog

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.

Figure 2.24 Selecting a table or range in a source document from the Web

82
Linking To Registered Data Sources
You can access a variety of databases and other data sources and link them into Calc
worksheets. First you need to register the data source with OpenOffice.org. (To register
means to tell OOo what type of data source it is and where the file is located.) The way
to do this depends on whether or not the data source is a database in *.odb format.
To register a data source that is in *.odb format:

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.

Figure 2.25 Registering databases

3. Enter the location of the database file, or click Browse to open a file browser and
select the database file.

83
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.

ACTIVITY

Create a set of worksheets for storing records of marks of differnet classes and compare
all these on a separate worksheet

QUESTIONS:

1. How can we rename a worksheet?

2. What are the two ways of referencing cells in other worksheets?
3. Differentiate between relative and absolute hyperlinks.
4. List the procedure involved in Linking HTML Tables to Calc Worksheet
5. Fill up the blanks

a. At the bottom of each worksheet window is a small tab that indicates the ____________
of the worksheets in the workbook.
b. A ___________ 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 formula to calculate.

SESSION 3: SHARING WORKSHEET DATA

Relevant Knowledge

In most office settings, there is a shared drive where teams can store common files for
everyone to use. This usually leads to sighting of the message: “The document [file name]
is locked for editing by another user. To open a read-only copy of this document, click“!!
This message appears because someone else already has the file open.
Sometimes however, it is necessary to have multiple people working on a file at the same
time. This can be to either speed up data entry or simply make things easier for
collaboration purposes.

84