Calculating a percentage is one of those things that many people cringe at. But if it’s helpful to display a percentage in your spreadsheet, we’ll show you some easy ways to calculate and format a percentage in Google Sheets.
Method One: Divide Part of a Total
The percentage for a part of a total is calculated with a simple formula in Google Sheets.
The syntax for the formula is
=(part/total) or divide divided by total. You can use cell references or insert values for the part and total, or a combination of both.
Use the following formula to find the percentage for a part in cell A1 of the total in cell B1:
Here the result is 0.25 or 25 percent.
Use this formula to find the percentage for a part in cell A1 for the total of 100:
Use the following to find the percentage for the part 25 of the total 100:
You’ll notice that when Google Sheets gives you the percentage, it’s formatted as a decimal by default. But you can change this automatically or manually in percentage if you want.
Automatically format as a percentage
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 decimals. To do this, use the TO_PERCENT function.
RELATED: Change and create a custom number format in Google Sheets
The syntax for the function is
TO_PERCENT(value) where you can use a cell reference or 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 earlier to calculate percentages:
Manually format as a percentage
To manually format your formula result as a percentage, select the cell containing the comma and do one of the following:
- Click the Format as Percentage button in the toolbar.
- Choose Format > Number > Percentage from the menu.
- Click the More Layouts button in the toolbar and choose Percentage.
Tip: You can also use the Increase Decimal Places or Decrease Decimal Places button in the toolbar to arrive at an integer value for your percentage.
Method Two: Divide and Multiply Part of a Total
As an alternative to the first method, you can eliminate the decimal result when calculating the percentage by multiplying the division by 100.
RELATED: Multiplying numbers in Google Sheets
This syntax is
=(part/total)*100 or a part divided by the total number 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.
Now you get the result 25 instead of 0.25.
The same goes for the other formulas:
Method Three: Calculate Percentage Based on Criteria
While the above options are ideal for simple percentages in Google Sheets, you may need something more robust, such as calculating a percentage based on certain conditions. In this case, you can use the PERCENTIF function.
RELATED: How to calculate percentage increments in Excel
The syntax for the function is
PERCENTIF(range, criteria) where the
criteria can be a number, text, or cell reference. The function also automatically formats the result as a percentage.
Use this formula to find the percentage of bills over $50 in the cell range B2 to B7:
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 for fuel.
Since the function also allows wildcard characters, let’s look at another example. Using this formula, you can calculate the percentage of students whose names begin with the letter J in the range D2 through D7.
Calculating percentages in Google Sheets might be something you want to do with your next sheet. So, keep this how-to in mind for a little help with those calculations!