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 of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.