Excel Pivot Pro: Grouping Text in Pivot - Episode 2080

Microsoft Excel Tutorial: Group text in a pivot table

Welcome to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing how to group text items in a pivot table. This question was sent in by Lourdes, who wanted to know how to group different rows together based on cell data. For example, grouping carbon steel pipe and carbon steel flanges together, and stainless steel pipe and stainless steel flanges together.

To start, we have four products listed in the left-hand side of our data. We will insert a pivot table and place it on the existing worksheet. Then, we will select the items we want to group together, in this case, the carbon steel items. On the Analyze tab in Excel 2010 (previously called the Options tab), we will click on Group Selection. This will create a new group called Group1. We will repeat this process for the stainless steel items, creating a second group called Group2. However, these group names are not very useful, so we can rename them to Carbon Steel and Stainless Steel for better organization.

If you have Excel 2013 or newer, there is another method to group text items in a pivot table. We will create a table on the right side of our data, mapping all the products to a category. Then, we will make both tables into a table using Ctrl+T or the Format as Table option. We will also rename the tables to Data and Lookup for easier identification. When inserting a pivot table, we will check the box that says "Add this data to the Data Model". This will allow us to join the two tables together. We will then choose Revenue from the original table and Category from the lookup table. In Excel 2016 or with Power Pivot in Excel 2010, we can simply click on Auto-Detect to create the relationship. For Excel 2013, we will need to click on CREATE and define the relationship between the two tables.

These are just two different ways to group text items in a pivot table. For more tips and tricks like this, check out my book "Power Excel with MrExcel, the 2017 Edition" where I solve 617 Excel mysteries. You can find the link to purchase the book in the top-right corner of this video. Thank you for watching and don't forget to subscribe to our channel for more helpful Excel tutorials. See you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

#excel
#microsoft
#microsoftexcel
#exceltutorial
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
#powerpivot

This video answers these common search terms:
how to group a pivot table in excel
how to group and collapse data in excel pivot table
how to group data in a pivot table in excel
how to group in excel pivot table
how to group in pivot table excel
how do you group fields in an excel pivot table
where is the grouping in excel pivot
how to create groups in pivot table in excel
how to group text in excel pivot table
how to group products in excel pivot table

Table of Contents:
(00:00) Grouping pivot table rows based on cell data in Excel
(00:10) Example of grouping carbon steel and stainless steel items
(00:20) Inserting a Pivot Table
(00:30) Creating new groups using Group Selection
(00:40) Renaming groups
(01:22) Optional step: removing original field from pivot table
(01:32) Alternative method for Excel 2013 or newer
(01:42) Creating a table to map products to categories
(02:59) Using the Data Model to join tables
(03:40) Recap of methods
(04:00) Buy the book
(04:13) Clicking Like really helps the algorithm

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.

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...