Calc Guide 7.5

Chapter 5
Using Styles and Templates
Using consistent formatting in spreadsheetsCopyrightThis document is Copyright © 2023 by the LibreOffice Documentation Team. Contributors arelisted below. You may distribute it and/or modify it under the terms of either the GNU General
Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative
Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.
All trademarks within this guide belong to their legitimate owners.

Contributors
To this edition
Olivier Hallot

To previous editions‍ kip Masonsmith
S
Barbara Duprey
Peter Schofield
Kees Kriek

Jean Hollis Weber
Christian Chenal

Steve Fanning
Samantha Hamilton

Randolph GamoJohn A Smith
Dave Barton
Leo MoonsFelipe Viggiano

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration isrequired) or send an email to: loguides@community.documentfoundation.org.

Note
Everything you post to a forum, including your email address and any otherpersonal information that is written in the message, is publicly archived andcannot be deleted. E-mails sent to the forum are moderated.

Publication date and software version
Published February 2023. Based on LibreOffice 7.5 Community.
Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS
Some keystrokes and menu items are different on macOS from those used in Windows andLinux. The table below gives some common substitutions for the instructions in this document.
For a more detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to thisguide.
Windows or LinuxmacOS equivalent

EffectTools > Options
menu selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click and/or right-click
depending on computer setup

Open a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

F11

⌘+T

Open Styles deck in Sidebar

Documentation for LibreOffice is available at https://documentation.libreoffice.org/en/Contents
Copyright.......................................................................................................................... 2
Contributors................................................................................................................................2
Feedback....................................................................................................................................2
Publication date and software version........................................................................................2
Using LibreOffice on macOS......................................................................................................2

What are styles? Why use them?...................................................................................5
Direct formatting and Style formatting........................................................................................5

Calc style types................................................................................................................5
Cell styles...................................................................................................................................5
Page styles.................................................................................................................................6

Applying cell styles......................................................................................................... 6
Styles menu................................................................................................................................6
Styles deck in Sidebar................................................................................................................6
Fill Format mode.........................................................................................................................7
Apply Style list............................................................................................................................8
Keyboard shortcuts.....................................................................................................................8

Applying page styles.......................................................................................................9
Creating new styles......................................................................................................... 9
Cell style attributes.....................................................................................................................9
Organizer tab.........................................................................................................................9
Numbers tab........................................................................................................................10
Font tab................................................................................................................................11
Font Effects tab....................................................................................................................12
Alignment tab.......................................................................................................................13
Asian Typography tab..........................................................................................................15
Borders tab..........................................................................................................................15
Background tab....................................................................................................................16
Cell Protection tab...............................................................................................................16
Resetting cell style attributes....................................................................................................17
Page style attributes.................................................................................................................17
Organizer tab.......................................................................................................................17
Page tab..............................................................................................................................17
Borders and Background tabs.............................................................................................19
Header and Footer tabs.......................................................................................................19
Sheet tab.............................................................................................................................19

Using themes................................................................................................................. 19
Managing styles.............................................................................................................20
Modifying styles........................................................................................................................20
Renaming styles.......................................................................................................................21
Deleting styles..........................................................................................................................21
Hiding and showing cell styles..................................................................................................21
Filtering the list of styles...........................................................................................................21

Chapter 5 Using Styles and Templates | 3

What is a template? Why use one?..............................................................................22
Using a template to create a spreadsheet...................................................................22
Creating a spreadsheet from the Templates dialog..................................................................22
Creating a spreadsheet from a template in the Start Center....................................................23

Creating a template........................................................................................................24
Editing a template..........................................................................................................25
Updating styles from a changed template................................................................................26

Changing the template assigned to a document........................................................26
Adding templates obtained from other sources.........................................................26
Installing templates...................................................................................................................27
Installing collections of templates.............................................................................................27

Setting the default template..........................................................................................28
Setting a template as the default..............................................................................................28
Resetting the default template..................................................................................................28

Organizing templates.................................................................................................... 29
Creating a template category...................................................................................................29
Deleting a template category....................................................................................................30
Moving a template....................................................................................................................30
Deleting a template...................................................................................................................31
Exporting a template.................................................................................................................31

4| Chapter 5 Using Styles and Templates

What are styles? Why use them?
A style is a set of formats that you can apply to selected elements in a document to quickly
change their appearance. Applying a style often means applying a group of formats at the same
time.
Many people manually format spreadsheet cells and pages without paying any attention to styles
and are accustomed to formatting documents according to physical attributes. For example, forthe contents of a cell you might specify the font family, font size, and any character formatting
such as bold, italic, or underline.
Using styles means that you stop applying attributes and characteristics individually (for example,
font size 14 pt, Times New Roman, bold, centered) and start using a style, for example Title.
Applying attributes and characteristics is time-consuming and prone to errors. In contrast, using astyle, which has been defined to include the attributes and characteristics you want, will save youtime and give greater consistency in formatting.
Styles also make major formatting changes easy. For example, you may decide to change theappearance of all subtotals in a spreadsheet to be 10 pt Arial instead of 8 pt Times New Roman
after you have created a 15-page spreadsheet. Assuming that you have formatted all subtotals
with a specific style, you can change all of the subtotals in the document by simply changing theproperties of that style.
In addition, Calc also provides page styles, as described below.
You can modify the attributes of the supplied styles and define as many new styles as you
require. Creating, modifying, and accessing styles is explained in the following sections.

Direct formatting and Style formatting
Direct formatting (also called manual formatting) overrides styles. You cannot remove the direct
formatting by applying a style to it.
To remove manual formatting, select the text, and then do one of the following:•

Right-click and choose Clear Direct Formatting in the context menu.

•

Press Ctrl+M.

•

Click the Clear Direct Formatting icon on the Standard toolbar.

•

Select Format > Clear Direct Formatting on the Menu bar.

Calc style types
While some components of LibreOffice offer many style types, Calc offers only two: cell styles
and page styles. These style types are explained in the following sections.

Cell styles
Cell styles are similar to paragraph styles used in LibreOffice Writer. They are the most basic
type of style in Calc. Cell styles are used for applying fonts, alignment, borders, background,number formats (for example, currency, date, number), and cell protection to format the data inthe cells.
A basic range of cell styles is supplied with Calc. Many of these styles are shown on the Styles
menu on the Menu bar and all are shown in the Styles deck of the Sidebar. If you create a newcell style, it will be shown in the Sidebar. To add it to the Styles menu, see Chapter 15, Setting
up and Customizing, for instructions.

Calc style types | 5

Initially, the basic styles are configured so that if you change the characteristics of the Default cellstyle, then all of the other styles, inheriting from it, will change to match.

Page styles
Page styles in Calc are applied to sheets. Although one sheet may print on several pages, only
one page style can be applied to a sheet. If a spreadsheet file contains more than one sheet,then a different page style can be applied to each sheet. For example, a spreadsheet might
contain some sheets to be printed in landscape orientation and other sheets to be printed in
portrait orientation.
Two page styles are supplied with Calc: Default and Report. Page styles allow you to set page
size and orientation, page margins, headers and footers, borders and backgrounds, and thesequence for printing sheets.

Applying cell styles
To apply cell styles in Calc, you can use the Styles menu on the Menu bar, the Styles deck on
the Sidebar, Fill Format mode, the Apply Style list on the Formatting toolbar, and keyboard
shortcuts.

Styles menu
By default, the Styles menu (Figure 1) lists many of the cell styles supplied with Calc. To apply acell style, select the cell or group of cells to be formatted, and then choose Styles and click on
the required style.

Figure 1: The Styles menu

Styles deck in Sidebar
The Styles deck on the Sidebar lists and optionally shows previews of the styles available.
1) If the Styles deck in the Sidebar is not open, do one of the following to open it:– Select View > Styles on the Menu bar.
– Select Styles > Manage Styles on the Menu bar.
6 | Chapter 5 Using Styles and Templates

– Press the F11 function key.
– If another deck of the Sidebar is open, select the Styles icon in the tab panel at theright of the Sidebar.
2) Click the Cell Styles icon on top of the Styles deck to open the list of cell styles. Figure 2
shows the deck with cell styles visible.
3) In the spreadsheet, highlight the cell or group of cells where you want to apply a cell style.
4) Double-click on the cell style name in the Styles deck and the formatting attributes for thatstyle are applied to the data contained in the highlighted cells.

Figure 2: Styles deck in Sidebar, showing cell styles

Fill Format mode
Fill Format mode is useful when you want to apply the same style to many scattered cells.
1) Go to the Styles deck on the Sidebar (Figure 2).
2) Select the style you want to apply.
3) Click the Fill Format Mode icon in the top right of the Styles deck. The mouse pointerchanges shape.
4) Click in each cell where you want to apply the selected style.
5) When you have finished formatting cells, click the Fill Format Mode icon to exit themode.

Applying cell styles | 7

Caution
When Fill Format mode is active, a right-click anywhere in the document cancels
the last Fill Format action. Be careful not to accidentally right-click and thus
cancel any formatting actions you want to keep.

Apply Style list
The Apply Style drop-down list is not included by default on the Formatting toolbar in Calc, but
you can add it as follows:
1) Right-click on any icon on the Formatting toolbar and select Visible Buttons in thecontext menu (Figure 3).
2) Click Apply Style. The context menu closes and the Apply Style list appears at the left
end of the Formatting toolbar.

Figure 3: Adding the Apply Style list to the Formatting toolbar
To apply a style using the Apply Style list:
1) Select a cell or a group of cells in the spreadsheet.
2) Click the down arrow at the right of the Apply Style list to open the drop-down menu
(Figure 4).
3) Select the required style. The formatting attributes of that style are applied to the selected
cells.

Figure 4: Using the Apply Style list
Only the styles that are in use are shown on the list. Click More Styles to go to the Styles deck
where all cell styles are available.

Keyboard shortcuts
You can create keyboard shortcuts to apply commonly-used cell or page styles, including any
custom styles that you have created. See Chapter 15, Setting up and Customizing, for more
information.

8 | Chapter 5 Using Styles and Templates

Applying page styles
The page style applied to a sheet within a spreadsheet is indicated on the Status Bar, as shownin Figure 5.

Figure 5: Page style (Default) indicated on Status Bar
To apply a different page style:1) Select the sheet to be styled by clicking its sheet tab.
2) Go to the Styles deck on the Sidebar (Figure 2).
3) At the top of the Styles deck, click the Page Styles icon.
4) Double-click on the required page style.

Creating new styles
To create a new style, follow the procedure described in this section.

