Often a cell area of your Calc sheet or a table inside a Writer text document needs to be formatted. The first row and/or column should have a different background, the font should look bold or italic, the values should get some numbering formats.

And you have at least three wishes when applying the formatting:

• Fast and easy, with as few clicks as possible.

• Always the same style.

• Looking nice.

The answer is easy: It is called AutoFormat. AutoFormat defines a set of different formatting rules and properties with a distinctive name. Several AutoFormats are already supplied and installed in Calc and Writer.

If you don't like the supplied AutoFormats, you can add your own AutoFormats and give them the names you want. Format a table as you like, select all the formatted cells, then open Format-AutoFormat to add your formatting as a new style. Easy, isn't it?

You can add backgrounds, borders, set automatic cell width and height, use currency formats, and more. You can even apply patterns, which means defining alternating colors for alternating rows or columns.

To apply an AutoFormat

1. Select an area of cells.

2. Choose Format - AutoFormat.

3. Select a name from the left list and watch the preview in the dialog.

4. Click OK to apply the AutoFormat.

Several methods are available to copy cell styles and page styles from one Calc spreadsheet to another file.

Copying cell styles

You found a document called "StylesApplied.ods" somewhere, and you like the styles that are used to format the cells. You want to apply the same nice cell styles in your own spreadsheet. You know that once the styles show up in the "Styles and Formatting" window, you can easily apply a style to the selected cells by double-clicking the style name.

An easy method is to copy the formatted cells from the source document into your document. For example, use drag-and-drop to copy the cells. This copies the cell styles, too. You can now apply the copied cell styles to the other cells in your spreadsheet, and then delete the cells that you did copy.

Copying cell and page styles

To copy any type of styles you can use any of the following methods:

Creating a new document

If you start with a new document, it is best to use the source document with all those nice styles as a template. Then you can start new documents based on that template whenever you want.

1. Open the source document which contains the styles.

2. Save the document as a template:

   Choose File - Templates - Save. You see the Templates dialog, where you can enter a name for the template and select a category. (Note that the picture shows the Templates dialog of StarOffice 8 on Solaris)
   

   
   

3. In this example, you enter the name "myCalcStyles" and click OK. This saves the document as a template with the name "myCalcStyles.ots" to your OOo user/template directory.

To start a new document based on this template, open the Templates and Documents dialog:

1. Choose File - New - Templates and Documents.

2. Click the Templates icon at the left, then double-click the My Templates category.

   
   

3. Double-click the "myCalcStyles" template.

   A new Calc spreadsheet opens. It is based on the template, contains the styles you want, and has an "untitled" name like all new documents.

Copying styles to an existing document

You can use drag-and-drop in the Template Organizer to copy or move styles between any two documents or templates of the same document type.

1. Open both documents - the source and the destination document.

2. Choose File - Templates - Organize to open the Template Management dialog.

3. Click the drop-down list at the lower left (where you see "Templates"), and select "Documents".

   
   

4. In one of the two list boxes, double-click the source file. If you want, you can double-click the destination file in the other list box to see its style contents, although this is not mandatory.

5. Double-click the Styles entry to open a list of all styles in that file.

6. Drag-and-drop the style you want to copy to the destination file name in the other list. Be sure to hold down the Ctrl key to copy the style. You see a plus sign at the mouse pointer. Without the Ctrl key you would move the style from one file to the other.

7. After copying all styles, click Close and save your destination document.

You can only copy or move styles between the same type of documents. Calc styles can only be copied to Calc documents or Calc templates, Writer styles only to other Writer files.

Several options control the printed output of your Calc spreadsheets. Some of the default settings have been changed for the next OOo 2.3 version.

One remark first: in a Writer text document, you normally observe that each page on screen will be printed on one page of paper. This is how Writer is designed to work. But this is not how Calc is designed to work.

A Calc document can contain several sheets with data. Each sheet can be much bigger than you would want to print on one page of paper. So you are in control to specify what to print and what not to print. Calc has all the controls ready that you need to finetune printing.

Another remark: the Export to PDF feature works basically the same as printing. The settings for printing will be also the settings for the exported PDF file.

To print the current sheet

To print all data cells from the current sheet, just execute the Print command.

If your data covers many pages of paper, you can setup properties like the page order or scaling:

Choose Format – Page and click the Sheet tab.

If there are some large gaps between your data cells that would result in empty pages to be output from the printer, don't worry. You can select to suppress empty pages when printing in two similar looking dialog boxes. To suppress empty pages from printing is a new default setting in OOo 2.3.

• To change the default for all print jobs, choose Tools – Options – OOo Calc – Print.

