How-To

How to Calculate Percentage Using Google Sheets

By Jeff Butts

Published December 5, 2023

If you’ve been wondering how to use Google Sheets to calculate a percentage value, we’ve got the answer here.

You may not realize it right away, but percentage calculations are pretty useful. In everyday life, you might need to calculate how much of a tip to leave or the percent discount or increase on something you’re looking to purchase. Luckily, it’s pretty easy to calculate a percentage using Google Sheets. Let’s look at a couple of ways to do it.

First, What exactly is a Percentage?

You probably know this already, but just in case you don’t. Percent (or percent) means a one-hundredth part of the whole. For example, let’s assume you and four friends are going on vacation together. If you’re splitting the cost of a $250 Airbnb, you might want to know what percent of that you’ve paid.

If the total cost is $250 and you pay $50 of that, you’ve paid 20% of the whole. Let’s break that down.

The equation to calculate a percentage is:

(Part/Total) * 100 = Percentage*

Let’s plug in your numbers: the $50 you paid is the “Part,” and $250 is the “Total.”

50/250 * 100 = 20

You’re going to do something similar in Google Sheets, so let’s dive into that now.

Working With Percentages in Google Sheets

You’ll use the same basic formula as before, but you don’t necessarily need to multiply by 100. This is because you can format the cell as a percentage, and Google Sheets will handle that multiplication for you.

We’ll use the same example from above.

The Simple Way to Calculate a Percentage in Google Sheets

1. Input your part and total amounts into separate cells.
2. In a third cell, use the formula =A1/B1 to calculate the percentage. Remember, in this example, A1 contains our partial amount and B1 is the total.
   
3. The value 0.2 appears. Now, click the percentage formatting icon with that cell selected. The value changes to display as 20.00%.
   
4. You can now use the decimal places buttons to add or remove values after the decimal.
   

Organizing and Displaying Your Percentages for Easier Understanding

That was, of course, a very simple example. What if you need to do even more with percentages? Let’s say you and your friends have paid different amounts for an Airbnb. How do you track what percentage each has paid?

Google Sheets has your back, and it’s both quick and easy to do.

1. Enter the names of each of you on separate rows, just for clarity.
2. Input the totals and amounts paid.
3. In the first row, use the formula =B1/C1.
4. Google Sheets may offer a suggested autofill to carry those calculations down through the other rows.
   
5. If it doesn’t offer the autofill suggestion, click the autofill handle (the blue dot in the bottom right corner of the cell containing the calculation.) Drag down until the cells beneath are selected, then release the mouse button.
   
6. Google Sheets copies the formula to the other cells, modifying it automatically to reflect the rows.
   

Of course, it looks a bit funny having the total in each row like that. Instead, let’s move the total to the bottom of our spreadsheet.

Calculating Percentage in Google Sheets with a Total at the End of the Table

To do this, we’ll need to use an absolute reference to the cell with the total. An absolute reference means that the value won’t change when you copy it to other cells.

1. Enter the formula as =B1/$B$13. The dollar signs ($) in the cell reference mark it as an absolute reference.
   
2. Use the autofill handle to copy the formula down your table.
3. Your formula will populate through the table, changing to use the appropriate “Part” values for each row.
   

Using Google Sheets to Calculate Percentage Changes

Of course, that’s just the tip of the iceberg. You can also use Google Sheets to calculate percentage changes, like when prices go up or down.

The formula to calculate a percentage change is:

=(New value - Old value) / Old value

Let’s see how that works in our spreadsheet.

1. Input your old and new values on each row.
2. Use the formula above to calculate the percentage change, using the cell references as appropriate. Our formula should be =(C2-B2) / B2.
   
3. If you have multiple rows, use the autofill handles to copy your formula to each.

To pretty it up a bit and make the data easier to digest, you can use conditional formatting to highlight the positive changes in one color and negative changes in another.

Finding a Total When You Know the Amount and Percentage

Finally, what if you know how much something costs and what percentage discount is already factored in? You can use that information to calculate the original, undiscounted total.

The formula you’ll use for this is:

Total = Amount/Percentage

Back in Google Sheets, let’s say we know a computer is priced at $1499 after a 40% discount. We can easily find the normal price.

1. Since the discount is 40%, we’re paying 60% of the regular retail price. The example below shows all of those values.
   
2. Beneath Total Undiscounted Price, enter the formula =A2/C2 to calculate the total price.
   
3. Google Sheets immediately displays the original total of the computer, $2,498.33.
   

Wrapping It All Up

As you can see, it’s easy to work with and calculate percentage values in Google Sheets. There are numerous ways you can use this, so play around and let the spreadsheet handle those annoying calculations you’ve been trying to do on paper or, even worse, in your head.