Calc Guide

14

Chapter
Setting up andCustomizing Calc
This PDF is designed to be read onscreen, two pages at atime. If you want to print a copy, your PDF viewer shouldhave an option for printing two pages on one sheet ofpaper, but you may need to start with page 2 to get it toprint facing pages correctly. (Print this cover page
separately.)CopyrightThis document is Copyright © 2009–2010 by its contributors as listed
in the section titled Authors. You may distribute it and/or modify it
under the terms of either the GNU General Public License, version 3 orlater, or the Creative Commons Attribution License, version 3.0 or
later.
All trademarks within this guide belong to their legitimate owners.

Authors
Peter Hillier-Brook
Stephen Buck
Regina HenschelJean Hollis Weber
Michele Zarri

Feedback
Please direct any comments or suggestions about this document to:authors@documentation.openoffice.org

Acknowledgments
Much of this chapter is taken from material first published in Getting
Started with OpenOffice.org 3.

Publication date and software version
Published 8 June 2010. Based on OpenOffice.org 3.2.

You can download
an editable version of this document fromhttp://oooauthors.org/english/userguide3/published/

Contents
Copyright............................................................................................... 2
Introduction........................................................................................... 5
Choosing options that affect all of OOo.................................................5
User Data options...............................................................................6
Print options....................................................................................... 6
Color options....................................................................................... 6
Security options.................................................................................. 8
Appearance options..........................................................................10
Choosing options for loading and saving documents...........................11
General Load/Save options...............................................................11
VBA Properties Load/Save options....................................................12
Microsoft Office Load/Save options..................................................13
HTML compatibility Load/Save options............................................14
Choosing options for Calc....................................................................15
General options for Calc...................................................................15
Metrics section..............................................................................15
Updating section............................................................................ 16
Input settings section....................................................................16
View options for Calc........................................................................17
Visual aids section.........................................................................17
Display section............................................................................... 18
Objects section..............................................................................19
Window section.............................................................................. 19
Zoom section.................................................................................19
Calculate options..............................................................................19
Iterative references section...........................................................20
Date section................................................................................... 20
Other options................................................................................. 21
Sort Lists options.............................................................................. 22
Changes options................................................................................ 23
Grid options......................................................................................23
Setting up and Customizing Calc

3

Grid section...................................................................................24
Resolution section.......................................................................... 24
Print options.....................................................................................24
Controlling Calc’s AutoCorrect functions.............................................24
Customizing the user interface............................................................25
Customizing the menu font...............................................................25
Customizing menu content...............................................................25
Creating a new menu..................................................................... 27
Modifying existing menus..............................................................27
Adding a command to a menu.......................................................28
Modifying menu entries.................................................................29
Customizing toolbars........................................................................29
Creating a new toolbar..................................................................30
Adding a command to a toolbar.....................................................31
Choosing icons for toolbar commands...........................................31
Customizing keyboard shortcuts.......................................................31
Example: Assigning styles to shortcut keys...................................33
Saving changes to a file.................................................................34
Loading a saved keyboard configuration.......................................34
Resetting the shortcut keys...........................................................34
Running macros from key combinations........................................35
Adding functionality with extensions...................................................35
Installing extensions.........................................................................35
Using extensions............................................................................... 36

4

Setting up and Customizing Calc

Introduction
This chapter describes some common customizations that you may
wish to do. In addition to selecting options from those provided, you
can customize menus, toolbars, and keyboard shortcuts, add newmenus and toolbars, and assign macros to events. However, you cannot
customize context (right-click) menus.
Other customizations are made easy by extensions that you can installfrom the OpenOffice.org website or from other providers.

Note

Customizations to menus and toolbars can be saved in a template.
To do so, first save them in a document and then save the document
as a template as described in Chapter 4 (Using Styles andTemplates in Calc).

Choosing options that affect all of OOo
This section covers some of the settings that apply to all thecomponents of OpenOffice.org. Other general options are discussed in
Chapter 2 (Setting Up OpenOffice.org) in the Getting Started guide.
1) Choose Tools > Options. The list on the left-hand side varies
depending on which component of OOo is open.
2) Click the + sign to the left of OpenOffice.org on the left-handside. A list of subsections drops down.

Note

The Back button has the same effect on all pages of the Options
dialog. It resets the options to the values that were in placewhen you opened OpenOffice.org.

Choosing options that affect all of OOo

5

User Data options
Calc uses the first and last name stored in the OpenOffice.org – User
Data page to fill in the Created and Modified fields in the document
properties, and the optional Author field often used in the footer of a
printed spreadsheet. Fill in the form on this page.

Print options
Set the print options to suit your default printer and your mostcommon printing method. You can change these settings at any time,either through this dialog or during the printing process (by clicking
the Options button on the Print dialog).
In the Options dialog, click OpenOffice.org > Print.
See Chapter 6 (Printing, Exporting, and E-mailing) for more about theoptions on this page.

Color options
On the OpenOffice.org – Colors page, you can specify colors to use in
OOo documents. You can select a color from a color table, edit anexisting color, and define new colors. These colors are stored in yourcolor palette and are then available in all components of OOo.

Figure 1: Defining colors to use in color palettes in OOo

6

Setting up and Customizing Calc

To modify a color:
1) Select the color to modify from the list or the color table.
2) Enter the new values that define the color. If necessary, change
the settings from RGB (Red, Green, Blue) to CMYK (Cyan,
Magenta, Yellow, Black) or vice versa. The changed color appears
in the lower of the two color preview boxes at the top.
3) Modify the Name as required.
4) Click the Modify button. The newly defined color is now listed in
the Color table.
Alternatively, click the Edit button to open the Color dialog, shown in
Figure 2. Here you can select a color from one of the color windows in
the upper area, or you can enter values in the lower area using yourchoice of RGB, CMYK, or HSB (Hue, Saturation and Brightness) values.
The upper right color window is linked directly with the color input
fields in the lower area; as you choose a color in the upper window, thenumbers change accordingly. The two color fields at the lower rightshow the value of the selected color on the left and the currently set
value from the color value fields on the right.
Modify the color components as required and click OK to exit the
dialog. The newly defined color now appears in the lower of the color
preview boxes shown in Figure 1. Type a name for this color in theName box, then click the Add button. A small box showing the newcolor is added to the Color table.

Figure 2: Editing colors
Choosing options that affect all of OOo

7

Another way to define or alter colors is through the Colors page of the
Area dialog, where you can also save and load palettes, a feature thatis not possible here. In Calc, draw a temporary draw object and use thecontext menu of this object to open the Area dialog. If you load apalette in one component of OOo, it is only active in that component;
the other components keep their own palettes.

Security options
Use the OpenOffice.org – Security page to choose security options forsaving documents and for opening documents that contain macros.

Figure 3: Choosing security options for opening and saving
documents
Security options and warnings
If you record changes, save multiple versions, or include hidden
information or notes in your documents, and you do not want someof the recipients to see that information, you can set warnings toremind you to remove this information, or you can have OOo remove
some information automatically. Note that (unless removed) much ofthis information is retained in a file whether the file is in
OpenOffice.org’s default OpenDocument format, or has been saved
to other formats, including PDF.

8

Setting up and Customizing Calc

Click the Options button to open a separate dialog with specificchoices (Figure 4).
Remove personal information on saving. Select this option toalways remove user data from the file properties when saving thefile. To manually remove personal information from specificdocuments, deselect this option and then use the Delete button
under File > Properties > General.
Ctrl-click required to follow hyperlinks. In older versions of
OOo, clicking on a hyperlink in a document opened the linked
document. Now you can choose whether to keep this behavior (byunchecking this box). Many people find creation and editing of
documents easier when accidental clicks on links do not activate thelinks.
The other options on this dialog should be self-explanatory.

Figure 4: Security options and warnings dialog
Passwords for web connections
You can enter a master password to enable easy access to sites that
require a user name and password.
Persistently save passwords protected by a master password.
If enabled, OOo securely stores all passwords that you use toaccess files from web servers. You can retrieve the passwords fromthe list after you enter the master password.

Choosing options that affect all of OOo

9Macro security
Click the Macro Security button to open the Macro Security dialog(not shown here), where you can adjust the security level for
executing macros and specify trusted sources.
File sharing options for this document
Select the Open this document in read-only mode option to
restrict this document to be opened in read-only mode only. This
option protects the document against accidental changes. It is still
possible to edit a copy of the document and save that copy with thesame name as the original.
Select the Record changes option to enable recording changes.
This is the same as Edit > Changes > Record. To allow other usersof this document to apply changes, but prevent them from disablingchange recording, click the Protect button and enter a password.

Appearance options
On the OpenOffice.org – Appearance page, you can specify which items
are visible and the colors used to display various items.

Figure 5: Changing the color of grid points in Calc
Scroll down in the page until you find Spreadsheet. To change thedefault color for grid points, click the down-arrow by the color andselect a new color from the pop-up box.
If you wish to save your color changes as a color scheme, click Save,
type a name in the Scheme box; then click OK.
10

Setting up and Customizing Calc

Choosing options for loading and saving
documents
You can set the Load/Save options to suit the way you work. This
chapter describes only a few of the options, those more relevant toworking with Calc. See Chapter 2 (Setting Up OpenOffice.org) in theGetting Started guide for a description of the other options.
If the Options dialog is not already open, click Tools > Options. Clickthe + sign to the left of Load/Save to display the list of load/save
options pages.

Figure 6: Load/Save options

General Load/Save options
Most of the choices on the Load/Save – General page are familiar tousers of other office suites. Those specific to OpenOffice.org are in the
Default file format and ODF settings section.

Figure 7: Choosing Load and Save options
Choosing options for loading and saving documents

11

ODF format version
OpenOffice.org by default saves documents in OpenDocument
Format (ODF) version 1.2. While this allows for improved
functionality, there may be backwards compatibility issues. When afile saved in ODF 1.2 is opened in an earlier version of
OpenOffice.org (using ODF 1.0/1.1), some of the advanced features
may be lost. Two notable examples are the functions ADDRESS and
INDIRECT and the formatting of numbered lists. If you plan to share
documents with people who are still using older versions of
OpenOffice.org, it is recommended that you save the document
using ODF version 1.0/1.1.
Size optimization for ODF format
OpenOffice.org documents are XML files. When you select thisoption, OOo writes the XML data without indents and line breaks. If
you want to be able to read the XML files in a text editor in a
structured form, deselect this option.
Document type
If you routinely share documents with users of Microsoft Excel, you
might want to change the Always save as attribute for spreadsheets
to one of the Excel formats.

VBA Properties Load/Save options
On the Load/Save – VBA Properties page, you can choose whether tokeep any macros in Microsoft Office documents that are opened in
OpenOffice.org.

Figure 8: Choosing
Load/Save VBA Properties
12

Setting up and Customizing Calc• If you choose Save original Basic code, the macros will notwork in OpenOffice.org but are retained if you save the file into
Microsoft Office format.
• If you choose Load Basic code to edit, the changed code issaved in an OpenOffice.org document but is not retained if you
save into an Microsoft Office format.
• If you are importing a Microsoft Excel file containing VBA code,
you can select the option Executable code. Whereas normallythe code is preserved but rendered inactive (if you inspect it with
the StarBasic IDE you will notice that it is all commented), withthis option the code is ready to be executed.

Microsoft Office Load/Save options
On the Load/Save – Microsoft Office page, you can choose what to dowhen importing and exporting Microsoft Office OLE objects (linked orembedded objects or documents such as spreadsheets or equations).
Select the [L] options to convert Microsoft OLE objects into thecorresponding OpenOffice.org OLE objects when a Microsoft document
is loaded into OOo (mnemonic: “L” for “load”).
Select the [S] options to convert OpenOffice.org OLE objects into thecorresponding Microsoft OLE objects when a document is saved in a
Microsoft format (mnemonic: “S” for “save”).

Figure 9: Choosing Load/Save Microsoft Office options

Choosing options for loading and saving documents

13

HTML compatibility Load/Save options
Choices made on the Load/Save – HTML Compatibility page
(Figure 10) affect HTML pages imported into OpenOffice.org and thoseexported from OOo. See HTML documents; importing/exporting in theHelp for more information.
The main items of interest for Calc users are in the Export section:
OpenOffice.org Basic and Display warning.
Export - OpenOffice.org Basic
Select this option to include OOo Basic macros (scripts) whenexporting to HTML format. You must activate this option before you
create the OpenOffice.org Basic macro; otherwise the script will not
be inserted. OpenOffice.org Basic macros must be located in theheader of the HTML document. Once you have created the macro in
the OpenOffice.org Basic IDE, it appears in the source text of theHTML document in the header.
If you want the macro to run automatically when the HTML
document is opened, choose Tools > Customize > Events. See
Chapter 12 (Calc Macros) for more information.
Export - Display warning
When the OpenOffice.org Basic option (see above) is not selected,
the Display warning option becomes available. If the Display
warning option is selected, then when exporting to HTML awarning is shown that OpenOffice.org Basic macros will be lost.

Figure 10: Choosing HTML compatibility options

14

Setting up and Customizing Calc

Choosing options for Calc
In the Options dialog, click the + sign to the left of OpenOffice.org
Calc on the left-hand side. A list of subsections drops down.

General options for Calc
In the Options dialog, choose OpenOffice.org Calc > General.

Figure 11: Selecting general options for Calc

