SOLVED: VIDEO: Separate Excel Rows Into Groups of 100

We had a client that wanted to separate a very large Excel list of about 3000 rows into groups of 100 so that they could print off wrote lists for about thirty volunteers. We didn’t have a good answer to this and then one of our in-house text came up with the idea of adding a helper column that used a very simple formula based on the INT function combined with the SUBTOTAL function in Excel.

It’s a pretty cool solution:

1. Add a Helper Column:
   • Add a new column to your worksheet. Let’s call it “Group”.
   • In the first cell of the “Group” column (assume it’s A2), enter the formula =INT((ROW()-2)/100)+1 This formula groups every 100 rows together by assigning them the same group number.
   • Drag the fill handle (small square at the bottom-right of the cell) down to copy this formula for all rows.
2. Group Excel Row:
   • Select all the cells in your Excel worksheet that you want to group.
   • Go to the Data tab, then in the Outline group, click Subtotal.
   • In the Subtotal dialog box, select “Group” from the At each change in dropdown list.
   • Select a function from the Use function dropdown list. This function will be applied to each group of 100 rows. For example, you can select Count to count the number of rows in each group.
   • In the Add subtotal to box, select the checkboxes for the column(s) you want to apply the function to.
   • Click the Page Break Between Groups check box