To calculate the percentage of total in Google Sheets you can use array or non-array based formulas.
Normally the output of such calculations will be automatically formatted to numbers like 0.25 for 25%, 0.15 for 15%, etc.
You can either use the Format menu or To_percent function to format the output of the percentage of total calculation in Google Sheets.
I will try to include all these tips in this quick tutorial. So here we go!
Sample Data: Administrative Expenses.
| A | B | |
| 1 | Licensing & Fees | 735.00 |
| 2 | Vehicle Registration | 0.00 |
| 3 | Visa Processing | 0.00 |
| 4 | Sundry Office Expenses | 653.00 |
| 5 | Local Conveyance | 82.00 |
| 6 | Medical Expense | 240.00 |
| 7 | Accommodation Charges | 0.00 |
| 8 | Office Rent | 6000.00 |
| 9 | Electricity | 733.00 |
| 10 | Trade License Renewal | 2200.00 |
| 11 | TOTAL | 10643.00 |
The sample data shows the administrative expenses of an organization in a quarter. Let’s calculate the percent distribution of the total administrative costs which is 10643.00.
Formulas to Get Percentage of Total in Google Sheets
Non-Array Formula:
Assume we have already the sum of expenses in B11 as above. If so, use =to_percent(B1/$B$11) in cell C1 and drag down the formula. Then increase the decimal places of the cells in column C that contain the formulas.
If you want to see the underlying values in column C (please see the just below image), simply remove the to_percent function from the formulas.
Looking at the percent distribution, you can easily find that the major part of the administrative cost is the office rent (56.38%).
Array Formula:
You can replace the drag and drop (non-array) formula to calculate the percentage of total in Google Sheets with an array formula.
Empty the range C1:C10 and enter the below array formula in C1.
=ArrayFormula(to_percent(B1:B10/$B$11))Percent Distribution without Total Row
In the above two example formulas, I have used cell B11 as a reference. It contains the total of the values in the array B1:B10 which is obviously the total administrative cost.
Assume you don’t have such a total row (A11:11) as you have the plan to add new account heads (new rows with expenses) in the future.
In that scenario, you can calculate the percentage of the total of a column as below.
Drag-Down (Non-Array) Formula for Infinite Rows: This formula to be applied in C1 and copy down.
=to_percent(B1/sum($B$1:$B))Array Formula to Get Percentage of Total in Infinite Rows: Just enter in cell C1.
=ArrayFormula(if(len(A1:A),TO_PERCENT(B1:B/sum(B1:B)),))Additional Resources:
