# Google Sheets percentage formulas - usage and examples

You may think percentage calculations are useful only if you use them for work. But in reality, they help you in everyday life. Do you know how to tip properly? Is this discount a real deal? How much will you pay with this interest rate? Come find the answers to these and other similar questions in this article.

## What is percentage

As you most likely already know, percent (or per cent) means one-hundredth part. It is marked by a special sign: %, and represents a part of the whole.

For example, your and your 4 friends are getting a birthday gift for another friend. It costs \$250 and you're chipping in together. What percent of the total you're investing in the present?

This is how you usually calculate percents:

(Part/Total)*100 = Percentage

Let's see: you're giving away \$50. 50/250*100 – and you get 20% of the gift cost.

However, Google Sheets makes the task simpler by calculating some parts for you. Below I'll show you those basic formulas that will help you get different results depending on your task, whether calculating percentage change, percentage of the total, etc.

## How to calculate percentage in Google Sheets

Part/Total = Percentage

Unlike the previous formula, this one doesn't multiply anything by 100. And there's a good reason for that. Simply set the format of cells to percent and Google Sheets will do the rest.

So how will this work on your data? Imagine you keep track of ordered and delivered fruits (columns B and C respectively). To calculate the percentage of what's been received, do the following:

• Enter the below formula to D2:

`=C2/B2`

• Copy it down your table.
• Go to Format > Number > Percent in Google Sheets menu to apply the percentage view.
Note. You'll need to go over these steps to create any percentage formula in Google Sheets.
Tip. You can decrease and increase decimal places if necessary, using the options from the Google toolbar: Here's how the result looks on real data: I removed all decimal places making the formula show the result as a rounded percent.

Here are a few more examples of calculating percentage of a total. Though the previous shows the same, it works great for that example but may not be enough for other data set. Let's see what else Google Sheets offers.

### A common table with a total at its end

I believe this is the most common case: you have a table with values in column B. Their total resides at the very end of the data: B8. To find the percentage of the total for each fruit, use the same basic formula as before but with a slight difference – an absolute reference to the cell with the total sum.

This type of reference (absolute, with a dollar sign) doesn't change when you copy the formula to other cells. Thus, each new record will be calculated based on the sum in \$B\$8:

`=B2/\$B\$8`

I also formatted the results as percent and left 2 decimals to display: ### One item takes a few rows – all rows are part of the total

Now, suppose a fruit appears more than once in your table. What part of the total is composed of all deliveries of that fruit? The SUMIF function will help answer that:

=SUMIF(range, criteria, sum_range) / Total

It will sum only numbers belonging to the fruit of interest and divide the result by the total.

See for yourself: column A contains fruits, column B – orders for each fruit, B8 – the total of all orders. E1 has a drop-down list with all possible fruits where I chose to check the total for Prune. Here's the formula for this case:

`=SUMIF(A2:A7,E1,B2:B7)/\$B\$8` Tip. Having drop-down with fruits is completely up to you. Instead, you can put the necessary name right to the formula:

`=SUMIF(A2:A7,"Prune",B2:B7)/\$B\$8`

Tip. You can also check a part of the total made by different fruits. Just add up a few SUMIF functions and divide their result by the total:

`=(SUMIF(A2:A7,"prune",B2:B7)+SUMIF(A2:A7,"durian",B2:B7))/\$B\$8`

## Percentage increase and decrease formulas

There's a standard formula you can use to calculate percent change in Google Sheets:

`=(B-A)/A`

The trick is to figure out which of your values belong to A and to B.

Let's assume you had \$50 yesterday. You have saved \$20 more and today you have \$70. This is 40% more (increase). If, on the contrary, you've spent \$20 and have only \$30 left, this is 40% less (decrease). This deciphers the formula above and makes it clear which values should be used as A or B:

=(New value – Old value) / Old value

Let's see how this works in Google Sheets now, shall we?

### Work out percent change from column to column

I have a list of fruits (column A) and I want to check how prices have shifted in this month (column C) compared to the previous one (columns B). Here's the percent change formula I use in Google Sheets:

`=(C2-B2)/B2`

I also used conditional formatting to highlight cells with percent increase with red and percent decrease with green: ### Percent change from row to row

This time, I'm tracking total sales (column B) over each month (column A). To make sure my formula works correctly, I should start entering it from the second row of my table – C3:

`=(B3-B2)/B2`

Copy the formula over all rows with data, apply the percent format, decide on the number of decimals, and voila: Here I also coloured percentage decrease with red.

### Percent change compared to one cell

If you take the same sales list and decide to calculate percentage change based on January only, you will have to always refer to the same cell – B2. For that, make the reference to this cell absolute instead of relative so it doesn't change after copying the formula to other cells:

`=(B3-\$B\$2)/\$B\$2` Now that you've learned how to operate percentages, I hope getting total and the amount will be a child's play.

### Find amount when having total and percentage

Let's imagine you've spent \$450 shopping abroad and you'd like to have the taxes returned – 20%. So how much exactly should you expect to receive back? How much is 20% of \$450? Here's how you should count:

Amount = Total*Percentage

If you put the total to A2 and the percent to B2, the formula for you is:

`=A2*B2` ### Find total if you know amount and percentage

Another example: you've found an ad where a used scooter is being sold for \$1,500. The price already includes a pleasant 40% discount. But how much would you need to pay for a new scooter like that? The below formula will do the trick:

Total=Amount/Percentage

As the discount is 40%, it means you are to pay 60% (100% – 40%). Then, work out the original price (total) according to cells with data:

`=A2/B2` Tip. As Google Sheets stores 40% as one hundredth – 0.4, you can get the same result with these two formulas as well:

`=A2/0.4`
`=A2/40%`

## Increase and decrease numbers by percentage

The following examples represent the formulas you may need a bit more often than other ones.

### Increase a number in a cell by percent

A general formula to calculate the raise by some percent is as follows:

=Amount*(1+%)

If you have some amount in A2 and you need to increase it by 10% in B2, here's your formula:

`=A2*(1+B2)` ### Decrease a number in a cell by percent

To make the opposite and decrease the number by a percent, use the same formula as above but replace the plus sign with a minus:

`=A2*(1-B2)`

### Increase and decrease a whole column by percent

Now assume you have lots of records written in a column. You need to raise each of them by a percentage in that same column. There's a quick way (6 extra swift steps to be exact) to do that with our Power Tools add-on:

1. Select all values you'd like to raise and run the Text tool from Add-ons > Power Tools > Text:  3. Enter an equal sign (=) to add it at the beginning of each cell: 4. Click Run to turn all your numbers to formulas: 5. Proceed to the Formulas tool in Power Tools and select the option to modify all selected formulas.

You will see %formula% is already written there. You are to add those calculations you want to apply to all formulas at once.

Remember the formula to increase a number by percent?

=Amount*(1+%)

Well, you already have those amounts in column A – this is your %formula% for the tool. Now you should only add the missing part to calculate the increase: *(1+10%). The whole entry looks like this:

`%formula%*(1+10%)` 6. Hit Run and all records will be raised by 10% at once: That's it! All these examples are easy-to-follow and are intended to remind those of you who've forgotten or show those who don't know the basic rules of calculating percentage in Google Sheets.

### 36 responses to "Google Sheets percentage formulas - usage and examples"

1. Frederic Adhitama says:

Great stuff!

2. Tyler says:

Can you do a dumbeddown version of this please because my penut sized brain dosent understand

3. Tyler says:

• Natalia Sharashova (Ablebits.com Team) says:

Hello Tyler,

Could you please specify what points of the article remain unclear?
At the moment I don't believe the video would feature something other than the same formulas.
If you could describe the task you're dealing with, I would gain a better understanding of what to improve.

Thank you.

4. Diana says:

Why does google sheets add 2 zeros to my values when I try to apply the "percent" format?? I just want the % to appear next to the number already in the cell.

• Natalia Sharashova (Ablebits.com Team) says:

Hello Diana,

That's simply the way Google Sheets works.

In your turn, you can adjust the number of zeros using the corresponding options from the Sheets toolbar. Look for two buttons with zeros right under the Format option: Decrease decimal places and Increase decimal places. The tip from this point of the article shows them.

5. matt says:

Hi,
How to do calculate =a2*b2 for multiple cells ex:a2-a5*b2-b5 etc...

• Natalia Sharashova (Ablebits.com Team) says:

Hi Matt,

If I understand you correctly, this formula should do the trick:
=ARRAYFORMULA(A2:A5*C2:C5)

6. Lisa says:

Hi,
I'm trying to keep track of attendance using a google sheet with checkboxes. Here is the formula for what I have: =COUNTIF(C6:X6,"TRUE")/COUNTA(C6:X6,"FALSE")
My issue is, if all of the boxes are checked, the percent should be 100% but it's only 95.65%.

Any ideas?

• Natalia Sharashova (Ablebits.com Team) says:

Hi Lisa,

Would you mind sharing your sample data with us (support@4-bits.com) so we could have a look?
Note. That email address' Inbox is unmonitored. Once you share the file, please confirm by replying here. Thank you.

7. Chris says:

I disabled my ad blocker, so much good content thanks for sharing.

8. Joseph E Haas says:

My family and I have a weekly running nfl pool. Each week column input and results of course change. I use check boxes to say who wins and who loses and the sumif function at the end of the column to give me the needed results. I decided to test the workings of the sheet and everything works fine except for one thing. When I attempt to divide cells d22(total wins) with e22(total losses) To get the percentage needed I have used the formula =(D22+E22)/D22 which does not work. What am I doing wrong?

• Natalia Sharashova (Ablebits.com Team) says:

Joseph,

Could you please specify what are you trying to get? The percent of losses or wins from the complete total? Or something else?
If it's possible, please also share your sample data with us (support@4-bits.com) and reply to my comment when we can take a look. Thank you.

9. kuro says:

Hi! thank you for this tutorial!

You may be able to help me, I am doing a spreadsheet where I track my business profits on a daily basis.
I already get my daily % growth, but I would like to do a TOTAL percentage, from my 1st day to today.
The trick is that I want it to fill itself, and in only 1 cell. I want the result of this growth to always appear in the 'k5' cell, for example, and for it to always take the last input and the very 1st one without me having to change the formula every day.

If you can help, I'll take it!
Thank you very much.

• Natalia Sharashova (Ablebits.com Team) says:

Hi Kuro,

thank you for the feedback!
I'm afraid I'll need more details on your data to help you out. Can you please describe where it's located in the spreadsheet in detail (Sheet? Column? The number of rows?) and give an example of how it looks?
What formula do you use and what do you update in it constantly?

10. kirstin b kochanek says:

need to calculate percentage markup

row of cells: cost retail %markup

need to multiply cost x100 then divide by retail? just can't figure out order

11. Adkins says:

Such a great article. I'm trying to increase a number in cell by a percentage and it doesn't seem to be working. i used the fomula you stated but it adds the percentage as 100% and not 10%. Any ideas as to why that is?

I appreciate it,

• Natalia Sharashova (Ablebits.com Team) says:

Can you please provide the exact formula you use in your sheet that returns the incorrect result?

12. Elyse Langer says:

Hello!

I'm looking to calculate a shopping spreadsheet and would like to calculate discounts & taxes. What formula can I use to calculate total + 15% tax for example?

• Natalia Sharashova (Ablebits.com Team) says:

Hello Elyse,

Assuming your total is in column A and tax is in column B, you can try this formula:
=A2+(A2*B2)

The multiplication in brackets is used to find the amount of the tax, then it is added to the total in column A giving you a sum you will need to pay.

13. Derek says:

Hi -
I have a number of identical laid-out sheets and have the same metric in each = open rate as a percent.
In my summary sheet, I want to calculate the average percent across the sheets.

14. Trevor says:

Thanks for providing a great resource. I have a table of columns and rows with sales figures that go up and down. Using formulas, the table displays monthly changes in sales as percentages. I have them color-coded so sales increases display in green and decreases display in red. The decreases show a minus character (-) automatically. Is there a way to automatically display an addition character (+) for a percentage increase? Thanks!

• Natalia Sharashova (Ablebits.com Team) says:

Thank you for your feedback, Trevor.

You will need to select your cells with the percentage increase and create a custom number format like this: "+"0%

• Trevor says:

Thank you. Is there any way to make the "+" automatically appear only in cases where a percentage exceeds 0.00%?

• Natalia Sharashova (Ablebits.com Team) says:

Trevor,

Try this custom number format instead:
[<0]"-"0.00%;[>0]"+"0.00%;0.00%

15. Martha says:

Hello,
I have the same problem as the person below did on 11/19/19. I will enclose my formulas.
These formulas gave me 98% COUNTIF(E5:E107,"No")/COUNTA(E5:E107,"no") =COUNTIF(E5:E107,"Yes")/COUNTA(E5:E107,"Yes")

This formula gave me a 99% =COUNTIF(H5:H107,"No")/COUNTA(H5:H107,"No")
=COUNTIF(H5:H107,"Yes")/COUNTA(H5:H107,"Yes")

Hi,
I'm trying to keep track of attendance using a google sheet with checkboxes. Here is the formula for what I have: =COUNTIF(C6:X6,"TRUE")/COUNTA(C6:X6,"FALSE")
My issue is, if all of the boxes are checked, the percent should be 100% but it's only 95.65%.

• Natalia Sharashova (Ablebits.com Team) says:

Hello Martha,

COUNTA counts all non-empty cells within the range no matter their contents. The values you're using as the second argument ("FALSE" or "no") don't tell what to count. The second argument is also a range to count, so these records are actually counted as "1" mixing the numbers up.
So please change all your COUNTA to COUNTIF and everything should work correctly then.

16. Raphael says:

Hi there,
Thank you so much for creating useful content and helping me thereby solving my google spreadsheet question. Thanks again!

17. عبد العزيز says:

مرحبا
H3=مبلغ
I3=5% نسبة
=H3*I3
يعطني خطاء
لماذا

• Natalia Sharashova (Ablebits.com Team) says:

Hello Abdel,

Can you please provide the exact formula you have in H3 and the result it returns?

18. Deidara23S says:

hey there!
I need help regarding a problem I'm having with sheets. I have a table that has Months (from Jan to Dec) in the X axis and the name of the asset in the Y axis. I want to add the new value of the asset under each month and I want sheets to indicate (in a column at the end of the table) the growth rate it has had from the very first month. I would like if I didn't have to keep changing manually the formula of said column. Is there someway it can do so automatically?

I would really appreciate your replies,

Cheers!

• Natalia Sharashova (Ablebits.com Team) says:

Hey Deidara,

If I understand you correctly, you can use ArrayFormula in the last column to calculate all rows, existing and future ones, automatically. If this is not exactly what you need, please describe the task in more detail.

You can also share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

19. Vedavyasa Huli says:

A very good explanation of formulas. This will help to a great extent those who are working on excel. Truly fantastic article. Thanks for sharing this. Kudos!!!