Metrics section
Choose the unit of measurement used in spreadsheets and the default
tab stops distance.

Choosing options for Calc

15

Updating section
Choose whether to update links when opening a document always, only
on request, or never. Regardless of this setting, you can manually
update links at any time. You might want to avoid updating links when
opening documents if they often contain many charts or linked
graphics that would slow down loading.

Input settings section
Press Enter to move selection
Specifies that pressing Enter moves the cursor to another cell. If
this option is selected, you can also choose the direction the cursor
moves: up, down, left, or right. If this option is not selected,pressing Enter completes data entry for a cell but does not move thecursor.
Press Enter to switch to edit mode
Specifies that pressing Enter puts the selected cell into edit mode.
Expand formattingSpecifies whether to automatically apply the formatting attributes ofan inserted cell to empty adjacent cells. If, for example, an inserted
cell has the bold attribute, this attribute will also apply to empty
adjacent cells. However, cells that already have a special format will
not be modified by this function. To see the affected range, press
Ctrl+* (multiplication sign on the number pad). The format will also
apply to all new values inserted within this range.
Expand references when new columns/rows are inserted
Specifies whether to expand references when inserting columns orrows adjacent to the reference range. This is only possible if thereference range, where the column or row is inserted, originallyspanned at least two cells in the desired direction.
Example: If the range A1:B1 is referenced in a formula and you
insert a new column after column B, the reference is expanded to
A1:C1. If the range A1:B1 is referenced and a new row is inserted
under row 1, the reference is not expanded, since there is only asingle cell in the vertical direction.
If you insert rows or columns in the middle of a reference area, thereference is always expanded.

16

Setting up and Customizing Calc

Highlight selection in column/row headings
Specifies whether to highlight column and row headers in theselected columns or rows.
Use printer metrics for text formattingSpecifies that printer metrics are applied for printing and also forformatting the display on the screen. If this option is not selected, aprinter-independent layout is used for screen display and printing.
Show overwrite warning when pasting data
Specifies that, when you paste cells from the clipboard to a cellrange that is not empty, a warning appears.

View options for Calc
In the Options dialog, choose OpenOffice.org Calc > View.

Figure 12: Selecting view options for Calc

Visual aids section
Specifies which lines are displayed.
Grid lines
Specifies whether to display grid lines between the cells whenviewed onscreen. If this option is selected, you can also specify theChoosing options for Calc

17

color for the grid lines in the current document. The color choice
overrides the selection made in Tools > Options >OpenOffice.org > Appearance > Spreadsheet > Grid lines.
For printing, choose Format > Page > Sheet and mark the Grid
option.
Page breaks
Specifies whether to view the page breaks within a defined printarea.
Guides while moving
Specifies whether to view guides when moving drawings, frames,graphics and other objects. These guides help you align objects.
Simple handles
Specifies whether to display the handles (the eight points on aselection box) as simple squares without a 3D effect.
Large handles
Specifies that larger than normal handles (the eight points on aselection box) are displayed.

Display section
Select various options for the screen display.
FormulasSpecifies whether to show formulas instead of results in the cells.
Zero values
Specifies whether to show numbers with the value of 0.
Comment indicator
Specifies that a small rectangle is shown in the top right corner ofthe cell when a comment exists for that cell. The text of thecomment is shown when you hover the pointer over the cell, if tips
are enabled under Tools > Options > OpenOffice.org > General.
To display a comment permanently, right-click on the cell and selectShow comment from the pop-up menu.
Value highlighting
Select this option to highlight all values in the sheet. Text is
highlighted in black; numbers in blue; and formulas, logical values,
dates, and so on, in green.

18

Setting up and Customizing Calc

When this command is active, any colors assigned in the document
are not displayed.
AnchorSpecifies whether the anchor icon is displayed when an inserted
object, such as a graphic, is selected.
Text overflow
If a cell contains text that is wider than the width of the cell, the text
is displayed over empty neighboring cells in the same row. If there isno empty neighboring cell, a small triangle at the cell borderindicates that the text continues.
Show references in color
Specifies that each reference is highlighted in color in the formula.
The cell range is also enclosed by a colored border as soon as thecell containing the reference is selected for editing.

Objects section
Specifies whether to display or hide objects in three object groups:
objects and graphics; charts; and drawing objects.

Window section
Specifies whether some elements are visible onscreen: column/row
headers, horizontal scrollbar, vertical scrollbar, sheet tabs, and outline
symbols.
If the Sheet tabs option is not selected, you can only switch between
the sheets by using the Navigator.
Note that there is a slider between the horizontal scrollbar and the
sheet tabs that may be set to one end.

