google sheets formula for percentage of another cell

Google Sheets: Calculate Percentage of Another Cell

By Christopher Martinez
AD

In Google Sheets, you might often find the need to calculate the percentage of another cell's value relative to a total or a comparison value. While it may seem complex, Google Sheets offers a simple and effective method to achieve this using its built-in functions. In this guide, we will explore formulas to calculate the percentage of another cell's value in Google Sheets.

Understanding the core concept is crucial before diving into the formulas. You might want to express a part (cell A) as a percentage of the whole (cell B), or compare two values (cell C and D) as a percentage of each other. We'll cover both scenarios in this article.

Calculating Percentage of a Total (Cell B)

To find the percentage of a cell (A) relative to another cell (B), you can use the following formula:

AD
the text reads,'get the sheet 100 + formulas'and an image of a
the text reads,'get the sheet 100 + formulas'and an image of a
How to Calculate Percentage Increase in Google Sheets: 3 Easy Formulas
How to Calculate Percentage Increase in Google Sheets: 3 Easy Formulas
Conditional Formatting Based on Another Cell in Google Sheets
Conditional Formatting Based on Another Cell in Google Sheets

(A2/B2)*100

Here, A2 is the value whose percentage we want to find, and B2 is the total or base value. Replace '2' with your respective cell reference.

Google Sheets - Conditional Formatting Based on Another Cell
Google Sheets - Conditional Formatting Based on Another Cell
How to Easily Calculate Percentages in Google Sheets: A Step-by-Step Beginner’s Guide
How to Easily Calculate Percentages in Google Sheets: A Step-by-Step Beginner’s Guide
How to Calculate Percentage in Google Sheets
How to Calculate Percentage in Google Sheets

For example, if A2 has 50 and B2 has 100, the formula will calculate 50% (as 50 is 50% of 100).

Using named ranges for clarity

When working with large datasets, named ranges can improve readability and maintainability. Here's how to name ranges 'TotalSales' and 'GoalSales':

AD
  1. Select the cell (B2 in our example),
  2. Click 'Insert' in the menu, then 'Named range' and 'Create' to confirm.

Now, the formula becomes (A2/TotalSales)*100.

How to Show Formulas in Google Sheets
How to Show Formulas in Google Sheets
Median IF in Google Sheets
Median IF in Google Sheets
20 Google Sheets Formulas Everyone Should Know (Save This!)
20 Google Sheets Formulas Everyone Should Know (Save This!)
Google Sheets: Creating Complex Formulas
Google Sheets: Creating Complex Formulas
6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
How to Use FILTER and LARGE Formula Together
How to Use FILTER and LARGE Formula Together

Auto-filling the formula

If you want to replicate this calculation for multiple rows, you can drag the formula in the fill handle (small square at the bottom right corner of the cell) or use array formula (=ARRAYFORMULA((A2:A/TotalSales))*100).

Calculating Percentage Change Between Two Cells (C and D)

To find the percentage change between two values (C and D), use the following formula:

AD

((D2-C2)/C2)*100

8 Google Sheets Formulas Everyone Should Know
8 Google Sheets Formulas Everyone Should Know

Here, D2 is the new value, and C2 is the original/previous value.

For instance, if C2 has 100 and D2 has 120, the formula will calculate +20% (as 120 is 120% of 100, and the change is +20%).

AD

Using the PERCENTCHANGE function

For a more straightforward approach, use Google Sheets' built-in PERCENTCHANGE function: =PERCENTCHANGE(B2,C2). Here, B2 is the new value, and C2 is the old value.

Google Sheets offers powerful and intuitive tools to work with percentages. By mastering these formulas and functions, you can simplify your data analysis and reporting. Happy calculating!

AD