June 24, 2011, 8:57 AM PDT
Takeaway: After using Excel’s Subtotal feature, you can quickly collapse the outline to view only the subtotal rows. You might get a surprise if you try to copy them though - here’s how to avoid a Subtotal gotcha!
In the last entry, I showed you how to use Excel’s Subtotal feature to count elements in a group. Today, I want to share a minor glitch when using this feature. I call it a glitch, but Excel’s not doing anything wrong - it just doesn’t do what you expect! Once you add subtotals, you can collapse the outline to view just the subtotal rows. If you try to copy only the subtotal rows, you’ll get a surprise - Excel copies the subtotal and detail rows, not just the subtotal rows. Fortunately, there’s an easy fix. Use Go To Special to select only the visible cells before copying.
Let’s look at a quick example using the sheet show below. Specifically, we’ll use Subtotal to find the average unit price of each category. Then, we’ll copy the subtotal rows and see what happens.
The first thing you have to do when using Subtotal, is to sort the data - you’ll want to do this with any Subtotal task. With the data sorted, you’re ready to subtotal the unit price values as follows:
- Select any cell in the data range.
- Click the Data tab and then click Subtotal in the Outline group. In Excel 2003, Subtotal is on the Data menu.
- In the resulting dialog box, choose Category from the At Each Change In dropdown.
- Select Average from the Use Function dropdown.
- Check the Unit Price field in Add Subtotal To.
- Click OK and Excel will display the average unit price for each category.
To view only the subtotal rows, click 2 in the outline symbols to the left. Now, copy these rows and see what happens. As I warned, Excel copies all the rows, not just the subtotaling rows.
Now, here’s the easy fix:
- Select the range you want to copy. In this case, that’s C4:G66 - that’s 62 rows! It might look like just a few subtotaling rows, but if you check the row headers, you’ll see that Excel’s just hidden the detail rows. (If you don’t select the range first, you’ll end up with a mess.)
- Press [Alt]+;, which is the equivalent of pressing [F5], clicking Special at the bottom of the Go To dialog box, clicking Visible Cells Only to select only the visible cells in the selected range, and then clicking OK. (That shortcut’s worth of an entry of its own!)
- Press [Ctrl]+C to copy the selected visible cells to the Clipboard.
- Select a target cell and paste the range by pressing [Ctrl]+V. Excel copies only the subtotaling rows. You can copy to another area of the same sheet, to a different sheet, or even to a new workbook.
Well, maybe you knew all that already. I see this question a lot, but I think many people who use Excel a lot know how to get around the subtotals copy problem. So, let’s explore a bit further. If this is something you do often, add the Select Visible Cells option to the Quick Access Toolbar (or the menu). If you’re using Excel 2010, do the following to add this option to the QAT:
- From the QAT dropdown, choose More Commands.
- From the Choose Commands From dropdown, choose All Commands.
- Thumb down and select Select Visible Cells.
- Click Add and then click OK to return to the sheet.
In Excel 2003,
- Select Customize from the Tools menu.
- Click the Commands tab.
- Select Edit in the Categories list.
- From the Commands list, select Select Visible Cells and drag it to the menu or toolbar.
- Click Close.
Now you won’t even have to press [Alt]+;–just click the option on the QAT (or the menu).
A word of warning when using Subtotal to average: Excel evaluates the derived values to calculate the grand total - not the actual detail data. Most of the time that won’t matter, but if you’re dealing with serious precision, you’ll want to watch for discrepancies.
