Unit 7

Electronic Spreadsheet (Advanced)

 Learning Objectives

This Unit Covers:

 Analyzing data using consolidation, subtotals, What-If scenarios, What- If tools,
goal seek, and solver
 Linking data and spreadsheets
 Sharing a spreadsheet as well as opening and saving a shared spreadsheet
 Reviewing a spreadsheet
 Working with macros in a spreadsheet
 Introduction

 OpenOffice Calc is a spreadsheet application that allows you to store, analyze,

manipulate, and visualize data.
 You can enliven your data by using charts available in Calc. Moreover,
OpenOffice Calc provides a wide variety of formulas and functions that you
can use to perform complex calculations with ease.
 OpenOffice Calc also provides various features for analyzing data, such as
consolidating data, What-if scenario, the goal seek, solver, etc.
 Apart from this, OpenOffice Calc provides various other useful features, such
as linking data and spreadsheet, sharing spreadsheet, and reviewing data of a
spreadsheet.
 OpenOffice Calc also provides another important feature, namely Macro,
which reduces the wastage of time in performing repeated tasks.
 Session 1: Data Analysis

 Data analysis is an integral function of OpenOffice Calc and refers to the

process of collecting data from a source, summarizing the data, and extracting
useful information from it.
 A data source may be a cell range in a sheet, a table, or data imported into a
sheet from an external source.
 Consolidating Data

 Consolidation is the process of combining data from within the same or

different spreadsheets or sheets.
 It can be used to create a report that consolidates and summarizes the data
from different source sheets into one sheet by applying some functions, such
as SUM, AVERAGE, etc.
 The steps to consolidate data in OpenOffice Calc are as follows:
1. Open a spreadsheet containing sales records of different cities of three
years in Year 2017, Year 2018, and Year 2019 sheets.
2. Select the cell range to specify the range name.
3. Type the name of the range in the Name Box. In our case, we have typed
Sales_Year_2017.
Similarly, you can assign name to the A1:E6 cell range of the Years 2018
sheet to Sales_Year_2018 and A1:E6 cell range of the Years 2019 sheet to
Sales_Year_2019.
 Consolidating Data

4. Select the A1 cell of the Report sheet to show the summarized result.
5. Select the Data → Consolidate option from the Menu bar.
6. Select the desired function from the Function drop-down list based on
which you calculate the values of the consolidation ranges. In our case, we
have selected the Sum function.
7. Select the desired source data range from the Source data range drop-
down list. In our case, we have selected the Sales_Year_2017 data range.
8. Click the Add button to add the source data range in the consolidation
range. The added source data range is displayed in the Consolidation
ranges list box.
9. Repeat steps 7 and 8 to add more source data range in the consolidation
range. In our case, we have added the Sales_Year_2018 and
Sales_Year_2019 source data range.
 Consolidating Data

10. Select the cell range in the Copy results to drop-down list where you want
to display the result after consolidating data.
11. Select the cell where you want to view the summarized result. In our case,
we have retained with the A1 cell of the Report sheet.
12. Click the More button to expand the Consolidate dialog box.
13. Select the Row labels check box to consolidate data row-wise.
14. Select the Column labels check box
to consolidate data column-wise.
15. Click the OK button to consolidate
the cell range, as shown in the
given figure.
The Report sheet shows the
consolidate ranges.
 Creating Subtotals

 Subtotal is a function that is used to calculate the subtotal in a cell range.

 This function has the ability to use different functions, such as Sum, Average,
Product, Count, Min for subtotaling.
 In Calc, when you use this feature, you get a subtotal and grand total of the
selected cell range.
 The steps to create a subtotal of data in OpenOffice Calc:
1. Open the sheet containing some data.
2. Select the cell range for calculating the subtotal.
3. Select the Data → Subtotal option from the Menu bar. The Subtotals
dialog box appears.
4. Select the desired option from the Group by drop-down list based on
which you want to group the subtotal.
5. Select the check box in the Calculate subtotals for list box to specify the
column whose value’s subtotals you want to calculate.
 Creating Subtotals

