Percentages allow us to make meaningful comparisons and represent data in a compact and easily interpretable manner.
The fastest way to calculate percentages in Google Sheets is to click the percentage % icon on the toolbar, which converts the number into a percentage.
In this post, I will show you how to easily calculate percentages in Google Sheets, along with real-life examples from calculating percentage changes, averages, and totals to multiplying, adding, and subtracting percentages. And a lot more!
Whether you’re working with budgeting, sales data, or any other type of data that requires percentages, this guide will make your life much easier.
If you’d like to follow along with the examples listed below, create a copy of this document
- 2 Ways to Calculate Percentages in Google Sheets
- Calculate Percentage – 7 Real-Life Examples That You May Be Facing
- Example 1: How To Calculate The Percentage Of A Total In Google Sheets?
- Example 2: How To Calculate The Percentage Change (Increase, Decrease) Formula In Google Sheets?
- Example 3: How To Multiply Percentages In Google Sheets?
- Example 4: How To Subtract Percentages In Google Sheets?
- Example 5: How To Calculate Percentages Based On A Condition?
- Example 6: How To Calculate The Average Percentage In Google Sheets?
- Example 7: How To Calculate The Percentage Of Checkboxes Completed?
- My Final Thoughts
2 Ways to Calculate Percentages in Google Sheets
While the easiest way to calculate percentages in Google Sheets is using the toolbar, there are two other methods. Let’s take a look at both of these.
Get Percentage using Custom Number Formatting
Custom number formatting is the easiest way to display numbers as percentages in Google Sheets. You can do it through the options menu:
Step 1: Click Format.
Step 2: Select the Number option. Click More Formats.
Step 3: Select Custom Number Format. This opens the number window, where you can enter values in the space provided next to the apply button.
Step 4: Type 0, then the percentage sign (%), and click apply.
You can use decimals like 0.00%, 0.000%, or even positive/negative numbers.
And here’s the final effect you should expect:
Use the TO_PERCENT Formula
If number formatting is confusing, you can use the TO_PERCENT formula. This Google Sheets function converts a number to a percentage.
What’s worth noticing is that this function returns a value converted to percentage with a base assumption being that 1 = 100%
For example, if cell A2 contains the number 0.50 and you want to convert it using the function, your formula should look like this:
=TO_PERCENT(A2). This, in turn, should give you a value of 50%.
Let’s have a look at a few more examples:
Calculate Percentage – 7 Real-Life Examples That You May Be Facing
Example 1: How To Calculate The Percentage Of A Total In Google Sheets?
To calculate the percentage of a total, use the SUM function: =SUM(range).
For example, if you hope to calculate the percentage of total sales made by the employees in a company, you need to have a column that includes the total sales made by each individual employee. The data in this column will be the range in the SUM function.
After you have calculated the total, divide the sales made by each employee by the total separately.
You can then use the percent change formula or number formatting to convert these to percentages.
Example 2: How To Calculate The Percentage Change (Increase, Decrease) Formula In Google Sheets?
Calculating a change in percentage is quite simple as long as you stick to this formula:
=(New Value - Old Value)/Old Value.
Using this formula will give you the correct answer in decimal form (e.g. 0.60).
As you’re likely to prefer converting it to a percentage form, select the cell and click the Format as percent button (%) on the toolbar.
This, in turn, will convert the number (0.60) to its percentage form (60%). Alternatively, you can click Format>Number>Percent from the menu bar.
For example, you booked 60 calls last week and 100 calls this week. Let’s calculate how much % an increase it was.
New Value = 100
Old value = 60
=(100 - 60)/60 * 100 = 66.6666666666667
Pressing the % toolbar icon gives the percentage 67% (if the number is rounded)
Example 3: How To Multiply Percentages In Google Sheets?
This formula can multiply percentages in Google Sheets:
=VALUE + (VALUE * (PERCENT/100))
While most of us are familiar with the common formulas used for functions such as adding and subtracting, this one isn’t as common. Let me break it down for you to make things simple.
The VALUE section should contain the value you hope to multiply by the percentage.
For example, if the value is 2 and you hope to multiply it by 50%, your formula will read:
=2 + (2*(50/100))
If the value is 10 and you want to multiply it by 75%, then your formula will look like this:
=10 + (10*(75/100))
And will return 17.5
Example 4: How To Subtract Percentages In Google Sheets?
Functions such as subtraction and addition are undoubtedly simple on Google Sheets (especially if you’re familiar with excel!).
To subtract percentages, you can use the following formula: =TOTAL-TOTAL PERCENTAGE.
For example, say you have a value of 100 in cell B4. You want to subtract 20% from 100. Your formula should look like this: =B4-B4*20%
Step 1: Type = and select the cell that contains the total.
Step 2: Enter the minus sign –
Step 3: Re-select the cell that contains the total, enter an asterisk * (the multiplication sign), and finally, the percentage you hope to subtract.
Step 4: Press enter.
If you’d like to “automate” it by referencing cell numbers, you can do it like so:
Example 5: How To Calculate Percentages Based On A Condition?
The PERCENTIF function is used to calculate a percentage based on a condition.
For example, if you need to calculate the percentage amount of salaries that are higher than 1000, your formula should look like this: =PERCENTIF(range, “<1000”)
Step 1: In an empty cell, type =PERCENTIF.
Step 2: In the same cell, open brackets ( and select the range. Mention the condition within quotation marks ‘’ ‘’.
Step 3: Click enter.
For Example: Let’s say an individual wants to track the amount saved each month.
It can be determined with the aid of =PERCENTIF(A2:A8, “Savings”)
Example 6: How To Calculate The Average Percentage In Google Sheets?
First, you need to have a column with the calculated percentages. This column will be the range used in the formula calculating the average.
Your formula will be =AVERAGE(range).
However, if you need to calculate the average percentage based on counts and percentages, your formula will be a little more detailed:
Although this formula is relatively long, it isn’t as complicated as it looks. Here’s a breakdown to make things easier!
Step 1: Type the correct formula after the = sign.
Step 2: In the section labelled ‘’range1” select the values in the count column.
Step 3: In the section labelled range 2, select the values in the percentage(%) column.
Example 7: How To Calculate The Percentage Of Checkboxes Completed?
Calculating the percentage of completed checkboxes can be done using a combination of two functions: COUNTIF and COUNT.
If you’d like to see a more advanced example of this, you can check this article where I created a habit tracker using checkboxes
But let’s focus on the basics for now.
For example, let’s say the checkboxes are in column A. You need the calculation to count the number of checkboxes in total and calculate the percentage of boxes that have been checked(completed).
Ideally, your formula should look like this :
The COUNTIF function will count the number of completed checkboxes. Therefore, you need to select only the boxes that have been checked as the range.
Meanwhile, the COUNT function will count the number of boxes in total. Make sure you select all the cells with boxes in column A for this range.
Step 1: In an empty cell, type =COUNTIF.
Step 2: In the same cell, open brackets “(”, select the range, and type in TRUE.
Step 3: Right after TRUE, close brackets “)”, type COUNTA, open brackets (, select the range and finally close brackets ).
Step 4: Press enter.
My Final Thoughts
Calculating percentages in Google Sheets sounds like an easy thing to do, and indeed it is. It allows you to compare different values, understand how numbers change, and track them over time.
This can help you get useful insights and make informed decisions in your private life or work.
I hope this tutorial was helpful for you, but if you have any questions, then feel free to ask them in the comments section!