Unit2-Digital Spreadsheet (Advanced)
Unit2-Digital Spreadsheet (Advanced)
Unit2-Digital Spreadsheet (Advanced)
Creating Subtotals :
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.
It is accessible from Data menu.
Steps to insert subtotal values into a sheet:
1) Ensure that the columns have labels.
2) In the Subtotals dialog , in the Group by box, select the column that
you want to add the subtotals to.
3) In the Calculate subtotals for box, select the columns that you want
to subtotal.
4) In the Use function box, select the function.
5) Click OK.
Using “What If” Scenarios :
Creating Scenarios :
1) Select the cells that contain the values that will change
between scenarios.
2) Choose Tools > Scenarios.
3) On the Create Scenario dialog , enter a name for the new scenario.
This name is displayed in the Navigator and on the title bar of the
scenario.
4) Optionally add some information to the Comment box.
5) Optionally select or deselect the options in the Settings section.
6) Click OK to close the dialog
NOTE : You can create several scenarios for any given range of cells
Goal Seek
Usually, you run a formula to calculate a result based upon existing
values. By contrast Goal Seek option under Tools menu, helps to find
values which will produce the result that you want. for example
Chief Financial Officer 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? Then Chief
Financial Officer runs a goal seek on the empty cell for Q4 sales and
receives the answer
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 specifically designed to minimize or
maximize the result according to a set of rules that you define.
Session 2 : Link Data and Spreadsheets
Using Multiple Workbooks and Linking
Cells
Spreadsheet allows you to link the cells from various worksheets to
summarize data from several sources. In this manner, you can create
formulas using a combination of local and linked information.
Multiple sheets help to keep the information organized.
Inserting New Sheets
When you open a new spreadsheet, by default, it has a sheet named
Sheet1. 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:
1. Select Insert > Sheet from the menu bar, or
2. Right-click on the tab and select Insert Sheet, or
3. Click in an empty space at the end of the line of sheet tabs.
Each method opens the Insert Sheet dialog box where you can choose
to put the new sheet before or after the selected sheet and how many
sheets to insert.
Renaming Sheets
There are three ways you can rename a worksheet. You can do any of the
following:
1. Double-click on one of the existing worksheet names.
2. Right-click on an existing worksheet name, then choose
Rename from the resulting Context menu.
3. 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.
since you opened it, one of the following events will occur:
1. 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.
2. 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.
3. If another user is trying to save the shared
worksheet and resolve conflicts, you see a message
that the shared spreadsheet file is locked due to a
merge-in in progress.
Note: Most spreadsheets software automatically turns off some features
in shared workbooks to simplify the workbook since multiple people
can be working on the file at the same time. For example, shared
workbooks don‘t allow merging cells, conditional formatting, or
inserting pictures/graphs/etc
Record Changes :
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. for example
If you are the sponsor of a youth baseball team. The coach has
submitted a budget to you and you are concerned that the coach won’t
see the changes you made, So you decided to use Calc with the record
changes feature turned on, so that the coach can easily see the changes
you have made.
How to turned on Record Changes feature ON :
1. Open the Shared Spreadsheet.
2. Select Edit > Changes > Record from the menu bar.
3. Begin editing the worksheet.
NOTE : A red colored border, with a dot in the upper left-hand
corner, appears around a cell where changes were made.
Viewing Changes :
Calc allows you to control what changes you see when reviewing a
worksheet. To change the available filters, select Edit > Changes > Show
You can filter based on:
Q2. What are the two ways of referencing cells in other worksheets?
Ans. Two ways to reference cells in other sheets: by entering the formula
directly using the keyboard or by using the mouse.
Ans. Hyperlinks can be used in Calc to jump to a different location from within
a spreadsheet. 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.
Q4. List the procedure involved in Linking HTML Tables to Calc Worksheet.
Ans. 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.
We can do this in two ways: using the External Data dialog or using the
Navigator.
Using the External Data dialog
a. Open the Calc worksheet where the external data is to be inserted. This is
the target worksheet.
b. Select the cell where the external data is to be inserted.
c. Choose Insert -> Link to External Data.
d. 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.
e. 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.
f. Click OK to close this dialog and insert the linked data.
Ans. Comments are mostly used in shared Calc sheet which is used to explain
the changes made in the sheet to the author of the sheet.
3. Spreadsheet software allows the user to share the workbook and place it in
the Network location where several users can access.
5. Macros are useful to repeat a task the same way over and over again.