Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to distribute a total evenly across rows in Excel and Google Sheets
Topic:Data cleaning & shapingExcel & Google Sheets
=INT(A2/B2)+(ROW(A1)<=MOD(A2,B2))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Total | Rows |
| 100 | 7 |
| Dist | |
| =INT($A$2/$B$2)+(ROW(A1)<=MOD($A$2,$B$2)) | |
| =INT($A$2/$B$2)+(ROW(A2)<=MOD($A$2,$B$2)) | |
| =INT($A$2/$B$2)+(ROW(A3)<=MOD($A$2,$B$2)) |
=INT(A2/B2)+(ROW(A1)<=MOD(A2,B2))→15
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter the total to distribute in A2 (e.g. 100) and the number of rows in B2 (e.g. 7).
- 2In A5 (first distribution row), enter: =INT($A$2/$B$2)+(ROW(A1)<=MOD($A$2,$B$2)). INT gives the floored base share; the second term adds 1 to the first MOD(...) rows to absorb the remainder.
- 3Copy A5 down by B2 rows. Each row gets either the base share or base share + 1, so the sum is exactly A2.
- 4Verify with =SUM(A5:A11)=$A$2 — this should return TRUE.
- 5For decimal amounts (e.g. currency), multiply A2 by 100 first, distribute integers, then divide results by 100 to avoid floating-point precision issues.
Tips
- ROW(A1) returns 1 in the first row of the formula, 2 in the second, and so on as you copy down — this is the position counter that drives the remainder distribution.
- If the distribution rows do not start at row 5, adjust the ROW reference: use ROW(A1) and let it increment naturally as you copy rather than referencing the actual row number.
- For weighted distribution (not even), replace INT($A$2/$B$2) with ROUND($A$2*weight/total_weight,2) per row, then reconcile the last row to the residual.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why not just use A2/B2 directly in each row?
A2/B2 produces a repeating decimal when A2 is not evenly divisible. Summing those decimals will drift from the exact total due to floating-point representation. The INT + MOD approach guarantees the parts sum to A2.
How do I distribute to a dynamic number of rows without knowing it in advance?
Put the row count in a reference cell (like B2) and use that cell in the formula. Change B2 and copy the formula block to match — or use SEQUENCE to generate the rows dynamically.
Can I use QUOTIENT instead of INT for the base share?
Yes. QUOTIENT(A2,B2) is equivalent to INT(A2/B2) for positive integers and is slightly more readable. For negative numbers they differ, but distribution totals are usually positive.
More on Data cleaning & shaping
See all →How-to
How to merge two columns
Combine two columns into one with the & operator — perfect for joining first and last names into a full name.
How-toHow to create a sequential invoice number
Automatically generate sequential invoice numbers with a prefix and zero-padded counter using TEXT and ROW in Excel and Google Sheets.
How-toHow to number rows within each group
Assign sequential row numbers that restart for each group using COUNTIF with a mixed reference in Excel and Google Sheets.
How-toHow to remove blank rows
Delete empty rows quickly by filtering for blanks and deleting visible rows, or use Go To Special in Excel for a one-click approach.
How-toHow to transpose rows to columns
Flip rows and columns using the TRANSPOSE function or Paste Special to convert horizontal data to vertical and vice versa.
How-toHow to combine multiple columns into one
Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.