Formatting cell data in Calc spreadsheet

Posted by Greten on 13 Apr 2013 under Tools

The LibreOffice/OpenOffice Calc allows you to format data in a certain way. The easiest and most obvious way to do this is by right-clicking on the cell that you want to format and click the "Format Cells" option. This allows you to select among several different formats: currency, percentage, date, etc. with more options under each of these.

Aside from this formatting capability, Calc also detects the what you encoded in a cell and automatically formats it. By typing a string made only of letters, Calc detects it as a text and format it as such. A text format is something that cannot be subjected to mathematical calculations. By typing 12 April 2013 in a cell and pressing enter, Calc detects and format it as date.

The manual formatting (that is right-clicking and selecting "Format Cells") is useful because Calc has default format configuration and changes the format into its default even if that is not the way you encoded it. Going back to our example, when you type 12 April 2013 and press enter, Calc will detect it as a date and change it to its default date format of 04/12/13. You need to format the cell if this is not the date format that you want.

Another default formatting that you might want to change is writing a decimal number with zero at the end, say 4.250 instead of 4.25. Even though this is just two different ways of writing the same number, they convey different information. The former has more significant figures and the extra zero indicates that it is a data obtained using a more accurate measuring instrument with smaller scales e.g., obtained using a micrometer caliper instead of ruler. If you type 4.250 in a cell, Calc automatically removes the trailing zero, and the only way to keep it is to use manual formatting.

Thus, in constructing spreadsheet problem generators, you might need to force Calc to follow certain format different from its default.

Selecting the cells to be formatted

To select a single cell to be formatted and just click on it or right-click on it. Clicking on it will move the cell pointer to it automatically. Right-clicking will also move the cell pointer to it and at the same time, open an option window which includes "Format Cells" among others.

selecting multiple cells by dragging the mouse

To select several adjacent cells, click on one of the cells, hold the mouse button and drag the mouse pointer across until you selected the cells that you need. Then, release the mouse button. This method allows you to select a group of cells that form a large square or rectangle.

selecting by holding control key and clicking each cell

To select several non-adjacent cells or a group of cells in a non-rectangular formation, hold the CTRL button and click on the cells that you want to select one-by-one.

Opening the Format Cell option window

It would be much easier if you type the data on the cells first before formatting them instead of formatting a blank cell. You can easily tell in what way you want the data to be formatted, and blank cells usually don't provide any clue that they have been formatted in some ways.

After selecting the cell(s) that you want to format, you need to open the Format Cell option window while the cell(s) is active. There are three ways of doing it.

  1. The aforementioned right-click. If you are selecting just one cell, just right-click on it and that cell will become active while at the same time, an option window will appear. Click "Format Cells". If you intend to format more than one cells and already selected them, right-click on one of those you already selected. If you right-click on a cell that you did not select, the selection will disappear and that cell will become the active cell.
  2. On the top bar, click Format ยป Cells.
  3. Press CTRL + 1. Yes, that's just the regular numeric key 1, not F1 or some other special key.

opening format cell option window by right-clicking a cell

The Format Cell option window will then open. It has all the formatting options including layout and font. Our concern however, is the data type. To access the formatting options of data type, click on the "Number" tab on top of the Format Cell option window. Of course. no need to click on it if it's already the active tab.

The format cell window of Calc showing the different options through which you can manipulate the format of data in the cells

Formatting the data in a cell

I will explain in details each of the possible predefined format that you can use and how you can alter them further. However, explaining most of them will require long explanation and thus I moved them to other posts which is listed at the bottom.

formatting spreadsheet cell data in CalcFor now, I will address first those that I will no longer explain. The All category simply list all the possible predefined format that you can select including those that are under each of those categories listed below it. The User-defined category will require typing certain symbols, which we will understand better after discussing the predefined formats. Boolean has value of True or False, but we will not use them now. When formatted as text category, the spreadsheet will accept whatever is encoded in that cell without trying to format it, but you would not be able to use that cell as part of mathematical computation.

Visit the following links to know more about formatting these data types:

Last updated on 29 Apr 2013.

Share your thoughts

* Required. Your email will never be displayed in public.

Instructional design and educational technology for effective learning