Zoom section
Select the Synchronize sheets option to apply any selected zoom
factor to all sheets in the spreadsheet. If this option is not selected,
separate zoom factors can be applied to individual sheets.

Calculate options
In the Options dialog, choose OpenOffice.org Calc > Calculate.
Use this page to define the calculation settings for spreadsheets.

Choosing options for Calc

19

Figure 13: Calc calculation options

Iterative references section
Iterative references are formulas that are continuously repeated until
the problem is solved. In this section you can choose the number ofapproximation steps carried out during iterative calculations and thedegree of precision of the answer.
Iterations
Select this option to enable iterations. If this options is not selected,
an iterative reference causes an error message.
Steps
Sets the maximum number of iteration steps.
Minimum Change
Specifies the difference between two consecutive iteration step
results. If the result of the iteration is lower than the minimumchange value, then the iteration will stop.

Date section
Select the start date for the internal conversion from days to numbers.
12/30/1899 (default)
Sets 12/30/1899 as day zero.

20

Setting up and Customizing Calc

01/01/1900 (StarCalc 1.0)
Sets 1/1/1900 as day zero. Use this setting for StarCalc 1.0
spreadsheets containing date entries.
01/01/1904
Sets 1/1/1904 as day zero. Use this setting for spreadsheets that areimported in a foreign format.

Other options
Specify a variety of options relevant to spreadsheet calculation.
Case sensitive
Specifies whether to distinguish between upper and lower case intexts when comparing cell contents. The EXACT text function isalways case-sensitive, independent of the settings in this dialog.
Decimal places
Defines the number of decimals to be displayed for numbers with
the Standard number format. The numbers are displayed as rounded
numbers, but are not saved as rounded numbers.
Precision as shown
Specifies whether to make calculations using the rounded values
displayed in the sheet. Charts will be shown with the displayed
values. If this option is not selected, the displayed numbers arerounded, but they are calculated internally using the non-rounded
number.
Search criteria = and <> must apply to whole cells
Specifies that the search criteria you set for the Calc databasefunctions must match the whole cell exactly. When this option is
selected, Calc behaves exactly like MS Excel when searching cells inthe database functions.
This search:

Has this result:win

Finds win, but not win95, os2win, or upwind

win.*

Finds win and win95, but not os2win or upwind.*win

Finds win and os2win, but not win95 or upwind.*win.*

Finds win, win95, os2win, and upwind

If this option is not selected, the win search pattern acts like .*win.*—the search pattern can be at any position within the cell whensearching with the Calc database functions.

Choosing options for Calc

21

Enable regular expressions in formulas
Specifies that regular expressions are enabled when searching andalso for character string comparisons. This relates to the databasefunctions, and to VLOOKUP, HLOOKUP and SEARCH.
Automatically find column and row labels
Specifies that you can use the text in any cell as a label for thecolumn below the text or the row to the right of the text. The text
must consist of at least one word and must not contain any
operators.
Example: Cell E5 contains the text Europe. Below, in cell E6, is thevalue 100 and in cell E7 the value 200. If the Automatically findcolumn and row labels option is selected, you can write thefollowing formula in cell A1: =SUM(Europe).

Sort Lists options
In the Options dialog, choose OpenOffice.org Calc > Sort Lists. Sort
lists are used for more than sorting, for example filling a series of cells
during data entry. In addition to the supplied lists, you can define andedit your own lists, as described in “Defining a fill series” in Chapter 2(Entering, Editing, and Formatting Data).

Figure 14: Defining sorting lists in Calc

22

Setting up and Customizing Calc

Changes options
In the Options dialog, choose OpenOffice.org Calc > Changes.

Figure 15: Calc options for highlighting changes
On this page you can specify options for highlighting recorded changes
in spreadsheets. You can assign specific colors for insertions, deletions,and other changes, or you can let Calc assign colors based on theauthor of the change; in the latter case, one color will apply to allchanges made by that author.

Grid options
The Grid page defines the grid settings for spreadsheets. Using a grid
helps you determine the exact position of any charts or other objects
you may add to a spreadsheet. You can also set this grid in line withthe snap grid.
If you have activated the snap grid but wish to move or create
individual objects without snap positions, you can press the Ctrl key todeactivate the snap grid for as long as needed.
In the Options dialog, choose OpenOffice.org Calc > Grid.

Choosing options for Calc

23

Figure 16: Calc grid options

Grid section
Snap to grid activates the snap function.
Visible grid displays grid points on the screen. These points are notprinted.

