Percentage in Google Sheets - tutorial with useful formulas

Percentage calculation is not only useful in work, it also plays an important role in daily life. Want to know how to tip properly? Is the discount really cost-effective? How much does it cost to pay at different interest rates? This article will answer these and other similar questions.

- What is percentage

• How to calculate percentages in Google Sheets
• Percentage of total in Google spreadsheets
• Percentage growth and decrease formula
• Calculate amounts and totals by percentage in Google spreadsheets
• Increase and decrease numbers by percentage

What is percentage

Percentage (or percentage) means one hundredth of the portion. It is represented by the % symbol, representing a part of the whole.

For example, you and your four friends are going to buy another friend a birthday gift worth $250, and you guys pay together. How much percentage have you paid for this gift?

The usual method of calculating percentages is as follows:

(part/total) 100 = Percentage Example: You paid $50. 50/250 100 = 20%, i.e. you pay 20% of the gift cost.

However, Google Sheets simplifies this task by calculating some of the content for you. Below I will show some basic formulas that can help you get different results based on different tasks, such as calculating percentage changes, total percentages, etc.

How to calculate percentages in Google Sheets

The method of calculating percentages on Google spreadsheets is as follows:

Part/Total = Percent is different from the previous formula, which is not multiplied by 100. There is a reason for this. Simply format the cell as a percentage and Google Sheets will do the rest.

How does this apply to your data? Suppose you have recorded the number of ordered and delivered fruits (columns B and C, respectively). To calculate the percentage of fruits you have received:

• Enter the following formula in cell D2: =C2/B2
• Copy it to your table.
• Go to Format > Numbers > Percentages in the Google Sheets menu to apply the percentage view.

Notice. You need to complete these steps to create any percentage formula in Google Sheets.

hint. If you want, you can use the options in the Google toolbar to reduce or increase the number of decimal places:

Here are the results in the actual data:

I deleted all the decimal places so that the formula shows the result as a percentage after rounding.

Percentage of total in Google spreadsheets

Here are some more examples of calculating total percentages. While the same approach was shown before, it works very well for that example, but may not be enough for other datasets. Let's see what else Google Sheets offers.

Common tables with totals at the end

I believe this is the most common situation: column B in your table contains values. Their total is at the end of the data: B8. To find the total percentage of each fruit, use the same basic formula as before, but slightly different - use absolute references for cells containing the sum.

This type of reference (absolute reference, with dollar sign) does not change when copying the formula to another cell. Therefore, each new record will be calculated based on the sum in $B$8:

=B2/$B$8

I also format the result as a percentage and keep two decimal places for display:

One item occupies several rows - all rows are part of the total

Now, suppose a fruit appears multiple times in your table. How many parts are totally made up of all delivery of this fruit? The SUMIF function will help answer this question:

=SUMIF(range, condition, sum range) / Total It only sums the numbers belonging to the fruit of interest and divides the result by the total.

Please take a look at it yourself: Column A contains fruits, column B contains orders for each fruit, and B8 contains the total of all orders. E1 contains a drop-down list with all possible fruits, and I chose to check the total of the plums . The formula for this case is as follows:

=SUMIF(A2:A7,E1,B2:B7)/$B$8

hint. Whether to use fruit or not depends entirely on you. Instead, you can put the necessary names directly in the formula:

=SUMIF(A2:A7,"李子",B2:B7)/$B$8

hint. You can also check the total portion of the different fruits. Just add up a few SUMIF functions and divide the result by the total:

=(SUMIF(A2:A7,"李子",B2:B7) SUMIF(A2:A7,"榴莲",B2:B7))/$B$8

Percentage growth and decrease formula

There is a standard formula that can be used to calculate percentage changes in Google Sheets:

=(BA)/A

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

Suppose you had $50 yesterday. You saved another $20, and today you have $70. This increased by 40%. Conversely, if you spend $20 and only $30 left, it's a 40% reduction. This explains the above formula and clearly illustrates which values ​​should be used as A or B:

=(New Value – Old Value) / Old Value Now let's see how this works in Google Sheets, OK?

Calculate the percentage change between columns

I have a fruit list (column A) and I want to check how the price of this month (column C) changes compared to last month (column B). The percentage change formula I use in Google Sheets is as follows:

=(C2-B2)/B2

hint. Don't forget to apply the percentage format and adjust the number of decimal places.

I also used conditional formatting to highlight cells with percentage growth (red) and percentage reduction (green):

Percentage change between rows

This time, I track the total sales (column B) for each month (column A). To make sure my formula works correctly, I should enter it from the second line of the table - C3:

=(B3-B2)/B2

Copy the formula to all rows containing data, apply the percentage format, determine the number of decimal places, and then finish:

Here I also color the percentage reduction in red.

Percentage change compared to a cell

If you use the same sales list and decide to calculate the percentage change based on January only, you must always refer to the same cell - B2. To do this, set the reference to that cell to an absolute reference instead of a relative reference so that it does not change after copying the formula to another cell:

=(B3-$B$2)/$B$2

Calculate amounts and totals by percentage in Google spreadsheets

Now that you have learned how to operate percentages, I hope to get the total and amount will be a piece of cake.

Find amounts when totals and percentages are known

Suppose you spend $450 on shopping abroad, you want a tax refund - 20%. So how much should you expect to refund? What is 20% of $450? The calculation method is as follows:

Amount = Total* Percent If you put the Total and the Percent at B2, your formula is:

=A2*B2

Find totals with known amounts and percentages

Another example: You find an ad that sells a used scooter for $1,500. Price already includes a 40% discount. But how much does it cost to buy a new scooter similar? The following formula will solve this problem:

Total = Amount/Percent As the discount is 40%, this means you will be paid 60% (100% – 40%). With these numbers, you can calculate the original price (total):

=A2/C2

hint. Since Google Sheets stores 60% as one percent - 0.6, you can also get the same result using the following two formulas:

=A2/0.6 =A2/60%

Increase and decrease numbers by percentage

The following examples represent formulas you may need more often than others.

Increase the number in the cell by percentage

The general formula for calculating growth by a certain percentage is as follows:

=Amount*(1 %) If you have some amount in A2 and need to increase it by 10% in B2, your formula is:

=A2*(1 B2)

Reduce numbers in cells by percentage

To do the opposite and reduce the number by percentage, use the same formula as above, but replace the plus sign with the minus sign:

=A2*(1-B2)

Increase and decrease the entire column of numbers by percentage

Now suppose you write a lot of records in a column. You need to increase each of them by percentage in that column.

With our Google Sheets Power Tools add-on, you can do this in a quick way (6 steps to be exact).

1. First select all the values ​​you want to add and apply the automatic format: Format > Numbers > Automatic :

Notice. This is to avoid formatting issues in the next steps. 2. Keep the selected value and run text from Extended > Power Tools > Tools > Text Tools : 3. Open Add text by position and add an equal sign (=) to add it to the beginning of each cell: 4. Click Run to convert all numbers into formulas: 5. Continue with the Formula tool in Power Tools and select the option to modify all selected formulas. You will see that %formula% has been written there. You should add those calculations that you want to apply to all formulas at once.

Remember the formula for increasing numbers by percentage?

=Amount (1 %) Well, you already have these amounts in column A - this is %formula% of your tool . Now you just need to add the missing part to calculate the increase: *(1 10%)*. The entire entry looks like this:

%formula%*(1 10%)

6. Click Run , and all records will be increased by 10% at one time:

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

Click the button below to install Google Sheets' Power Tools and do the same for your data.

That's it! All of these examples are easy to understand and are designed to remind those who have forgotten or don't know how to calculate the basic rules of percentage in Google Sheets.

Spreadsheet with examples of formulas

Google Sheets Percentage Formula (copy yourself to practice)

The above is the detailed content of Percentage in Google Sheets - tutorial with useful formulas. For more information, please follow other related articles on the PHP Chinese website!