Note
Any new styles, and any changes to existing styles, apply only to the spreadsheet
in use. To save new styles in a template, see “Creating a template” for more
information.
1) Go to the Styles deck on the Sidebar (Figure 2).
2) To create a new cell style, click the Cell Styles icon, or to create a new page style, click
the Page Styles icon.
3) Right-click in the Styles deck and select New in the context menu to open either the Cell
Style dialog or the Page Style dialog.
4) Use the various tabbed pages on the dialogs to set the attributes for the new style. See“Cell style attributes” below and “Page style attributes” for more information.
5) Click OK to close the dialog and save the new style.

Note
Style names are case sensitive. You can define, for example, a style called red
and another style called Red. Make sure all style names are sufficiently different
to prevent any confusion.

Cell style attributes
The following attributes are available on the tabbed pages of the Cell Style dialog (Figure 6).

Organizer tab•

Name – displays the name of the selected style. If you are creating or modifying acustom style, enter a name for the style. You cannot change the name of a predefinedstyle.

Creating new styles | 9•Inherit from – you can choose to link the new style to an existing style and then modify
some of the attributes. If you do this, any change to the parent style’s attributes will affectthe new style. For example, you could make the styles identical except for the font color;
if you later change the font family of the parent style, the new style’s family will alsochange.

•Edit Style – enables you to view and edit the properties of the parent style.

•

Category – displays the category for the current style. If you are creating or modifying a
new style, select Custom Styles from the list. You cannot change the category for apredefined style. Select Hidden Styles if you want that style to appear only in the Styles
list.

•

Contains – describes the formatting used in the style.

Figure 6: Cell Style dialog – Organizer tab

Numbers tab•

Category – select a category from the list.

•

Format – select how you want the contents of the selected cells to be displayed.

When you select Currency as a category, you need to select a currency in the drop-down list
and then select the format to use from the options available for that currency.
The currency format is not dependent on the language that you select in the Language box. The
default currency format for a cell is determined by the regional settings of your operating system.
•

Language – specifies the language settings for the selected cells. With the language set
to the default, Calc automatically applies the number formats associated with the systemdefault language. The language setting ensures that date and currency formats arepreserved even when the document is opened in an operating system that uses a
different default language setting.

•

Options – specify the options for the selected format:– Decimal places – enter the number of decimal places that you want to display.
– Leading zeroes – enter the maximum number of zeroes to display in front of thedecimal point.
– Negative numbers red – select to change the font color of negative numbers to red.
– Thousands separator – select to insert a separator between thousands. The type of
separator (for example, a comma or a space) depends on your language settings.

10 | Chapter 5 Using Styles and Templates•

Format code – displays the number format code for the selected format. You can also
enter a custom format.

•

Add – adds the number format code that you entered to the user-defined category.

•

Edit Comment – adds a comment to the selected number format.

•

Remove – deletes the selected number format.

•

Preview field – displays a preview of the current selection.

Figure 7: Cell Style dialog – Numbers tab

Font tabDepending on your language settings, you can change the formatting for the following font types
on this tab – Western text font (Latin character sets), Asian text font (Chinese, Japanese, orKorean character sets), and Complex text layout font (right-to-left text direction). Figure 8 shows
the Font tab of the Cell Style dialog when the Asian and Complex text layout options are
selected in the Default Languages for Documents area of the Tools > Options > Language
Settings > Languages page. The layout of the Font and Font Effects tabs are modified if eitherof these two options is deselected.
•

Font – select an installed font in the list.

•

Style – select the formatting you want to apply, for example bold, Italic, or underline.

•

Size – select or enter the font size that you want to apply. For scalable fonts, you canalso enter decimal values. If you are creating a style that is based on another style, you
can enter a percentage value or a point value (for example, 2 pt or 5 pt).

•

Language – sets the language that the spelling checker is to use. The spelling checker
works only when you have installed the corresponding language module. A language
entry has a check mark in front of it if the spelling checker is activated for that language.

Creating new styles | 11•Features – some fonts offer special features such as Discretionary Ligatures andHorizontal Kerning. Click this button to access the Font Features dialog (not shown) to
select those features.

•

Preview – displays a preview of the current selection.

Figure 8: Cell Style dialog – Font tab

Font Effects tab

Figure 9: Cell Style dialog – Font Effects tab•

Font color – sets the color for the text. If you select Automatic, the text color is set toblack for light backgrounds and to white for dark backgrounds.

12 | Chapter 5 Using Styles and Templates•Transparency – sets the transparency of the character text. The value 100% means
entirely transparent, while 0% means not transparent at all.

•Overlining – select the overlining style that you want to apply. To apply the overlining towords only (not the spaces between them), select the option box Individual words.

•

Overline color – select the color for the overlining, in the pallet when you click the button
next to the Overlining field. Automatic matches the font color.

•

Strikethrough – select a strikethrough style for the selected text. To apply the
strikethrough to words only (not the spaces between them), select Individual words.

•

Underlining – select the underlining style that you want to apply. To apply the underlining
to words only (not the spaces between them), select Individual words.
If you apply underlining to a superscript text, the underlining is raised to the level of thesuperscript. If the superscript is contained in a word with normal text, the underlining is
not raised.

•

Underline color – select the color for the underlining, in the pallet when you click the
button next to the Underlining field. Automatic matches the font color.

•

Individual words – applies the selected effect only to words and ignores spaces.

•