Resolution section
Here you can set the unit of distance for the spacing between
horizontal and vertical grid points and subdivisions (intermediate
points) of the grid.
Synchronize axes changes the current grid settings symmetrically.

Print options
In the Options dialog, choose OpenOffice.org Calc > Print. See
Chapter 6 (Printing, Exporting, and E-mailing) for more about theoptions on this page.

Controlling Calc’s AutoCorrect functions
Some people find some or all of the items in OOo’s AutoCorrectfunction annoying because they change what you type when you do notwant it changed. Many people find some of the AutoCorrect functionsquite helpful; if you do, then select the relevant options. But if you findunexplained changes appearing in your document, this is a good place
to look to find the cause.
To open the AutoCorrect dialog, click Tools > AutoCorrect Options.
(You need to have a document open for this menu item to appear.)

24

Setting up and Customizing Calc

In Calc, this dialog has four tabs, as shown below. Options aredescribed in the Help; many will be familiar to users of other officesuites.

Figure 17: The AutoCorrect dialog in Calc

Customizing the user interface
Customizing the menu font
If you want to change the menu font from that supplied by OOo to thesystem font for your operating system, do this:1) Choose Tools > Options > OpenOffice.org > View.
2) Check Use system font for user interface and click OK.

Customizing menu content
In addition to changing the menu font, you can add and rearrange
items on the menu bar, add items to menus, and make other changes.
To customize menus:
1) Choose Tools > Customize.
2) On the Customize dialog, pick the Menus page (Figure 18).
3) In the Save In drop-down list, choose whether to save thischanged menu for Calc or for a selected document.
4) In the section OpenOffice.org Calc Menus, select from the
Menu drop-down list the menu that you want to customize. The
list includes all the main menus as well as sub-menus, that is
Customizing the user interface

25

menus that are contained under another menu. For example, in
addition to File, Edit, View, and so on, there is File | Send andFile |Templates. The commands available for the selected menu
are shown in the central part of the dialog.
5) To customize the selected menu, click on the Menu or Modify
buttons. You can also add commands to a menu by clicking on the
Add button. These actions are described in the following sections.
Use the up and down arrows next to the Entries list to move theselected menu item to a different position.
6) When you have finished making all your changes, click OK tosave them.

Figure 18: The Menus page of the Customize dialog

26

Setting up and Customizing Calc

Creating a new menu
In the Customize dialog, click New to display the dialog shown in
Figure 19.
1) Type a name for your new menu in the Menu name box.
2) Use the up and down arrow buttons to move the new menu into
the required position on the menu bar. Click OK to save.
The new menu now appears on the list of menus in the Customize
dialog. (It will appear on the menu bar itself after you save yourcustomizations.)
After creating a new menu, you need to add some commands to it, as
described in “Adding a command to a menu” on page 28.

Figure 19: Adding a new menu

Modifying existing menus
To modify an existing menu, select it in the Menu list and click the
Menu button to drop down a list of modifications: Move, Rename,
Delete. Not all of these modifications can be applied to all the entries
in the Menu list. For example, Rename and Delete are not available
for the supplied menus.
To move a menu (such as File), choose Menu > Move. A dialog similar
to the one shown in Figure 19 (but without the Menu name box)
opens. Use the up and down arrow buttons to move the menu into therequired position.

Customizing the user interface27

To move submenus (such as File | Send), select the main menu (File) in
the Menu list and then, in the Menu Content section of the dialog,
select the submenu (Send) in the Entries list and use the arrow keys tomove it up or down in the sequence. Submenus are easily identified in
the Entries list by a small black triangle on the right hand side of thename.
In addition to renaming, you can specify a keyboard shortcut thatallows you to select a menu command when you press Alt+ an
underlined letter in a menu command.
1) Select a menu or menu entry.
2) Click the Menu button and select Rename.
3) Add a tilde (~) in front of the letter that you want to use as anaccelerator. For example, to select the Save All command bypressing Alt+V, enter Sa~ve All.

Adding a command to a menu
You can add commands to the supplied menus and to menus you havecreated. On the Customize dialog, select the menu in the Menu list andclick the Add button in the Menu Content section of the dialog.

Figure 20: Adding a command to a menu
28

Setting up and Customizing Calc

On the Add Commands dialog, select a category and then thecommand, and click Add. The dialog remains open, so you can selectseveral commands. When you have finished adding commands, click
Close. Back on the Customize dialog, you can use the up and downarrow buttons to arrange the commands in your preferred sequence.

Modifying menu entries
In addition to changing the sequence of entries on a menu or submenu,
you can add submenus, rename or delete the entries, and add groupseparators.
To begin, select the menu or submenu in the Menu list near the top of
the Customize page, then select the entry in the Entries list under
Menu Content. Click the Modify button and choose the required action
from the drop-down list of actions.
Most of the actions should be self-explanatory. Begin a group adds a
separator line after the highlighted entry.

Customizing toolbars
You can customize toolbars in several ways, including choosing whichicons are visible and locking the position of a docked toolbar, as
described in Chapter 1 (Introducing OpenOffice.org) of the Getting
Started guide. This section describes how to create new toolbars andadd other icons (commands) to the list of those available on a toolbar.
To get to the toolbar customization dialog, do any of the following:• On the toolbar, click the arrow at the end of the toolbar andchoose Customize Toolbar.
• Choose View > Toolbars > Customize from the menu bar.
• Choose Tools > Customize from the menu bar and pick the
Toolbars page (Figure 21).
To customize toolbars:
1) In the Save In drop-down list, choose whether to save thischanged toolbar for Calc or for a selected document.
2) In the section OpenOffice.org Calc Toolbars, select from the
Toolbar drop-down list the toolbar that you want to customize.
3) You can create a new toolbar by clicking on the New button, or
customize existing toolbars by clicking on the Toolbar or Modify
buttons, and add commands to a toolbar by clicking on the Add
button. These actions are described below.

Customizing the user interface

294) When you have finished making all your changes, click OK tosave them.

Figure 21: The Toolbars page of the Customize dialog

Creating a new toolbar
To create a new toolbar:
1) Choose Tools > Customize > Toolbars from the menu bar.
2) Click New. On the Name dialog, type the new toolbar's name andchoose from the Save In drop-down list where to save thischanged menu: for Calc or for a selected document.

30

Setting up and Customizing Calc

The new toolbar now appears on the list of toolbars in the Customize
dialog. After creating a new toolbar, you need to add some commands
to it, as described below.

Adding a command to a toolbar
If the list of available buttons for a toolbar does not include all thecommands you want on that toolbar, you can add commands. When you
create a new toolbar, you need to add commands to it.
1) On the Toolbars page of the Customize dialog, select the toolbar
in the Toolbar list and click the Add button in the Toolbar Content
section of the dialog.
2) The Add Commands dialog is the same as for adding commands tomenus (Figure 20). Select a category and then the command, andclick Add. The dialog remains open, so you can select several
commands. When you have finished adding commands, click
Close. If you insert an item which does not have an associatedicon, the toolbar will display the full name of the item: the next
section describes how to choose an icon for a toolbar command.
3) Back on the Customize dialog, you can use the up and downarrow buttons to arrange the commands in your preferred
sequence.
4) When you are done making changes, click OK to save.

Choosing icons for toolbar commands
Toolbar buttons usually have icons, not words, on them, but not all of
the commands have associated icons.
To choose an icon for a command, select the command and click
Modify > Change icon. On the Change Icon dialog (Figure 22), scroll
through the available icons, select one, and click OK to assign it to thecommand.
To use a custom icon, create it in a graphics program and import it into
OOo by clicking the Import button on the Change Icon dialog. Custom
icons must be 16 x 16 or 26 x 26 pixels in size and cannot contain morethan 256 colors.

Customizing keyboard shortcuts
In addition to using the built-in keyboard shortcuts (listed in Appendix
A), you can define your own. You can assign shortcuts to standard OOofunctions or your own macros and save them for use with the entire
OpenOffice.org suite or only for Calc.
Customizing the user interface

31

Figure 22: Change Icon dialog

CautionBe careful when reassigning your operating system’s or OOo’s
predefined shortcut keys. Many key assignments are universally
understood shortcuts, such as F1 for Help, and are always
expected to provide certain results. Although you can easily
reset the shortcut key assignments to the OOo defaults,changing some common shortcut keys can cause confusion,
frustration and possible data loss or corruption, especially ifother users share your computer.

To adapt shortcut keys to your needs, use the Customize dialog, as
described below.
1) Select Tools > Customize > Keyboard. The Keyboard page of
the Customize dialog opens.
2) To have the shortcut key assignment available only with Calc,
select Calc in the upper right corner of the page; otherwise selectOpenOffice.org to make it available to every component.
3) Next select the required function from the Category and Function
lists.
4) Now select the desired shortcut keys in the Shortcut keys list andclick the Modify button at the upper right.
5) Click OK to accept the change. Now the chosen shortcut keys will
execute the function chosen in step 3 above whenever they arepressed.

32

Setting up and Customizing Calc

