Controlling Spreadsheet Layout and Scrolling

Controlling Spreadsheet Layout and Scrolling

As your spreadsheet grows, it's useful to controlling scrolling within it.

Creating Non-Scrolling Regions

If you have long rows or columns of data that extend well beyond the viewable area of the spreadsheet window, scrolling to the areas you want to see means you no longer see the column or row labels associated with the data you're viewing. For example, if you have a spreadsheet that has columns for different types of dollar amounts (interest paid, payment, and balance), and you scroll down to see data beyond the bottom border of the work area, you may not remember which column corresponds to which type of dollar amount because your column headings have scrolled up.

To fix this problem, you can make column or rows non-scrolling (see Figure 19-27).

Figure 19-27. Non-scrolling region


Horizontal Non-Scrolling Region
  1. Select the row below the row you want to be in the non-scrolling region. All rows above the selection will be non-scrolling.

  2. Choose Window > Freeze.

Vertical Non-Scrolling Region
  1. Select the column to the right of the column you want to be in the non-scrolling region. All columns to the left of the selection will be non-scrolling.

  2. Choose Window > Freeze.

Horizontal and Vertical Non-Scrolling Regions
  1. Click the cell that is below the row and to the right of the column you want to make non-scrolling; for example, cell B2.

  2. Choose Window > Freeze.

Turning Off Non-Scrolling Regions

Choose Window > Freeze.

Splitting a Window

If your spreadsheet is larger than the boundaries of your screen, and you're constantly scrolling to work with different parts of the spreadsheet, consider splitting it. Splitting lets you view and work with different sections of a spreadsheet simultaneously .

You can split the spreadsheet vertically, horizontally, or into fourths, depending on which cell is selected.

The split command won't work if cell A1 is selected.

Splitting vertically

Select any cell in row 1 (except cell A1) and choose Window > Split.

Splitting horizontally

Select any cell in column A (except cell A1) and choose Window > Split.

Splitting into fourths

Select any cell in the middle of the spreadsheet and choose Window > Split.

Resizing the split

You can resize the split areas by moving the cursor over the split line until it changes shape, then clicking and dragging the line to the new position, as shown in Figure 19-28.

Figure 19-28. Resizing a split area


To unsplit the window, choose Window > Split.

Power Formatting With Styles and Templates

In the previous sections we talked about formatting cells manually, which is how most people format: selecting cells and either clicking quick-formatting tools on the object bar and ruler or choosing Format > Cells to set specific formatting options.

There are legitimate reasons to use only manual formatting (such as quick formatting of a short spreadsheet whose styles you don't plan to reuse). However, to get the most out of Calc and to work more quickly with more consistency, use styles and templates.

There are two types of styles in Calc: Cell Styles, which affect cells and their contents, and Page Styles, which affect pagination and print output.

Why You Should Use Styles

Using styles in Calc is a no-brainer, especially if you work with spreadsheets a lot.

Following are the reasons why you should use styles. Any one of these reasons alone justifies using them.

Instant Formatting

With a double-click you can transform a plain cell into one with a different font, font size, font color, indentation, spacing, alignment, and background color . All cells that are given that style are identical.


When you modify a style, all cells with that style are updated automatically. Automation is good! It doesn't mean "cookie- cutter "; it means you work more quickly, efficiently , and consistently.

Maintaining Consistency

Using styles ensures your spreadsheets will maintain a consistent style.

Conditional Formatting

You can set a cell to take on specific cell formats automatically when the contents of the cell reach a certain state, or condition (see Conditional Formatting on page 578).

Using the Stylist

If you use styles in Calc, the Stylist should be your closest companion. To show it (and hide it), press the F11 key.

Styles apply only to the spreadsheet in which you create them. To make styles in one document available to other documents, see Loading Individual Styles on page 266.

The Stylist, shown in Figure 19-29, is the control center for viewing, applying, adding, modifying, and deleting styles. The following picture and table describes the elements of the Stylist.

Figure 19-29. The Stylist


Table 19-1 describes style categories in the drop-down list at the bottom of the Stylist.

Table 19-1. Calc Stylist categories




Shows all defined styles for each style type.

Applied Styles

Shows all the styles you've used in your spreadsheet so far.

Custom Styles

Shows the styles you've created beyond the default styles provided by Calc. The styles you create remain in this category even if you assign them to a different category.


Displays styles in a hierarchical tree view. If a style has a plus sign next to it (+), click the plus sign to view the styles that were created based on that style.

The Style Catalog

You can also create, modify, and delete styles using the Style Catalog (Format > Style Catalog).

Tips for Using Styles

With the Stylist docked , make sure you have tooltips turned on (Help > Tips) to help you select the style type you want. When the mouse pointer hovers over a style type icon, its name is displayed. (If the Stylist is a floating window, the name of the selected style category is displayed in the window's title bar.)

You don't have to have all your styles perfect before you start using them. You'll want to make adjustments to them as you work. The great thing about styles is that you can change them when you want, and all of the cells and pages that use them are updated automatically.

Cell Styles

Cell styles control all elements included in cell formatting.

This section describes how to create cell styles. For information on applying styles, see page 557; for modifying styles, see page 557; for deleting styles, see page 557.

Creating a Cell Style

Creating a cell style is fairly easy. In fact, if you know how to format text manually (see Quick Cell Formatting on page 539), you know 90 percent of creating a text style.

  1. In the Stylist, click the Cell Styles icon.

  2. Select the category in which you want to put the new style.

  3. Right-click in the Stylist and select New. The Cell Style window is displayed.

    If you want to create a new style based on an existing style, select the style you want to base it on before you right-click.

  4. Set the options you want for the cell style.

  5. Click OK.

Page Styles

Page styles control such elements as margins, borders, background, headers, footers, and spreadsheet printing options.

Creating a Page Style
  1. In the Stylist, click the Page Styles icon.

  2. Select the category in which you want to put the new style.

  3. Right-click in the Stylist and select New. The Page Style window is displayed. See Figure 19-21 through Figure 19-21 for more information.

  4. Set the options you want for the page, and click OK.

    If you need help setting Page, Borders, and Background options, see the Creating Page Styles on page 257 section in Writer on page 257.

Applying Styles

  1. Select the cell(s) or page to which you want to apply a style.

  2. In the Stylist, select the type of style you want to apply (cell or page), select a category, and double-click the name of the style you want to use.

To return to a generic style, double-click the Default style.

Modifying Styles

  1. In the Stylist, select the style type containing the style want to modify.

  2. Select the category the style belongs to.

  3. Right-click the style and select Modify.

  4. Change settings for the style.

  5. Click OK.

  6. If a style doesn't update automatically in the document, select the name of the style in the Stylist and click the Update Style icon at the top of the Stylist.

Deleting Styles

You can't delete default styles. You can delete only custom, user -defined styles.

Before you delete a style, select it in the Stylist, right-click it, select Modify, and select the Organizer tab. Look at style selected in the Based on field (if applicable ). When you delete the style, if it was used in the spreadsheet, the parts of the spreadsheet with that style become the style shown in the Based on field.

  1. In the Stylist, select the style you want to delete.

  2. Right-click it, and select Delete.

  3. Click Yes in the confirmation window.


Calc uses the same template principles as other applications. For a full description of templates and procedures for previewing, creating, maintaining, and applying templates to documents, see the Writer section, Using Templates on page 262.

The only sub-section that doesn't apply to Calc is Loading All Styles on page 265.

Formatting Using Color Themes

Calc also comes with a predefined set of themes you can apply to your spreadsheets. Themes are like templates that contain only predefined styles.

You can't add themes to Calc, and you can't modify them. You can, however, modify their styles after you apply them to your spreadsheets.

  1. Click the Choose Themes icon on the main toolbar.


  2. In the Theme Selection window, select the theme you want to apply to the spreadsheet. Double-click it to preview it.

  3. Click OK.


To remove the effects of a theme, click the Choose Themes icon in the toolbar and select the Standard theme.