MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Varied Bucket Sizes

June 19, 2017 - by Bill Jelen

Varied Bucket Sizes

Pivot tables are great for creating frequency distributions. But the built-in functionality assumes that each bin or bucket is an identical size. What if you have uneven bins?

Watch Video

  • Creating Stratifications with a Pivot Table
  • Yesterday, in episode 2101, used automatic grouping
  • That forces all buckets to be the same size - $10K in yesterday's example
  • To create buckets of varying sizes, there are two options:
  • Select a manual group and choose Group
  • Use a Lookup table to apply a category and add that to the pivot table

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2102 varied bucket sizes hey welcome
  • back to MrExcel net cast I'm Bill
  • gelling yesterday in episode 20 101 I
  • showed how you could do group to take
  • these two great stratifications and
  • Excel dot I Adam pointed out that you
  • could have different bucket sizes i
  • limited yesterday that all these bucket
  • sizes are ten thousand that's how I set
  • it up so let's take a look at how to do
  • that to set it up manually choose one
  • cell here we'll go to analyze and
  • ungroup and then out what Adam is
  • suggesting is to take all the items that
  • are should be in the first bucket so I'm
  • going to go up to ten thousand
  • everything up to ten thousand like this
  • and then instead of group field I use
  • group selection and it makes all of
  • those be group one and then we'll come
  • out over here find the next set so from
  • ten thousand up to twenty thousand like
  • that and then group selection okay and
  • then we'll go from twenty thousand up to
  • thirty thousand and group that selection
  • and then finally we'll take everything
  • all the million-dollar orders just group
  • them into a single selection and at this
  • point we can get rid of the revenue
  • column just leaving us these four and
  • you have to remember let's see under 10k
  • 10k 20k 20k to 30k and then over 1mm I'm
  • using mm for a million there right so
  • yeah that absolutely works although it's
  • a bit of a hassle to go through it as
  • Adam said so what I might do instead is
  • come back to the original data set and
  • build a little lookup table over here
  • and we'll just create a simple little
  • formula equals vlookup that revenue
  • amount into this table the table has to
  • be sorted because it's going to be one
  • of those weird lookups where I'm going
  • to do comma true at the end for exact
  • match I usually recommend against that
  • but at this particular case is one of
  • those rare times
  • or work all right so we will insert
  • pivot table make sure that we're going
  • out to the new column J the size click
  • OK take sighs put size down on the left
  • hand side and then to see the number of
  • orders go with customer and then we can
  • look at profit or whatever just as we
  • did yesterday so profit and this one
  • could be a percentage running total I
  • like that all right so a couple of
  • different ways to create stratifications
  • between yesterday's episode 20 101 and
  • today's episode twenty one or two okay
  • now all of those topics have the manual
  • grouping and the lookup table are all
  • covered in this book power excel at mr.
  • excel click that I on the top right hand
  • corner okay episode recap we're creating
  • stratifications with a pivot table again
  • yesterday in episode 20 101 we used
  • automatic grouping but that forces all
  • buckets to be the same size yesterday
  • ten thousand for each bucket three
  • buckets of varied sizes there are two
  • options select a manual group and choose
  • group or use a lookup table to apply a
  • category and add that to the pivot table
  • okay I want to thank Adam for that I
  • don't want thank you for stopping by
  • we'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2102.xlsm

Title Photo: Photoshot / Pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

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