How to calculate percentage in Google Sheets - formula examples included

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

Percent (or per centum from Latin) means one hundredth part. It is marked by a special sign: %, and represents a part of the whole.

For example, you and your 4 friends are getting a birthday gift for another friend. It costs $250 and you're chipping in together. How much should you give, percentage wise?

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 everything simpler since it calculates some parts for you. Though there is no unique formula for each and every case, there are some basic ones that will help you get different results depending on your task, whether calculating percentage change, percent increase, percentage of total, etc.

Percentage formula in Google Sheets

This is how Google spreadsheet calculates percentage:

Part/Total = Percentage

Compared to the formula I mentioned previously, this one doesn't include multiplying 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 delivered, do the following:

  • Enter this formula in 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 a rounded percent of delivered fruits.

Calculate percentage of total in Google Sheets

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.

Example 1. The total is at the end of the table in a certain cell

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:

=B2/$B$8

Tip. To turn relative reference into absolute quickly, click on it in the formula bar and press F4 on your keyboard. 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.

I also formatted the results as percent and left 2 decimals to display:
Calculate percentage of a total.

Example 2. Parts of the total are in multiple rows

Now, suppose a fruit appears more than once in your table. You need to find what part of the total is composed by all orders of that fruit.

The SUMIF function will help:

=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 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 what part of the total is made by few products. Just use several SUMIF functions and divide their result by the total:

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

Percent change formulas for Google Sheets (increase/decrease)

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 which 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?

Example 1. Percent change between columns

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:
Google Sheets percentage formula.

Example 2. Percent change between rows

This time, I'm tracking total sales (column B) over each month (column A). To calculate percentage change in rows, I use the following:

=(B3-B2)/B2

Note. To make sure this percentage formula in Google Sheets works correctly, start entering it from the second row of your data (skipping the first one) - in my example, it's C3.

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 colored percentage decrease with red.

Example 3. 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.

Calculate amount and total by percentage

Now that you've learned how to operate percentages, I hope getting total and amount will be a child's play.

Example 1. Find amount by 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.

Example 2. Find total by amount and percentage

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

Total=Amount/Percentage

Since the discount is 40%, it means you are to pay 60% (100% - 40%). Then, calculate the original price (total) according to cells with data:

=A2/B2

Find total by amount and percentage in Google Sheets.

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 / decrease by percentage

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

Example 1. Increase a number in a cell by percent

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

=Amount*(1+%)

Suppose 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.

Example 2. Decrease a number in a cell by percent

To decrease the number by a percentage, use the same formula as above but replace the plus sign with a minus:

=A2*(1-B2)

Example 3. Increase / decrease entire 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 (5 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:
    Run the Text tool from Power Tools.
  2. Open Add text by position and choose to add an equal sign (=) at the beginning of each cell:
    Add an equal sign to each cell.
  3. Click Run to turn all your numbers to formulas:
    Turn all records to formulas by adding leading equal sign.
  4. 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.

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

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.

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!