Options in OpenOffice.org Calc

Like other OpenOffice.org applications, Calc has several dozen options in how it is formatted and operates. These options are available from Tools -> Options -> OpenOffice.org Calc. Thanks to OpenOffice.org's habit of sharing code between applications, some of the tabs for these options resemble those found in other OpenOffice.org applications. Others are unique to Calc and the business of spreadsheets. Either way, the more you know about Calc's options, the more you can take control of your work.

A survey of options

Several of the tabs in the Calc options are similar to the ones for other OpenOffice.org applications. The Grid tab sets options for a background grid, although, in Calc, you can often use cell borders instead for positioning objects like charts and graphics. Similarly, the Changes tab, where options are set for revision and multiple authoring, resembles the one in Writer in functionality, lacking only the accompanying illustrations. The Print tab may also be familiar, although under-populated with options, since spreadsheets are mainly online documents, and options for printing are contained in the Sheet tab for Calc page styles. However, one option that you may want to take note of is Print only selected sheets, which you can use by selecting Sheet tabs at the bottom of the editing window while pressing the Ctrl key.

The first place where most people will want to spend more time is the General tab. There, you can customize some of the basic behavior of Calc. Most people, I suspect, will want to set Calc to update links whenever they open a file unless they are using an older and slower computer. Conversely, few are likely to want to use printer metrics for text unless they have an older printer with embedded fonts that they choose to use or a printer that uses a non-standard formatting language. However, the options to expand formatting can be useful, and so can the option to expand references in formulas (that is, to automatically update them) when new rows or columns are added -- especially in spreadsheets that are constantly being altered or edited.

Options to highlight the headers of selected rows and columns and to show a warning when pasting data are enabled by default in most versions of OpenOffice.org, and should probably be left untouched when they are. But it's probably up to your work habits whether you customize how the cell selected shifts when you press the Enter key, or whether you use the Enter key to enter Calc's edit mode.

The next stop for most should be the View tab. One of its most practical uses is speeding up redraws and file opening on older computers by suppressing the display of different objects. Other options on the View tab are largely customizations. For instance, you might choose to go to the Window pane of the tab to turn off various widgets in the editing window, or to change the default color of cell borders or the size of the handles for manipulating selected objects. You can also choose whether zero values are displayed, or an indicator for notes. Long-time spreadsheet users have the option of having cell content that takes up more space than the cell's dimension display on top of adjacent cells -- although, with Calc's options for line wrap, there is really no need to endure this anachronism unless you like it.

Perhaps the most practical option on the View tab is Display -> Value highlighting, which changes the color of cell borders according to color. When this option is active, text cells are outline in black, numerical cells in blues, and cells with formulas or dates in green. And if you like value highlighting, you will almost certainly want to be sure that Show references in color is also selected, so that the cells that provide input into a formula are highlighted when the formula is selected -- although you can get the same effect only when desired by choosing Tool -> Detective.

Sort Lists is a less commonly used tab, but it can be a powerful one if you go to the trouble of customizing it. A sort list is a reoccurring pattern of cell contents, such as the days of the week or the months in the year (the default entries are for both, either in three letter abbreviations or with full names). You can add your own sort lists to the defaults, such as the names of people whose names will reoccur in a task list, or a pattern of numbers, such as counting by threes. If you type any item on the list, then drag vertically or horizontally on the lower right corner of the cell, you can immediately fill adjacent cells with other items in the list, repeating the items in the same order as often as necessary. Alternatively, when you select Data -> Sort, you can select a sort list as a custom sort order on the Options tab of the Sort window, the feature that gives sort lists their name.

All these options are potentially useful (at least to someone) but by far the most useful option tab for Calc users is Calculations -- calculations, after all, being what spreadsheets are mainly about. On this tab, you can set the default date format, as well as the number of times an iterative formula is run. Other options set whether Calc is case-sensitive when dealing with text, and whether Calc attempts to identify row and column labels when dealing with ranges of data.

However, the options with the farthest reaching consequences on the Calculations tab are Decimal Places and Precision as shown. Both can strongly affect the results you get when using Calc. As the name implies, the Decimal Places option sets the number of decimal places displayed in formula results. What is potentially confusing is that formulas are calculated with the full values, but that Calc may only display results rounded to the number of decimal places set. The default of two decimal places is enough for most users, but, if you don't know the difference between the values used for calculation and those for display, you may get frustrated by the apparent inaccuracy. All you need to do, though, is to change the number of decimal places displayed to get more accurate results.

