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. I have a spreadsheet created to show cost of merchandise, I need the cost in one column, then add markups of 8% to those, and I want the new total in a new column. How can I get this to figure into the spreadsheet correctly so when I enter a number in Column D it automatically adds 8% and places that total in column E? Thanks

  2. I am attempting to calculate student grades that have improved from one test measure to the next. I have two columns (B and C) where I have recorded the two scores and I have set the formula to =percentif (B2:C32, "valueC>valueB") to see if I can calculate the percent of students who had an increase in score from test 1 to test 2. I keep getting a percent of 0 (even though I can see there are student who did in fact improve). Is there a better way to calculate a percent of students who have shown improvement between two test measures using two columns of the spreadsheet? Any help with what formula wording might work with this scenario is appreciated!

    • Hello Shayne,

      The PERCENTIF function is not equipped to handle comparisons between two ranges in the way you're attempting. Instead, use this formula:
      =COUNTIF(ARRAYFORMULA(C2:C32-B2:B32),">0")/COUNT(B2:B32)

      COUNTIF calculates the number of students who improved their scores. COUNT calculates the total of students. And then divide one by another to find the percentage. Don't forget to apply the Percent format to the result.

  3. Hello,

    i have a 1k students and i need to know how many have a grade of 60% or higher and lower

    what formula can i use?

    thanks

  4. hi, I'm not too savvy with formulas. I'm creating a spreadsheet on which teachers will enter an assessment score (whole number). That score is __/60; and I'd like the value in the cell to be represented as the percentage. Thanks in advance.

  5. I am trying to figure out and "if equal to or less than "amount" multiply by 6.5% of cell value and if greater than "amount" multiply by 8.5% of cell value. example:

    If cell F1 is equal to or less than $40,000, multiply the amount input into cell F1 by 6.5%, but if cell F1 is greater than $40000, multiply the amount input in cell F1 by 8.5%

  6. I'm trying to figure out how to set each cell in column B to calculate percentage from each adjoining cell in column A, using 1's for correct answer and 0's for incorrect answers, so looking for percentage of correct answers. Can you help please? Example below:
    Column A Column B
    10110001000 Need % from adjoining cell in column A
    0011111001110 Need % from adjoining cell in column A

    • Hello Michael,

      I'm sorry but your task is not clear. Please consider sharing an editable copy of your sample spreadsheet with us (support@apps4gs.com) and include the result you'd like to get. 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.

  7. I am looking to get percentages for individuals, the number of jobs they’ve done and the number of incidents.

    Joe measured 10 jobs (B1 name)
    Out of 10 jobs (using check box) 5 had incidents (C1 check box)
    Joe has a 50% incident rate (D1 percentage)

    I hope I am asking this question accurately

    • Hello Zac,

      Have you tried this formula? If it doesn't work as you need, please consider sharing an editable copy of your sample spreadsheet with us (support@apps4gs.com) and include the result you'd like to get & your formula.
      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.

  8. Hello! I have a bit of a complicated one. Is there a way to have a formula that can calculate a score based on certain criteria being checked off?

    A3: Drop Down Menu (option A - 0%, option B - 10%, option C - 20%, option D - 30%)
    B3: Check Box - 20% if checked
    C3: Check Box - 20% if checked
    D3: Check Box - 5% if checked
    E3: Check Box - 25% if checked
    F3: total score

    the criteria being if the drop down menu has option D selected, and all boxes checked, that would be a score of 100%. Then of course, the score would change depending on what is checked or which drop down option applies.

    So is there a formula that can do this? I know it's a little complicated!!

  9. How would I calculate this--

    What percentage of the values in this column are less than ________?

  10. Hi, I'm a little stuck and hoping you can help. Lets say in column A I have a list of names (person 1, person 2, person 3, person 1, person 2, person 3) which appear multiple times and not in the same order, and Column B states YES or NO next to each name. How can I calculate the percentage of YES or NO for each person if the names appear multiple times in Column A? Thanks!

    • Hi Rebecca,

      To find percentage of a total, you need to divide the number by total.

      The total here is all occurrences of each name: =COUNTIF(A2:A10,A2)
      Where A2 is person 1.

      The amount of YES next to person 1:
      =COUNTIFS(A2:A10,A2,B2:B10,"yes")

      The percentage of YES:
      =COUNTIFS(A2:A10,A2,B2:B10,"yes")/COUNTIF(A2:A10,A2)

      You count all NO for person 1 in a similar manner.
      To count this for other people, change A2 accordingly.

      Functions used in the formula:
      COUNTIF
      COUNTIFS

      • Thanks SO much for the fast response. This is exactly what I needed!

  11. I have a column of numbers. I'd like to be able to find out the percentage of how many times the numbers in this list are greater than 0.

    Example:

    0
    0
    4
    -1

    This would be 25%.

    Is there a formula for this?

  12. I need to make a column of percentages based on a column of fractions. So my column C has fractions (example: 20/43) and I want column D to show thoses in percentages.

  13. Hello, I would like to capture the percentage of three numbers (represents three year totals) to see a trend either negative or positive.

    Thank you,

    Brian

    • Hello Brian,

      For me to be able to help you, please describe the task in detail. Provide examples of the data you have and specify how you want it to look as a result. Thank you.

      • Hello,
        I am working with year-to-date statistics over a three-year period covering 2021, 2022, 2023. Example: 2021= 40 2022= 45 and 2023= 35. I would like to reflect a positive or negative % over the three year period. I used this function =TO_PERCENT((Q29-Z27)/Z27) to calculate 2022 and 2023 and it reports -22% which is perfect. Except I need it for three years, I’m not sure how to include 40 from 2021 into the equation. The total for 2021 is displayed in H29 with the percentage result being displayed on the same sheet in Z29

        Your expertise is certainly appreciated by many.

          • Hello,

            Correct, showing a % trend. Is 2023 higher or lower than 2021 & 2022

            Thank you again.

            • If you're trying to compare 2023 with 2021 & 2022, you will need 2 formulas:

              1. one for 2022 vs 2023: =TO_PERCENT((B4-B3)/B3)
              2. and another for 2021 vs 2023: =TO_PERCENT((B4-B2)/B2)

              If you need both results in the same cell, you will have to concatenate the calculations:
              =ROUND((B4-B3)/B3*100)&" & "&ROUND(((B4-B2)/B2)*100)

              If I'm missing something and this is not what you want, please specify the result you expect to get. Thanks!

  14. Hi,
    I was wondering if you can make a progress bar, once a certain numbers of cells are checked pr have a certain color.
    Thank you

  15. Hi,

    I need to figure out the following.

    Example:
    I need the total from row 1 plus the total from row 2 plus just 30% of the total from row 3, how would this be done?

    Thanks

  16. Hi Natalia,

    I've already learned a bunch through your posts so please keep it up!!
    I do have a question i can't seem to find the answer to though, if you could point me in the right direction i'd be forever grateful to you!

    I'm building out a material spreadsheet for my plumbing company, I have a cell that is designated for my cost from supplier. My material markup is based on a sliding range meaning the higher the cost, the less my markup percentage will be.

    For example,
    from $.01-$5 the markup will be 4x
    from $5.01-$10 the markup will be 3.25x
    and so on...

    Is there a way to set up a few different formulas based on different cost ranges and different percentages to calculate my selling price? This spread sheet is roughly 1400 items and my cost gets updated once every two months so going through each line item would take forever.

    If you have any insight on how to do this or what you feel would be the best way to go about it i'd greatly appreciate it.
    Thank you!

  17. Whats the formula of

    plus 1% per 14

      • If I were to make a formula in excel how can I come up with it?

        If I put X I would like to know how many percent it is in total. If 14 is equal to 1%

          • It works perfectly! You're good at this and thank you for your help Natalia! More power to your ablebits team

  18. I am using a formula to add 2 percentages =1.45+2.41. However, when it completes the formula it gives me 386.00% instead of 3.86%. Right now I'm dividing by 100 to get the correct number, but I've never had this happen before. What could cause this?

    • Hello Sarah,

      This happens because 1.45 and 2.41 are numbers. They total in 3.86 that you format as percent. This turns them into 386.00%

      I'd advise you to enter 1.45 and 2.41 to separate cells and format those cells as percent first. Then sum them up. Otherwise, dividing the total by 100 is a correct solution.

  19. Hello,

    I have averages from scores that I need to combine into a range and then create a chart with three different x-axis data points for numbers in the defined ranges. On the y-axis, I need the percentage out of 100%. Example (I have 100 scores with averages from 0-5. Scores that fall between 0-3 = never, 3-3.49 = sometimes, 3.5-5 = often). I need the chart to group all of the scores for those three ranges (x-axis), and I need the percentages on the y-axis to be out of all 100 scores

    • Hello Green,

      Sorry, your task is not quite clear. You can read about Google Sheets Charts and their possible settings in this tutorial.

      Alternatively, please consider sharing 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. I'll look into it.

      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.

  20. Hello, this is a helpful tutorial, but I'm not finding what I need.
    Is there a way to turn a decimal into a payment factor?

    Example:
    6.5 into 0.065
    10 into 0.10
    9.75 into 0.0975

    Any insight would be appreciated

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 :)