MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Stratifications


June 18, 2017 - by Bill Jelen

Stratifications

How many small orders do you process each month? How much revenue is associated with those small orders. Pivot tables make this easy.

Watch Video

  • Creating Stratifications with a Pivot Table
  • Build a pivot table with Revenue in the Rows and Customer in the Values (Backwards!)
  • Select the first revenue cell in the pivot table
  • Use the Group command
  • Choose a bucket size and a starting point
  • Add Additional Fields, such as Profit (normal), Profit (Percent of Column), Profit (% Running Total In)
  • When you re-use Revenue, you will have to change from Count to Sum

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2101 stratifications with a
  • PivotTable
  • hey welcome back to MrExcel net-caster
  • I'm Bill Jelen this is one the question
  • one of the things I talk about all the
  • time in my live power Excel seminars on
  • a surprise I don't have a video out on
  • YouTube once and this goes back to when
  • I was working in accounting 20 25 years
  • ago and we had this great VP of Sales
  • who moved from California to our home
  • office and John they had to find a
  • corner office for him so he ended up on
  • my floor and he was surrounded by cubes
  • and cubes and cubes of people doing
  • order entry and so every day John will
  • walk past all these order entry people
  • and he would look at all the payroll
  • that was involved in order entry into
  • John our business was simply every month
  • he would have a million dollar sale
  • right and those were the things that
  • made him all the money the reason we had
  • eighteen or twenty order entry people
  • was because we were then selling spare
  • parts batteries and you know things that
  • broke him and he he was just he he was
  • looking at all of these small orders and
  • how many people we had to employ for the
  • small dollar he's like I want to give
  • all those small small orders we just
  • want a value-added reseller let them
  • deal with the small things I just want
  • to deal with a million dollar orders you
  • could get rid of all these order entry
  • people just have one person to enter my
  • bigot quarter each month and so I wanted
  • to demonstrate John and those small
  • orders those smaller's were higher
  • profit all right and I did that using a
  • pivot table so choose one sell this is
  • our invoice register two years worth of
  • data let go to a new worksheet click OK
  • and now this pivot tables going to be
  • unlike any other pivot table you've ever
  • built because we're going to take the
  • revenue field which normally goes to the
  • values area we're going to move it to
  • rows like that alright and these are
  • dollar Maps all right so here's an order
  • for seventeen hundred thirty-five
  • dollars here's in order for seventeen
  • hundred forty-one dollars and all the
  • way down at the bottom those are the big
  • orders right the million dollar orders
  • all right so first thing very unusual
  • taking a numeric field are moving in two
  • rows second thing that's very unusual is
  • we're going to take a text field such as
  • customer and move it to values all right
  • that just is this is completely
  • backwards usually customers will be here
  • some of revenue there and this has count
  • of customer but this is really number of
  • orders number of orders all right so
  • we're seeing
  • there was one order for $1,699 one order
  • for 1736 and again not interesting in
  • this current format but this is where
  • pivot tables are really good they can
  • help us create stratifications take this
  • data and show it in layers or in buckets
  • so we choose our first revenue field and
  • that's a group field and they choose
  • some crazy things here and I always
  • override this I'm going to go from zero
  • to one point six million dollars in
  • let's just say ten thousand dollar
  • buckets like that and you might have to
  • experiment with this you can always come
  • back to this grouping field and change
  • it but I want to really kind of focus on
  • those small lowers and click OK all
  • right and now here we are we can see for
  • each of these items that were sure
  • enough 458 small orders under ten
  • thousand dollars five hundred thirty
  • orders under twenty thousand between ten
  • and twenty thousand and then it kind of
  • falls off not too many orders out there
  • on the 20,000 to 29,000 now what would
  • be really nice here is if in the
  • grouping we could have different sized
  • buckets all right that would be good
  • because these items up here I really
  • want that just to be one big bucket I
  • can't do that all right but now that I
  • have this simple I mean it's a one-page
  • report it'll get my point across I'm
  • going to take profit and move that to
  • the values area or as a profit and move
  • it to the values area again I'm gonna
  • take profit a third time and move it to
  • the values area and Free Cities we're
  • going to we're going to change the
  • calculation all right so the first one I
  • just will see how many dollars are in
  • each bucket but then the second one I'm
  • going to double click I'm going to say
  • show the values as a percentage of the
  • columns so percentage of profit and then
  • here I'm going to say percentage a
  • running percentage so show values as and
  • this is brand new in Excel 2010 so if
  • you're for some reason back in Excel
  • 2007 you're not going to have this one
  • the percentage running total in you'd
  • have to build the calculation outside
  • the pivot table so accumulated
  • percentage of profit click OK alright
  • and so I can say hey look John you know
  • if you're going to get rid of all these
  • small orders yeah it's a lot of orders
  • but it's over a third of your profit
  • alright and if you're going to be losing
  • 20 points all these you know that is a
  • huge chunk of money that you're going to
  • give away hey one thing that
  • watch out for here with the
  • stratifications is that field revenue
  • that I put in the rose area that feels
  • not going to work in this pivot table by
  • default if I take the revenue field and
  • drag it down here to the bottom it will
  • always default to count I don't quite
  • understand why that happens I know what
  • happens I'm used to it
  • double-click and first change it to some
  • and then maybe change it to whatever
  • calculation want to do a percentage
  • running total in and we'll call this
  • accumulated percentage of revenue like
  • that click OK
  • and sure enough you'll be able to see
  • that you know yeah it's 22% of the total
  • revenue but higher profit margin on
  • those small items almost 30 percent of
  • your profit and you can at least try and
  • make the argument that there's value to
  • all of those folks over an order entry
  • because they're entering high profit
  • orders alright so creating
  • stratifications
  • in a pivot tool now all kinds of pivot
  • table topics are in this book power
  • excel with MrExcel 617 excel mystery
  • solved click that I on the top right
  • hand corner go and take a look on how to
  • buy the book today's episode creating
  • stratifications with the pivot table
  • first we build a pivot table it's kind
  • of backwards revenue in the rows area
  • and customer in the values area select
  • that first revenue selling the pivot
  • table uses a group command choose a
  • bucket size and the starting point would
  • be nice the buckets could be variable
  • but they aren't add additional fields
  • such as profit percentage of the column
  • and running percentage total in when you
  • reuse revenue though be careful make
  • sure to change from count to sum or your
  • numbers will appear all right there you
  • go why I want to thank you for stopping
  • by we'll see you next time for another
  • net cast from MrExcel

Download File

Download the sample file here: Podcast2101.xlsm

Title Photo: 2554813 / Pixabay