Formatting cell data in spreadsheet part IV: percentage

Posted by Greten on 19 Aug 2020 under Tools

The percentage is a numerical representation that comes with a percentage sign and denotes a part of a whole. It commonly represents quantities with a perceived maximum value, such as grades, capacity, and efficiency. The maximum value, taken as 100%, is numerically equal to one. Thus, 50% is equal to 0.5 or 1/2, and 20% is equal to 0.2 or 1/5. You can also have a decimal percentage value that is less than one; for example, 0.5% is equal to 0.005.

In computations, if cell A1 has 38, B1 has 50%, and you encoded =A1*B1 in C1, it will display 19.

This entry is part IV of the post series: Formatting cell data in spreadsheet software programs. The features and functions discussed in this entry are based on Calc 7.0.0, Excel 365, and the version of Google Sheets as of August 2020.

Formatting percentage in Calc

There are three ways you can format a number as a percentage in Calc:

• Enter a number in a blank cell and append a percent sign at the end of it. Calc automatically formats the number as a percentage with two decimal digits. If you type a percentage with more than two decimal digits, it rounds off. For example, type 1.555%, and the cell will show it as 1.56%. The Input line / Formula bar will still show 1.555%. You can bypass this restriction using the Format Cells window or the Format code.
• Select a cell or a group of cells, and click the Format as Percent icon. This icon is among the Formatting toolbar with the other number-related formatting icons. You can also press Ctrl+Shift+5 in lieu of clicking this icon. Numbers formatted in this manner also automatically acquire two decimal digits.

• Select a cell or a group of cells, and open the Format Cells window. You can open this window by right-clicking on a cell or any of the selected cells and selecting Format Cells. Another way of opening this window is by pressing Ctrl+1. When the Format Cells window open, select Percent under Category, work on other formatting option available if you need to, and select OK.

Formatting percentage in Sheets

There are four ways you can format a number as a percentage in Sheets:

• Enter a number in a blank cell and append it with a percent sign on its end. Sheets formats the percentage as you type it. If you include decimal digits, they become part of the format. If you type 50%, the cell shows 50%. If you type 50.00%, the cell shows 50.00%. The Formula bar will have 50% in both cases.  You can bypass this restriction using the Custom number formats.
• Select a cell or a group of cells, and click the Format as percent icon. You can find this icon with the other number-related formatting icons between the zoom dropdown and the font dropdown. Numbers formatted as a percentage in this manner acquires two decimal digits. If the number has three or more decimal digits, the number rounds off to two decimal digits.

• Select a cell or a group of cells. On the top menu, select Format » Number » Percent. This method has the same effect as selecting the Format as percent icon.
• Select a group of cells and open the Custom number formats. On the top menu, select Format » Number » More Formats » Custom number format. In the list, select a code that is appended by a percent sign. You can also append a percent sign to the current code if it contains only zeroes, hashes (#), decimal separator, or a combination of them.

Formatting percentage in Excel

There are three ways you can format a number as a percentage in Excel:

• Enter a number with a percent sign on the right end in a blank cell. Excel formats the percentage in two ways.
  • If you enter a whole number (that is, without a decimal point) and append it with a percent sign, then Excel formats it as a percentage without decimal digits, like 1%, 12%, 95%, 0r 120%.
  • If you entered a number with decimal digits, and then a percent sign, the decimal digits are always formatted to two digits. For example, typing 10.1% will show the cell containing 10.10% and typing 5.555% will show the cell containing 5.56%. The actual values without a trailing zero or rounding-off, still show in the Formula bar.

  You can bypass this restriction using the Format Cells window.

• Select a cell or a group of cells. Then, click the Percent Style icon or press Ctrl+Shift+5. You can find this icon with the other number-related formatting icons in the ribbon's Number group. Numbers formatted as a percentage in this manner are always rounded off to remove decimal digits; for example, 0.50 becomes 50%, and 0.05 becomes 5%, but 0.005 becomes 1%, which is 0.5% rounded-off. The value that is not rounded-off still shows in the formula bar.

• Select a cell or a group of cells, and open the Format Cells window. You can open this window by right-clicking on a cell or any of the selected cells and selecting Format Cells. When the Format Cells window opens, select Percentage under Category. Enter the number of decimal places you want to display and click OK.

Difference between the appending of percent sign and selecting percent icon

Appending the percent sign and clicking the percent icon (Format as Percent in Calc, Format as percent in Sheets, and Percent Style in Excel) are two of the fastest way of formatting a number or a cell as a percentage. However, they behave differently, and this difference is consistent with Calc, Sheets, and Excel.

• Appending a number with a percentage sign changes the value of a number. For example, you enter 25 in a cell. Its value is 25, and any computation that refers to its cell is going to treat it as 25. However, if you return to it later and append it with %, its value will change to 25% or 0.25. Formulae that refer to its cell will return results as if its value is 0.25.
• Selecting the percent icon changes the appearance of the number to keep its value. For example, you enter 25 in a cell. You move the cell pointer to this cell and click the percent icon. The number 25 will change to 2500% percent. If the number is 0.5, it will turn to 50%.

Using Format code to format percentage

The Format code for percentage can be any possible Format code for numbers followed by a percent sign. It could be #0.00%, or #,##0.0% or #%. You can also put space between the percent sign and the last hash if that's your preference. The default configuration of percentage is that it always has two decimal places, but you can alter it by changing the decimal places attribute or by editing the Format code.

You can customize the Format code of a percentage by opening the Format Cells window if you are using Excel or Calc, or Custom number formats if you are using Sheets.

• In Calc, right-click on a cell and select Format Cells or press Ctrl+1. When the Format Cells window opens, select Fraction under Category, enter or edit a code with a percent sign in the Format code field, and select OK.
• In Sheets, open the Custom number formats. On the top menu, select Format » Number » More Formats » Custom number format. Enter the format code with a percent sign on the top field and click Apply.
• In Excel, right-click on a cell and select Format Cells. Under Category, select Custom. Then, enter or edit the Format code with a percent sign in the Type field, and click OK.

Conclusion

You can enter and format percentages in spreadsheet applications Calc, Excel, and Sheets. In the cells, you can configure them to display a certain number of decimal digits and whether there is space between the number and the percent sign. Each spreadsheet application has its own default formats, but you can change it using the Format code. The Formula bar (or Input line in Calc) always displays the true value, preserving the number of decimal digits, or lack of it, as you originally typed the number, with no space between the number and the percent sign.

Last updated on 19 Aug 2020.

Share your thoughts

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