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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.