Cells inside a spreadsheet can contain text or numerical data. Data in Calc can be formatted into various forms in order to denote the type of data and thus make cells more readable without changing the actual data values.

Text

The most basic data a cell can contain is simply text. To enter text inside a cell, simply select the cell and type the text using the keyboard. Text is left-aligned by default and can exceed the cell boundaries. This can be confusing to inexperienced users who mistakenly think the text is located to the adjacent cell. To check the actual contents of a cell select the cell and look at the input line in the Formula bar on the top of the window. A solution to this problem is to increase column width, force a new line or wrap text around the cell.

To force a new line of text (or a paragraph) use the CTRL + ENTER key combination. When a cell contains multiple lines of text you might find it more practical to use the input line text box of the Formula Bar. Click on the Extend Formula Bar icon located on the right of the Formula Bar and the text box becomes multi-line, as shown in the image above.

Numerical data

We use spreadsheets mainly to process, analyze and extract information from numerical data and create this way applications to help us with everyday personal, business and scientific tasks. Calc can format numerical data and change their appearance according the quantity they represent. The most basic data formats Calc supports are:

  • Integer numbers
  • Numbers with decimal places (real numbers)
  • Currency
  • Percent
  • Date and Time

For example you can control the number of decimal digits, the date and time format or display a currency symbol next to a value. In this way we help the user to better understand the information displayed in a spreadsheet while at the same time keeping the numerical value intact. Furthermore you can quickly change the formatting of any cell with just one command. The following table shows some examples of numerical data formatting.

Formatting using the toolbar

To quickly format a value entered in a cell to currency, percent, number, date or add decimal places use the corresponding buttons in the Formatting toolbar. Note that the default formatting is defendant on the locale settings. If for example you have an English UK locale setting, the currency symbol will be the English pound £ instead of the dollar sign.

Formatting with the Properties tab

For more data formatting options you can use the Number Format section in the Properties tab in the Sidebar.

The Format Cells window

In this window you have all the formatting options available. To open this window click the icon in the sidebar or use the context menu item Format cells. Choose the Category and the Format type. A preview of how data is displayed is shown on the right of the Cell formatting window. If you must use a formatting from a different locale change the Language in the drop down menu.

Date and Time

It is important to understand that Date and Time in Calc are stored and treated internally as numbers. However you can format the display of a date and time to one of the available formats. To enter a date you must separate the date elements with a slash (/) or a hyphen (–). The date format automatically changes to the selected format used by Calc. When you enter a time, separate time elements with colons, for example 10:43:45. The time format automatically changes to the selected format used by Calc. In both cases Calc recognizes the data you entered as date or time and not as simple text or numbers.

The following are valid date or time formats:

  • 20/10/2012
  • 20-10-2012
  • 20 Oct 2012
  • 10:43:45
  • 10:43 AM

Once you entered a date or time you can change the default format to one of the available formating options for date and time. Use the Format Cell window to list and preview the available formats.

Internally Calc stores dates and time as numbers. For example the 0 number represents the 30/12/1999 date, 1 represents 31/12/1999 and so on. This allows Calc to make calculations with dates.

Last modified: Friday, 13 April 2018, 1:22 PM