6. Select the function in the Use function list box that you want to apply to
calculate the subtotal.
7. Click the 2nd Group tab to calculate the subtotal based on the other
group.
8. Select the desired option from the Group by drop-down list based on
which you want to group the subtotal.
9. Select the check box in the Calculate subtotals for list box to specify the
column whose value’s subtotals you want to calculate.
10. Select the function in the Use function list box that you want to apply to
calculate the subtotal.
11. Click the OK button to calculate the subtotal.
 Using What-If Scenarios

 In OpenOffice Calc, you can create, edit, and manage scenarios with the help
of the What-If Scenarios tool.
 It allows you to save different sets of values (or scenarios) so that you can
easily view or use them at any time.
 The steps to use What-If Scenario are as follows:
1. Open a new or existing spreadsheet that contains data, as shown in the
given figure.
2. Type the formula =C3*C4*C5 in the cell C7 to
display the total interest to be paid by a user
for a loan.
3. Press the Enter key. The total interest appears in the cell C7.
4. Type the formula =C3+C7 in the cell C8 to display the total amount of the
loan to be paid by the user.
5. Press the Enter key. The total amount of the loan to be paid appears in the
cell C8.
 Using What-If Scenarios

6. Type the formula =C8/(C5*12) in the cell C9 to display the monthly

payment to be paid by the user for the loan.
7. Press the Enter key.
8. Select the cell range for creating scenario. In our case, we have selected
the A3:C9 cell range.
9. Select the Tools → Scenarios option from the Menu bar. The Create
Scenario dialog box appears.
10. Type the name of the scenario in the Name of scenario text box.
11. Select the Display border check box to display border around the
scenario.
12. Select the desired colour for the border from the drop-down list.
13. Uncheck the Copy back check box to do not modify the scenario while
changing the values.
 Using What-If Scenarios

14. Select the Prevent changes check box to restrict for modifying the settings
or properties of the scenario.
15. Click the OK button. The spreadsheet shows the created scenario with a
green colour border.
16. Modify the values in the scenario to create a new scenario.
17. Repeat steps 8 to 15 to create a new scenario.
Similarly, you can create more scenarios.
18. Click the down-arrow button of the scenario to switch from one scenario
to another. A drop-down list appears.
19. Select the scenario from the drop-down list that you want to display.
 Using What-If Tools

 Multiple Operations tool is used as a planning tool for What-If questions.

 Multiple Operations tool creates a formula array that is used to test and
analyze the results on a lot of data.
 Multiple Operations tool shows how the result of the formula will be affected
by changing the value of one or two input variables.
 In OpenOffice Calc, you can create two types of formula array, using one-input
variable and two-input variables.
 Using One-Input Variable

 A one-input variable-based formula array shows the results of one or more

formulas for various values of a one-input cell.
 In this formula array, you can specify the value of either the row input cell or
the column input cell.
 The steps to create a formula using one-input variable are as follows:
1. Open a new or existing spreadsheet that contains data, as shown in the
given figure.
2. Type the formula =B1*B2*B3 in
the cell D2 to display the total
interest to be paid by a user.
3. Press the Enter key. The total
interest appears in the cell D2.
 Using One-Input Variable

4. Type the formula =B1+D2 in the cell E2 to display the total payment to be
paid by the user.
5. Press the Enter key. The total loan payment appears in the cell E2.
6. Type the formula =E2/(B3*12) in the cell F2 to display the monthly
amount of the loan to be paid by the user.
7. Press the Enter key. The monthly amount to be paid for the loan appears
in the cell F2.
Now, let’s calculate the total interest, total amount of the loan, and the
amount to be paid every month according to the other loan amount in the
spreadsheet.
8. Select the cell range on which you want to perform the calculation. In our
case, we have selected the cell range A9:D14.
9. Select the Data → Multiple Operations from the Menu bar.
 Using One-Input Variable

The Multiple operations dialog box appears and the cursor displays in the
Formulas text box.
10. Select the cell range D2:F2 to specify the formula. The address of the
selected cell appears in the
11. Formulas text box.
12. Press the Tab key twice to move the cursor to the Column input cell text box.
13. Select the cell B1 to specify the column input value. The address of the
selected cell appears in the Column input cell text box.
14. Click the OK button, as shown in the following figure:
 Using One-Input Variable

The calculations are performed in the selected cells, as shown in the following
figure:
 Using Two-Input Variables

 The two-input variable-based formula array shows the result of only one
formula for the values of two-input cells.
 In this formula array, you can specify the value in both the row input cell and
the column input cell.
 The steps to create a formula using two-input variables are as follows:
1. Open a new or existing spreadsheet that contains data, as shown in the
given figure.
2. Type the formula =B1*B2*B3 in
the cell B6 to display the total
interest to be paid by a user.
3. Press the Enter key. The total
interest (1500000) appears in the
cell B6.
 Using Two-Input Variables

Now, let’s calculate the total interest amount based on the different loan
amount and interest rates in the spreadsheet.
4. Select the cell range on which you want to perform the calculation. In our
case, we have selected the cell range E2:I13.
5. Select the Data → Multiple Operations from the Menu bar.
The Multiple operations dialog box appears and the cursor appears in the
Formulas text box.
6. Select the cell range B6 to specify the formula. The address of the selected
cell appears in the Formulas text box.
7. Press the Tab key to move the cursor to the Row input cell text box.
8. Select the cell B2 to specify the row input value. The address of the
selected cell appears in the Row input cell text box.
9. Press the Tab key to move the cursor to the Column input cell text box.
 Using Two-Input Variables

10. Select the cell B1 to specify the column input value. The address of the
selected cell appears in the Column input cell text box.
11. Click the OK button.
The calculations are performed in the selected cells, as shown in the
following figure:
 Using Goal Seek

 Goal Seek tool is used when you know the result of the formula you are using,
but not the input value that the formula needs to obtain the result.
 The steps to use the goal seek feature for analysing data are as follows:
1. Open a spreadsheet that contains the information about the loan.
2. Select the cell B5 and type the
formula =(((B1*B2*B3)+B1)/(16*12))
to calculate the monthly payment of
loan with interest, as shown in given
figure.
3. Press the Enter key.
4. Select the Tools → Goal Seek option from the Menu bar. The Goal Seek
dialog box appears.
5. Select the cell from the sheet that contains the formula you want to
resolve. In our case, we have selected cell B5.
 Using Goal Seek

The address of the selected cell appears in the Formula cell text box.
6. Press the Tab key.
7. Type the desired value in the Target value text box to specify the result
you want. In our case, we have typed 81250.
8. Press the Tab key.
9. Select the cell from the sheet whose value you want to adjust. In our case,
we have selected cell B1. The address of the selected cell appears in the
Variable cell text box.
10. Click the OK button, as shown in the following figure:
 Using Goal Seek

The OpenOffice Calc message box appears.

