Google Sheets Percentage Formula (5+ Easy Examples)

Video how to get a percentage in google sheets

Expressing quantities in percentages can provide a better sense of proportion and make comparisons easier. From sales tax to bank interest rates, percentages are a common way to represent these quantities. In this guide, we will explore the Google Sheets percentage formula and its applications.

Learn More Before You Start

Before diving into the tutorial, it’s recommended to go through structured learning. Udemy offers excellent courses on Google Sheets and the entire Google Suite. These courses will help you master the skills and formulas needed for effective data processing.

Understanding Percentages

The term “percent” or “percentage” refers to one part out of a hundred. Instead of expressing a ratio as a fraction, we use percentages to provide a common baseline and visualize differences in quantity proportions.

Why Use the Google Sheets Percentage Formula?

The percentage format in Google Sheets is extremely useful for expressing values in percentages. By applying this format, not only will a percentage sign be added next to the value, but the number itself will be converted into a percentage. For example, formatting the fractional number 0.15 with the percentage format will automatically convert it to 15%.

To format a number as a percentage in Google Sheets, select the cell and click on the ‘%’ button in the toolbar.

Formatting percentages makes spreadsheets easier to read and understand, as demonstrated in the example above. Now, let’s explore how to calculate percentages in practical situations.

How to Automatically Format as Percentage

Instead of manually formatting decimals as percentages using the toolbar, you can use the TO_PERCENT function in Google Sheets. This function automatically converts a decimal to a percentage.

For example, if you have a decimal value in cell C2 and want to convert it to a percentage, you can use the formula =TO_PERCENT(C2/B2).

How to Calculate Percentage Using *100

Another way to calculate percentages in Google Sheets is by multiplying the value by 100. This method is useful when you want to display the number as a percentage without the percentage sign.

To calculate a percentage using 100, use the formula `=(Value/total number)100. For example, if you want to calculate the percentage of a value in cell C2 out of the total in cell B2, you can use the formula=(C2/B2)*100`.

In this case, the number won’t show the percentage sign, but it has already been converted to a percentage by multiplying it by 100.

Example Spreadsheet

To better understand how to calculate percentages in different scenarios, you can make a copy of our example spreadsheet.

How to Calculate Percentage of a Proportion

One common application of percentages is calculating the percentage of a proportion. This involves determining the portion of a task that belongs to a certain category.

For example, let’s say you have the sales target allotted for each employee in column B, and the sales made by them in column C. To calculate what percentage of their allotted target each person has achieved, you can use the formula Sales Made / Allotted Target * 100.

To apply this formula, follow these steps:

Select cell D2, where the first row’s result will be displayed.
Enter the formula =C2/B2.
Press the ‘Format as percent’ button (%) in the toolbar or navigate to Format->Number->Percent from the menu bar to convert the result to a percentage.
Drag down the fill handle to copy the formula to the rest of the cells in column D.
If the results include decimal places, you can choose to remove them to round the percentage to the nearest whole number.

Now you have a list of percentages representing the sales made by each employee. This allows you to easily identify the most efficient employees in achieving their targets.

How to Calculate the Percentage of Total

Another scenario is when you have the sales made by each employee and want to determine what percentage of the total sales each employee has contributed. This helps evaluate individual performance.

To calculate the percentage of total sales made by each employee, use the formula Sales made by the employee / Total sales * 100. The total sales made by all employees can be calculated using the SUM function.

For example, assuming the sales made by each employee are in column B, and you want to display the percentages in column C, follow these steps:

Calculate the total sales by using the SUM function and displaying the result in cell B7. The formula for the total is =SUM(B2:B6).
Select cell C2, where the first row’s result will be displayed.
Enter the formula =B2 / $B$7.
Press the ‘Format as percent’ button (%) in the toolbar or navigate to Format->Number->Percent from the menu bar to convert the result to a percentage.
Drag down the fill handle to copy the formula to the rest of the cells in column C.
If the results include decimal places, you can choose to remove them to round the percentage to the nearest whole number.

Now you have a list of percentages representing the contribution of each employee to the total sales. This allows you to determine which employees have made the most or least contributions.

Explanation of the Formula

In the previous case, you may have noticed that we used a similar formula. However, we made a slight difference by using an absolute reference to the total sales made (cell B7).

The dollar sign ($) is used to indicate an absolute reference, ensuring that every cell value in column B is divided by the same cell value (B7). This way, every new result in column C is calculated based on the sum in cell B7: =B2/$B$7.

If you sum up all the percentages in column C, you should get a total of 100%, which makes sense.

How to Calculate Percentage Change

Once you have the percentages of an employee’s sales calculated over a period, you can easily monitor their performance and determine if their percentage increases or decreases.

To calculate the percentage change in sales between two periods, use the formula (New Value - Old value) / Old value * 100.

For example, assuming the sales made by an employee in week 1 are in column B and the sales in week 2 are in column C, follow these steps:

Select cell D2, where the first row’s result will be displayed.
Enter the formula =(C2-B2)/B2.
Press the ‘Format as percent’ button (%) in the toolbar or navigate to Format->Number->Percent from the menu bar to convert the result to a percentage.
Drag down the fill handle to copy the formula to the rest of the cells in column D.
If the results include decimal places, you can choose to remove them to round the percentage to the nearest whole number.

Now you have a list of percentage increases or decreases in each employee’s sales over a one-week period. This allows you to analyze and compare their performance over time.

How to Calculate Percentage Based on Criteria

Calculating percentages based on specific criteria is another useful application. To achieve this, combine the PERCENTIF function with the IF function.

The PERCENTIF syntax is as follows: =PERCENTIF(Range, Criterion).

Range: The data set to be converted to a percentage.
Criterion: The argument used to filter data from the range. It can be a cell reference, a value in quotation marks, or logical expressions such as greater than (>) and less than (<).

For example, assuming the sales data is in column C and you want to calculate the percentage of sales that are greater than 10,000, use the formula =PERCENTIF(C2:C9, ">10000").

How to Calculate a Percentage of Checkboxes in Google Sheets

You can also calculate the percentage of checkboxes that are ticked or unchecked in Google Sheets. This requires a formula that counts the number of checked boxes and divides it by the total number of checkboxes, then converts it to a percentage.

The basic formula for calculating percentages of checkboxes is =COUNTIF(range, True) / COUNTA(range).

For example, assuming the checkboxes are in column C and you want to calculate the percentage of employees paid, use the formula =COUNTIF(C2:C6, True) / COUNTA(C2:C6).

You can then convert the decimal into a percentage by clicking the ‘%’ icon on the toolbar.

Increase and Decrease Numbers by a Percentage

You can create formulas in Google Sheets to either increase or decrease a value by a certain percentage.

Increase by Percentage

To increase a value by a percentage, use the formula =Amount*(1+%). This formula multiplies the value by the percentage increase to provide the result.

For example, if you want to increase a value in cell A2 by 20%, you can use the formula =A2*(1+B2).

Decrease by Percentage

The formula to decrease a value by a percentage is similar to the increase formula, but with a minus sign instead of a plus sign. Use the formula =Amount*(1-%).

For example, if you want to decrease a value in cell A5 by 10%, you can use the formula =A5*(1-B5).

The value in the brackets determines the percentage to multiply with. For example, (1+20%) gives 120%, while (1-10%) gives 90%.

Conclusion

In this comprehensive guide, we’ve explored the Google Sheets percentage formula and its various applications. While there are many other scenarios where percentages can be calculated, these examples provide a good foundation for understanding the possibilities offered by the ‘Format as percent’ feature.

Apply these convenient formulas to your daily data processing needs, and watch your work become more efficient. For additional guidance, you can refer to other Google Sheets tutorials, such as calculating a weighted average, standard deviation, or performing basic arithmetic operations.

Mr Reviews