Seemanto Saha
A percentage value can be defined as a sum, proportion, or part in relation to a larger sum. In Google Sheets. We often deal with percentages by changing the cell format. Still, we can use the TO_PERCENT function to calculate percentage values without changing the format of the cell which means it will return the output directly as a percentage. The TO_PERCENT function is an exclusive function for Google Sheets since it is not available in MS Excel. However, in this article, I’ll discuss examples of 6 easy applications of the TO_PERCENT function.
A Sample of Practice Spreadsheet
You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the described applications of the TO_PERCENT function in Google Sheets.
What Is TO_PERCENT Function in Google Sheets?
The TO_PERCENT function is used for converting a number to a percentage.
Syntax
The syntax of the TO_PERCENT function is as follows:
=TO_PERCENT(value)
Argument
There is only one argument in the TO_PERCENT function and it is as follows:
| Argument | Requirement | Function |
|---|---|---|
| value | Required | The value or reference to a cell that will be converted to a percentage. |
Output
The formula TO_PERCENT(0.2) will show 20% as output.
6 Easy Examples to Use TO_PERCENT Function in Google Sheets
We can use the TO_PERCENT function to calculate percentage values in numerous applications. Here, we’ll discuss examples for 6 of the most common applications with suitable datasets.
1. Calculating Grand Total Percentage
Calculating a percentage of a grand total is perhaps the most frequent use of the TO_PERCENT function. We’ll determine each employee’s salary as a percentage of the total salary for the dataset below.
Steps:
- Firstly, we have to calculate the grand total of the employee salaries.
- For that, select Cell C12 and then type in the following formula-
=SUM(C5:C11)- After that, press Enter key to get the total of salaries.
- Next, select Cell D5 and type in the following formula-
=TO_PERCENT(C5/$C$12)- Then, press the Enter key to get the required percentage value.
- Now, select Cell D5 again and hover your mouse pointer above the right-bottom corner of the selected cell to make the Fill Handle icon visible.
- Use the Fill Handle icon to copy the formula in other cells of Column D.
Read More: Google Sheets Calculated Field Percentage of Total in Pivot Table
2. Enumerating Proportional Percentage
We can use the TO_PERCENT function to calculate percentage values that depict the proportion a value takes from a larger amount. Here, we’ll enumerate what proportion of the salary every employee spends on their house rent.
Steps:
- Select Cell C5 first.
- Then, type in the following formula-
=TO_PERCENT(D5/C5)- Afterward, press Enter key to get the required value.
- Finally, use the Fill Handle icon to copy the formula to other cells.
Read More: How to Calculate Percentage in Google Sheets (4 Ideal Examples)
3. Determining Increase or Decrease of Percentage
We can also use the TO_PERCENT function to 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 the following 3 examples.
3.1 Computing for a Fixed Reference Cell
First, we’ll compute percentage change based on a fixed reference cell for the following dataset. The dataset contains the monthly revenue of a company. We’ll compute the net profit margin as a percentage value based on the fixed total cost.
Steps:
- Firstly, select Cell D6.
- Afterward, type in the following formula-
=TO_PERCENT((C6-$D$4)/C6)- Then, press Enter key to get the required net profit margin.
- Finally, use the Fill Handle icon to copy the formula in other cells.
Read More: How to Calculate Percentage Increase in Google Sheets (4 Ways)
3.2 Applying Between Two Columns
Now, let’s calculate the increase or decrease of percentage values between two columns for the dataset below. The dataset contains prices of several novels in two consecutive years. We’ll determine the change in price as a percentage value using the TO_PERCENT function.
Steps:
- Select Cell E5 first and then type in the following formula-
=TO_PERCENT((D5-C5)/C5)- After that, press Enter key to get the required percentage change.
- Finally, use the Fill Handle icon to copy the formula to other cells in Column E.
Read More: How to Calculate Percentage Change in Google Sheets (4 Ways)
3.3 Executing Between Two Rows
Finally, we’ll use the TO_PERCENT formula to calculate the yearly increase or decrease of the profit values for several years for the dataset below. Since we require two values to calculate a change, we can not calculate any output in Cell D5.
Steps:
- In the beginning, select Cell D6.
- Afterward, type in the following formula-
=TO_PERCENT((C6-C5)/C5)- Then, press the Enter key to get the required percentage of change.
- In the end, use the Fill Handle icon to copy the formula to other cells.
4. Computing Average Percentage
We can describe the data much more effectively by using percentage values for an average. Let’s now use the TO_PERCENT function joined with the SUM function to compute the average percentage for the dataset below, which contains marks obtained by several students across three distinct subjects.
Steps:
- Firstly, select Cell F6.
- Then, type in the following formula-
=TO_PERCENT(SUM(C6:E6)/300)- After that, press Enter key to get the required average percentage value.
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)
Then, 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.
Read More: How to Make a Percentage Chart in Google Sheets (3 Ways)
5. Applying for Calculating Discount
We can enumerate the discount percentage from the actual price value and discounted price value. Here, we’ll use the TO_PERCENT function to establish a formula to determine the discount percentage for the dataset below.
Steps:
- Firstly, select Cell E5.
- Afterward, type in the following formula-
=TO_PERCENT((C5-D5)/C5)- Then, press the Enter key to get the required discount percentage.
- Finally, use the Fill Handle icon to copy the formula to other cells of Column E.
Read More: How to Subtract a Percentage in Google Sheets
6. Combining ARRAYFORMULA and TO_PERCENT Functions
Instead of using the Fill Handle icon to copy our formula in other cells, we can use the ARRAYFORMULA function to perform similar operations for a range. Here, we’ll determine the percentage of a grand total for a range by combining the ARRAYFORMULA and TO_PERCENT functions for the dataset below.
Steps:
- First, select Cell D5.
- Afterward, type in the following formula:
=ARRAYFORMULA(TO_PERCENT(C5:C12/C12))- Finally, press Enter key to get the required value as an array at once instantly.
Formula Breakdown
- TO_PERCENT(C5:C12/C12)
The TO_PERCENT function converts the calculated values to percentage format.
- ARRAYFORMULA(TO_PERCENT(C5:C12/C12))
Here, the ARRAYFORMULA function helps the non-array function TO_PERCENT to deal with an array and display the array.
Things to Be Considered
- The TO_PERCENT function can take only one input at a time.
- 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 use the TO_PERCENT function in Google Sheets. I hope the demonstrated applications were helpful to you. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.
