Quick Answer & Formula
Excel
Sheets
Beginner
=Cell_Value / SUM($Range$Start:$Range$End) To calculate percent of total, divide the cell value by the sum of all values locked with absolute references: =B2 / SUM($B$2:$B$10), then format as Percentage (Ctrl+Shift+%).
How to Calculate Percent of Total in Excel & Google Sheets
Understanding sales mix share, expense breakdowns, or portfolio asset weights requires dividing an individual line item by the grand total.
1. The Core Formula
= B2 / SUM($B$2:$B$6)2. Departmental Expense Breakdown Example
| Department (Col A) | Monthly Expense (Col B) | Percent of Total Formula (Col C) | Share of Total |
|---|---|---|---|
| Engineering & Tech | $180,000 | =B2 / SUM($B$2:$B$6) | 45.0% |
| Sales & Marketing | $120,000 | =B3 / SUM($B$2:$B$6) | 30.0% |
| Customer Support | $40,000 | =B4 / SUM($B$2:$B$6) | 10.0% |
| General & Admin | $36,000 | =B5 / SUM($B$2:$B$6) | 9.0% |
| Facilities & Rent | $24,000 | =B6 / SUM($B$2:$B$6) | 6.0% |
| Total | $400,000 | =SUM(C2:C6) | 100.0% |
?
Frequently Asked Questions
Why do I need dollar signs ($) in the SUM range?
Dollar signs create absolute references (e.g. $B$2:$B$10). When you copy the formula down to lower rows, the denominator stays locked to the full dataset rather than shifting down.