MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Grouping Text in Pivot


April 20, 2017 - by Bill Jelen

Grouping Text in Pivot

Excel Pivot Tables will let you group similar text entries. Say you wanted to group Chicago, Cleveland, and Detroit into a territory called Midwest. This article shows you how.

Watch Video

  • Lourdes wants to group text in a pivot table
  • Method 1: Create the pivot table with Product in the Rows area.
  • Select all of the items for the first group. Use Analyze, Group Selection.
  • Select all of the items for the second group. Use Analyze, Group Selection.
  • Type new category names instead of Group1 and Group2.
  • Optionally, remove the original Product from the pivot table
  • Method 2: Build a lookup table
  • Make both tables be Ctrl + T tables (Don't forget to check Use Headers)
  • Build a pivot table from both tables using the All link at the top of the field list
  • Define a relationship.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2080 group text items in a pivot table
  • hey welcome back to the mr excel metcalf
  • i'm Bill Jelen today's questions and
  • then by lourdes how can I group two
  • different rows together depending on
  • cell data for example trying to group
  • items such as carbon steel pipe and
  • carbon steel flanges together to make
  • one group and then the rest of the
  • comments a stainless steel pipe and
  • stainless steel flanges to go to make a
  • second group alright so here's what we
  • have we have 4 products going on the
  • left hand side I'm going to insert a
  • pivot table and we'll just put it right
  • here on the existing worksheet like that
  • click OK items down the left hand side
  • revenue in the values are end and then
  • to create these brand new groups or what
  • we're going to do want to choose all of
  • the carbon steel items in this case I
  • just have to and right here on the
  • analyze tab in excel 2010 an earlier
  • that was called the options tab and
  • you're in a group selection alright and
  • it calls it group 1 and then we're going
  • to choose all the stainless steel and
  • now it's harder now because they're not
  • consecutive you can't just a select
  • small but it was just too so that's not
  • hard group selection and now we have
  • things called group 1 and group 2 which
  • are not great names I realize that so
  • we'll call this the carbon steel just
  • type a new name press ENTER seeing it
  • all changes and then over here stainless
  • steel and press ENTER and that call
  • changes and now the optional part is you
  • can actually take the original field the
  • product filled out and just have a
  • report of carbon steel and stainless
  • steel now this version are this method
  • works all the way you know back to as
  • long as Excel 2003 probably even earlier
  • than that if you have Excel 2013 or
  • newer though there is another way to go
  • what we can do is we're going to create
  • a little table off here to the right
  • mapping all of the products to a
  • category so under data I'm going to
  • remove duplicates in the second table
  • click okay so we have product and then
  • we'll create category like this and
  • we'll call these carbon stainless
  • alright and the extra thing is a little
  • bit of a hassle as we're going to make
  • both of these into a table I always just
  • do this with ctrl T but you're more than
  • welcome to do it with home format as
  • table and choose one of the table Styles
  • my table has headers make sure that gets
  • checked that might be an issue over here
  • because it's a small table so ctrl T my
  • to see they didn't check it by default
  • you have to check it all right now
  • here's the thing that is annoying this
  • is called table 1 this is called Table
  • two I always change these this will be
  • just be called data or fact or whatever
  • geeky thing you want to call it and then
  • here this will be called the lookup it
  • looks like did I forget to put a heading
  • there now there's a heading it's just
  • not showing up because it's light blue
  • that's weird okay this will go back to
  • white sorry so now we have our two
  • tables all right and in Excel 2013 when
  • we insert a pivot table insert pivot
  • table choose this extra box down here
  • add this data to the data model that's
  • going to allow us to join us two tables
  • together put it here an existing
  • worksheet off to the right of all of our
  • data like that you'll notice it takes a
  • little bit longer to create the report
  • and what we're going to do is choose
  • revenue from the original table and then
  • click on all where we get to choose from
  • the lookup table and can put category
  • down the left hand side like that and if
  • you're in Excel 2016 or if you have
  • power pit I pivot in Excel 2010 you just
  • click Auto detect it works but assuming
  • you're an Excel 2013 you have to click
  • create and it's this easy we have a
  • table called data it has a column called
  • product it's related to a lookup table
  • with a column called product click ok
  • and it is giving us the correct answers
  • doing the join in the data model and
  • then creating a pivot table like that
  • all right now all of these tricks are my
  • new book powers that with MrExcel the
  • 2017 edition 617 Excel mystery solved
  • click that I on the top right hand
  • corner for a link to purchase the book
  • ok recap we're trying to group text and
  • table the first method create a pivot
  • table the product in the Rose area
  • select all the items for the first group
  • and then group selection select all the
  • items for the second group group
  • selection and then replace the category
  • names of group 1 and group 2 with
  • something useful and at that point you
  • can actually remove the original product
  • from the pivot table the second method
  • though is where we're using the data
  • model so we built a lookup table using
  • remove duplicates declare both of those
  • tables the original table and they look
  • up to be control tea tables paying
  • attention to checking the box or use
  • headers or my data as headers build a
  • pivot table from both tables using the
  • the checkbox that says add this data to
  • the data model and then in the field
  • list you click on all and you'll be able
  • to choose from either either table and
  • then define a relationship using the
  • auto detect in 16 or create relationship
  • in 2013 so two different ways to solve
  • that problem I want to thank lord is for
  • sending that question I want thank you
  • for stopping by we'll see you next time
  • for another netcast from MrExcel

Download File

Download the sample file here: Podcast2080.xlsm

Title Photo: Silentpilot / Pixabay