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

This is how Google spreadsheet calculates percentage:

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: Adjust decimal places for Google Sheets percentage.

Here's how the result looks on real data: Google Sheets percentage formula.

I removed all decimal places making the formula show the result as a rounded percent.

Percentage of a total in a Google spreadsheet

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: Calculate percentage of a total.

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 Find the percentage of prune in Google Sheets.

Tip. Having a 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

Tip. Don't forget to apply the percent format and adjust the number of decimal places.

I also used conditional formatting to highlight cells with percent increase with red and percent decrease with green: Google Sheets percentage formula.

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: Calculate percentage change in Google Sheets.

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 Percent change based on one cell.

Amount and total by percentage in Google spreadsheets

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 amount by total and percentage.

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%). With these numbers at hand, you can work out the original price (total):

=A2/C2 Find total by amount and percentage in Google Sheets.

Tip. As Google Sheets stores 60% as one hundredth – 0.6, you can get the same result with these two formulas as well:

=A2/0.6
=A2/60%

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) Increase the amount by percentage.

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) Decrease the amount by percentage.

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 steps to be exact) to do that with our Power Tools add-on:

Google Workspace Marketplace badge

  1. Select all values you'd like to raise first and apply the automatic format: Format > Number > Automatic: Apply automatic Number format to your column.

    Note. This is required to avoid problems with formats on the next steps.

  2. Keep the values selected and run the Text tool from Extensions > Power Tools > Tools > Text: Run the Text tool from Power Tools.
  3. Open Add text by position and add an equal sign (=) to add it at the beginning of each cell: Add an equal sign to each cell.
  4. Click Run to turn all your numbers to formulas: Turn all records to formulas by adding a leading equal sign.
  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%) Use the Formulas tool to adjust all formulas.

  6. Hit Run and all records will be raised by 10% at once: Modify all formulas at once to increase all values by 10%.

    Tip. Feel free to apply any number format back to these formulas.


Google Workspace Marketplace badge

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.

Spreadsheet with formula examples

Google Sheets percentage formulas (make yourself a copy to practice)

You may also be interested in

Table of contents

198 comments

  1. Hi - the formula for percentage increase/decrease is working perfectly for the majority of my data, but some of the numbers involved are zeros, which brings up an error as you can't divide by zero. Is there any way to fix this?
    e.g.
    2021 = 0
    2022 = 13
    How can I calculate the percentage increase from 2021 o 2022 when the =(2022 value - 2021 value) / 2021 value brings up a #DIV/0 error?
    Thanks in advance!!

  2. hello ma’am. if i have over 10 columns with checkboxes, how do i make the total of these columns equals to 100%? in my example, i have 13 columns and the total is 130% when i check all the boxes.

    • Hello ariana,

      Use a formula like this:
      =COUNTIF(A1:M10,TRUE)/COUNTA(A1:M10)

      Make sure to format a cell with the formula as Percent.

  3. Hey,
    I was just wondering how you would get google sheets to show something out of a total. for example, I have checkboxes in separate cells and if I check say 3 out of 10 of the boxes, is there a formula to show that in a separate cell? (eg. 3/10) So if I checked another box, it would automatically change instead of having to change it manually.
    Thanks

  4. Dear Sir,
    I got a formula from here, I really appreciate you for these formulas.

    Thank you very much.

  5. Hi;

    Thanks for this post. I am trying to implement this tax bracket as a formula for my family Budget Spreadsheet- I need this >>>( $9,615 plus 22% of the amount over $83,550) .. can someone help me write this formula to add to my yearly income.

  6. Hello I'm wondering if anyone knows the formula

    I want to be able to type a monetary amount into a single cell and have it decreased by 20% In that cell
    For example
    I want to type $100 but it automatically deduct 20% of that and shows the deduction in the same cell not the $100
    Is that possible?

    • Hello Lori,

      Due to the nature of Google Sheets, you can't enter a value into a cell and have a working formula in that cell at the same time. It's either one or another I'm afraid. Hence, for your task, you need to have two columns (they can be on different sheets): one with the value to enter, another with a formula to decrease that value by percent.

  7. I am new to SHEETS and need help with the following. I have searched for hours and can only figure out half of what I need.

    If the Value of C13 is 20% OR HIGHER than the value of C20 = Red (I have this part figured out with =C13>=.80*C20)

    If the Value of C13 is 20% or more in the OPPOSITE direction of C20 = Green *Please help with this*

  8. Hi there, I have a Sheet with loads of data (for a game) and need to make a Query.

    I want to input a number (say 1000) and then get a output of all thats in the database thats between 10% lower and 10%higher than the 1000 and output it for me, the 1000 will become a variable input so I can use the formula for different searches in my database (over 40000 entries)
    (I am using sheets, can convert to excel if needed)
    Thank you

    • Hi Aylana,

      I believe you need to incorporate the percentage formulas to the QUERY function. There's even a tool that will ease the job.

      Yet, if you're still unable to make it work, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.
      I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and try to help.

  9. Hi, I want to get the formula to calculate the overall increase/decrease in profit% showing it row by row if that makes sense.

    example: row 1 is not profitable so 0% would be shown on that row.

    row 2 is profitable so % would increase to 50% on that row as one out of the two rows is profitable.

    row 3 is profitable so % would increase to 66.6% on that row as two out of the three rows are profitable.

    row 4 is not profitable so % would decrease back to 50% on this row and so on.

    Hopefully this makes sense, Any help is appreciated thanks.

      • Hi,

        That worked perfectly, Thank you very much for the help!

  10. I'm trying to use Google sheets for pricing on my products I have fortnightly to monthly specials where between 2500 to 5000 products are affected. some of my suppliers release a downloadable file with their special prices for the promo period.

    I add 2 columns E and F as below

    Column D Cost of product column E 20% Column F Sell price

    =D2*(1+E2) works for 1 product answer showing in F2

    but how do you do entire columns at a time? Rows 2 to 2500 instead 1 row at a time.

    • Hello!
      Write the formula in a cell. Double-click the fill handle. Fill handle is a small square that appears in the bottom-right corner when you select a cell or range. The formula will be copied down as many rows as there is data in adjacent left column.

      • Thank you so much

  11. Hi,

    I'm trying to do the percentage of a sum and I don't know which function to use for it. Here's my example:
    Column A=10.62
    Column B=5.50
    Column C= 7.99 Percent of (A+B), 7.99% of (16.12) = 1.29 (rounded)

    Does this make sense? I want C to add A and B together then find the percent of it.

    Thanks!

  12. hello! mam,
    i have a some values as a,
    no. of quantity 12 and 150 rate and percent 30% = ans
    means that
    12*150/30%=ans
    how to used formula.

    • Hello SHIVKUMAR,

      I'm sorry, your question is not quite clear. If you specify what you're trying to calculate, I may be able to suggest a formula.

  13. Hi Natalia,
    14.75 13.25 12.30 11.40 10.75 10.10 9.50 8.95
    15.25 13.70 12.75 11.80 11.10 10.45 9.85 9.25
    15.75 14.20 13.20 12.25 11.55 10.85 10.20 9.60
    - .80 - .30 -.30 -.10 -.10 -.10 -.10

    This is a sample of the sheet I am doing. The first column is the main number each column after follows an =A1-(then amount on the bottom row) however, I need to have that formula less than 5% as well. I can't seem to get the additional 5% subtracted from that formula. How do I do that?

  14. After searching found this post with variety of cases and detailed explaination.. very useful.

    I was searching variation of "Work out percent change from column to column" mentioned above but containing both positive and negative numbers in column. for example say [B ,C] columns cosist of
    [-16,-13], [1,0.1],[-4,-8], [0.5,-1], [2,4],[-0.1, 0.5]. Could you please review and share your suggestion?

    • Hello Jagdish,

      Thank you for your feedback!

      Try dividing the subtraction to the absolute number using the ABS function:
      =(C2-B2)/ABS(B2)

      Or try a formula like this:
      =(C8-B8)/AVERAGE(B8:C8)

      Whichever one you choose, don't forget to apply the percent format.

  15. How do I calculate 1 cell as a % of another cell? e.g. I should have 100 units but only have 80 units so the unit accuracy is 80%

  16. HI, super helpful, I need to see what 7% or any percentage eventually of a total volume goal. Each rep is assigned a goal, one reps is let's say 2000 for the month. how do a find a formula that will calculate each ones goal? hope that makes sense. thank you in advance

  17. Hello!
    I am making a budget sheet, where I have a total income box and several different expense boxes, with the percent of total income for each. Is there a way to change the percent of one of the expense boxes and have one of the others automatically adjust its percent/dollar value so that total expenses don't exceed total income?
    Thanks!

  18. if I want to calculate a % based on wins vs losses how would I do that?

    I have a column of W's and L's for wins and losses and I want a formula that calculates the win % of all those cells

  19. I'm trying to add 20% to prices but want the people to just type in the amount and it to automatically add the 20% to it, how do I do this?
    Example: the price is $10 but need to add 20% to make the amount show $12. Is there a formula I can put in the column to make this happen without adding another column to do the calculations in?

    • Hello Teri,

      I'm afraid Google Sheets doesn't work like this. You can't enter the value and the formula for that value in the same cell. It's either one, so you will have to use an extra column with formulas referencing other cells.

  20. I am trying to either create a pie chart or a percentage of the contents of a specific column. I have used conditional formatting and data validation to color the values in each row based on 5 possible answers. Now I need to know the percentage of each possible value.

    For example, In one row I have several names, all different. In the next row, I have their choice: Red, blue, Green. How do I create a pie chart to show how many chose each color, or at least calculate a percentage?

    Thank you!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)