Calculating a percentage is one of those things that sets a lot of people back. But if displaying a percentage in your spreadsheet is beneficial, we’ll show you some easy ways to calculate a percentage and format it in Google Sheets.
First method: dividing part of a total
You can calculate the percentage of a part of a total using a simple formula in Google Sheets.
The syntax of the formula is =(part/total) or part divided by total. You can use cell references or inserted values for part and total or a combination of both.
To find the percentage of the part of cell A1 compared to the total of cell B1, you must use the following formula:
Calculate percentage for cells
Here, the result is 0.25 or 25%.
To find the percentage of part of cell A1 of the total of 100, you would use the following formula:
Calculate percentage for a cell and value
To find the percentage for part 25 of the total of 100, you would use the following formula:
Calculate percentage for values
You will notice that when Google Sheets gives you the percentage, it is formatted as a decimal by default. But you can change it automatically or manually to a percentage if you want.
Automatic percentage formatting
To automatically format the result as a percentage when you calculate it, you can add a function. The advantage is that you can not only calculate and format the result at the same time, but also adjust the decimal places. To do this, use the TO_PERCENT function.
The function syntax is TO_PERCENT(value), where you can use a cell reference or a number for the required argument.
Using our first percentage formula above, you can use this formula to convert the result:
The same goes for the other two formulas we used to calculate the percentages earlier:
Manual percentage formatting
To manually format the result of your formula as a percentage, select the cell containing the decimal and do one of the following:
Cliquez sur le bouton Format en pourcentage dans la barre d'outils. Choisissez Format > Nombre > Pourcentage dans le menu. Cliquez sur le bouton Autres formats dans la barre d'outils et choisissez "Pourcentage".
Format a number as a percentage
HOW TO CALCULATE THE AVERAGE IN GOOGLE SHEETS
Second method: dividing part of a total and multiplying it
As an alternative to the first method, you can eliminate the decimal result when calculating the percentage by multiplying the division by 100.
The syntax is: =(part/total)*100 or part divided by total times 100. You can include or remove the parentheses in the formulas below and get the same results.
Using the first example above, you would use this formula.
You now get the result 25 instead of 0.25.
Calculate percentage by multiplying
The same goes for the other formulas:
Calculate percentage by multiplying
Third method: Calculate a percentage based on criteria
While the options above are great for simple percentages in Google Sheets, you may need something more robust, like calculating a percentage based on certain conditions. In this case, you can use the PERCENTIF function.
The function syntax is PERCENTIF(range, criteria) where the criteria can be a number, text, or a cell reference. The function also automatically formats the result as a percentage.
To find the percentage of invoices over $50 in the range of cells B2 to B7, you would use the following formula:
PERCENTIF function for greater than
For an example of text criteria, you can use this formula to calculate the percentage of our expenses in cells A2 through A7 that are fuel related.
PERCENTIF function for text
Since the function also allows wildcards, let’s see another example. Using this formula, you can calculate the percentage of students whose names begin with the letter J in the range D2 to D7.
PERCENTIF function with a wildcard
You may be planning to calculate percentages in Google Sheets on your next sheet. So keep this manual in mind to help you with those calculations!