Split a grant across budget categories by percentage — each line is the grant total times its allocation percent. Build a tidy grant budget that always sums back to the award.
The example
$50,000 grant, 60% to programs.
| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Programs 60% | $30,000 |
| 3 | Personnel 30% | $15,000 |
The formula
The formula:
How it works
How it works:
- List each category with its allocation percent (totaling 100%).
- Each line =
grant_total × category_percent, rounded to cents. - Verify the lines sum to the grant with
SUM— rounding can leave a penny off. - A SUMPRODUCT check (
SUMPRODUCT(percents)) confirms allocations total 100%.
Make the budget tie out exactly. Rounding each line independently can make them sum to $49,999.99 instead of $50,000. Fix it by computing all but one line normally and making the last line the remainder: =grant_total - SUM(other_lines). Funders expect the budget to total the award to the penny.
Try it: interactive demo
Grant total and a category percent.
Variations
Last line as remainder
Force the tie-out:
Check the total
Should equal grant:
Percents sum to 1?
Validation:
Pitfalls & errors
Must tie to the award. Make the last line a remainder so allocations sum exactly to the grant.
Percents total 100%. Check with SUMPRODUCT before trusting the split.
Watch restrictions. Some grants cap indirect/overhead — respect funder limits.
Practice workbook
Frequently asked questions
How do I allocate a grant budget by percentage in Excel?
How do I make the budget total exactly?
How do I confirm my percentages add to 100%?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class