Using Subtotal to create paging for grouped data

Using Subtotal to create paging for grouped data

header subtotal

A feature of the Subtotal TOOL is the ability to also group your data into a form ready for printing. For example, let’s say you have a lot of records that you want to separate by one of the fields in the records. You could simply sort the records but this would still print out on the same sheet/s. Using this tool you can choose a field to count or total, divide your data into groups and add only these groups to each printed page. That way when you print the worksheet each of the groups prints on a separate page as shown below.

overlap

How to do this:

  1. In the worksheet to be changed sort the field that you will subtotal. In this example (above) I sorted by Grade.
  2. To fix the printable rows at top of each printed page go to the Page Layout tab and click Print Titles. On the Sheet tab > Rows to repeat at top click into the input box then highlight the row header number for rows to retain. In this example I chose the first 4 rows to include the column headers. This will add $1:$4 to the input box – this is the numbers of the first 4 rows. Click OK.
  3. Select the data from entire range of cells to include in the subtotal.
  4. Go to the Data tab. In the right hand Outline group click Subtotal.
  5. Change the setting as pictured (below). Make sure you select Page break between groups at the bottom. Click OK.OnePixeldialog 1
  6. Go to File > Print. The Print Preview appears on the right side. Click the scroll bar to see the paged results of the Subtotal.
  7. To remove the subtotal paging highlight the same range of cells > Data > Subtotal > Remove All.

If your page breaks also include column breaks at places you don’t want go to View > Page Break Preview and drag any dotted breaks to right. Then in the same group click Normal to return to normal view. Preview the result.


Leave a comment

Your email address will not be published.

Required fields are marked with required


required

required

required