What Is Percentage In Google Sheets?
Percentage in Google Sheets is a number format to display numeric values or results of mathematical calculations in terms of percentage followed by the percentage symbol ‘%’.
Percentage represents fractions conveniently and helps us visualize and understand the proportions w.r.t comparisons of values or quantities.
For example, the below table shows a number in cell A2. Let us change the value to a percentage.
- First, copy the cell value from A2 and paste it in cell B2.
- Next, choose cell B2 and click the “Format as percent” icon from the toolbar, as shown below.
- Finally, we will get the output shown below in percentage format.
Table of contents
Key Takeaways
- The Percentage in Google Sheets is a Format, not a function or a formula. It displays a number as a proportion of 100, followed by the ‘%’ symbol.
- Users can use the Percentage format to display fractional or decimal values conveniently. It is easy to understand when applied in graphs or reports as a percentage of income, profit, loss, finances, etc.
- We can access the Percentage format from the “Format” tab on the Menu Bar, the “Format as percent” iconon the toolbar or the other “More Formats” icon on the toolbar with the selection of the “Percent” option. window.
- This option is useful when the mathematical calculations involve percentage values. For which we can set the required custom Percentage before itself, or change the format later using the options we learned in the article.
Percentage() Google Sheets Formula
We do not have an inbuilt Google Sheets Percentage formula, as the Percentage is not a function or a mathematical expression, instead, it is a format that can be used to display results in percentage form.
How To Calculate Percentage In Google Sheets?
We can calculate the Percentage in Google Sheets in 2 ways, namely,
- Using the Percent Icon on the ribbon.
- Using the Menu bar.
#Method 1 – Percent Icon on the ribbon
Choose the required cells and click the “Format as percent” icon from the toolbar, as shown below.
#Method 2 – Using the Menu Bar –
First, select the “Format” tab à click the “Number” group right arrow → click the “Custom number format” option, as shown below.
The “Custom number formats” window opens. Here, select the required percent format and click the “Apply” button, as shown below.
Examples
Let us consider some to understand Percentage in Google Sheets examples.
Example #1
The below table contains the scores of 3 students in a class in four subjects. We will determine the aggregate of all subjects and the total percentage, up to two decimal places, secured by each student.
The steps to calculate and get the results in percentage format are as follows:
1: Select the target cell F2, enter the formula =SUM(B2:E2) and press Enter.
2: Using the fill handle, drag the formula from F2 to F4.
3: Next, select the target cell G2, enter the formula =F2/400, and press Enter.
4: Using the fill handle, drag the formula from G2 to G4.
5: Finally, select the cell range, G2:G4, and click the “Format as percent” icon to convert the results to percentage format, as shown below.
The output is shown above.
Example #2
The below table shows a set of items and their initial and final prices. We will update the loss or profit in their prices in column D. Then, show the values, whether negative or positive, using a customized percentage format.
The steps to perform the required calculations and get the results in percentage format are,
1: Select the cell range D2:D4 → select the “Format” tab →click the “Number” group right arrow →click the “Custom number format” option, as shown below.
2: The “Custom number formats” window opens. Here, type “00%;[Red]-0.00%” and click the “Apply” button, as shown below.
[Note: The specified custom percentage format shows negative percentages in red and up to two decimal places. On the other hand, the format will not apply to positive percentages, and they will display as it is.]
3: Select the target cell D2, enter the formula =(C2-B2)/B2, and press Enter.
4: Using the fill handle, drag the formula to cells D2:D4.
While all the target cells show the percentages in red, cell D3 is an exception. The reason is that the specified custom percentage format only applies to negative percentages and not to positive values.
Example #3
Let us consider the data given below of a person’s income, expenses and savings. Then, we will convert the savings into Percentage format to understand the proportion better.
Now, select cell B5, enter the formula =B4/B2 and press “Enter”.
We will get the output we see above. Now, select cell B5 and click the “Format as percent” icon on the toolbar to get the final result in percentage format, as shown below.
Example #4
Let us consider another example to change the format to percentage using another alternate method.
The below table contains different types of values. Let us change the value to a percentage and observe the changes for different values.
1: First, copy the cell values from A2:A8 and paste it in cells B2:B8, as shown below.
2: Next, choose cells B2:B8 and click the “More Formats à Percent” icon from the toolbar, as shown below.
3: Finally, we get the output we see below in percentage format.
Example 4 Output Observation:
- In cells B2, B6, B7 and B8, the whole numbers are converted to percentage with a pecentage symbol, as they got multiplied with 100.
- The cells B3 and B4 are untouched and displayed as the same because they are text or alpha-numeric values.
- The cell B5’s value hasn’t changed, as it is already in percentage format, just ignorable decimal points get added.
Important Things To Note
- To convert a value to Percentage in Google Sheets in a cell, make sure the cell contains a valid numeric value or a formula returning a valid number.
- We can enter the cell value and then apply the Percentage format to view it as a percentage or pre-format a cell with the Percentage format. When we pre-set it, the advantage is that if a number is entered or the result of a formula will appear in the Percentage format itself.
Frequently Asked Questions (FAQs)
There is no Percentage function in Google Sheets. However, we can apply the Percentage format in a required cell to show its value as a percentage or pre-set an empty or a result cell to display the typed or calculated result in the Percentage format, respectively.
The Percentage is calculated by dividing a part of the value by the total and then multiplying the result by 100.
(Part of the Total / Total) * 100
For example, if the amount of an item is $7500 and there is a $3000 discount, we can calculate the discount percentage using the formula as,
($3000/$7500) * 100 = 40%
In this example, the discount percentage is 40%.
We have seen 2 ways in the article to use Percentage in Google Sheets that are by using the “Format as percent” icon and with the help of the “Format” menu.
We can also find another alternate way on the Google Sheets toolbar which is used as follows:
• First, select an empty cell or the result cell, and click the “More formats” icon on the toolbar as shown below.
• Next, click the “Percent” option to get the percentage format, as shown below.
Few reasons that the Percentage in Google Sheets may be incorrect are,
a. The data format of the supplied value is Text or alphanumeric otherwise, we will get “#VALUE!” error.
b. The formula return value in the cell where we applied the Percentage format is invalid or inaccurate.
c. We did not apply the appropriate Percentage format and must have selected the wrong formats as decimals or something else.
d. The cell format is already Percentage, but we multiply the cell value by 100.
Download Template
This article must help understand the Percentage in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What is Percentage In Google Sheets. Guide to What is Percentage In Google Sheets. We learn how to format numeric data as percentages using different methods with examples. You can learn more from the following articles. –
Leave a Reply