User Problem: Why doesn’t Excel offer horizontal subtotals (see Figure1, below)?
Solution: This is a great question. I had several people write in to say that they regularly
used the following method to add horizontal subtotals. Although it is a lot of steps, if you use shortcut keys, it is actually fast.
1). Select the original data with Ctrl+*.
2). Go a few rows below the data. Paste with Alt+E+S+E+Enter.
3). Alt+D+B to display the Subtotals dialog. Click OK.
4). Ctrl+C to copy the vertical data set with the subtotals.
5). Select cell A1.
6). Paste Transpose with Alt+E+S+E+Enter.
7). Fix the column widths with Alt+O+C+A (see Figure 2, left).
8). Delete the temporary table at the bottom.
9). Optionally, select columns B:D and choose Data, Group.
10). Select columns F:H and press F4 to re-do the group command.
11). Repeat step 10 for J:L and N:P.
12). Select B:Q and choose Data, Group. You now have collapsible horizontal subtotals
(see Figure 3).
CFO contributor Bill Jelen. a.k.a. Mr. Excel, is an Excel MVP and the author of 35 books about Microsoft Excel. This article originally appeared in the September 8, 2014, edition of the CFO LearningPro newsletter.