# How to calculate percent of total with ARRAYFORMULA in Google Sheets

One formula at the top fills the column: `=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10)))`.

**Google Sheets:** `=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10)))`

**Excel:** In Excel 365 the same formula works without ARRAYFORMULA and spills down: `=IF(B2:B10="","",B2:B10/SUM(B2:B10))`. In older versions, use `=B2/SUM($B$2:$B$10)` and fill down.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Channel | Revenue | Share |
| Online | 120 |  |
| Retail | 80 |  |
| Wholesale | 45 |  |
| Partners | 30 |  |
| Events | 15 |  |
| Other | 10 |  |
|  |  |  |
|  |  |  |
|  |  |  |

Formula in C2: `=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10)))`

Result (spills 9 × 1): 40.0%; 26.7%; 15.0%; 10.0%; 5.0%; 3.3%; ; ; 

## How it works

ARRAYFORMULA makes the division work on the whole range at once: each revenue in B2:B10 is divided by the total, and the results fill down from C2. The IF leaves a blank where there is no revenue yet, so new rows typed into B7:B10 get a share automatically.

## Other ways

- `=ARRAYFORMULA(B2:B10/SUM(B2:B10))`: Without the IF, empty rows show 0%. Fine for a range with no gaps. (Shows 0.0% in the three empty rows.)

## Common mistakes

- Anything typed in C3:C10 blocks the result and shows #REF!. Leave the column below the formula empty.
- An open-ended range like B2:B works, but then SUM(B2:B) also includes any total row you add at the bottom.

## Questions

**Do I need ARRAYFORMULA in Excel?**

No. Excel 365 spills array results automatically; ARRAYFORMULA is a Google Sheets function.

**How do I round the shares?**

Wrap the division: `=ARRAYFORMULA(IF(B2:B10="","",ROUND(B2:B10/SUM(B2:B10),3)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percent-of-total-whole-column-in-google-sheets