Grouping Text in Pivot

Learn Excel from MrExcel Podcast, Episode 2080: Group Text Items in a Pivot Table

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Today's questions sent in 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 comment said, stainless steel pipe and stainless steel flanges together to make a second group.

Alright, so here's what we have.  We have four products going down 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 area.  And then to create these brand new groups, therefore what we're going to do, we’re going to choose all of the carbon steel items.  In this case, I just have two and right here on the Analyze tab in Excel 2010, earlier that was called the Options tab, and you're going to Group Selection, alright.  And it calls it Group1.  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 select them all but it was just two, so that's not hard.  Group Selection and now we have things called Group1 and Group2, which are not great names.  I realized that, so we'll call this the Carbon Steel, just type a new name, press Enter.  See that it all changes.  And then over here, Stainless Steel and press Enter and that all changes.  And now, the optional part is you can actually take the original field, the Product field out and just have a report of Carbon Steel and Stainless Steel. 

Now, this version or 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 OK.  So we have product and then we'll create Category, like this, and we'll call this Carbon, call this Stainless, alright.  And the extra thing is a little bit of a hassle.  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 ‘cause it's a small table so Ctrl+T.  See they didn't check it by default, you have to check it. 

Alright now, here's the thing that is annoying.  This is called Table1, this is called Table2.  I always change these, so this will 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?  No, there's a heading, it's just not showing up because it's light blue.  That's weird, okay.  So, go back to white. 

Sorry, so now we have our two tables, alright?  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 those two tables together.  I'll put it here on Existing Worksheet off to the right of all of our data, like that.  And 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.  We can put Category down the left-hand side like that.  And if you're in Excel 2016 or if you have Power Pivot in Excel 2010, you just click Auto-Detect it works.  But assuming you're in 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 to join in the Data Model and then creating a pivot table like that.

Alright now, all of these tricks are in my new book, Power Excel 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.

Okay, recap.  We're trying to group text in a pivot table.  The first method: Create a pivot table, the Product in the rows 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 Group1 and Group2 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 the lookup to be Ctrl+T tables, paying attention to checking the box for Use Headers or My data has headers, build a pivot table from both tables using the check box 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 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 Lourdes for sending that question and I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.