Note

All existing shortcut keys for the currently selected Function arelisted in the Keys selection box. If the Keys list is empty, it
indicates that the chosen key combination is free for use. If it
were not, and you wanted to reassign a shortcut key
combination that is already in use, you must first delete theexisting key.
Shortcut keys that are greyed-out in the listing on the Customize
dialog, such as F1 and F10, are not available for reassignment.

Example: Assigning styles to shortcut keys
You can configure shortcut keys to quickly assign styles in yourdocument.
1) On the Keyboard page of the Customize dialog, choose theshortcut keys you want to assign a style to. In this example, wehave chosen Ctrl+3.

Figure 23: Assigning a cell style to a key combination

Customizing the user interface

332) In the Functions section at the bottom of the dialog, scroll downin the Category list to Styles. Click the + sign to expand the list of
styles.
3) Choose the category of style. (This example uses a cell style, but
you can also choose page styles.) The Function list now displays
the names of the available styles for the selected category. The
example shows OOo’s predefined cell styles.
4) To assign Ctrl+3 to be the shortcut key combination for the
Heading style, select Heading in the Function list, and then click
Modify. Ctrl+3 now appears in the Keys list on the right, andHeading appears next to Ctrl+3 in the Shortcut keys box at thetop.
5) Make any other required changes, and then click OK to savethese settings and close the dialog.

Saving changes to a file
Changes to the shortcut key assignments can be saved in a keyboard
configuration file for use at a later time, thus permitting you to create
and apply different configurations as the need arises. To save keyboard
shortcuts to a file:1) After making your keyboard shortcut assignments, click the Save
button on the Customize dialog.
2) In the Save Keyboard Configuration dialog, select All files fromthe Save as Type list.
3) Next enter a name for the keyboard configuration file in theFile name box, or select an existing file from the list. If you needto, browse to find a file from another location.
4) Click Save. A confirmation dialog appears if you are about tooverwrite an existing file, otherwise there will be no feedback andthe file will be saved.

Loading a saved keyboard configuration
To load a saved keyboard configuration file and replace your existing
configuration, click the Load button near the bottom right of the
Customize dialog, and then select the configuration file from the Load
Keyboard Configuration dialog.

Resetting the shortcut keys
To reset all of the keyboard shortcuts to their default values, click the
Reset button near the bottom right of the Customize dialog. Use this34

Setting up and Customizing Calc

feature with care as no confirmation dialog will be displayed; thedefaults will be set without any further notice or user input.

Running macros from key combinations
You can also define shortcut key combinations that will run macros.
These shortcut keys are strictly user-defined; none are built in. For
more information on macros, see Chapter 12 (Calc Macros).

Adding functionality with extensions
An extension is a package that can be installed into OpenOffice.org to
add new functionality.
Although individual extensions can be found in different places, theofficial OpenOffice.org extension repository is at
http://extensions.services.openoffice.org/. Some extensions are free ofcharge; others are available for a fee. Check the descriptions to see
what licenses and fees apply to the ones that interest you.

Installing extensions
To install an extension, follow these steps:
1) Download an extension and save it anywhere on your computer.
2) In OOo, select Tools > Extension Manager from the menu bar.
In the Extension Manager dialog (Figure 24), click Add.
3) A file browser window opens. Find and select the extension you
want to install and click Open. The extension begins installing.
You may be asked to accept a license agreement.
4) When the installation is complete, the extension is listed in the
Extension Manager dialog.

Tip

To get extensions that are listed in the repository, you can open
the Extension Manager and click the Get more extensions
here link. You do not need to download them separately as instep 1 above.

Note

To install a shared extension, you need write access to the
OpenOffice.org installation directory.

Adding functionality with extensions

35

Figure 24: Installing an extension

Using extensions
This section describes a few of the extensions to Calc. In each case,you need to install the extension as described in the previous section.
Professional Template Pack II
Provides more than 120 templates for Writer, Calc, and Impress.
Available in several languages. After you have installed thisextension, you will find the templates under File > New >Templates and Documents.
Solver for Nonlinear Programming
Calc ships with a solver engine for linear programming only. Thisextension adds more solvers, which are capable of handling
nonlinear problems as well as linear ones:• DEPS - Differential Evolution & Particle Swarm Optimization• SCO - Social Cognitive Optimization

DataPilot Tools for OpenOffice.org Calc
Adds some utility functions that make using the DataPilot easier:• Display the current source range for a DataPilot• Change the source range for a DataPilot• Refresh all DataPilots on the current sheet

36

Setting up and Customizing Calc