• Excel Book Excerpt

Excel Horizontal Subtotals

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Horizontal Subtotals

Problem: Why doesn’t Excel offer horizontal subtotals?


Figure 682 Add a subtotal in E for Q1.

Strategy: This is a great question. In my podcast episode 1001, I had several people write in to say that they regularly used this 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.


Figure 683 Horizontal subtotals.

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.


Figure 684 After manually adding groups.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: