Expressing quantities in percentage always help give a better sense of proportion.
It helps us visualize the quantity better in our heads and is great for making comparisons.
It’s no wonder that most common quantities like sales tax, discounts, bank interest rates, etc. are commonly expressed in percentages.
In this tutorial, we will show you how to do percentages in Google Sheets using the Google Sheets percentage formula and other applications.
Table of Contents
Learn More Before Your Start
Although our guides are extremely useful for learning single skills or formulas at a time, it’s often best to go through some structured learning first. Luckily, Udemy has some awesome Google Sheets courses. And if you want to master the whole package, they also have excellent Google Suite courses.
What is Percentage?
The term percent or percentage means one part of a hundred.
Instead of expressing a ratio as a fraction of a random number, we usually express it in fractions of one hundred.
In this way, we get a common baseline, helping us to clearly see the difference in the quantity proportions.
Why Use The Google Sheets Percentage Formula (s)?
The percentage format in Google Sheets is one of the most useful number formats.
It helps you express values in percentages by not only adding a percentage sign next to it, but also by converting the number to a percentage value.
So if you have a fractional number like, say, 0.15 in a cell, formatting it with the percentage format automatically converts it into 15%.
All you need to do is select the cell, and click on the ‘%’ button from the Google Sheets toolbar.
Coming to the question of why we use percentage in Google Sheets, take a look at the images below:
Notice that the Value Added Tax percent is formatted differently for each sheet. The VAT is displayed as a fraction of the first image, while the same value is displayed as a percentage in the second one.
You would agree that the second spreadsheet is much easier to read than the first.
Seeing how helpful it is to format percentages, we are going to discuss how you can calculate percentage in Google Sheets in different practical situations, like:
- calculating the Percentage of proportion
- calculating the Percentage of total
- calculating the Percentage change
There are a number of other situations in which the percentage can be applied, but we are only going to concentrate on these three in this tutorial.
How to Automatically Format as Percentage
Instead of using the percent toolbar to convert decimals to percentage, you can also turn the value into percentage directly.
To do this, you simply add the TO_PERCENT function to your percentage formula in Google Sheets.
For example, in our sheet above, we can automatically convert the value to a percentage using the formula:
=TO_PERCENT (C2/B2)
How to Calculate Percentage Using *100
Another way we can calculate percent in Google Sheets directly is by dividing the value of the total and multiplying it by 100 using the formula for percentage in Google Sheets below.
 (=(Value/total number)*100).
Using our example sheet, we would 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 by 100.
Example Spreadsheet
You can make a copy of our example spreadsheet.
How to Calculate Percentage of a Proportion
The first application of percentages is in calculating the percentage of a proportion. In other words, what portion of a task belongs to or does not belong to a certain category?
For example, in the table below, we have the sales target allotted for each employee in Column B, and the sales made by them are given in Column C.
Let us say we want to calculate what percent of their allotted target each person has achieved. The general formula for this situation would be:
Sales Made / Allotted Target * 100
To apply this general formula to the above dataset, we need to follow the steps given below:
- Select cell D2 (where the first row’s result will be displayed).
- Type the formula =C2/B2
- Press the ‘Format as percent’ button (%) from the toolbar. Alternatively, you could navigate to Format->Number->Percent from the menu bar. This will convert the result of the formula to a percentage and display a ‘%’ sign next to the result.
- You should see the result in the second row of column D.
- Drag down the fill handle to copy the formula to the rest of the cells in column D.
- If you see the results include decimal places, you can choose to remove the decimal so you now have the percentage rounded up to the nearest whole number.
You now have a list of percentages of sales made by each employee. You will find it is easier to see who has been most efficient in achieving their targets.
How to Calculate the Percentage of Total
Next, let us look at a situation where we have the sales made by each employee (assuming that they all have a combined target).
We want to know what percentage of the total sales each employee has made.
The general formula for a given employee would be:
Sales made by the employee / Total sales * 100
The total sales made by all employees can be calculated using the SUM function as follows:
=SUM(B2:B6)
To apply this general formula to the above dataset, we need to follow the steps given below:
- First, calculate the total sales made. For this, we can use the SUM function and display the result in cell B7. The formula for the total will be =SUM(B2:B6).
- Next, select cell C2 (where the first row’s result will be displayed).
- Type the formula =B2 / $B$7
- Press the ‘Format as per cent’ button (%) from the toolbar. Alternatively, you could navigate to Format->Number->Percent from the menu bar. This will convert the result of the formula to percentage and display a ‘%’ sign next to the result.
- You should see the result in the second row of column C.
- Drag down the fill handle to copy the formula to the rest of the cells in column C.
- If you see the results include decimal places, you can choose to remove the decimal so you now have the percentage rounded up to the nearest whole number.
You now have a list of percentages of total sales made by each employee.
You will find it is easier to see which employee contributed the most or least to the total sales. You can even use this to gauge their performance over a period of time.
We will also take a look at a situation like this in the next section.
Explanation of the Formula
You will notice that we used more or less the same basic formula as the previous case, but with a very slight difference. We used an absolute reference to the total sales made (cell B7).
The dollar sign ($) is used to specify that a reference is absolute, so it does not change when you copy the formula to the other cells.
We do this to ensure that every cell value of column B gets divided by the same cell value (in B7). Thus, every new result in column C will get calculated based on the sum in cell B7:
=B2/$B$7
Note: If we sum up all the percentages in column C, we should get a total of 100%, which obviously makes sense.
How to Calculate Percentage Change
Once you have the percentage of sales of an employee calculated for every month or week, it then becomes easy to monitor their performance.
You can easily see if their percentage increases or decreases.
Let us now take a situation where we have the sales made by employees in week 1 and in week 2.
We want to know what percentage change (improvement or decline) there has been in each employee’s sales.
The general formula for this situation would be:
(New Value - Old value) / Old value * 100
To apply this general formula to the above dataset, we need to follow the steps given below:
- Select cell D2 (where the first row’s result will be displayed).
- Type the formula =(C2-B2)/B2
- Press the ‘Format as per cent’ button (%) from the toolbar. Alternatively, you could navigate to Format->Number->Percent from the menu bar. This will convert the result of the formula to percentage and display a % sign next to the result.
- You should see the result in the second row of column D.
- Drag down the fill handle to copy the formula to the rest of the cells in column D.
- If you see the results include decimal places, you can choose to remove the decimal so you now have the percentage rounded up to the nearest whole number.
You now have a list of percentage increases/decreases in each employee’s sales over a one-week period.
You will find it is easier to see, analyze, compare, and trace their performances over the course of a few weeks, months, or even years.
How to Calculate Percentage Based on Criteria
There are more applications of the percentage calculator in Google Sheets. One of them is calculating percentages based on criteria. To do this with we will need to combine the PERCENT function with the IF function.
The PERCENTIF syntax is as follows:
=PERCENTIF( Range, Criterion)
Range: This is the data set that is to be converted to a percentage.
Criterion: This is the argument used to filter out data from the range. It can be a cell reference or a value in quotation marks. It can also include logical expressions like greater than(>) and less than(<).
Let’s look at our example below:
We can get the percentage of the sales that are less than 10,000 using the formula:
=PERCENTIF(C2:C9, ">10000")
How to Calculate a Percentage of Checkboxes in Google Sheets
It is also possible to get the percentage of checkboxes ticked or unchecked in Google Sheets. The process is a bit more mathematical than most functions since you will need a formula that will count the number of checked boxes.
In this case, we will use the COUNT formula, which will give us the number of checkboxes that are either ticked or unchecked. We can then divide this by the number of total checkboxes and then convert it to a percentage.
The basic Google Sheet formula for percentage is:
=COUNTIF(range,True) / COUNTA(range)
Let’s look at our example spreadsheet below:
We can get the percentage of employees paid by getting the number of checkboxes divided by the total number of checkboxes.
We will use the formula:
=COUNTIF(C2:C6,True) / COUNTA(C2:C6)
We can then convert the decimal into a percentage by clicking the % icon on the quick access toolbar. This will give us 40%.
Increase and Decrease Numbers by a Percentage
We can create a Google Sheets formula for percentages to either increase or decrease a value by a certain percentage.
We will use the following example:
Increase by Percentage
To increase a value by a percentage, we can use the formula:
=Amount*(1+%)
This formula multiplies the value by the percentage increase to give you the results.
In our example, we would use the formula:
=A2*(1+B2)
This will give us 1200 as the result.
Decrease by Percentage
The way to decrease a value by a percentage is similar to increasing it. The only difference is that we will use a minus instead of a plus in the formula.
=Amount*(1-%)
In our example, we would use the formula:
=A5*(1-B5)
This will give us 900 as the result. The formula in the brackets is used to get the percentage that we will multiply the value with, for example, (1+20%) will give us 120%, and (1-10%) will give us 90%.
Conclusion
In this tutorial, we showed you the Google Sheets percentage formula options.
We agree there are a host of other situations where percentages would need to be calculated, but we assume these three examples would be enough to give you a basic idea of the possibilities you have, thanks to the ‘Format as percent’ feature.
We encourage you to apply this small convenience to your daily data processing needs and hope it helps you get your work done at least a small percent quicker. You can also check out our guide to the percentile function in Google Sheets.
Other Google Sheets tutorials you may like:
