Formatting cell data in spreadsheet software programs

Posted by Greten on 29 Mar 2020 under Tools

Spreadsheet software programs allow you to format data in several different ways, such as currency, percentage, date, text, and boolean, with more options under some of these. This entry will cover three widely-used spreadsheet programs: LibreOffice Calc, Microsoft Excel, and Google Sheets.

You can encode data into any cell in a spreadsheet, and the spreadsheet can recognize it and format it automatically. For example, when you type 12 March 2020 and press enter, the spreadsheet will detect it as a date and format it as such. The default format may vary among different spreadsheets and can be based on your local settings. The date as it appeared in the cell may also differ from how it appeared in the Input line (Formula Bar in Excel). You can then decide whether you want to keep this format or change it. The hint that the date you encoded is recognized as a date, which is a numerical data, is that it is automatically aligned to the right, but you can change its alignment later.

Another default format that you might want to change is for decimal numbers with trailing zeroes, say 4.250 instead of 4.25. Even though these are 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 piece of data obtained using a more accurate measuring instrument e.g., a micrometer caliper instead of a ruler. These small difference in format is vital in certain science subjects, such as chemistry and physics. If you type 4.250 in a cell, Calc automatically removes the trailing zero, and the only way to keep it or to put it back is to use manual formatting.

Thus, in constructing spreadsheet problem generators, there will be times when you need to force the spreadsheet program to follow a specific format different from its default.

Select the cells to be formatted

To select a single cell to be formatted, click on it or right-click on it. Clicking on it will move the cell pointer to it automatically. In Excel and Calc, right-clicking will also move the cell pointer to it and, at the same time, open an options menu that includes "Format Cells".

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.

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.

Access the format options

It is easier to type the data on the cells first before formatting them instead of formatting blank cells. The automatic default format might be what you want all along, and empty cells do not provide clues 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 options while the cell(s) is active.

Open the Formal Cells window in Excel and Calc

In Calc, open the Formal Cells window after you selected the cells. There are three ways of doing it.

• Right-click and select Format Cells. If you are selecting just one cell, right-click on it, and that cell will become active while at the same time. When the options menu appears, click "Format Cells". If you intend to format several cells, 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.
• On the top bar, click Format » Cells.
• Press CTRL + 1. Yes, that's just the regular numeric key 1, not F1 or some other special key.

In Excel, open the Formal Cells window after you selected the cells. There are two ways of doing it.

• Right-click and select Format Cells. The behavior of the cell selector and the options menu that appears on right-click is the same as that of Calc.
• On the ribbon, select Home (if it is not yet selected). Then, in the Number group, select the dropdown menu to see the predefined formats that you can immediately use. To open the Format Cells window, select More Number Formats at the bottom of the dropdown. The number group also offers limited number formatting below the drop-downdrop-down that allows you to format monetary values, format a number as a percentage, or change the number of decimal digits.

The Format Cell window 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.

Access the format options in Sheets

Google Sheets does not have a Format Cell window. To format, select a cell or the cells that you wish to format. Then, on top menu, select Format » Number and select from among the default formats under Number. At the bottom of the Number menu is the option More Formats. More Formats has three options under it: More currencies, More date and time formats, and Custom number format.

The options More currencies, More date and time formats, and Custom number format are the closest thing Sheets has that is similar to Format Cells windows of Excel and Calc. When you select any of these, a lightbox window will open, providing you with additional options in formatting currencies, date and time, and other numerical data.

Formatting the data in a cell

I will explain in detail each of the predefined format and custom formats that you can use and how you can alter them further. However, I cannot cram them in just one entry, so this will be the first of the post series that covers how to format data in spreadsheet programs.

Visit the following links to know more about formatting these data types. Note that as of today, 2020 March 29, I am in the process of mass revising these posts from covering only Calc to including Excel and Sheets. Some of the links below are not yet updated. This entry is based on the following spreadsheet versions: Microsoft Excel 2016, LibreOffice Calc 6.4.2, and the version of Google Sheets as of the date of this post.

• Formatting the number of digits
• Formatting the decimal and thousand separators
• Formatting percentage
• Formatting fractions
• Formatting scientific notations
• Formatting currency
• Formatting date and time (old post last update in 2013)

Last updated on 05 Dec 2020.

Share your thoughts

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