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


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.