Seemanto Saha
We often use percentage values in our calculations. It makes our calculations easier and helps us compare one quantity with another. In this article, we’ll discuss 4 simple examples to learn how to calculate any percentage values in Google Sheets.
A Sample of Practice Spreadsheet
You can download our practice spreadsheets by clicking on the following link. The spreadsheets contain an overview of the datasheet and an outline of the described ways of how to calculate percentage in Google Sheets.
What Is Percentage?
The percentage can be defined as an amount, proportion, or share in relation to a larger amount. We can calculate a percentage value using the following formula-
4 Ideal Examples to Calculate Percentage in Google Sheets
We can calculate percentages for several applications. Here, we’ll discuss 4 of such applications with examples.
1. Calculating Percentage of a Grand Total
Perhaps, the most common use of percentage values is to calculate the percentage of a grand total for any value. Here, we’ll calculate the percentage of sales for each employee for the following dataset.
- Since we’ll calculate the percentage of the grand total, let’s first calculate it using the SUM function. For that, select Cell C12 and then type in the following formula:
=SUM(C5:C11)- Then, press the Enter key to get the grand total. Now, let’s calculate the percentages. We’ll discuss two methods.
Read More: Google Sheets Calculated Field Percentage of Total in Pivot Table
1.1 Using Built-In Percentage Format Menu
This method will use the built-in percentage format menu to convert the calculated fractions into percentages.
Steps:
- First, select Cell D5 and then, type in the following formula-
=C5/$C$12- After that, press the Enter key to get the required value.
- Now, selecting the cell with the calculated fraction, click on the Format as Percent option.
- The value has now been formatted as a percentage. You can use use the built-in Increase or Decrease Decimal Places option to increase or decrease decimal places. We’ll keep only one decimal place.
- Now, hover your mouse pointer above the selected cell. The Fill Handle icon will be visible.
- Use the Fill Handle icon to copy the formula and format it to other cells.
Read More: How to Calculate Percentage Increase in Google Sheets (4 Ways)
1.2 Applying TO_PERCENT Function
We can also use the TO_PERCENT function in lieu of the built-in Format as Percent option. The TO_PERCENT function can convert a provided number into a percent format.
Steps:
- Select Cell D5 first.
- Afterward, type in the following formula-
=TO_PERCENT(C5/$C$12)- Then, press the Enter key to get the required percentage.
- Finally, use the Fill Handle icon to copy the formula to other cells in Column D.
Read More: How to Use TO_PERCENT Function in Google Sheets
2. Determining Percentage of a Proportion
We can use the percentage format to depict what portion of a task has been completed. Here, we’ll determine the percentage of actual sales as a proportion of the target sales for the following dataset.
Steps:
- First, select Cell E5.
- After that, type in the following formula-
=TO_PERCENT(D5/C5)- Then, press the Enter key to get the required percentage.
- Finally, use the Fill Handle icon to copy the formula in the other cells of Column E.
3. Enumerating Percentage Change
We can also calculate the changes (i.e. the increase or decrease) of percentage in values across columns and rows or based on fixed cells as percentage values. Read through the following 3 examples.
3.1 Calculating Percentage Change Between Two Columns
First, we’ll calculate the sales percentage change between two columns for the following dataset. The dataset contains sales values for two consecutive months for several employees. Now, let’s calculate the percentage change for each employee.
Steps:
- To start, select Cell D5 first.
- Then, type in the following formula-
=TO_PERCENT((D5-C5)/D5)- Next, press the Enter key to get the required percentage.
- Now, to end, use the Fill Handle icon to copy the formula in other cells.
Read More: How to Calculate Percentage Change in Google Sheets (4 Ways)
3.2 Determining Percentage Change Between Two Rows
Now, let’s determine the percentage change between two rows for the following dataset which contains the sales of a company for each month of the year. Since we require two values to calculate a percentage, we can not calculate any output in Cell D5.
Steps:
- First, select Cell D6.
- Afterward, type in the following formula-
=TO_PERCENT((C6-C5)/C5)- Next, press the Enter key to get the required percentage.
- Finally, use the Fill Handle icon to copy the formula in the other cells of Column D.
Read More: How to Change Pie Chart Percentage to Number in Google Sheets
3.3 Calculating Percentage Change Based on a Fixed Reference Cell
Finally, let’s demonstrate how to calculate percentage change based on a fixed reference cell in Google Sheets for the following dataset. We’ll calculate the percentage of saving based on the fixed allowance.
Steps:
- To start, select Cell D5.
- Next, type in the following formula-
=TO_PERCENT(($D$10-C5)/$D$10)- Then, press the Enter key to get the required percentage.
- Now, to end, use the Fill Handle icon to copy the formula in the other cells of Column D.
4. Calculating Percentage Based on Criteria
We can also calculate percentages based on certain criteria in Google Sheets using the PERCENTIF function. For the following dataset, we will determine the percentage of employees who has a higher sales value in February than in January.
Steps:
- First, select Cell E13 and then, type in the following formula-
=PERCENTIF(E5:E11,“>0%”)- Finally, press the Enter key to get the required percentage.
How to Calculate Average Percentage in Google Sheets
The percentage values for an average help us represent the data much more efficiently. Now, let’s calculate the average percentage for the following dataset which contains marks attained by several subjects in 3 different subjects.
Steps:
- Select Cell F6 first.
- Then, type in the following formula-
=TO_PERCENT(SUM(C6:E6)/300)- After that, press the Enter key to get the required percentage.
Formula Breakdown
- SUM(C6:E6)
First, the SUM function calculates the summation of each number in the range C6:E6.
- TO_PERCENT(SUM(C6:E6)/300)
Next, after the returned value by the SUM function is divided by the value 300, the TO_PERCENT function converts it into percentage format.
- Finally, use the Fill Handle icon to copy the formula in the other cells of Column F.
How to Make a Percentage Calculator in Google Sheets
We can apply a simple formula using the IFFERROR function to create a percentage calculator in Google Sheets.
Steps:
- First, create a data table like the following in Google Sheets.
- Now, type in the following formula in Cell D5–
=IFERROR(B5/C5,“”)- Afterward, click on the Format as Percent option to format the value in Cell D5 as a percentage.
- Now, as soon as you enter values in Cell B5 and C5, you will get your required percentage value.
Read More: How to Make a Percentage Chart in Google Sheets (3 Ways)
Things to Be Considered
- Certain cells have been used for the entire column in our calculations. While applying the formula, remember to fix the reference for those cells.
Conclusion
This concludes our article to learn how to calculate percentage in Google Sheets. I hope the demonstrated examples were sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.
