Shift Subtotals Right 1 Column

Learn Excel from MrExcel Podcast, Episode 2168: Shift Subtotals Right One Column.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was just in Springfield, Missouri, doing a seminar there, and Rebecca came up and said that she wanted to add Subtotals to a data set-- which is easy enough-- but doesn't let the Subtotals here in Column F. Wants to shift those Subtotals right, one column, so they're over in Column G.

Okay, so to add Subtotals by Account, easy enough. Choose one cell in the Account column, go to the Data tab, and A to Z, like that. And then, also on the Data tab, out here under the Outline group, choose Subtotal, and we say At each change in Account, we're going to use the Sum function, and add the Subtotal to Amount, and click OK, and BAM! Like that, we get Totals at each change in Account number, right? It's beautiful, it's fast. Alright.

But to do this trick, what we're going to do is, we're going to collapse down to the #2 view-- these are called Group and Outline buttons, they were added by the Subtotal command and it gives us just those Subtotal lines-- and then what we have to do is, we have to select just the things we can see. And I'm going to choose one column to the left of our Subtotal column. So I'm going to start here at E and I'll hold down the Shift key while I page down, and then arrow down to select all of these cells. Now I'm going to use a shortcut key here of Alt+; --Alt+; selects the visible cells, it's a shortcut for Home-- Find & Select, Go to Special, Visible Cells Only, OK. Alright, so just Alt+; selects those cells, and then Insert, Insert Cells, Shift cells right, click OK, and it moves them over like that. And we go back to the #3 view, you can see the Totals sitting out there in Column G, all the way down like that. Alright. There you go.

That tip and a lot more Subtotal tips and all kinds of tips, 617 Excel mysteries solved in this book. Click that "I" in the top right-hand corner for more information about the book.

Alright, wrap-up: Springfield, Missouri, Rebecca-- how to shift the columns over one column. Alright? So, first we add the Subtotal; Sort the data by account; At each change in Accounts; On the Amount; use the #2 Group and Outline button; show only the Totals; select all the blanks in one column to the left of the subtotals; and then Alt+semicolon, which selects the visible cells only; and then just Home; Insert Cells; Shift cells to right; go back to the #3 Group and Outline button and you're done.

I want to thank Rebecca for showing up in my seminar in Springfield, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.