Relief – select a relief effect to apply to the text. The Embossed relief makes thecharacters appear as if they are raised above the page. The Engraved relief makes thecharacters appear as if they are pressed into the page.

•

Outline – displays the outline of the characters. This effect does not work with every font.

•Shadow – adds a shadow that casts below and to the right of the characters.

•

Emphasis Mark – select a character to display over or below the entire length of theselected text.

•

Position – specify where to display the emphasis marks.

•

Preview – displays a preview of the current selection.

Alignment tab

Figure 10: Cell Style dialog – Alignment tab

Creating new styles | 13

•Text Alignment – sets the alignment options for the contents of the current cell, or theselected cells.
– Horizontal – select the horizontal alignment option that you want to apply to the cell
contents:– Default – aligns numbers to the right and text to the left.
– Left – aligns the contents of the cell to the left.
– Center – horizontally centers the contents of the cell.
– Right – aligns the contents of the cell to the right.
– Justified – aligns the contents of the cell to the left and to the right cell borders.
– Filled – repeats the cell contents (number and text) until the visible area of the cell isfilled. This feature does not work on text that contains line breaks.
– Distributed – aligns contents evenly across the whole cell. Unlike Justified, it also
justifies the very last line of text.
– Indent – indents from the left edge of the cell by the amount that you enter.
– Vertical – select the vertical alignment option that you want to apply to the cell
contents:– Default – aligns the cell contents to the bottom of the cell.
– Top – aligns the contents of the cell to the upper edge of the cell.
– Bottom – aligns the contents of the cell to the lower edge of the cell.
– Middle – vertically centers the contents of the cell.
– Justified – aligns the contents of the cell to the top and to the bottom cell borders.
– Distributed – similar to Justified, unless the text orientation is vertical. Then it behavesin a similar manner to the horizontal Distributed setting, with the very last line
justified.

•

Text Orientation – defines the text direction of cell contents.
– ABCD wheel – click anywhere in the wheel to define the text orientation. The
characters “ABCD” on the wheel correspond to the new setting.
– Vertically stacked – assigns vertical text orientation for cell contents.
– Asian layout mode – only available if Asian language support is enabled and the text
direction is set to vertical. Aligns Asian characters one below the other in the selectedcell(s). If the cell contains more than one line of text, the lines are converted to text
columns that are arranged from right to left. Western characters in the converted text
are rotated 90 degrees to the right. Asian characters are not rotated.
– Degrees – allows you to manually enter the orientation angle.
– Reference edge – specify the cell edge from which to write the rotated text:– Text Extension From Lower Cell Border – writes the rotated text from the bottom celledge outwards.
– Text Extension From Upper Cell Border – writes the rotated text from the top cell edge
outwards.
– Text Extension Inside Cell – writes the rotated text only within the cell.

14 | Chapter 5 Using Styles and Templates•

Properties – determine the text flow in a cell.
– Wrap text automatically – wraps text onto another line at the cell border. The numberof lines depends on the width of the cell. To enter a manual line break, press
Ctrl+Enter in the cell.
– Hyphenation active – enables word hyphenation for text wrapping to the next line.
– Shrink to fit cell size – reduces the apparent size of the font so that the contents of thecell fit into the current cell width. You cannot apply this command to a cell that
contains line breaks.
– Text direction – choices are Left-to-right, Right-to-left, and Use superordinate
object settings (the settings applied to the preceding cell).

Asian Typography tab
If Asian language support is enabled, the Asian Typography tab is displayed on the Cell Style
dialog. For more details of the options available on this tab, see the description of thecorresponding tab of the Format Cell dialog in Chapter 2, Entering and Editing Data.

Figure 11: Cell Style dialog - Asian Typography tab

Borders tab
Sets the border attributes for the selected cells.

Figure 12: Cell Style dialog – Borders tab

Creating new styles | 15•Line Arrangement – select a preset border style or define your own combination byclicking in the diagram.

•Line – select the line style, width, and color that you want to apply. The style is applied tothe borders selected in the preview.

•

Padding – specify the amount of space that you want to leave between the border andthe contents of the cell. You can set each distance (left, right, top, bottom) individually or
select Synchronize to apply the same padding to all four borders when you enter a newvalue.

•

Shadow Style – applies a shadow effect to borders. You can specify the position of the
shadow (bottom right, top right, bottom left, or bottom right), the distance it extends, andits color.

Background tab
Click Color to select a color to use as a background for the formatted cells. You can choose fromseveral palettes. To change the color, select another one. To remove the background color, selectNone.

Figure 13: Cell Style dialog – Background tab

Cell Protection tab
In this tab you can set some basic protection to you spreadsheet.
•

Protection•

Hide all – hides formulas and contents of the selected cells.

•

Protected – prevents the selected cells from being modified. This cell protection only
takes effect if you also protect the sheet.

•

Hide formula – hides formulas in the selected cells.

•

Print

16 | Chapter 5 Using Styles and Templates•

Hide when printing – prevents the selected cells from being printed.

Figure 14: Cell Style dialog – Cell Protection tab

Resetting cell style attributes
The buttons at the bottom of the Cell Style dialog have the following functions:•

Reset – returns values on the current tab to those applicable when the dialog was
opened.

•Standard – for a style that inherits settings from a parent style, updates the settings on
the current tab to match those of the parent style.

•

Cancel – cancels any changes made (to all tabs in the dialog) since the last save of this
dialog (by clicking OK).

•

OK – saves all changes made (to all tabs in the dialog).

