MrExcel.com - Your One Stop for Excel Tips & Solutions

Horizontal Subtotals

October 07, 2022 - by

Horizontal Subtotals

Problem: Why doesn’t Excel offer horizontal subtotals?

Subtotals can not work horizontally. Reading across Row 1, you have a heading for Quarter and then Q1 Q1 Q1 then Q2 Q2 Q2 then Q3 and so on.
Figure 741. 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.

Copy the horizontal range and Paste Transpose below the original range. Add Subtotals here. Copy the data with subtotals and paste Transpose over the original data.
Figure 742. 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.

You still have to manually add the Group and Outline buttons.
Figure 743. After manually adding groups.

This article is an excerpt from Power Excel With MrExcel

Title photo by Dave Hoefler on Unsplash