Formatting cell data in spreadsheet part II: number of digits
Posted by Greten on 25 Apr 2020 under Tools
Spreadsheet application has gone a long way from being something of a calculator in steroids to something a notch lower than a full-pledge programming language. However, many of us still use it for one thing, processing of numerical data.
If you're a teacher, spreadsheets are useful to compute the grades of your students quickly and to keep a record of these grades. You can also use spreadsheets to have several variations of the same kind of math or science problems.
This entry is part two of the series of formatting cell data in spreadsheet software programs. Just like in the first part, this entry also covers LibreOffice Calc, Microsoft Excel, and Google Sheets.
IMPORTANT: This entry assumes that you write numbers like in most English-speaking countries, with period as the decimal separator (hence, decimal point) and comma as thousand separators. I'll cover in another post if you need to change the symbols used as separators.
I reviewed this entry after writing, and I ended up using the word "number" several times in different contexts. Hence, I will use the term "data" or "numerical data" to refer to numbers that you enter on the cells of a spreadsheet, "Number" to something that appears in a user interface, and "number" for a quantity of something, e.g., type a certain number of zeroes.
This entry is based on the following spreadsheet versions: Microsoft Excel 2016, LibreOffice Calc 6.4.3, and the version of Google Sheets as of the date of this post.
General and Automatic formats
In Excel and Calc, the default number format is called General. In Sheets, the default format is called Automatic. When you type a piece of data in any of the cells that you didn't format before and then press Enter (or click outside the cell), the following happens:
- The numerical data automatically aligns to the right.
- Leading zeroes are removed.
- Trailing zeroes are removed.
- Aside from the aforementioned leading and trailing zeroes, the spreadsheet retains the numerical data as you type it
The General or Automatic format remains if you type only numerals, decimal separators (decimal point), and hyphens for negative sign. If you type some other symbol such as $ or %, the format might change to currency or percentage, respectively; Calc, Excel, and Sheets behave differently with regards to this.
General format in Calc
Calc and Excel indicate the General format differently. In Calc, when you select the General, right-click, and open the Format Cells window, you will see that the General format is under the Number category.
There are also Options and Format code fields that allow you to change the format. Modifying any of these fields will turn the number format into something else other than General; one of the number formats listed below the General will be highlighted. If the change you made does not match any of the number formats listed below the general, a new format will appear at the bottom of the list, and that one will be highlighted instead.
General format in Excel
In Excel, there are two ways you can immediately tell if the number format is General. First, select the cells that contain numerical data. Then, go to the Home tab if you're currently not there. In the ribbon's Number group, the dropdown menu will display General if the selected cells are in General format.
The other option is similar to Calc: select the cell, right-click, and open the Formal Cells window.
Unlike in Calc, the General format in Excel is an independent category and not a category under Number. You cannot change the format of a General data unless you change its Category to one of those listed under General.
Automatic format in Sheets
The equivalent of General format in Sheets is called Automatic. To find out if a number is formatted as Automatic, select the cells containing numerical data.
Then, select Format on the top menu. Hover the mouse pointer to Number to see the different number formats. The first submenu under Number is Automatic. The selected cell is formated as Automatic if the Automatic submenu has a check on the left.
Assign the number of decimal places
If you have a set of two or more numerical data obtained from the same source, it is easier to compare them and run them through mathematical calculations if they have the same number of decimal places. For example, a set of measurements obtained using the same measuring instrument must be written with the same significant figures on the decimal side to reflect the accuracy of the measuring instrument.
When you specify a cell to have a certain number of decimal places, any piece of numerical data entered in that cell will have the same number of digits on the right of the decimal separator.
- Trailing zeroes will simply replace any decimal digit that you did not enter.
- If the piece of numerical data you entered has more decimal digits than the specified decimal places, the spreadsheet is going to round off the data to retain the decimal places.
For example, you format a cell to have three decimal places. You entered 5.5. The cell will display it as 5.500. You changed it to 5.54. The cell will display it as 5.540. You changed it again, this time to 5.548. The cell will display it as 5.548. Lastly, you changed it to 5.5489. Since the digits already exceeded the specified decimal places, the spreadsheet is going to round it off to 5.549.
The adjustment of decimal places is very convenient if you are adding several data of the same kind. It looks neat if the decimal separators are aligned. Since numerical data tend to align to the right by default, a column of cells formatted with the same number of decimal places will have the same place values align vertically.
The zero(es) added to the right to maintain the number of decimal places is called trailing zero. Trailing zeroes do not change the value of a number; 5.540 is the same as 5.54. The use of trailing zeroes is to show the correct significant figures.
Specify decimal places in Calc
To specify the number of decimal places in Calc, select the cell or cells you want to format. Then, open the Format Cells window (by right-click or Ctrl+1).
Look at the Options section of the Numbers tab. Numerical data with General format have a blank Decimal places field. Enter the number of decimal digits that you want.
Specify decimal places in Excel
To specify the number of decimal places in Excel, select the cell or cells you want to format. Use the Increase Decimal and Decrease Decimal icons in the Number group of the ribbon. You will find these icons near the $ and % icons.
You should already have a piece of numerical data in the selected cell, or at least one among the selected cells, to see the change in the number of decimal places as you click these icons until you reach the number of decimal places that you want. You cannot format a blank cell to have specific decimal places in this manner unless it's among the several cells selected.
The default display of dropdown menu automatically changes to Number when you start changing the decimal places.
You can also enter the number of decimal places instead of clicking until you reach the decimal places that you want. After selecting the cells, you want to format, open the Format Cells window (by right-click or Ctrl+1).
Under the Number tab, change the Category from General to Number. Several options are going to appear on the right, among them are the Decimal places field. The default number of decimal places is 2. Delete it and enter the new number of decimal places that you want. You can also use the small up and down arrow buttons on the left of the field to increase or decrease the number of decimal places.
You can use the Format Cells window to change the number of decimal places of a blank cell.
Specify decimal places in Sheets
To specify the number of decimal places in Sheets, select the cell or cells you want to format. Use the Increase decimal places and Decrease decimal places icons near the top; they are between the Zoom and Font dropdown menus.
Similar to Excel, you cannot use these icons to format a blank cell. However, if you selected multiple cells, if at least one of them has a piece of data, then the blank ones are also formatted.
Another option, which can work in both blank cells and cells with data, is to use the Format menu. If you need only two decimal places, select Format » Number » Number.
If you need a number of decimal places other than two, select Format » Number » More Formats » Custom number format. When the Custom number formats lightbox open, select 0.00 or #,##0.00 (the difference between these two options is whether you want to have a thousand separator or not). Then, change the number of zeroes on the right of the decimal separator to the number of decimal points that you want. For example, change to 0.000 if you intend three decimal places.
Assign the number of leading zeroes
A leading zero is a zero on the left side of the first non-zero digit of a whole number. Like trailing zeroes, leading zeroes do not change the value of a number; 3 is equal to 03 and 003.
The purpose of leading zeroes is formatting consistency; leading zeroes are commonly used in invoice numbers, student or employee ID numbers, and any other big collection of records where a unique number identifies each individual.
The title of this section is a misnomer. You cannot assign the number of leading zeroes, although I called it such because that's what's the Calc's user interface calls it. What you can assign is the number of whole number digits. If the digits required to represent a piece of numerical data is lower than the assigned number of digits, leading zeroes take the place of those digits. If the digit required to represent a piece of numerical data is larger than the assigned number of digits, then the spreadsheet will show that piece of data with more digits than assigned.
Look at the spreadsheet below. The cells are formatted to show four digits whole number.
Specify the leading zeroes in Calc
To specify the number of whole number digits in Calc, select the cell or cells you want to format. Then, open the Format Cells window.
The number of digits is on the text field labeled as "Leading zeroes" in the Options section of the Numbers tab. The default number is 1, which means you will never see a leading zero unless you entered a decimal number between zero and one.
To specify the number of whole number digits, replace 1 with your preferred number of digits. Then, select OK.
When you set the Leading zeroes (actually, the whole number digits) of a cell to something other than 1, Calc will not accept a blank for Decimal places. When you select OK, it will automatically become 0. If the cells you selected have numerical data with decimal digits, those digits will disappear from the view, and your data will round-off to whole numbers.
You may, however, change the number in Decimal places field after you changed the one in Leading zeroes to prevent your decimal places from disappearing. Doing this will keep your numerical data with the specified number of digits on both the whole number and the decimal sides, with trailing and leading zeroes added as necessary.
Specify the leading zeroes in Excel
To specify the number of whole number digits in Excel, select the cell or cells you want to format. Then, open the Format Cells window.
From the Category, select Custom. Then, select either 0 or 0.00 depending on whether you want to round off your numerical data to whole numbers or if you want to show decimal places, respectively. Enter a number of zeroes in the type field equal to the number of digits that you want to display.
For example, you want to display four digits like 0005, 0024, 0186, or 9371. Then, enter 0000 in the Type field.
If you want to keep a number of decimal places, then enter that number of zeroes on the right of the decimal separator. For example, you want to format your numbers to have four whole number digits and three decimal places, enter 0000.000 in the Type field.
Specify decimal places in Sheets
To specify the number of whole number digits in Sheets, select the cell or cells you want to format. Then, select Format » Number » More Formats » Custom number format.
When the Custom number formats lightbox opens, select either 0 or 0.00 depending on whether you want to round off your numerical data to whole numbers or if you want to keep some decimal places, respectively.
Enter the number of zeroes that is equal to the number of whole number digits you want to show in the Custom number formats field. If you also want to show decimal places, then enter the number of zeroes on the left side of the decimal separator for the number of whole number digits, and the number of zeroes on the right side of decimal separator for the decimal places. The Custom number format field works in the same way as the Type field in Excel.
You can format the number digits in spreadsheet cells. Limiting the number of digits of the decimal places rounds off the numerical data or adds trailing zeroes as needed. Limiting the number of whole number digits adds leading zeroes or use more than the assigned number of digits only when necessary for large numbers. This is how it works in Calc, Excel, and Sheets.
My original plan for this entry is to include the thousand separators, decimal separators, local settings, and number format codes, because that's how it was covered in the old version of this post that focuses only on Calc. However, it is getting much longer than the usual length of posts for this blog. Thus, I will cover them in the next entry.
Last updated on 25 Apr 2020.
Learn from others
Share your thoughts
* Required. Your email will never be displayed in public.