Page style attributes
The following attributes are available on the tabs of the Page Style dialog (Figure 15).

Organizer tab
This tab is similar to the Organizer tab for the Cell Style dialog, although page styles cannot
inherit settings from other styles.
•

Name – displays the name of the selected style. If you are creating or modifying acustom style, you can enter or change the name of the style. You cannot change thename of a predefined style.

•

Category – displays the category of the current style. If you are creating or modifying a
new style, select Custom Styles in the list. You cannot change the category for apredefined style.

•Contains – describes the formatting used in the style.

Page tab
You can define page layouts for single and multiple-page spreadsheets, as well as page
numbering and paper formats.
•

Paper Format – select from a list of predefined paper sizes, or define a custom paper
format.

Creating new styles | 17

– Format – select a predefined paper size, or create a custom format by entering thedimensions for the paper in the Height and Width boxes.
– Width – displays the width of the selected paper format. To define a custom (user)
format, enter a width here.
– Height – displays the height of the selected paper format. To define a custom (user)
format, enter a height here.
– Orientation – Portrait displays and prints the spreadsheet with the paper oriented
vertically. Landscape displays and prints the spreadsheet with the paper oriented
horizontally.
– Text direction – select the text direction that you want to use in your document. The"right-to-left (vertical)" text flow direction rotates all layout settings to the right by 90
degrees, except for the header and footer. This option is only available if Complex
text layout is selected at Tools > Options > Language Settings > Languages.
– Preview Field – displays a preview of the current selection.
– Paper tray – select the paper source for your printer. You can assign different paper
trays to different page styles. For example, you can assign a different tray for the firstpage style in the spreadsheet and load the tray with your company letterhead paper.

Figure 15: Page Style dialog – Page tab•

Margins – specify the amount of space to leave between the edges of the paper and the
spreadsheet contents.
– Left/Inner – the space between the left edge of the page and the spreadsheet. If you
are using Mirrored page layout, enter the space between the inner edge of the paper
and the spreadsheet. You may want to have a wider space as the left/inner margin if
you will be punching or binding the pages.

18 | Chapter 5 Using Styles and Templates

– Right/Outer – the space between the right edge of the paper and the spreadsheet. If
you are using Mirrored page layout, enter the space between the outer edge of thepaper and the spreadsheet.
– Top – enter the space between the upper edge of the paper and the spreadsheet.
– Bottom – enter the space between the lower edge of the paper and the spreadsheet.
•

Layout settings – select the page layout style to use for the spreadsheet.
– Page layout – Right and left – both odd (right) and even (left) pages have the same
margins on all four sides. Mirrored – odd and even pages with inner and outermargins as specified (Use this layout if you want to bind the printed pages like abook. Enter the binding space as the Inner margin). Only right – the current pagestyle shows only odd (right) pages (Even pages are shown as blank pages). Only left
– the current page style shows only even (left) pages (Odd pages are shown as blankpages).
– Page numbers – select the page numbering format to use for the page style. Typicallythis will be 1,2,3; or a,b,c; or i,ii,iii.
– Table alignment – Horizontal centers the cells horizontally on the printed page.
Vertical centers the cells vertically on the printed page (the Preview Field will change
according to the selected in this option).

Borders and Background tabs
The Borders and Background tabs of the Page Style dialog are similar to those on the cell Style
dialog. See “Borders tab” and “Background tab” in “Cell style attributes” above for information on
how to set the borders and background for page styles. Page styles can have a bitmap
background, which is not available for cell styles.

Header and Footer tabs
Adds a header or footer to the page style. A header is an area in the top page margin where you
can add text or graphics. A footer is a similar area in the bottom page margin. See Chapter 7,
Printing, Exporting, Emailing, and Signing for details.

Sheet tabSpecifies the elements to be included in the printout of all sheets with the page style. Additionally,
you can set the print order, the first page number, and the page scale. See Chapter 7, Printing,Exporting, E-mailing, and Signing for details.

Using themes
Calc comes with a predefined set of formatting themes that you can apply to spreadsheets. It is
not possible to add new themes to Calc and the predefined styles cannot be modified. You can
modify styles after you apply them to a spreadsheet, but the modified styles are only available foruse for that spreadsheet.
To apply a theme to a spreadsheet:
1) Go to Format > Spreadsheet Theme on the Menu bar, or click the Spreadsheet Themeicon on the Tools toolbar, to open the Theme Selection dialog (Figure 16), which lists theavailable themes for the whole spreadsheet.
2) Select the theme that you want to apply. As soon as you select a theme, the theme styles
are applied to the spreadsheet and are immediately visible.
3) Click OK.

Using themes | 19

Figure 16: Theme Selection dialog
The themes tool is effective when you format a range in the spreadsheet as a table. The tool
adds new styles to the spreadsheet and modifies the Default cell style. The added cell styles are:•"Card" cell styles: Column Header, Top, Bottom, Left, Right, Top Right, Top Left,
Bottom Right, Bottom Left of the selected range.

•

Background: The background style of the range. Apply this style to the table contents
not in the table edges.

•

Input: used for data input. This cell style has the cell protection property unchecked.

If you wish, you can now use the Styles deck on the Sidebar to modify specific styles. These
modifications do not modify the theme; they only change the appearance of the style in the
spreadsheet you are creating.

Managing styles
Modifying styles
1) Go to the Styles deck on the Sidebar and click the icon for the type of style you want tomodify.
2) Right-click on the name of the style and select Modify in the context menu to open either
the Cell Style dialog (Figure 6) or the Page Style dialog (Figure 15).
3) Make the necessary changes to the style attributes. For more information on styleattributes, see “Cell style attributes” above and “Page style attributes” above.
4) Click OK to save the changes and close the dialog.

Note
The Modify command is unavailable if one or more sheets are protected, even if
the cell style to be modified is being used in an unprotected sheet.

20 | Chapter 5 Using Styles and Templates

Renaming styles
You can rename custom styles, but not the styles supplied with Calc.
1) Go to the Styles panel on the Sidebar (Figure 2) and click the icon for the type of style
you want to rename.
2) Right-click on the style you want to rename and select Modify in the context menu to
open either the Cell Styles dialog or the Page Styles dialog.
3) On the Organizer tab, rename the style, and click OK.

Deleting styles
You can delete custom styles, but not the styles supplied with Calc. Custom cell styles can only
be deleted if they are not applied. If an applied page style is deleted, affected sheets revert to theDefault page style.
1) Go to the Styles deck in the Sidebar (Figure 2) and click the icon for the type of style youwant to delete.
2) Right-click on the style you want to delete and select Delete in the context menu.
3) Select Yes on the warning message, if displayed.

Hiding and showing cell styles
You can shorten the list of cell styles by hiding some of them. Only styles that are not being usedin the spreadsheet can be hidden.
1) Go to the Styles deck in the Sidebar and click on the Cell Styles icon.
2) Right-click on an unused cell style and select Hide in the context menu. The style is thenmoved to the Hidden Styles category in the Styles deck.
To show hidden cell styles:
1) Go to the Styles deck on the Sidebar.
2) Click on the drop-down list at the bottom of the panel and select Hidden Styles in the list.
3) To revert, right-click on the hidden style you want to show and select Show in the context
menu.

Filtering the list of styles
You can filter the list of styles as follows:
1) Go to the Styles deck on the Sidebar.
2) Click on the drop-down list at the bottom of the panel.
3) Select the category of styles to display on the Styles deck:– Hierarchical – lists the styles in their hierarchical relationship, for example Heading 1
and Heading 2 under Heading, or Accent 1, Accent 2, Accent 3 under Accent.
– All Styles – lists all the styles that are available in Calc and in the spreadsheet,
except for hidden styles, in alphanumeric order.
– Hidden Styles – lists all the styles that have been hidden in the spreadsheet.
– Applied Styles – lists only the styles that have been applied to the spreadsheet. Not
applicable for page styles.
– Custom Styles – lists only the user-defined styles defined for the spreadsheet.
Managing styles | 21

What is a template? Why use one?
A template is a model that you use to create other documents in a standard format. For example,
you can create a template for invoices that has your company logo and address at the top of thepage. This means that any new spreadsheets (invoices) created from this template will have thecompany logo and address on the first page of the spreadsheet.
Templates can contain anything that regular documents can contain, such as text, graphics,
styles; user-specific setup information such as measurement units, language, default printer; andtoolbar and menu customization.
All documents in LibreOffice are based on templates. You can create, or download and install, as
many templates as you wish. If you do not specify a template when you start a new spreadsheet,then that new spreadsheet is based on the included Calc default template. This default template
consists of a blank spreadsheet which you can customize to your needs and then, if you wish,
use as a basis to create a custom default template.

Using a template to create a spreadsheet
A new installation of LibreOffice may not include any spreadsheet templates other than the Calc
default, but you can create your own (see “Creating a template”) or install templates obtained
from other sources (see “Adding templates obtained from other sources”).
When you have an available spreadsheet template, you can use it to create a new spreadsheet
from the Templates dialog or the LibreOffice Start Center.

Creating a spreadsheet from the Templates dialog
To use a template to create a spreadsheet:1) On the Menu bar, choose File > New > Templates, or click the small arrow next to the
New icon on the Standard toolbar and select Templates, or press Ctrl+Shift+N. The
Templates dialog (Figure 17) opens.
2) In the Filter lists at the top of the Templates dialog, select Spreadsheets and the categoryof template you want to use. All the templates in that category are then listed on the page,
as shown in Figure 17.
3) Select the required template and click Open, or double-click the required template, orright-click the required template and select Open in the context menu. A new spreadsheet
based on the template opens in Calc.
In the spreadsheet, the template it is based upon is listed in File > Properties > General. The
connection between a template and a spreadsheet remains until that template is modified. The
next time a spreadsheet based on that template is opened, you can choose either to update, or
not update, the spreadsheet to match the modifications.
Figure 17 shows the default configuration for the Templates dialog, with the available templates
represented by thumbnails. It is also possible to view the available templates in a list format that
includes additional information which may help identify the template of interest, as shown in
Figure 18. To switch between these two views, use the Thumbnail View and List View icons
located at the bottom right of the dialog.

22 | Chapter 5 Using Styles and TemplatesFigure 17: Templates dialog, showing a selected template in thumbnail view

Figure 18: Templates dialog, list view

Creating a spreadsheet from a template in the Start Center
You can create a spreadsheet from the Template view of the LibreOffice Start Center, which isvisible when no other document is open. (See Chapter 1, Introduction.)

Using a template to create a spreadsheet | 23

