By Bruce Byfield on Thu, 2007-06-28 15:30.
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.