Under this arrangement, the Precision as shown option can either be a helpful simplification or else a source of further error. If the option is selected, then subsequent calculations will use the rounded values displayed, not the numbers actually used in the original calculation. Given several generations of formulas, the inaccuracy caused by this option can mount steadily. For this reason, no doubt, Precision as shown is turned off by default, so that spreadsheets maintain the difference between the displayed and actual numbers.


Many users, especially new ones, can ignore the options in Calc. Which ones are turned off by default may vary slightly depending on whether you download OpenOffice.org from the project itself or get it through your distribution, but, in all cases, the choice of options is generally sensible for the majority of users. However, if you want to customize Calc to suit your work habits, desire more efficiency, or simply need to track down the source of some unexpected Calc behavior, take the time to familiar yourself with them. You may find just the settings you need to make your life easier.

Bruce Byfield is a computer journalist who writes regularly for Datamation, Linux.com, and Linux Journal.


Bruce Byfield (nanday)


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Calc Sucks

Calc sucks.'s picture

It's been like this for a long time. OO needs to fix this decimal rounding crap. If I don't want to see the digits then I will round it myself...stupid!

"Decimal Places"

Anonymous's picture

However, the options with the farthest reaching consequences on the Calculations tab are Decimal Places and Precision as shown.

The default of displaying 2 decimal places is ridiculous. OOo Calc should show everything to the limits of it's precision (as implied by calling the cell format Number General) or ZERO decimals (as implied by Number General's settings of 0 decimal places), but not some random default of 2 decimal places. Why is this even an option? If I wanted a cell's value to be displayed rounded, I'd ** round it!

Yes, I just got burned bad by this, and now I'm repling to random blog entries as found by Google to get the word out.

Error, mistake, bug ?

Anonymous's picture

There's a huge mistake in Openoffice Calc!!!!
That is the reason why i don't migrate from MS Oficce yet.
Watch this : If you put the string ="123" in a cell, and you multiply that cell by 2, on OOffice you get the value 0 !!!! Not even an err:??? . This is serious, because if you have many cells connected, this error passes unseen, and you think you're doing the right calcs.
On MS Office, Excel assumes ="123" as numeric, not alfanumeric, and you'll be able to do math calculation.
And, above all, the spreadsheets with this "little" incompatibility will give final results diferent on OOffice and MSOffice.

Very, very disapointing!!!

Good Article

Computer Help Forum's picture

Bruce your Opencalc articles are always usefull,
Today I tried some Excel exercises for my Tomorrow exame at University, and this article helped me to make my Opencalc look like the Excell installation at my University exam station.


summing a time (base 60) column

Anonymous's picture

I can not find a way to properly sum a column of time values.
Calculating the elapsed time between two time values of the same day works with 24 hour format, but summing a column of 30 elapsed times does not give me a good value even thought the cell format is "time".

I'll let someone else say

Anonymous's picture

I'll let someone else say whether or not it should work the way you describe.

What I've done to get the result you're looking for is mix the integer result of x/60 (hours) with the MOD of x/60 (leftover minutes).

There might be a more elegant way, but it works.


Same solution as with Excel

Anonymous's picture

Formatting the cell containing the total time like this worked for me:


(Just put brackets around the hours.)

Linuxworld 2007 to showcase the latest open source & Linux Tech

LinuxWorld's picture

The UK’s only Linux event to take place at Olympia 2, 23-24th October 2007

London, 28th June 2007: LinuxWorld 2007 is tipped to set the latest trends in Open Source and Linux technologies. The show, which is expected to attract IT decision makers, business professionals and Linux enthusiasts, will showcase the latest technology, debate the use of Open Source in the desktop environment, touch on the use of Linux in the datacentre and discuss the development of a common Linux-based platform for mobile phones. LinuxWorld Conference & Expo will take place at Olympia 2, 23rd -24th October 2007.

The show is the only Linux and Open Source event in the UK and will appeal to a cross section of industries including automotive, health, banking, retail, education and local government. Participation has already been confirmed by some of the largest players in the Open Source world including ThoughtWorks, Linux International and Mozilla.

“Many industry watchers thought that the recent launch of Microsoft Vista would have challenged the ever-increasing update of Linux and Open Source within the enterprise,

Very interesting article. I

Bjorn Solstad's picture

Very interesting article. I don't use Calc that much i Oo, but it's useful to get some more insight about it anyway.