Click the Templates button in the left pane and icons are displayed in the Start Center for all
available LibreOffice templates. The top right of the Start Center offers a drop-down list to selectthe templates of a given type of document. If you select the Spreadsheets option on the list, only
the available spreadsheet templates are displayed in the Start Center. Click on the required
template to create a new document based on that template.

Creating a template
You can easily create a template from any spreadsheet:1) Open a new or existing spreadsheet that you want to use as a base for the template.
2) Add any necessary pre-defined content that you want to appear in a spreadsheet eachtime you use the new template, for example company logo and name, company
information, page numbers, and so on. Delete any information that you do not want in thetemplate.
3) Create or modify the cell and page styles that you want to use in the template.
4) On the Menu bar, choose File > Templates > Save as Template, or click the down arrowat the right of the Save button on the Standard toolbar and select Save as Template, orpress Shift+F11. The Save As Template dialog (Figure 19) opens, displaying theexisting template categories and a name box.
5) Type a name for the new template and select the required template category. You canalso choose to make this the default template (see “Setting the default template” for more
information).
6) Click Save to save the new template.

Figure 19: Save As Template dialog

Note
Although the Save as Template dialog shows no document type when saving a
new template of any type, the template will appear on the correct tab(Spreadsheets, in this case) when you return to the Templates dialog. It is
unnecessary to save or do anything further with the spreadsheet file you havebeen using to create the new template. It can be safely closed without saving.

24 | Chapter 5 Using Styles and Templates

Note
You can also save a template through the Save As dialog, accessed by a variety
of interactions including selecting File > Save As on the Menu bar or pressing
Ctrl+Shift+S. Select the ODF Spreadsheet Template option in the Save astype menu and navigate to the appropriate template folder before clicking theSave button.

Editing a template
You can edit a template’s styles and content. If you wish, you can then apply the new styles tospreadsheets that were created from the original template (see below for details).
To edit a template:1) On the Menu bar, choose File > Templates > Manage Templates, or press
Ctrl+Shift+N, to open the Templates dialog. You can also manage templates from theStart Center.
2) Navigate to the spreadsheet template that you want to edit. Right-click to open thecontext menu and choose Edit (Figure 20). The template opens in Calc.

Figure 20: Templates dialog – Editing templates3) Edit the template as you would edit any other spreadsheet. To save the changes, choose
File > Save on the Menu bar or perform any equivalent action. To save the edited
template as a new template, choose File > Save As Template or perform any equivalentaction.

Editing a template | 25

Updating styles from a changed template
If you make any changes to a template and its styles, the next time you open a spreadsheet thatwas created before you changed the template, a confirmation message is displayed.
1) Click Update Styles to apply the changed styles in the template to the spreadsheet.
2) Click Keep Old Styles if you do not want to apply the changed styles in the template to
the spreadsheet.

CautionIf you select Keep Old Styles, the confirmation message will not appear again
the next time you open the spreadsheet using the same changed template.
Although the template is still listed under File > Properties > General, thespreadsheet is no longer connected to the template, so it will not update. You canstill import styles manually from the template, but to reconnect the spreadsheet to
the template, you need to use one of the procedures described in “Changing thetemplate assigned to a document” below.

Changing the template assigned to a document
You can change the template assigned to a document, or reconnect a document to a template, in
two ways: manually or using the Template Changer extension to LibreOffice.
To change or reassign a template manually, first create a new, blank document from the required
template, as described above. Then copy the contents of the old document into the new
document.
To use the Template Changer extension, first you need to download and install it, then restart
LibreOffice to activate it. After that, you can open the document and use File > Templates >Change template (current document) on the Menu bar.
The Template Changer extension works with Writer, Calc, Draw, and Impress.

Adding templates obtained from other sources
LibreOffice refers to sources of templates as repositories. A repository can be local (a directoryon your computer to which you have downloaded templates) or remote (a URL from which you
can download templates).
To get to the official LibreOffice template repository, type https://extensions.libreoffice.org/en inyour browser’s address bar. This opens the Extensions web page (Figure 21). In the Searchsection, click the Templates tag filter. You can narrow your search by also choosing other tag
filters. After setting the filters, click the Search button. In the search results, find the required
template and click on its name to display a page with details; click the Download button to put
the template anywhere on your computer. Proceed to “Installing templates” below.
You can also use the Extensions button in the lower right of the Templates dialog, as shown in
Figure 22, to open an Extensions: Templates dialog, where you can scroll through a list ofextensions. In this version of LibreOffice, there is no easy way to filter this list, so you may preferto go directly to the web page, as described above.

26 | Chapter 5 Using Styles and TemplatesFigure 21: Searching for templates in the repository

Figure 22: Getting more templates for LibreOffice
On other websites you may find collections of templates that have been packaged into extension(.OXT) or .ZIP files.

Installing templates
You can import and add templates to your collection as follows:
1) Find and download the template you want to use, as described in the previous section.
2) Open the Templates dialog and click the Import icon near the bottom right.
3) In the pop-up dialog, select the category where you want to store the template. (You canchange this later, as described in “Moving a template” below.)4) A file browser opens. Navigate to the location of the downloaded template on yourcomputer.
5) Select the template and click Open. After import, the template becomes available in the
Templates dialog.

Tip
You can manually copy new templates into the template folders. The location
varies with your computer’s operating system. To learn where the template folders
are stored on your computer, go to Tools > Options > LibreOffice > Paths.