11. Click the OK button in the OpenOffice Calc message box.
The amount of the loan changes from `3000000 to `6000000 according
to the monthly amount you want to pay, i.e., `81250, as shown in the
following figure:
 Using Solver

 The Solver tool is used to calculate the result of a particular business data
analysis.
 In this process, you specify a parent value and then bind conditions such as
less than or greater than and so on to different cells to see which range of
cells, product, or profitability meets the optimum result in accordance with
the parent value.
 Session 2: Linking Data and Spreadsheets

 Linking is the process to get data from an external spreadsheet or sheet into a
currently open or active spreadsheet or sheet by using cell references.
 The change in the source spreadsheet or sheet also gets reflected in the
destination spreadsheet or sheet data.
Using Multiple Sheets
 A new spreadsheet contains three sheets, named Sheet1, Sheet2, and Sheet3,
by default and, if required, you can add more sheets in a spreadsheet and
rename them according to your needs.
 You can rename these sheets to accurately reflect their content.
 Before you perform any of these tasks, you need to select the sheet you want
to work on from the Sheet tab.
 Adding a New Sheet

 In OpenOffice Calc, you can add a new sheet by using the following ways:
 By selecting the Insert → Sheet option from the Menu bar
 By right clicking on the sheet tab and then selecting the Insert Sheet
option from the context menu
 By clicking in an empty space in the sheet tab
 The Insert Sheet dialog box appears, as
shown in the given figure.
 With the help of the Inset Sheet dialog
box, you can add the sheet before or after
the currently selected sheet in the Sheet
tab.
 Renaming a Sheet

 OpenOffice Calc provides default names for the sheets you create.
 The names of the sheets appear in the Sheet tab of the Calc window.
 However, these names (Sheet1, Sheet2, Sheet3, and so on) are not very
descriptive.
 You can easily rename these sheets to reflect their content more clearly.
 In OpenOffice Calc, you can add a new sheet by using the following ways:
 By right clicking on the sheet in the Sheet tab and then select the Rename
Sheet option from the context menu
 By double-clicking in the sheet in the Sheet tab
 Referencing Other Sheets

 OpenOffice Calc allows you to use the reference of cell from one sheet to
another within a spreadsheet.
 You can do this by either using a mouse or keyboard.
 The steps to reference other sheets in a spreadsheet are as follows:
1. Open a spreadsheet that contains data. This work contains three sheets,
namely Year 2018, Year 2019 and Report. The Year 2018 sheet contains
sales record of an organization of 2018, as shown in the following figure:

The sales analysis of Year 2019 is shown in the following figure:

 Referencing Other Sheets

2. Select the Report sheet in the Sheet tab.

3. Enter the = formula in B2 cell.
4. Select the Year 2018 sheet in the sheet tab.
5. Select the F2 cell in the sheet. The referenced sheet name and cell
address, i.e., =‘Year 2018’.F2 is displayed in the Formula Bar.
6. Click the Accept button beside the Formula Bar.
The Report sheet shows the value of the referenced cell, as shown in the
following figure:
 Referencing Other Sheets

 You can also reference the cell from one sheet to another within a spreadsheet
by typing the sheet name and cell address in the following format:
='SheetName'.CellAddress
 For example, if you want to display the total sales of Meerut in Year 2019 in C3
cell of Report sheet, then type =‘Year 2019’.F2 and then press the Enter key.
 The Report sheet shows the value of the referenced cell.
 Referencing Other Documents

 OpenOffice Calc allows you to use the reference of cell from one spreadsheet
to another.
 You can do this by either using a mouse or keyboard.
 The steps to link cells across spreadsheets are as follows:
1. Open the Sales Report spreadsheet.
2. Open a new spreadsheet by selecting the File → New → Spreadsheet
option from the Menu bar.
3. Enter the required information in the new spreadsheet
4. Enter the = formula in cell B2.
5. Click the Windows menu in the Menu bar. A drop-down menu appears.
6. Select the Sales Report spreadsheet from the drop-down menu. The Sales
Report spreadsheet appears.
7. Select the cell F2.
 Referencing Other Documents

The reference path of Sales Report

spreadsheet and selected cell range
displays in the cell B2, as shown in the
given figure.
8. Press the Enter key. The value gets displayed in cell B2.
 You can also reference the cell from one spreadsheet to another by typing the
path and file name, Sheet name, and cell address in the following format:
='file:///Path & File Name'#$SheetName.CellAddress
 For example, if you want to display the total sales of Meerut in Year 2019 in C3
cell, then type =’file:///E:/Sales Report.ods’#$’Year 2019’.F2 and then press
the Enter key.
 Understanding Hyperlinks

 In OpenOffice Calc, hyperlink is used to jump to the Web page, email message,
or document.
 In Calc, you can create, edit, and remove the hyperlink.
 In Calc, you can create a hyperlink in two ways:
 By selecting the Insert → Hyperlink option from the Menu bar
 By clicking the Hyperlink button on the Standard toolbar
 You can edit the hyperlink by placing the cursor over the hyperlink and then
click the Hyperlink button on the Standard toolbar or select the Insert →
Hyperlink option from the Menu bar.
 You can remove the hyperlink by the following ways:
 By right-clicking the cell and then selecting the Default Formatting option
from the context menu
 By selecting the Format → Default Formatting option from the Menu bar.
 Linking External Data into Spreadsheet

 In OpenOffice Calc, you can link sheets as well as spreadsheet. Hence, you can
avoid manual copying and pasting data and get error-free result.
 In OpenOffice Calc, you can link the tables of HTML Web page as well as the
data specified in range name from an OpenOffice Calc or Microsoft Excel
spreadsheet.
 In OpenOffice Calc, you can link the external data into spreadsheet by using
the following ways:
 By using the External Data dialog box
 By using the Navigator
 You can use the External Data dialog box to link the external data when you
know the name of the table or the range.
 Linking Registered Data Sources into Spreadsheet

 Calc allows you to link various types of databases and data sources into a
spreadsheet.
 Before linking the data source, you need to first register the data source with
OpenOffice.org.
 The steps to link the registered data source into spreadsheet are as follows:
1. Open a new or an existing spreadsheet.
2. Select the cell from where you want to insert the linked data source.
3. Select the View → Data source option from the Menu bar to open the
data source. The data source is displayed in the spreadsheet.
4. Click the + (expand) button beside the desired database that you want to
expand. The database gets expanded.
5. Click the + (expand) button beside the Table option to view all tables.
 Linking Registered Data Sources into Spreadsheet

6. Select the desired table. The record of selected table is displayed in the
data source.
7. Select the rows of data of a table you want to add to the spreadsheet.
8. Click the Data to text button in the Table Data toolbar to insert data into
the spreadsheet cells.
The selected rows are displayed in the spreadsheet.
 Session 3: Sharing a Spreadsheet

 OpenOffice Calc provides the facility of sharing a spreadsheet at a location on

a network from where multiple users can work on the spreadsheet at the
same time.
 The steps to share a spreadsheet on a network location are as follows:
1. Open a spreadsheet.
2. Select the Tools → Share Document option from the Menu bar. The Share
Document dialog box appears.
3. Select the Share this spreadsheet with other users check box.
4. Click the OK button. The OpenOffice 4.1.7 message box appears.
5. Click the Yes button to share a spreadsheet.
 Session 3: Sharing a Spreadsheet

The work is saved by adding

[Shared] in the existing file
name on a network location
from where other users can
access the file and work on
it, as shown in the following
figure:
 When you open a shared document, the OpenOffice 4.1.7 message box
appears displaying that the spreadsheet is in the shared mode, which means
that multiple users can access and edit the spreadsheet simultaneously.
 In the shared spreadsheet, various features are disabled, such as compare
spreadsheet, move or delete sheets, insert charts, insert objects, movie and
sound, protect spreadsheet, etc.
 Saving a Shared Spreadsheet

 Various cases may occur during saving of a shared spreadsheet. These cases
are as follows:
 When the shared spreadsheet was not changed and saved by the other
user since you opened it, the spreadsheet is saved.
 When the shared spreadsheet was changed and saved by the other user
since you opened it, the following two cases may occur:
 If the modification done by the other user does not conflict with the
modification done by you, then the document is saved and the
modification done by the other user is displayed in the red colour
border.
 If the modification done by the other user conflicts with the
modification done by you, then the Resolve Conflicts dialog box
appears. This dialog box allows you to decide to keep your own
modification or the modification of the other user for each conflict.
After resolving all conflicts, the spreadsheet is saved.
 Saving a Shared Spreadsheet

 When the other user saves the shared spreadsheet and resolves conflicts,
then you see a message that the shared spreadsheet file is locked due to a
merging process. Now, you have to cancel the Save command and retry to
save the spreadsheet later.
 After saving a shared spreadsheet, the spreadsheet displays the latest version
of modification.
 Session 4: Reviewing Spreadsheet

 Sometimes, many persons review a spreadsheet before it is marked as final or

submitted for printing.
 While reviewing the spreadsheet, every reviewer can give his/her suggestions
in the form of comments or can directly change the content.
 After reviewing the document, a final draft of the spreadsheet is prepared,
incorporating all the suggestions and changes in it, if there are any.
 Preparing a Spreadsheet for Review

 Before sending a spreadsheet to someone for its review or editing, you may
need to protect the record.
 This is to prevent the possibility of the person who edits or reviews the
spreadsheet to change the original data of the spreadsheet.
 Any person can open the protected spreadsheet for disabling the protection as
well as accepting or rejecting changes by entering the correct password.
 The steps to prepare a spreadsheet for review are as follows:
1. Open the spreadsheet that you want to prepare for review.
2. Select the Edit → Changes → Record option from the Menu bar to record
the modification.
3. Select the Edit → Changes → Protect Records option from the Menu bar
to protect the record. The Protect Records dialog box appears.
4. Type a password in the Password text box.
5. Type the password in the Confirm text box, which you typed in the
Password text box.
6. Click the OK button.
 Recording Changes

 OpenOffice calc allows you to record the changes, such as addition and
deleting in the spreadsheet done by an editor or reviewer.
 The change in the spreadsheet is displayed in the red colour box.
 The steps to record the changes in the spreadsheet are as follows:
1. Open the spreadsheet that you want to prepare for review.
2. Select the Edit → Changes → Record option from the Menu bar to record
the modification.
3. Edit the spreadsheet as per your requirement.
The red colour border appears with a dot in the upper left-hand corner of
cell that indicates the cell is edited by reviewer or editor, as shown in the
following figure:
 Adding, Editing and Formatting Comments

 Comments are used by the editor, author, or reviewer for exchanging their
thoughts as well as for giving suggestions.
 In OpenOffice Calc, comments are well-organized and easy to read.
 In OpenOffice Calc, the cell that contains the comment appears with the red
colour dot in the upper right-hand corner.
 In OpenOffice Calc, you can add, edit, format, or delete comments.
 Adding Comments
 The steps to add comment in the spreadsheet are as follows:
1. Select the cell in the spreadsheet in which you want to add comment.
2. Select the Insert → Comment option from the Menu bar to add comment.
The box appears connected with the selected cell.
3. Type the desired comment in the box.
4. Click outside the box to close it. Now, a red dot appears at the upper right-
corner of the cell indicating the presence of comment in the cell.

Deleting Comments
 The steps to delete a comment are as follows:
1. Right-click the cell whose comment you want to delete. A context menu
appears.
2. Select the Delete Comment option from the context menu to delete the
comment. The comment from the selected cell gets deleted.
 Editing Comments

 The steps to edit comments are as follows:

1. Right-click the cell that contains the comment. A context menu appears.
2. Select the Shows Comment option from the context menu to show the
comment. The box appears that contains the comment.
3. Double-click the box to edit the comment. The cursor changes to the usual
blinking text-entry cursor.
4. Edit the comment according to your requirement.
5. Click outside the box after editing the comment.
 Formatting the Comment

 OpenOffice Calc allows you to format the comment by changing the

background colour, border, line style and width of the border, etc.
 The steps to format the comment are as follows:
1. Right-click the cell that contains the comment. A context menu appears.
2. Select the Shows Comment option from the context menu to show the
comment. The box appears that contains the comment.
3. Select the box that contains the comment. The option of the formatting
toolbar changes to format the comment.
4. Format the comment according to your requirement with the help of the
options available on the Formatting toolbar.
5. Click outside the box after completing the formatting of the comment.
 Finding Comments Using Navigator

 Locating cells containing comments in a spreadsheet can be really a difficult

task.
 OpenOffice Calc provides you the facility to find comments in a spreadsheet
with the help of a navigator.
 The steps to find comments using a navigator are as follows:
1. Open the spreadsheet that contains comments.
2. Select the View → Navigator option from the Menu bar to open the
Navigator dialog box. The Navigator dialog box opens.
3. Click the + (expand) button beside the Comments option. The list of all
comments displays in the Navigator dialog box
4. Double-click on the comment which you want to see in the spreadsheet.
 View, Accept and Reject Changes

 OpenOffice Calc allows you to view the changes done by a reviewer or editor.
 Calc gives you the facility to view the changes based on different filters, such
as based on specific reviewer or editor, specific cell range, specific date and
time, etc.
 OpenOffice Calc provides you the option to accept or reject the modification
done in a spreadsheet by a reviewer or editor.
 The best way to handle these modifications is to go through the document,
reject the changes that are irrelevant, and complete reviewing by accepting all
the remaining changes.
 Viewing Changes

 The steps to view changes in the spreadsheet are as follows:

1. Open the spreadsheet whose changes you want to view.
2. Select the Edit → Changes → Show option from the Menu bar.
The Shows Changes dialog box appears containing various options for
filtering changes. The description of these changes is as follows:
 Date: Displays the changes within a particular time frame
 Author: Displays changes done by a specific author
 Range: Displays changes made in a particular cell range
 Comment: Displays changes made in a cell that contains a comment
and matches the search criteria
 Show accepted changes: Displays changes that you accept
 Show rejected changes: Display the changes that you reject.
 Accept or Reject Changes

 The steps to accept or reject changes in a spreadsheet are as follows:

1. Open the spreadsheet in which you want to accept or reject the changes
made by reviewer, editor, or other author.
2. Select the Edit → Changes → Accept or Reject option from the Menu bar.
The Accept or Reject Changes dialog box appears with the list of changes.
3. Select the changes that you want to accept or reject.
4. Click the Accept button to accept the changes or the Reject button to
reject the changes.
You can click the Accept All button to accept all changes and the Reject
All button to reject the changes in the spreadsheet.
 Merging Spreadsheets

 Merging different versions of a spreadsheet helps you combine the different

versions of the spreadsheet along with all the changes of the two documents.
 Merging of spreadsheets is done to identify and compare all the changes of
both the documents that are merged.
 Now, perform the following steps to merge spreadsheets:
1. Open the spreadsheet in which you want to merge the other spreadsheet.
2. Select the Edit → Changes → Merge Document option from the Menu
bar. The Insert dialog box appears.
3. Select the spreadsheet in the Insert dialog box that you want to merge.
4. Click the Open button.
After completing the merging process of spreadsheets, the Accept or
Reject Changes dialog box appears. This dialog box shows the
modifications or changes done by different authors or reviewers.
5. Repeat steps 2 to 4 for merging more spreadsheets.
 Comparing Spreadsheet

 Comparing different versions of spreadsheets helps in analyzing certain

differences between two spreadsheets, for example, insertion, deletion, and
changes in cells.
 The steps to compare spreadsheets are as follows:
1. Open the spreadsheet that you want to compare with another
spreadsheet.
2. Select the Edit → Compare Document option from the Menu bar. The
Insert dialog box appears.
3. Select the spreadsheet in the Insert dialog box that you want to compare.
4. Click the Open button.
After completing the process of comparing spreadsheets, the Accept or
Reject Changes dialog box appears. This dialog box shows the inserted,
deleted and changed data in the spreadsheet.
 Session 5: Macros in a Spreadsheet

 A macro can be defined as a set of commands or actions that is recorded and

then executed in a spreadsheet when required.
 Macro provides the facility to reuse the recorded action in a spreadsheet
several times when required, thereby making your work easier.
 By using macros, you can reduce the wastage of time in performing a repeated
task.
 Using the Macro Recorder

 The steps to use the macro recorder in OpenOffice Calc are as follows:
1. Open a new or existing spreadsheet.
2. Enter data in a spreadsheet.
3. Select the cell A5, as shown in the given figure.
4. Click the Copy button on the Standard toolbar.
5. Select the cell range. In our case, we have selected the cell range A1:D5.
6. Select the Tools → Macros → Record Macro option from the Menu bar.
The Record Macro dialog box appears with the Stop Recording button.
7. Select the Edit → Paste Special option from the Menu bar to open the
Paste Special dialog box. The Paste Special dialog box appears.
8. Select the desired check box in the Selection section. In our case, we have
selected the Paste all check box.
 Using the Macro Recorder

9. Select the desired radio button in the Operations section. In our case, we
have selected the Divide radio button.
10. Click the OK button. Now, the value of the selected cell range is divided by
8.
11. Click the Stop Recording button in the
Record Macro dialog box to stop recording
of macro, as shown in the given figure.
The OpenOffice Basic Macros dialog box
appears.

12. Click the + (expand) button of the current document.

13. Select the Standard library under the Untitled 1 spreadsheet.
14. Click the New Module button to create a module in the Standard library.
The New Module dialog box appears.
 Using the Macro Recorder

15. Type the name of the module in the Name text box.
16. Click the OK button. Now the module is created and displayed under the
Standard library.
17. Type the name of the module in the Macro name text box in the
OpenOffice Basic Macros dialog box.
18. Click the Save button to save the macro.
Now, the macro is saved with the specified name.
 Creating Your Own Macros

 The steps to create a custom macro are as follows:

1. Open a new or existing spreadsheet.
2. Select the Tools → Macros → Organize Macros → OpenOffice Basic
option from the Menu bar. The OpenOffice Basic Macros dialog box
appears.
3. Click the Organizer button to open the OpenOffice Basic Macro Organizer
dialog box. The OpenOffice Basic Macro Organizer dialog box appears.
4. Click the Libraries tab in the OpenOffice Basic Macro Organizer dialog box.
5. Select the spreadsheet in the location drop-down list.
6. Click the New button to create new library. The New Library dialog box
appears.
7. Type the name of the library in the Name text box.
 Creating Your Own Macros

8. Click the OK button. The Library list box of the OpenOffice Basic Macro
Organizer dialog box shows the newly created library.
9. Click the Edit button to edit the library. The OpenOffice Basic window
opens that shows the macro with name Main in the Module 1 module
under the created library.
10. Modify the code of the macro according to your requirement, as shown in
the following figure:

11. Click the Save button on the Standard toolbar to save the macro.
12. Click the Close button to close the OpenOffice Basic window.
 Using a Macro as a Function

 The steps to call a macro as a function are as follows:

1. Open a new or existing spreadsheet that contains a macro.
2. Select the cell in which you want to call the macro.
3. Type =Addition() in the selected cell.
4. Press the Enter key.
The result of the macro is displayed in the selected cell.
 Passing Arguments to a Macro

 The steps to create a macro with argument are as follows:

1. Open a new spreadsheet.
2. Save the spreadsheet with the name Simple_Interest.
3. Select the Tools → Macros → Organize Macros → OpenOffice Basic
option from the Menu bar. The OpenOffice Basic Macros dialog box
appears.
4. Click the Organizer button to open the OpenOffice Basic Macro Organizer
dialog box. The OpenOffice Basic Macro Organizer dialog box appears.
5. Click the Libraries tab in the OpenOffice Basic Macro Organizer dialog
box.
6. Select the spreadsheet in the location drop-down list.
7. Click the New button to create a new library. The New Library dialog box
appears.
 Passing Arguments to a Macro

8. Type the name of the library in the Name text box.

9. Click the OK button. The Library list box of the OpenOffice Basic Macro
Organizer dialog box shows the newly created library.
10. Click the Edit button to edit the library. The OpenOffice Basic window
opens that shows the macro with the name Main in the Module 1 module
under the created library.
11. Modify the code of
the macro to calculate
the simple interest, as
shown in the given
figure.
 Passing Arguments to a Macro

12. Click the Save button on the Standard toolbar to save the macro.
13. Click the Close button to close the OpenOffice Basic window.
 Now, call the macro in the spreadsheet by passing the value of the arguments.
 The steps to call the macro in the spreadsheet by passing the value are as
follows:
1. Open a new or existing spreadsheet that contains a macro.
2. Select the cell in which you want to call the macro.
3. Type the =Simple_Interest(B1;B2;B3) in the selected cell.
4. Press the Enter key.
5. The result of the macro is displayed in the selected cell.
Thank You