• To change the setting for just one print job, choose File – Print, then click the Options button.

In addition, the Print only selected sheets option is now enabled by default. The selected sheet is normally the one you are watching on screen. You can click another sheet tab in the lower left corner of the Calc screen to make that the selected sheet. The selected sheet tab is highlighted.

To select multiple sheets, hold down Ctrl while you click the tabs. But be aware that when you select multiple sheets and enter some data into a cell, the same data will be entered on all selected sheets. You may want this to save some time, or you may hate this if you do not know this feature.

To preview the print output on screen

There are two different windows where you can preview the printing output.

• Choose View – Page Break Preview to open an interactive window where you can see and change the data that will be printed on each page of paper.

In this window, you can see the page breaks as blue lines. You can grab a blue line and move it to set the page break to a new position. The scaling will be set automatically to print the data.

Choose View – Normal to get back to the normal Calc window.

• Choose File – Page Preview to see the same preview that you may already know from Writer documents.

Unlike in Writer, the page preview of Calc does not offer too many options, so we'll click the Close Preview button to go back to the normal document view.

To print a range of cells

If you always want to print only selected cells, you can define those cells to be part of a print range.

1. Select all cells that are to be printed.

2. Choose Format – Print Ranges – Define.

You can visually add another print range on a sheet or change the print ranges on the Page Break Preview.

1. Choose View – Page Break Preview.

2. Select some cells.

3. Right-click to open the context menu and choose your command.

Now when you print or export to PDF, you see only the cells from the print range. If your print range spans multiple sheets, by default you now see only the cells from the selected sheet or sheets (see the “Print only selected sheets” option mentioned above).

To print only some pages of paper

You may want to print only some pages of paper. For example, when you want to print all the odd numbered pages first, then flip over the paper sheets and reinsert them into the printer to print the even numbered pages on the other side.

• Choose File – Print, then select All, Pages, or Selection.

The range of printed output  that you select here is the last filter in the chain. When you select All, you will get all the pages according to the other settings of print ranges, suppressed pages, and selected sheets. The printed pages are numbered from 1 to n.

Enter a list of page numbers to print only the pages with the numbers you enter, from all the content that you have defined by "selected sheets" and "print ranges".

When you suppress some empty pages, this will change the numbers of the printed pages that follow the empty pages. So when you enter something like 4-6  in the Pages box, the printer will print the pages 4, 5, and 6 from the current "All" pile of pages.

When you select some cells from your sheet and click Selection in the Print dialog box, this will override any defined print range.

Referencing Cells in Calc

If you are working on a spreadsheet containing multiple sheets that all share a common set of categories as a first column, and these category titles may change with time, you don't need to modify them on each and every sheet, just use cell references.

Background

Cell References in OpenOffice.org Calc have the following general syntax:

FILENAME#SHEETNAME.CELLADDRESS

with

• FILENAME being the name of the file that contains the value to be referenced
• SHEETNAME being the name of the sheet in that file that contains the value to be referenced
• CELLADDRESS being the name of the cell that contains the value to be referenced
  

If you are referencing within the same file, the FILENAME# part is optional, if you are referencing within the same sheet, the SHEETNAME. part is optional, too.

Example

On Sheet1, Mary had a little lamb, and you would like Mary to have a little lamb on Sheet2, too:

Go to Sheet2, click on the first cell and insert the magic formula:

=Sheet1.A1

telling Calc to insert a reference to cell A1 on Sheet1 in the current document. That cell says Mary and so does this cell after entering the formula. But as you can see on its formula bar, it just points to cell A1 on Sheet1 and displays its content:

Now for the rest of the rows, you don't need to enter the formula again. Just select the range of cells:

and select " Edit - Fill - Down" from the main menu to let OpenOffice.org fill the cell contents for you:

Now, if you decide that Mary should rather have a cow, she'll have it on all referenced sheets as well:

Printing All Formulas Used in Your Calc Sheets

Sometimes you want a printed reference of the formulas that you entered to a spreadsheet. You can get a printout of all formulas by a few mouse clicks.

Most Calc users already know that it is easy to see the formulas on screen:

• Choose Tools - Options - OOo Calc - View, then enable Display - Formulas.

Now, to print the formulas you have to look at a different option.

• Choose Format - Page - Sheet, then enable Print - Formulas. Print the spreadsheet.

Don't forget to reset the Print - Formulas setting after printing, so you will get the calculated values again on later printouts.

If you need a file that lists the formulas, enable printing formulas as above, then choose File - Export to PDF. The resulting PDF file lists all formulas in the defined print range.