Function Problems

How to Split a List of Amounts by Percentage in Excel

Huma Ashraf Ch Sep 27, 2026 869 views

Question details

The user needs to divide a list of dollar amounts into two groups based on a specific, changing target percentage (such as 72%/28% or 22.94%/77.06%) using a reusable template.

How to Split a List of Amounts by Percentage in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Allocating portfolios or lists of amounts involving hundreds or thousands of rows between two parties by distributing the values to meet a precise target percentage share.
Observed behavior: The amounts must be assigned individually while keeping the running total as close to the designated target percentage as possible, rather than using a fixed 50/50 split.

Before you start

Before applying any formulas, ensure that your list of amounts is sorted in descending order (largest to smallest) to help the allocation logic distribute the values more accurately.

Solution 1Recommended

Use a Running Total IF Formula to Allocate Amounts

Utilize a conditional IF formula that compares the running total of allocated values against your target percentage to automatically distribute the amounts.

This method creates a dynamic, reusable template. By referencing a single control cell for your percentage, you can quickly change the split ratio (e.g., from 72% to 51%) without rewriting the logic.

Sort your data

Select the column containing your dollar amounts and sort them from largest to smallest. This ensures large values are allocated first, leaving smaller amounts to fine-tune the final percentage.

Set up a control cell

Designate a cell, for example, B1, to hold your target percentage (e.g., 22.94%). This allows you to easily update the split later.

Enter the allocation formula

In an adjacent column (e.g., Column B, starting at row 4), enter a formula adapted to your layout, such as =IF(SUM($B$2:B3)/SUM($B$2:C3)<$B$1,A4,0). This checks if the running total share is below the target percentage and assigns the value from column A accordingly.

Apply the formula to all rows

Drag the fill handle down to apply the formula across your entire list of amounts, which can efficiently handle up to 3,000 rows.

Use a Running Total IF Formula to Allocate Amounts

Test with a small sample: Always test your template and formula references with a small dataset first to verify that the math aligns correctly with your expected percentage split before processing large files.

Solution 2

Use a VBA Macro for Complex Splitting

For scenarios requiring the data to be physically split into two separate workbook files based on the percentage, a VBA macro can automate the extraction.

Data Management Solution

Easily Split Data by Percentage with WPS Spreadsheet

WPS Spreadsheet offers powerful formula calculation, massive data processing capabilities, and complete compatibility with standard spreadsheet logic, making it easy to allocate portfolios by percentage.

1. Sort the data: Open your file in WPS Spreadsheet, select your amounts, and sort them in descending order.
2. Set target percentage: Type your desired percentage split (e.g., 77.06%) into a designated control cell like B1.
3. Apply formula: Enter the running total IF formula in the allocation column to automatically divide the list.
4. Fill down: Double-click the fill handle to apply the logic to all remaining rows instantly.

Fully compatible with Microsoft Excel formulas like IF and SUMEasily handle large datasets with up to thousands of rows without lagFree, lightweight, and features an intuitive tabbed interfaceSupports complex VBA macros for advanced data splitting

microsoft office alternative - wps office

Frequently Asked Questions

Why is it important to sort the amounts from largest to smallest?

Sorting in descending order forces the formula to allocate the largest amounts first. The smaller amounts at the bottom of the list are then used to fine-tune the running total, resulting in an end distribution that is mathematically much closer to your exact target percentage.

Can I change the percentage split without rewriting the formula?

Yes. By referencing a single control cell (like $B$1) in your formula instead of typing a hardcoded number, you can change the percentage simply by typing a new value into that cell. The entire template will recalculate automatically.

Will this formula work for large datasets with up to 3,000 rows?

Absolutely. Standard conditional formulas based on running sums are highly efficient and can easily process thousands of rows. Just ensure your absolute and relative cell references are set up correctly before dragging the formula down the column.

Excel & Data Problems