MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Shift Subtotals Right 1 Column


November 06, 2017 - by Bill Jelen

Shift Subtotals Right 1 Column

You've added subtotals in Excel. But you want those subtotals to move one column to the right, so they will be easy to see. Here is a method.

Watch Video

  • Rebecca in Springfield Missouri asks: How to shift the subtotals over one column?
  • Sort the data by account
  • Data, Subtotals, At Each Change in Account, Sum the Amount
  • Use the #2 group and outline button to show only the totals
  • Select all of the blanks in one column to the left of the subtotals
  • Use Home, Find & Select, Go To Special, Visible Cells Only or Alt + ;
  • Home, Insert, Cells, Shift Cells Right
  • Click the #3 Group & Outline button

Video Transcript

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.

Download File

Download the sample file here: Podcast2168.xlsm

Title Photo: geralt / pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.