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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2168 shift subtotals right one column
  • hey welcome back to MrExcel in that
  • cast I'm Bill Jelen I was just in
  • Springfield Missouri doing a seminar
  • there and Rebecca came up and said that
  • she wanted it 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 subtitles but I 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 to subtly and we're
  • saying each change an account we're
  • gonna use the sum function and add the
  • subtotal to amount and click OK and bam
  • like that we get totals at each change
  • and account number right it's beautiful
  • it's fast all right but to do this to
  • write what we're gonna do is we're going
  • to collapse down to the number to 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 gonna choose one
  • column to the left of our subtotal film
  • so I'm gonna start here and Ian I'll
  • hold down the shift key while I page
  • down and then arrow down to select all
  • of these cells now I'm gonna use a
  • shortcut key here of alt semicolon Oh
  • semicolon selects the visible cells it's
  • a shortcut for home flying and select go
  • to special visible cells only okay all
  • right so just all it's a mic alone
  • selects those cells and then insert
  • insert cells shift cells right click
  • okay and it moves them over like that
  • and we go back to the number 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 subtil tips and
  • all kinds of tips 617 excelled mystery
  • solved in this book click that I in the
  • top right hand corner for more
  • information about the book are I wrap up
  • Springfield Missouri Rebecca how to
  • shift the columns over one column
  • alright so first we had the subtotal
  • sorted
  • by account at each change in accounts on
  • the amount used the number to group an
  • outline button show only the totals
  • select all the blanks in one column to
  • the left of the subtitles and then alt
  • semicolon which selects the visible
  • cells only and then just home insert
  • cells shift cells to right go back to
  • the number three group add outline
  • button and you're done
  • I want to thank Rebecca for showing it
  • my seminar in Springfield I want to
  • thank you for stopping by we'll see you
  • next time for another net cast 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.