Calculation Issues

Group Excel Rows Sequentially Until a Maximum Total Is Reached

Maira Mehtab Oct 9, 2026 868 views

Question details

The user needs to organize over 5,000 rows of data into sequential groups where the sum of units in each group does not exceed a maximum limit (e.g., 90 units), while strictly preserving the original row order.

How to Group Excel Rows Sequentially Until a Maximum Total Is Reached

Product: Excel
Device & OS: not provided
Scenario: Batching a large dataset into smaller, ordered lists for processing or shipping based on a cumulative sum threshold.
Observed behavior: Previous formula attempts failed, causing some groups to exceed the 90-unit limit or start new groups prematurely at much lower totals.

Before you start

Ensure your dataset is sorted in the exact sequential order required before applying any formulas or macros, as rearranging rows later will break the cumulative grouping logic.

Solution 1

Use Helper Columns and IF Formulas

This method uses basic Excel functions to calculate a running total and assign a group number. It is best for users who prefer avoiding macros.

By setting up two helper columns—one for the running total and one for the group ID—you can logically check if adding the current row's value will exceed your limit.

Assuming your data values are in Column B starting from row 2, you will dedicate Column C to the Group ID and Column D to the Running Total.

Initialize the first row

In cell C2 (Group ID), manually type '1'. In cell D2 (Running Total), enter '=B2'.

Create the Group ID formula

In cell C3, enter the formula: '=IF(D2+B3<=90, C2, C2+1)'. This checks if the previous running total plus the current row exceeds 90. If it doesn't, it keeps the same group number; if it does, it starts a new group.

Create the Running Total formula

In cell D3, enter the formula: '=IF(C3=C2, D2+B3, B3)'. This ensures the running total resets to the current row's value whenever a new group begins.

Apply to the dataset

Select cells C3 and D3, then double-click the fill handle at the bottom right corner of the selection to drag the formulas down across all 5,000+ rows.

Dynamic Limits: You can replace '90' in the formula with an absolute cell reference (e.g., $F$1) to easily change the maximum total limit without rewriting the formulas.

Solution 2Recommended

Use a VBA Macro for Automated Grouping

A VBA macro automates the sequential grouping process and is highly recommended for large datasets (5,000+ rows) to prevent recalculation lag.

Group Large Datasets Easily with WPS Spreadsheet

WPS Spreadsheet offers robust formula calculation engines and full VBA/Macro support, allowing you to process large-scale datasets with custom grouping limits efficiently.

1. Open your data: Launch WPS Spreadsheet and open your dataset, ensuring the rows are sequentially ordered.
2. Apply calculation logic: Use the IF and Running Total formula method in the adjacent columns to categorize your rows.
3. Use advanced developer tools: For massive datasets, navigate to the Developer tab to run VBA macros that group your rows instantly.

Handles 5,000+ row datasets without lagging or crashingFully compatible with Microsoft Excel formulas and VBA scriptsFree, lightweight, and features an intuitive tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my running total formula skipping numbers or stopping too early?

This usually happens due to incorrect cell references. Ensure you are using relative references for the rows directly above (e.g., D2) and not accidentally locking them with absolute reference symbols ($) when dragging the formula down.

Can I group rows sequentially using Power Query instead of formulas?

Yes, but it requires advanced M code. You can use 'List.Generate' or 'List.Accumulate' in Power Query to iterate through rows, maintain a running total, and assign a group index. However, for most users, standard spreadsheet formulas or VBA are faster to implement.

What happens if a single row has a value greater than the maximum limit?

If a single row's value (e.g., 100) exceeds the limit (e.g., 90), the standard grouping logic will place it in its own group, but that group will technically violate the maximum limit. You should add data validation to prevent single entries from exceeding your predefined limit.

Excel & Data Problems