Installing collections of templates
The Extension Manager provides an easy way to install collections of templates that have been
packaged as extensions. Follow these steps:
1) Download the extension package (.OXT file) and save it anywhere on your computer.
2) In LibreOffice, select Tools > Extension Manager on the Menu bar, or press
Ctrl+Alt+E. In the Extension Manager dialog, click Add to open a file browser window.
Adding templates obtained from other sources | 27

3) Find and select the package of templates you want to install and click Open. The
package begins installing. You may be asked to accept a license agreement.
4) When the package installation is complete, you may need to restart LibreOffice. The
templates are available for use through the Templates dialog and the extension is listed in
the Extension Manager.
See Chapter 15, Setting up and Customizing, for more about the Extension Manager.

Tip
Some extensions are packaged as .ZIP files.

Setting the default template
If you create a new spreadsheet using File > New > Spreadsheet on the Menu bar or anequivalent action, Calc creates the document from the default template for spreadsheets. You
can set any template as the default template. You can also reset the default template to the Calc
default template.

Setting a template as the default
Most default settings, such as page size and page margins, can be changed but those changes
normally apply only to the spreadsheet you are working on. To make those changes the default
settings for spreadsheets, you need to replace the default Calc template with a new one.
You can set any template displayed in the Templates dialog to be the default for that document
type:1) On the Menu bar, choose File > Templates > Manage Templates or press
Ctrl+Shift+N.
2) In the Templates dialog, open the category containing the template that you want to set as
the default.
3) Right-click on the template and click the Set As Default option (Figure 20). The template
is now indicated by a green check mark in the upper left.
The next time you create a spreadsheet by choosing File > New, by clicking the New icon on theStandard toolbar, or by pressing Ctrl+N, it will be created from this template.

Resetting the default template
To set the default spreadsheet template back to the default template supplied with Calc:
1) In the Templates dialog, click the Manage button on the upper right (Figure 24).
2) Point to Reset Default Spreadsheet in the drop-down menu. The green check mark isremoved from the template.
This choice does not appear unless a custom template has been set as the default, as describedin the previous section.

28 | Chapter 5 Using Styles and TemplatesFigure 23: Resetting the default template for spreadsheets
You can also reset the default template as follows:
1) Go to the Templates dialog and open the category containing the template you set as
default. It is indicated by a green check mark.
2) Right-click on this template and select Reset Default in the context menu. The next timethat you create a new spreadsheet, it will be created using the Calc default template.

Note
Resetting the default template back to the Calc default template is only available
if the default has been changed previously.

Organizing templates
LibreOffice can only manage templates that are in its template folders (categories), although you
can create a spreadsheet from a template that is not in one of these folders. You can create newtemplate folders and use them to organize your templates and import templates into thesefolders. For example, you might have one template folder for sales templates and another for
invoice templates. You can also export templates.

Tip
The location of template folders in LibreOffice depends on the operating systemof your computer. If you want to know where the template folders are located, go
to Tools > Options > LibreOffice > Paths.
To begin, choose File > Templates > Manage Templates, or press Ctrl+Shift+N, to open the
Templates dialog.

Creating a template category
1) Click the Settings button in the lower left of the Templates dialog.
2) Click New Category in the context menu (Figure 25).
3) In the pop-up dialog (not shown), type a name for the new category and click OK. The
new category now appears in the list of categories.

Note
You cannot create a sub-category inside a template category.

Organizing templates | 29

Figure 24: Creating a new category

Deleting a template category
You cannot delete template categories supplied with LibreOffice. Nor can you delete categories
added by the Extension Manager unless you first remove the extension that installed them.
However, you can delete a category that you created:
1) Click the Manage button on the Templates dialog and select Delete Category in thecontext menu (Figure 24).
2) Select the category to be deleted in the Delete Category dialog (not shown) and click OK.
3) Select Yes in the confirmation dialog to confirm the deletion.
4) Calc displays a warning if it is unable to delete the category.

Moving a template
To move a template from one template category to another:
1) Open the Templates dialog and select the template you want to move.
2) Click the Move icon near the bottom of the dialog, select the destination category fromthe popup dialog (Figure 25), and click OK. The selected template is moved to the folder.
You can also create a new category into which to move the template.

Figure 25: Moving a template to another category

30 | Chapter 5 Using Styles and TemplatesDeleting a template
You cannot delete templates supplied with LibreOffice. Nor can you delete any templates
installed by the Extension Manager except by removing the extension that installed them. You
can only delete templates that you have created or imported.
1) In the Filter drop-down lists at the top of the Templates dialog, select the category that
contains the template you want to delete.
2) Right-click on the template that you want to delete to open the context menu and click
Delete (Figure 20).
3) A message box appears and asks you to confirm the deletion. Click Yes.

Note
Moving or deleting a template has no impact on any document that was createdfrom that template. The spreadsheet will retain the style definitions that it inheritedfrom the moved or deleted template. The Template field on the File > Properties> General page can continue to show the name of a template that no longer
exists.

Exporting a template
The easiest way to send a template to someone else, or to store it on a network, is to export it:1) Open the Templates dialog and find the template that you want to export.
2) Select the template and right click on it.
3) The Select Folder dialog opens. Navigate to the location where you want to export the
template and click Select Folder. You can also rename the template.

Note
Exporting a template does not remove it from the Templates dialog. The action
places a copy of the template in the location you specify.

Organizing templates | 31