GTD Wannabe

Excel Tip: Copy Subtotal (Aggregate) Data Only

Have you ever used Excel’s subtotal functionality? It’s great for counting things. For instance, I’m running some tests right now, where the output is a sequence. If I run the test 10,000 times, then I have 10,000 sequences. I want to know how many times each sequence happens.

I copy the output into Excel, then sort the column containing the sequence into alphabetic order, thereby sticking like sequences with like. Then, I use the subtotal feature to count how many times each sequence occurs.

It’s great – it does exactly what I want. See the snapshot below.

The only problem I have is that sometimes I want to manipulate the aggregate information, say, calculate percentages, or compare it to some hand-created date from somewhere else. So, what I really want to work on is the aggregate data, not the underlying information.

As you can see in the diagram, there are actually over 10,000 rows of information in the sheet, even though I can hide all but 20-some. But, when I want to do a calculation on the data, I end up also doing the calculation on the *underlying* data as well. I can’t even just copy the subtotal information to somewhere else, because all of the underlying data comes too!

There has got to be a way around it. After some searching, and some bad suggestions (i.e., didn’t work for me), I discovered help in the form of Joseph Rubin’s ExcelTip.com. You can follow his instructions. It’s really easy. Basically, all you do is:

1. In the view I have above, I just click on some cell in the data range, e.g., A260.
2. Press Ctrl+A to select all of the subtotal data (would also include the underlying data).
3. Magic Step: Press Alt+; (This selects only the *visible* cells. Magic!)
4. Copy and paste as desired.

Wow, that little Alt+; step is pure magic. You can also get there by using the F5 key (which brings you up the Go To Dialog), selecting Special… and then selecting Visible cells only. Why you can select visible cells only by going through the “Go To” menu is completely beyond me.

As you can see in the screenshots below, selecting visible cells makes them look slightly different. The shot on the left (first) is the Ctrl+A selection (all data, including underlying, hidden, data). Notice the dark border. The shot on the right (second) is Alt+; selection (visible cells only). notice that the border is gone. A good visual way to make sure you’ve selected exactly what you want.

An interesting sidebar: I’ve just gone through the Help for Excel 2007. I originally went there, but had no joy when looking for information about copying and subtotals. But, if you search for “visible data”, you can get to a set of instructions that will let you copy just the subtotals. Funnily enough, there are no shortcut keys provided, just how to do the task with the ribbon. And, there’s not even a listing discussing what the Alt+; keyboard combination is good for! Shocking. It really is magic :)