MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Subtotals Pre-Sort


April 12, 2017 - by Bill Jelen

Subtotals Pre-Sort

How to sort data within each subtotal group in Excel.

Watch Video

  • Most people don't realize that you can sort subtotals after collapsing
  • This sorts an entire group of records into a new position.
  • But it does not sort within the group.
  • Jeff from Columbus Indiana offers a simple solution: Sort by Revenue descending before adding subtotals.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2074 subtotal pre sort I have another
  • tip from neuro this one from Columbus
  • Indiana seminar did there in March
  • thirties a lot of great seminars coming
  • up Bloomington Indianapolis Sarasota
  • this tip is from Jeff now I usually talk
  • about doing subtotals right so if we
  • have data that's sorted by date like
  • this and you want to subtotal by
  • customer first thing you do is select
  • one cell in the customer column sort a
  • to z and then out here on the data tab
  • use subtotal at each change in customer
  • wearing use the sum function and choose
  • all the numeric fields that make sense
  • like that click OK and what we get is
  • every time the customer changes they
  • insert a new row with those subtotals
  • right really cool now this next trick
  • I've talked about in the podcast before
  • this is from Derek and Springfield
  • Missouri once you collapse down to the
  • number to view if you want the largest
  • customer at the top just choose one cell
  • in the revenue column and click Z to a
  • and the largest customer will come to
  • the top cool right but then when we go
  • back and look to see what happened if we
  • go back to the number three view you see
  • that the data within the customer is not
  • sorted it comes in exactly the same
  • situation sorted by date it's in days
  • sequence and a lot of times that's what
  • we're one but you might have a manager
  • says well yeah I want to see the largest
  • customer at the top but within the
  • largest customer I want to see the
  • largest revenue at the top all right so
  • Jeff pointed out that he has to do this
  • is manager asked for this and he says
  • simple before you add the subtitles just
  • sort by revenue like a Z to a that way
  • the largest customers at the time then
  • come back sort by customer subtotal at
  • each change in customer leaves of some
  • function choose those numeric fields
  • click OK collapse it down on the number
  • to view still use one cell in revenue
  • column Zita a and now wag more dog store
  • in San Antonio comes to the top as the
  • largest customer but we go look within
  • that it will be sorted correctly a
  • couple of cool tricks there I love Derek
  • of sorting subtotals when they're
  • collapsed and then now Jess trick to get
  • the data with the end sorted as well all
  • these tricks are in this book powers old
  • MrExcel the 27
  • in addition click the I up there in the
  • top right-hand corner to get to that rap
  • of this episode most people don't
  • realize you can sort the subtotals after
  • collapsing it sorts an entire group of
  • Records into a new position but does not
  • sort within the group Jeff from columbus
  • indiana offers a simple solution sort by
  • revenue descending before adding the sub
  • tails I think Jeff for showing up in my
  • seminar and offer me that trick I want
  • thank you for stopping by we'll see you
  • next time for another netcast from mr.
  • excel

Download File

Download the sample file here: Podcast2074.xlsm

Title Photo: sign/post/8d36c011-679f-4405-9c9f / 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.