CCNC/CCNC Module 4/Formatting cells/Formatting cells containing numeric data

From WikiEducator
Jump to: navigation, search

Formatting cells containing numeric data

Most of the data presented in a spreadsheet are numeric, and these data are of various types.

Format cells to display thousands

Because it is difficult to read large numbers, Calc contains a feature that allows the user to separate the thousand part of a many-digit number.

Suppose we have the spreadsheet Sales.sxc on our screen as shown below.


  • Highlight the numbers to be formatted.
  • Format->Cells
  • Click the Number tab.
  • Select General.

You may find that Number and General are already highlighted since these are the default settings. However you should still check, since selections previously made may still be highlighted

  • Tick the Thousands separator check box.


  • Click OK.

The result is shown on the next screen.


To remove the thousands separator:

  • Repeat the above process, but untick the Thousands separator check box.

Format cells to display decimal places

You can also instruct Calc to display specific decimal places. In the screen above, this option is set to display 0 decimal places. However, you the user can change this. Observe the Decimal Places button under Options when next you open the Format Cells dialogue box. You can change the value either by typing in a new value or by clicking on the arrows next to the box to either increase or decrease the value. For example, you can opt to have one or two decimal places displayed. The Format code box gives you a preview of the form the number will take with the decimal places. Note that the numbers we practiced with earlier were all whole numbers, so the actual decimals will be zero, regardless of the number of decimal places specified, for example, 14, 562.0 or 14,562.00. In a later demonstration, with percentages, we will see examples of numeric data where the number generated includes a percentage with a value higher than zero.

Format cells to display different date formats

Calc is able to recognize any date format that you enter into a cell and to reformat it in several ways.

  • Type the entry 3/11/2003 into a cell in a blank spreadsheet.

Calc will interpret this as a date and reformat it according to its default date setting. So as soon as you press enter, the display of the cell above will become 3/11/03.

The input window on the Formula bar shows the actual data entry, while the cell illustrates how it has been reformatted.


Calc offers other formats for displaying the date. So you can select any format other than the default.

  • Select the cell to be reformatted.
  • Format->Cells
  • Click the Numbers tab on the navigation bar across the top of the window, that is, if it is not already selected.
  • Select Date in the Category window.
  • Select the date format that you wish to change to, in the Format window.


Click OK.

Icon present.gif
Tip: Suppose you selected the format that displayed the date as Tuesday, March 11, 2003. What do you think will happen when you click OK? What will you do to remedy that situation?

Format cells as currency style

To format numeric data as currency, we proceed as follows:

  • Select the range of cells to be formatted as currency.
  • Format->Cells
  • Click the Numbers tab.
  • Select Currency in the Category window.
  • Select a language in the Language window. This will select the appropriate currency symbol.
  • Select the desired format in the Format window.


  • Click OK.


Format cells to display numbers as percentages

The next screen shows the previous spreadsheet with the Gross Profit column filled in and the percentage profit calculated as a decimal fraction.


In situations such as this it is more common to express the numbers in E5:E8 as percentages.

  • Select the numbers to be formatted.
  • Format->Cells
  • Select the Numbers tab.
  • Select Percent in the category window.
  • Select the Number of decimal places in the Options area.
  • Set any other options as required.


  • Click OK.