Microsoft Excel: Grouping Text in Pivot Tables

Grouping is very powerful. It allows you to combine multiple related items into fewer (or one) item.
The most primitive form of grouping is with Text.
However, it is extremely useful and beneficial. You NEED it. Find out why…

Estimated reading time 7 min

Sample data

This data contains simple transactional data about Accounts Receivable. Open the file and understand the columns. Each row represents some invoice which you have sent to customers and you have not received the payments. That is money outstanding in the market. Obviously, you want to get your money as fast as you can.

The Need

Often data has mistakes – spelling mistakes, abbreviations, synonyms… all kinds of things. You realize these mistakes AFTER creating the Pivot Table. You need a method to correct the mistakes WITHIN the Pivot table. That is the need. Grouping solves this problem quickly and reliably.

Another situation is that you have multiple items and then you want to put them into fewer categories. You don’t have a column in the raw data which has the categories. Grouping allows you to quickly create categories.

Spelling Mistakes

Use the data and put Customer Type in ROWS and Amount in COLUMNS.
We have two type of customers Preferred and Regular.

Go to Pivot Table Tools – Design Tab – Report Layout
and choose Show in Tabular Form

There is an obvious spelling mistake. How do we correct it?

The first thought which comes to your mind is to filter on each spelling mistake, select all and then manually correct the raw data. DO NOT DO THAT. That is inefficient. What if you have many variations instead of just two?

Why not ask pivot table to combine both items into one?
That is called Grouping.

Select all the three items – the spelling mistakes and the Preferred item and right click.
Choose Group… option

Excel now creates another column. It does not know name to use. So it uses the same name as original column but adds a number to it so that the name is unique.

Excel also does not know what to call the group. So it uses a generic name Group 1.

We know what to call the group – the correct spelling of Preferred.
Just click inside the Group 1 cell and overtype Preferred.

Notice that the field Customer Type 2 is added in the Field List.

Now we don’t want the original Customer Type field. So drag it out.

Now we get correct results instantly.
Remember that the raw data still has wrongly spelt data.
But we corrected it in Pivot Table.

While this is a nice option, if you have too many spelling mistakes or variations, this manual approach becomes cumbersome.

In that case we have to use a VLOOKUP() based approach. We first create a two column table. First column containing all the unique items and second column containing the correct spelling for each item. Then we add a column in the raw data, lookup the original item and find the equivalent correctly spelt item from the lookup table. We will cover this concept in a future article.

Grouping text for categorization

Correcting spelling mistakes is operational work. But Grouping is also useful in more business related situations.

Create this pivot table : Rows – Segment, Data – Amount.
Right click in data – Show Values As – % of Column

We can see how much money is outstanding by industry. This is good.
Money not yet received is risky. Of course. But I want to do some more analysis.

Suppose, economy crashes suddenly. Will I lose all this money?
Or are there some industries which are more risky compared to others?

To answer this question, I do not want to see so many segments.
I only want two things – Risky and Safe.

In short, I am trying to categories each segment as Risky or Safe.
If you look at the raw data there is no column called Risk Type.

Don’t even think of adding that column in raw data and performing manual data entry (or even using VLOOKUP). Grouping will do this for you – right within the Pivot Table.

Select the risky Segments using CTRL Click.
Right click within the selection and choose Group.

Rename the Group1 to Risky.
Rename the column name Segment2 to Risk Type.

Now select the remaining three items Government, Healthcare and Telecom in the Segment column, right click and choose Group.
Rename Group2 to Safe.

Now remove the Segment column and suddenly we have a completely different perspective of our data.

This is how Grouping is used – to classify or categorize.

I could have used the SAME method to classify the SAME segments into a completely different way – Labor intensive vs. Capital Intensive  OR Manufacturing vs. Service oriented.

Imagine the benefits of such analysis. It will suddenly unearth immensely useful information which you can utilize to your advantage.

That is called efficiency!

Multiple levels of grouping are possible

Select individual items and create groups. Then select groups and combine them into higher level of groups. Using this method you can create a hierarchy.

Power Pivot does not support grouping

Therefore, you will have to create a separate table containing the unique raw values and additional columns for different levels of categorization. After importing both tables (raw data and this categorization table) you will need to create a relationship between them using the common column.

Once this is done, you can use any grouped column from the mapping table in a Power Pivot Table. It also allows you to create a hierarchy with simple drag drop.

We will cover Power Pivot in upcoming articles. This was mentioned here just for the sake of completeness.

Repetitive grouping

Unfortunately, grouping is required repetitively if you receive more and more data periodically. Pivot Tables based manual grouping is NOT a convenient method in this case. A more convenient method is to use VLOOKUP based text Grouping

What Next?

In the next article, we will see how to use Grouping on Numbers and create Ageing Analysis report in seconds.

Articles in this series

Grouping Text in Pivot Tables (this article)
VLOOKUP based text grouping
How to correct spelling mistakes while analyzing data?
Ageing analysis in seconds (Number Grouping in Pivot)
Ageing Analysis (Numeric Grouping in Pivot) – Part 2
Ageing Analysis (Bucket Analysis) using VLOOKUP – Part 3
Grouping Dates into months, quarters and years – in seconds!
Custom Date Grouping using VLOOKUP
Grouping with Date – Time Data using Pivot

***