How to Calculate Percentage of Total in Google Sheets

Published on

Let’s master a key skill in Google Sheets: calculating percentages of a total. This is useful for analyzing sales data, budgets, and survey results, and it’s a relatively simple task.

Percentages help you understand how each value compares to the total. In this guide, I’ll walk you through the steps using simple formulas. By the end, you’ll be confident in applying this technique to your own sheets.

The Formula to Calculate Percentage of Total in Google Sheets

Use the following formula to calculate the percentage of a total in Google Sheets:

=ArrayFormula(TO_PERCENT(IF(range="",,range/SUM(range))))
  • range refers to the column or row where your data is located.
  • For example, if your data is in B1:B, replace range with B1:B.

Example: Calculating Percentage of Total in Google Sheets

Here’s an example using administrative expenses in column B and their descriptions in column A. Let’s calculate what percentage each expense contributes to the total.

Sample Data: Administrative Expenses

AB
Licensing & Fees735
Vehicle Registration0
Visa Processing0
Sundry Office Expenses653
Local Conveyance82
Medical Expense240
Accommodation Charges0
Office Rent6000
Electricity733
Trade License Renewal2200

To calculate the percentages, enter the following formula in cell C1:

=ArrayFormula(TO_PERCENT(IF(B1:B="",,B1:B/SUM(B1:B))))
Example of calculating the percentage of a total in Google Sheets

Key Points:

  • Replace B1:B with your data range if it differs.
  • Ensure the column where you apply the formula has enough blank rows to display the results, as it’s an array formula. For example, if the range is B1:B and you apply the formula in C1, C1:C should be empty. If the range is B1:B100 and the formula goes in C1, then C1:C100 must be empty.

Let’s break down the formula so that you can understand what each component does to return the percentage of the total.

Explanation of the Formula

  1. ArrayFormula
    • Enables the formula to process the entire range (B1:B) in one go, without dragging it down row by row.
  2. IF(B1:B="",,...)
    • Logical Test: Checks if a cell in B1:B is empty.
      • If empty, returns a blank ("").
      • Otherwise, it proceeds to calculate the percentage of the total.
  3. B1:B/SUM(B1:B)
    • Divides each value in B1:B by the total sum of all values in the range.
    • This calculates the proportion of each value relative to the total.
  4. TO_PERCENT(...)
    • Converts the resulting decimal values into percentage format.
    • Example: 0.25 becomes 25%.

Additional Resources

Explore these guides for more on working with percentages in Google Sheets:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.