For example, you have a list of your monthly expenses and income in Google Sheets. You want to know what percentage of your income you spend each month.
For example, if your income for June is $4,000, and your expenses for June are $1,200, you can calculate the percentage of your income that you spent on expenses.
What is a Percentage?
A percentage expresses a number as a part of a whole, where the whole is always 100. In everyday life, percentages help us understand proportions. For example, if you spend $20 out of $100, you’re spending 20% of your money. Percentage is one of the most basic but useful calculations in mathematics.
There are two major ways to calculate the percentage when using Google Sheets. One is when you want to apply the percentage format to the value, and the other is when you don’t want to apply the percentage format.
Basic Percentage Calculation in Google Sheets
As I said, there are two ways to calculate percentages in Google Sheets.
Method 1
In the first method, we need to use (part/whole)*100, which we have used in the example.
Here, the formula calculates the percentage of income spent as expenses for January. The formula divides the expenses by the income (C2/B2), which gives the proportion of income spent.
Then, it multiplies the result by 100 to convert this proportion into a percentage.
So, =(1200/4000)*100 calculates to 30. This means 30% of the income was spent on expenses in January. When you are using this formula, in that case, you don’t need to apply the percentage format to the cell.
Alert – Be careful with divisions to avoid errors. For instance, ensure the whole is not zero, as this will cause a division by zero error.
Method 2
Now, let’s move to the second method. In this method, you don’t need to multiply it with 100. You can divide the part by the whole and then apply the percentage format to the cell.
The result shows 0.3. Now, this 0.3 is the part of 1 here instead of 100. But the proportion is the same: 0.3 in 1 and 30 in 100 have the same proportion. Right?
That’s why you need to apply the percentage format to the cell: it will show as a percentage value instead of a number. To do this, you need to go to Format > Number > Percentage, or you can also go to Toolbar > Number Format (123) > Percentage.
Get Percentage Part from a Number
In Google Sheets, you can use a simple formula to find the percentage part of a number. For example, suppose you want to find out what 35% of 10000 is.
Make sure B1 is formatted as a percentage. Go to the Toolbar and click the Format menu > Number > Percentage. After that, in the cell C1, enter the formula =A1 * B1. The formula =A1 * B1 will then calculate 35% of 10000, which is 3500.
Check for the Percentage Increase in Google Sheets
Let’s say you want to know how much the income increases or decreases over two months as a percentage. In this case, you need to write a formula using the below syntax:
=((New Value - Old Value) / Old Value) * 100
The formula will be:
=((I3 - I2) / I2)
This formula subtracts the old income (January) from the new income (February) and divides the result by the old income (January). As the cell we are using, the formula already has a percentage format applied, and the result is shown as a percentage value.
If you don’t want to apply the percentage format and want to get the percentage value as a number, you can use the formula below:
=((New Value - Old Value) / Old Value) * 100
=((I3 - I2) / I2) * 100
Running Percentage Variance
In the same way, you can create a running percentage variance, like the one we have in the following example:
These things we have done to get the percentage variance:
- Difference – The formula calculates the difference between the income for February and January: B3 – B2, which is 4200 – 4000 = 200.
- Percentage Change – The formula divides this difference by the January income to find the percentage change: (200 / 4000) = 0.05.
- Formatting as Percentage – Finally, this result is formatted as a percentage in the cell, which gives 5%.
You must drag from C3 to C13 to apply the same monthly calculation.
Percentage of Total
Let’s say you have a list of products and their quantities, and now you want to get the percentage of each product against the total quantity of all the products. To write a formula for this, if you have data in the column B:
First, you need to refer to the quantity of the first product and then the sum of all the products. In the SUM, you need to use an absolute reference to the range (it will not change when you drag the formula down).
- SUM($B$2:$B$6) – This part of the formula adds up all the sales quantities from cells B2 to B6. In our example, it sums 150 (A) + 300 (B) + 250 (C) + 200 (D) + 100 (E) = 1000.
- B2/SUM($B$2:$B$6) – This part divides the sales quantity of product A (150) by the total sales quantity (1000). So, 150 / 1000 equals 0.15.
So, in cell C2, the formula calculates that product A’s sales quantity is 15% of the total sales quantity. In the end, when you get the result in the cell, you need to drag down the formula to the last cell and then apply the percentage format to the entire range to show the values as a percentage.
If you total the sums of all the percentages in the range, the sum will be equal to 100.