How To Use Google Sheets Percentage Formula
Feb 14, 2025
Did you know that Google Sheets can calculate percentages in just a few clicks?
Google Sheets' percentage formula can be useful for various spreadsheet tasks, such as tracking budgets, analyzing sales growth, or grading student exams. This article will teach you different ways to use the percentage formula in Google Sheets. We will start from basic calculations to more advanced applications.
Understanding Percentages in Google Sheets
A percentage represents a fraction of 100. In Google Sheets, percentages are expressed as decimals (e.g., 50% is stored as 0.5). Google Sheets allows you to apply percentage formatting to read and interpret values without manually converting them.
How Google Sheets handles percentages.
Get an idea of how Google Sheets handles percentages below:
-
When you enter a number and apply percentage formatting, Google Sheets converts it to a percentage automatically.
-
You can also input percentages directly using the % sign (e.g., entering 20% stores it as 0.2).
Read more: How to calculate percentage in Excel.
Basic Percentage Calculations in Google Sheets
You can quickly perform simple percentage calculations with Google Sheets formulas. Here are some common percentage formulas with examples:
1. Calculating a percentage of a number.
To find a percentage of a given number, use Google Sheet's basic percentage formula:
=Number * Percentage
Example: To calculate 20% of 150:
Result is 30.
2. Finding the percentage of a total.
You can determine what percentage one number is of another with a simple formula. Here’s how you can do it:
Formula: Part / Total
Example: Assuming a student scored 75 out of 100 on a test, you can find the percentage with this solution:
Result: 0.75 (Format as percentage to display 75%).
Example 2: Let's assume you have a table that contains the total sales derived from a list of items. To know how much each item sales contributed to the total in percentages:
Note: We used an absolute reference ($B$7) to ensure the referenced cell remains fixed as we copy the formula down. This keeps the sheet error-free.
Formatting Cells as Percentages
As our previous example shows, Google Sheets won't always display your result as a percentage. To make your percentage calculations appear correctly:
-
Select the cell(s) containing the calculated values.
-
Click Format > Number > Percent.
-
Adjust the decimal places as needed by clicking Increase decimal places or Decrease decimal places in the toolbar.
The result will look like this in two decimal places:
How to Calculate Percentage Change in Google Sheets
Percentage change is a mathematical concept that expresses the difference between an old value and a new value as a percentage of the old value. It helps measure how much something has increased or decreased relative to its original amount. You can use it to track growth or decline over time.
Percentage change formula: =((New Value - Old Value) / Old Value) * 100
Example:
To see how to use the percentage change formula in Google Sheets, let's measure some patients' weight loss/gain progress.
Enter the formula =(C2-B2)/B2 and drag the formula down to apply it to all rows. After that, you should format the cells as percentages.
Advanced Percentage Functions in Google Sheets
When working with percentages in Google Sheets, you may need to analyze data beyond simple percentage change calculations. Here are two powerful functions—PERCENTILE and PERCENTRANK—that help in statistical analysis and ranking values in a dataset.
1. PERCENTILE function.
The PERCENTILE function helps determine a specific percentile in a dataset. Percentiles indicate the value below which a certain percentage of data points fall. This is useful in grading, financial analysis, and performance tracking.
Formula: =PERCENTILE(range, percentile)
-
range → The dataset from which to calculate the percentile (e.g., A1:A10).
-
percentile → A value between 0 and 1 representing the desired percentile (e.g., 0.9 for the 90th percentile).
Example:
If you have exam scores in A1:A10 and want to find the 90th percentile (the score that is higher than 90% of the other scores):
2. PERCENTRANK function.
The PERCENTRANK function ranks a specific value in a dataset relative to other values and expresses it as a percentage. It tells you where a value stands compared to the rest of the dataset.
Formula: =PERCENTRANK(range, value, [significance])
-
range → The dataset in which you want to rank the value.
-
value → The specific number whose percentile rank you want to determine.
-
significance (optional) → The number of decimal places in the result (default is three decimal places).
Example:
If A1:A10 contains revenue figures and you want to find the relative rank of the value in A5:
The result is formatted as a percentage.
Common Mistakes and Tips
While percentages in Google Sheets are simple to work with, errors and inefficiencies can arise if certain best practices are overlooked. This section covers common pitfalls and how to avoid them to ensure accuracy in your calculations.
1. Dividing by zero errors.
If your denominator is zero, you will get an error. To avoid this, use an IFERROR function:
=IFERROR(A1 / B1, "N/A")
2. Using cell references instead of static numbers.
Consider using cell references (=A1 * B1) instead of static numbers (=150 * 20%).
3. Checking formatting issues
Ensure that percentages are formatted correctly so calculations display properly.
Final Thoughts
Understanding how to use Google Sheets' percentage formula can significantly enhance one's productivity and improve decision-making. It has use cases in various fields, including education, financial analysis, data analysis, etc. Start practicing these formulas, and soon, you'll be a pro at handling percentages in Google Sheets!
Visit Simple Sheets for more easy-to-follow guides and examples, and remember to visit the related articles section of this blog post.
For the most straightforward Excel video tutorials, Subscribe to Simple Sheets on YouTube!
FAQ
1. How do I format numbers as percentages in Google Sheets?
Select the cell(s), go to Format → Number → Percent in the Google Sheets menu. Adjust decimal places if needed.
2. Why is my percentage formula not displaying correctly?
Ensure the result is formatted as a percentage and check for division by zero errors.
3. How can I calculate the percentage change between two values?
Use the formula: =((New Value - Old Value) / Old Value) * 100
Related Articles
How to Count Cells with Text in Google Sheets
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
