Diane already knew how to use the Subtotals command, as Subtotals were applied to the data set. If you have never used Subtotals before, here’s how to show a total amount for each Control Value:
- Select cell L1 and press the AZ button in the Data tab of the ribbon to make sure that the data is sorted by Control Value.
- With any single cell in your data selected, go to the Data tab and select Subtotal.
- By default, the Subtotal dialog assumes that you’re adding subtotals whenever the value in column A changes. To correct that, change the At Each Change In dropdown from the value in column A to Control Value.
- In the Use Function dropdown of the Subtotal dialog, choose Sum.
- By default, the Subtotal dialog automatically checkmarks the last column in your data for the Add Subtotal To list. Uncheck that field and add a checkmark next to Amount. The dialog box will appear as shown in Figure2.
- Click OK.
When you click OK, the status bar flashes as individual totals are added. Don’t panic. Excel isn’t crashing. Wait until you see new rows appear with the Subtotals for each group. In my testing, the thousands of rows contained 80 unique Control Values. The Subtotals command took about 15 seconds to finish.
At this point, it’s easy to see which groups of journal entries total to zero. But even with that information, it will be tedious to manually select a group of records and delete them, particularly if you’re repeating this process several times during each month. I can understand why Diane was hoping for a macro to automate the process.
But instead, I have a cool trick brought to you by Olen Greer and Derek Fraley of IMA’s Greater Ozarks Chapter. I’ve done hundreds of Excel seminars for various IMA chapters, but I will never forget when Derek raised his hand and showed me their amazing trick.
After you use the Subtotals command, a new set of icons appears to the left of column A. There is a minus sign icon next to each Subtotal. At the very top is a set of three group and outline icons labeled 1, 2, 3.
Click the #2 icon and you will only see the Subtotal rows. You can look at row 68 and infer that Rows 33 to 67 all contain journal entries for a Control Value of A17800-100. You can only see the subtotal in row 68, but we know the hidden rows contain the details behind that Control Value.
Here’s where the trick comes in: When the data is shown in this collapsed view, select one cell in the Amount column and sort either ascending or descending. Sorting while in the #2 view will cause all of the data in rows 33 to 67 to follow the $43,333.56 total to its new, sorted location.
But in this case, we don’t necessarily care about the $43,333.56 group. Our concern is with all of the groups of data that subtotal to $0. By sorting in the #2 view, all of these journal entries have moved into an adjacent set of rows.
To finish the task, follow these steps:
- Take note of all the rows that total to $0. In our sample, row 3 contains a Control Value that’s negative. Rows 31-1,069 contain Control Values that total to $0. Rows 1,089-1,455 contain positive amounts. That means rows 4-1,069 contain the entries we want to delete.
- Click the #3 Group and Outline button to the left of column A. You will now see all of the detail rows again.
- Delete Row 4 to Row 1,069. You are now left with only the journal entries that are not yet in balance.
- Select cell A1.
- From the Data tab, choose Subtotals to open the Subtotals dialog box again. Click the Remove All button in the lower-left corner. Excel will remove the subtotal rows.
Note that Excel won’t perform a sort within the detailed records for a group. When rows 33-68 are moved as a group of records, the data in rows 33-67 stays in the original sequence relative to other members of the group.
I’ve been using this Sorting by Subtotal trick to get the largest customers to the top of a report. As this month’s problem illustrates, it also does a great job of getting all the “in-balance” records to one contiguous range so they can be deleted.
Sorting by subtotal is one of those powerful Excel features that isn't well know.