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 of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.