Here is a flim-flam 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 social club to accomplish this, we'll use various commands in Excel including Subtotals, Paste Special and Transpose.

You can download the file hither and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a elementary information set showing the sales and committee for three salespeople for iv years:

31116-1

I desire to be able to add together a subtotal for the sales and commission after each year plus a thou total at the end. If the data ran vertically, we could just employ the Subtotal command to accomplish this. All the same, nosotros are going to have to manipulate the information a bit to attain our goal.

The first matter we will practise is re-create the data and then paste information technology a few cells beneath using the Transpose control from the Paste Special dialog box. I'll highlight the data, use Ctrl + C to copy it, then utilize the keyboard shortcut Ctrl + Alt + 5 to bring up the Paste Special dialog box, and Alt + East to select Transpose, and striking Enter:

31116-2

At present that the information is in a vertical format, nosotros can add Subtotals usually. I'll select the Subtotal command from the Data tab and choose to Sum the Sales and Committee categories at each change in twelvemonth:

31116-3

When I click OK, Excel has added Subtotals to my vertically formatted information:

31116-4

At present, while the data is highlighted, I'll re-create it, go up to cell A1 and paste the data in that surface area using the Transpose command again:

31116-5

I now take my data in a horizontal format with the Subtotals I need. With just a little formatting and column width aligning, my data is but the way I want information technology!

31116-6

At present 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:

31116-7

And here'due south the final outcome:

31116-8

What can yous do next?
Share this post with others that can do good from it!
Get out a comment or reply below – let me know what you think!
Subscribe to this blog for more than bully tips in the time to come!
Bank check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!