Here is a trick I learned from a book by Mr. Excel, Bill Jelen on how to incorporate subtotals into your data when it runs horizontally rather than vertically. In order to accomplish this, we’ll use various commands in Excel including Subtotals, Paste Special and Transpose.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a simple data set showing the sales and commission for three salespeople for four years:
I want to be able to add a subtotal for the sales and commission after each year plus a grand total at the end. If the data ran vertically, we could just use the Subtotal command to accomplish this. However, we are going to have to manipulate the data a bit to achieve our goal.
The first thing we will do is copy the data and then paste it a few cells below using the Transpose command from the Paste Special dialog box. I’ll highlight the data, use Ctrl + C to copy it, then use the keyboard shortcut Ctrl + Alt + V to bring up the Paste Special dialog box, and Alt + E to select Transpose, and hit Enter:
Now that the data is in a vertical format, we can add Subtotals normally. I’ll select the Subtotal command from the Data tab and choose to Sum the Sales and Commission categories at each change in year:
When I click OK, Excel has added Subtotals to my vertically formatted data:
Now, while the data is highlighted, I’ll copy it, go up to cell A1 and paste the data in that area using the Transpose command again:
I now have my data in a horizontal format with the Subtotals I need. With just a little formatting and column width adjustment, my data is just the way I want it!
Now I can delete the data below, and also remove the outlines by going to the Ungroup command on the Data tab and choosing the Clear Outline option:
And here’s the final result:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!