FOLLOW

FOLLOW

SHARE

Horizontal Help

How to create horizontal subtotals in Microsoft Excel.

7Feb

Q. Why doesn’t Excel offer horizontal subtotals? For example, I’d like to add a subtotal in E for Q1 (see Figure 1).

A. This is a great question. Although it takes a lot of steps to create horizontal subtotals, using the shortcut keys speeds up the process. Follow the steps below.

1. Select the original data with Ctrl+*.
2. Go a few rows below the data. Paste with Alt+E+S+E+Enter.
3. Press Alt+D+B to display the Subtotals dialog. Click OK.
4. Press 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).

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 redo 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).

If you would like to submit a question to Bill “MrExcel” Jelen, e-mail him at billjelen@cfo.com.

Comments

comments powered byDisqus
Wrestlingring

Read next:

Becoming a Value Integrator: A CFO Journey

i