In this tutorial, I will show you how to do percentages in Google Sheets using the Google Sheets percentage formula and other applications.
Table of Contents
What is a Percentage?
Expressing quantities in percentages always helps 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.
The term percent or percentage means one part of a hundred. It’s a fundamental concept for everyday life, and that’s one reason I wanted to cover it here. I use it to calculate tips at restaurants and to evaluate the effects of compounding interest on my mortgage. And it makes sense, right? I’m not going to express a ratio as a fraction of a random number. Instead, I’ll give it a base of 100. That’s the foundation of percentages.
You can also find the percent for a set of data. I’ll show you how to do that below. In that case, you’ll use the total data set as the denominator (instead of the base 100). It will give you a percent of your total, and you can format that as a percent in Google Sheets.
The Google Sheets Percentage Formula
How do you use the percentage formula in google sheets? You just use an equal sign, then divide one number over another. As you know, I usually break my spreadsheet formulas down into their corresponding syntax. I cover what you need to include in the parenthesis to get them to work. That’s not needed for the percentage formula in Google Sheets, because no formula looks like =PERCENT().
Instead, you’ll just use division over the full range of data. If you want to format it like a normal formula, it looks like this.
=X/Y
Here, X represents the portion of the whole and Y represents the whole. So 15% would be =15/100. Note that this formula gives you a fractional number, which you can then turn into a percent with formatting. And there’s a quick way to do that. I find it easiest to use the percentage button when formatting a percent in Google Sheets. But that’s not a formula.
Want a deeper dive? I’ll show my step-by-step instructions on how play with percentages below. I’ll include screenshots so you can follow along with each step of my process.
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. Here’s what it looks like on your 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.
I don’t know about you, but I find the second spreadsheet much easier to read than the first. And that’s why we use percentages in Google Sheets. Let’s move on to some more advanced ways to use this. Here are a few practical situations where I find this helpful:
- calculating the Percentage of proportion
- calculating the Percentage of the total
- calculating the Percentage change
There are several other situations in which the percentage can be applied, but I’m 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. That’s done with the percent button. There’s also a formula you may want to try out. It’s supposed to automatically format as percentage.
To use it, add the TO_PERCENT function to your percentage formula in Google Sheets. For example, in our sheet above, I can automatically convert the value to a percentage using the formula:
=TO_PERCENT (C2/B2)
Note that the TO_PERCENT formula is currently the worst way to format a percentage in Google Sheets. It doesn’t automatically change your formatting into a percentage. Instead, it just completes your formula and puts your number into whatever format you’ve already chosen for the cell. That means you’ll have to click that “format as percent” button to get it to look right. For me, that makes this the worst way to calculate percentages in Google Sheets.
Video Guide
Here’s a video I made that shows how the TO_PERCENT formula works (and when it does the automatic formatting).
How to Calculate Percentage Using *100
Another way I 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. It’s exactly what you’d do on paper to show a fraction as a percent. So, this should look familiar. Here’s the percent formatting formula:
(=(Value/total number)*100).
Using my example sheet above, 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. Note that using the percent button on this would show an incorrect result. So use this only if you’re looking to express percentages automatically and without the percent sign.
Example Spreadsheet
Want to follow along as I demonstrate how to calculate percentages? Please make a copy of my example spreadsheet. It contains my demonstrations on how to use the PERCENTIF function, divide COUNTIF functions over totals, and calculate the percentage of a proportion. If you learn like I do, you may find it faster to see my actual examples in the spreadsheet.
Note that all of my examples below come from this example spreadsheet. You can follow along in your own copy and make changes to see how it affects the results.
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
Here’s how you increase or decrease numbers by a percentage in Google Sheets. It’s a pretty simple process, and I’ve got an example built into my example spreadsheet. Here’s what my sheet looks like before I apply the formula. Next, we’ll talk about how to increase by a percentage.
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 gives 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, I showed you the Google Sheets percentage formula options. Of course, there are a host of other situations where percentages would need to be calculated, but I 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.
I 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. I made a whole video about how to use it.
Other Google Sheets tutorials you may like:
