Formatting data

From Apache OpenOffice Wiki
< Documentation‎ | OOo3 User Guides‎ | Calc Guide
Revision as of 20:49, 15 July 2018 by Sancho (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

The data in Calc can be formatting in several ways. It can either be edited as part of a cell style so that it is automatically applied, or it can be applied manually to the cell. Some manual formatting can be applied using toolbar icons. For more control and extra options, select the appropriate cell or cells, right-click on it, and select Format Cells. All of the format options are discussed below.

Documentation note.png All the settings discussed in this section can also be set as a part of the cell style. See Using Styles and Templates in Calc for more information.

Formatting multiple lines of text

Multiple lines of text can be entered into a single cell using automatic wrapping or manual line breaks. Each method is useful for different situations.

Using automatic wrapping

To set text to wrap at the end of the cell, right-click on the cell and select Format Cells (or choose Format > Cells from the menu bar, or press Ctrl+1). On the Alignment tab, under Properties, select Wrap text automatically.

Format Cells > Alignment dialog

The results are shown below.

Automatic text wrap

Using manual line breaks

To insert a manual line break while typing in a cell, press Ctrl+Enter. This method does not work with the cursor in the input line. When editing text, first double-click the cell, then single-click at the position where you want the line break.

When a manual line break is entered, the cell width does not change. The figure below shows the results of using two manual line breaks after the first line of text.

Cell with manual line breaks

Shrinking text to fit the cell

The font size of the data in a cell can automatically adjust to fit in a cell. To do this, select the Shrink to fit cell option in the Format Cells dialog. The figure below shows the results.

Shrinking font size to fit cells

Formatting numbers

Several number formats can be applied to cells by using icons on the Formatting toolbar. Select the cell, then click the relevant icon. Some icons may not be visible in a default setup; click the down-arrow at the end of the Formatting bar and select other icons to display.

Calc number formats
Number format icons. Left to right: currency, percentage, date, exponential, standard, add decimal place, delete decimal place.

For more control or to select other number formats, use the Numbers tab of the Format Cells dialog.

  • Apply any of the data types in the Category list to the data.
  • Control the number of decimal places and leading zeros.
  • Enter a custom format code.

The Language setting controls the local settings for the different formats such as the date order and the currency marker.

Format Cells > Numbers

Formatting the font

To quickly choose the font used in a cell, select the cell, then click the arrow next to the Font Name box on the Formatting toolbar and choose a font from the list.

Tip.png To choose whether to show the font names in their font or in plain text, go to Tools > Options > > View and select or deselect the Show preview of fonts option in the Font Lists section. For more information, see Setting Up and Customizing Calc.

To choose the size of the font, click the arrow next to the Font Size box on the Formatting toolbar. For other formatting, you can use the Bold, Italic, or Underline icons.

To choose a font color, click the arrow next to the Font Color icon to display a color palette. Click on the required color.


(To define custom colors, use Tools > Options > > Colors. See Setting up and Customising Calc for more information.)

To specify the language of the cell (useful because it allows different languages to exist in the same document and be spell checked correctly), use the Font tab of the Format Cells dialog. See Styles and Templates for more information.

Choosing font effects

The Font Effects tab of the Format Cells dialog offers more font options.

Format Cells > Font Effects

Overlining and underlining

You can choose from a variety of overlining and underlining options (solid lines, dots, short and long dashes, in various combinations) and the color of the line.


The strikethrough options include lines, slashes, and Xs.

Relief The relief options are embossed (raised text), engraved (sunken text), outline, and shadow.

Setting cell alignment and orientation

Some of the cell alignment and orientation icons are not shown by default on the Formatting toolbar. To show them, click on the small arrow at the right-hand end of the toolbar and select them from the list of icons.

Cell alignment and orientation

Some of the alignment and orientation icons are available only if you have Asian or CTL (Complex Text Layout) languages enabled (in Tools > Options > Language Settings > Languages). If you choose an unavailable icon from the list, it does not appear on the toolbar.

For more control and other choices, use the Alignment tab of the Format Cells dialog to set the horizontal and vertical alignment and rotate the text. If you have Asian languages enabled, then the Text orientation section shows an extra option (labeled Asian layout mode) under the Vertically stacked option, as shown below.

Asian layout mode option

The difference in results between having Asian layout mode on or off is shown below.

Types of vertical stacking

Formatting the cell borders

To quickly choose a line style and color for the borders of a cell, click the small arrows next to the Line Style and Line Color icons on the Formatting toolbar. In each case, a palette of choices is displayed. (If the Line Style and Line Color icons are not displayed in the formatting toolbar, select the down arrow on the right side of the bar, then Visible Buttons.)

For more control, including the spacing between the cell borders and the text, use the Borders tab of the Format Cells dialog. There you can also define a shadow. See Styles and Templates for details.

Documentation note.png The cell border properties apply to a cell, and can only be changed if you are editing that cell. For example, if cell C3 has a top border (which would be equivalent visually to a bottom border on C2), that border can only be removed by selecting C3. It cannot be removed in C2.

Formatting the cell background

To quickly choose a background color for a cell, click the small arrow next to the Background Color icon on the Formatting toolbar. A palette of color choices, similar to the Font Color palette, is displayed.

(To define custom colors, use Tools > Options > > Colors. See Setting up and Customizing Calc for more information.)

You can also use the Background tab of the Format Cells dialog. See Styles and Templates for details.

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools