Pivot Table Grouping, Ungrouping And Conditional Formatting

This tutorial explains what is Pivot Table Grouping, Ungrouping, and Conditional Formatting in Excel Pivot Tables:

In our previous tutorial, we understood how Pivot Table helps us in easily analyzing the data. However, the table looks more appropriate if we can combine similar types of data and present them together in a single table.

This tutorial will include details related to Pivot Table grouping and ungrouping features present in the Pivot Table. Along with that, we will also see how to format and highlight cells with certain values based on conditions or rules.

=> Check ALL Excel Tutorials Here

Pivot Table Grouping

Pivot Table Grouping is a method that is used to combine data of similar types present in the data source. It helps us to combine multiple related items under one heading, to get a clear picture of data.

For example, you can group all the sales details of the first 3 months under the group name called quarter1. All vegetables under one group etc. You can also give an appropriate name for the group.

Grouping works on 3 types of datasets:

1. Grouping By Numbers
2. Grouping By Dates
3. Grouping By Text

Data Source Sample File

We shall use the below-mentioned sample Excel file as our data source. Click on the link to download Sample DataFile Grouping

Grouping By Text

Before we jump into grouping, let’s create a Pivot table with the Employee Name under the Rows Area.

Now suppose all these employees belong to different departments like HR, IT & Admin and you want to group all the employees belonging to a single department under one group.

To achieve this, follow the below-mentioned steps.

#1) Click on the first name you want to add to the group.

#2) Now press the Ctrl key on the keyboard and select all the other employees you want to add to the same group.

#3) Right-click on one of the selected names and click on Group in the context menu.

#4) Similarly, select the other names that you want to group together and click on Group in the context menu.

3 groups are created based on the selection that we have made. All the groups have been automatically given a generic name called Group1, Group2, etc. as shown below.

Now if you notice in the PivotTable Fields, a new field is created called as Employee Name2 and it’s automatically added to the Rows area. The new field is positioned above the original field.

Rename The Group Title

The table above is not presentable as all the groups have a generic name and you cannot differentiate between who belongs to which department. Hence it is a good practice to add a meaningful name to the group.

#1) Click on the Group title i.e. Group1, Group 2.

#2) Enter a new name.

To Add More Items To A Group

Suppose a new employee has joined the organization and you want to include him in one of the groups.

We have added a new name called Vidya to the data source and updated the Pivot table and the resulting table will look as shown below.

To add Vidya to Group HR

1. Select all of the items present in the HR group.
2. Press Ctrl on Keyboard and select the name that has to be added to the group, In this example Vidya.
3. Right-click on one of the selected names and click on Group.
4. Vidya will be added to Group HR.

Note: Observe that the Group name is overridden by the default name as Group1. So every time you add a new entry to the group, make sure to change the name because it will be overridden.

Renaming The Row Name In Pivot Fields

We have seen that grouping created a new field automatically with a random name example Employee Name2, in order to rename that to a more meaningful name.

#1) Click on any of the group names example HR.

#2) Navigate to Analyze.

#3) Change the name under Active Field.

Note: The changes made to the name of the Field and Groups are only specific to the Pivot Table i.e. if you create a new Pivot table they will have the default name.

Grouping By Numbers

If you have a long list of numbers, in order to have a summarised data in the table, you can group the numbers and present them in a more readable format.

Let’s create a Pivot Table from the data source, and a sample file is attached at the end of this tutorial.

Suppose you want to count how many Engineers, Senior Engineers, and Lead Engineers are presently based on their age group in the organization. So let’s drag the Age under the Rows area to create our Pivot table.

#1) Right-click on any number in the pivot table.

#2) On the context menu, click Group.

#3) Grouping dialog box appears, in this example, the least number is 25, so by default the Starting number is entered as 25, and you can change if necessary.

#4) In this example, the highest number is 55, so by default, the highest number is entered at the End in the text box.

#5) The number entered in the By text box will be used to group in the pivot table.

For example, if you enter 10, then 25-34 will be in one group.

#6) Click OK.

Now to get the count again, drag the Age field under Values.

The resulting table will be as shown below.

The table shows that there are 5 employees between 25-34 (Engineers) 3 employees between 35-44 (senior engineers) and 3 Lead engineers with the age between 45-55.

You can also move the grouped field under the Columns area, as headings, to get a more concise layout as shown below.

Grouping By Date

Grouping by date can be done in many different ways. It can be grouped by Years, Months, Quarters, Seconds, Minutes, Hours, and Days.

Suppose you want to count the number of new joiners in the organization and show the details based on years and months.

Let’s create a pivot table with the Date of Joining under the Rows.

When you add the Date of Joining under Rows, you can see it automatically added Years and Quarters. But if you want to group specifically using years, months, etc., group by date can be used.

Group Dates By Month And Year

1. Right-click on one of the dates present in the pivot table.
2. Click Group on the context menu.
3. On the Grouping dialog, select one or more options under By. I have used the Years and Months grouping
4. Start and End dates will be automatically updated. It’s the lowest and the highest date on your table. You can opt to change it as desired.
5. Click OK to close the dialog box.

The resulting table will be as shown below.

Now drag the Date of Joining under the Values areas.

You can see the grouping of Years and Months and the count of employees who joined.

If you want to add the date as well in the table, navigate again to the Grouping dialog from the right-click context menu and select Days under the By list.

Grouping By Week

Now if you notice in the Grouping dialog, there is no option to group by week. To achieve this there is a list box called Numbers of days.

In order to group by week, we have to select Days and select 7, because there are 7 days in a week. As an example, we have created a pivot table for DOB and grouped it by week.

The resulting table is shown below.

Similarly, if you want to group by 6 months then you have to make the number of days as 180 and the result will show the 6 months’ time period.

Pivot Table Ungrouping

Ungrouping is fairly simple and it will revert the changes made to the table as well as the new fields created.

Ungroup The Dates And Numbers

Date and Number ungrouping works in the same way.

1. Right-click on any of the date values like year, months, etc.
2. Select Ungroup from the context menu.

The result is shown below that all the grouping has been removed and a plain date is presented in the table.

Ungrouping Text

Ungrouping text works a little differently. If you click on any one of the Group name like HR, IT, and Admin in our example, then only that will be ungrouped as shown below.

In order to remove the entire grouping, you have to select HR, IT, and admin.

1. Click on HR
2. Press the Ctrl key on the keyboard and click on IT and Admin.
3. You can notice that the selected cells are highlighted in grey colour.
4. Right-click on any one of 3 cells and select Ungroup.

The resulting table is as shown below.

Conditional Formatting Pivot Table

Conditional formatting is used to define rules to format data values in the table. It helps us to identify the important data easily in a large set of records by allowing us to change the font, color, add icons, etc.

For better understanding, we have created a new data source with multiple values. The sample is shown below. Attached is the sample file at the end of this tutorial.

Let’s first create a simple pivot table with Student Name in Rows and Total in Values.

Applying Conditional Formatting

#1) Top/Bottom Rules

Assume that you want to highlight 3 students who scored the least marks in Red.

#1) Select the entire column under the Sum of Total column in the pivot table.

#2) Navigate to Home -> Conditional Formatting

#3) Select Top/Bottom Rules -> Bottom 10 items.

#4) In the dialog reduce the count to 3 (since we want just the bottom 3) and you can choose any highlighter from the drop-down.

The resulting table will highlight the bottom 3 totals in red.

Similarly, let’s highlight the top 3 totals in the table.

1. Select the entire column under the Sum of Total column in the pivot table.
2. Navigate to Home -> Conditional Formatting.
3. Select Top/Bottom Rules -> Top 10 items
4. In the dialog reduce the count to 3 (since we want just Top 3) and you can choose any highlighter from the drop-down.

You can notice that the Grand Total is highlighted in Green as it will always be among the top list.

To discard the grand total from the selection:

1. Click on any one cell under the Sum of Total column.
2. Navigate to Home -> Conditional Formatting
3. Select Top/Bottom Rules -> Top 10 items
4. In the dialog reduce the count to 3 (since we want just Top 3) and you can choose any highlighter from the drop-down.
5. You can notice a small Icon next to the highlighted cell, Click on the Icon.
6. Select “All Cells showing Sum of Total Values for Student Name.

Similarly, you can use the same for Top/Bottom percentages, Above Average or Below Average.

#2) Highlight Cells Rules

You can also highlight the cells based on certain rules like greater than, less than, etc.

Let’s create one more pivot table adding all the subjects under Values and student name under Row.

The resulting table is as shown below.

Greater Than

In this table, let’s try to highlight all the marks which are greater than 90, and we shall use custom coloring.

#1) Select all the cells that contain marks example 97 from Subject1 till 77 on Subject4.

#2) Go to Home -> Conditional Formatting -> Highlight cells Rules -> Greater than.

#3) In the dialog, enter 90 and select Custom Format.

#4) From the Format cell window, you can select the highlight pattern of your choice. We will select to just fill the cells with green.

#5) Click ok and the resulting table will be as shown below.

Less Than

Similar to Greater than you can use Less than as well. Considering the same table, let’s highlight the cells with marks less than 35.

#1) Select all the cells that contain marks example 97 from Subject1 till 77 on Subject4.

#2) Go to Home -> Conditional Formatting -> Highlight cells Rules -> Less than.

#3) In the dialog, enter 35 and select Custom Format.

#4) From the Format cell window, you can select the highlight pattern of your choice. We will select to just fill the cells with Red.

#5) Click Ok

Similarly, you can other highlight options like Between 2 numbers, equal to 2 numbers, Duplicate, etc.

#3) Data Bars

Data Bars give the bars on the marks that represent how close the obtained total is close to the total marks.

We will create one more pivot table with just the student names in Rows and Total in Values.

1. Go to Conditional Formatting -> Data Bars.
2. Select from the list of options available or you can even select the custom format.

The resulting table will be shown below.

Similar to Data Bars, you can add color scales with which you will color the cells differently based on their values.

#4) Icon Sets

This will give you various icons that can be added to the cells.

You can anytime edit these rules from the Manage Rules options.

#1) Select any Pivot table and click on Conditional Formatting -> Manage Rules.

#2) This will give you the dialog with all the rules for that Pivot table.

#3) From this dialog you can create a new rule, edit rule, or even delete the rule for that Pivot table.

#4) We will edit the Icon set rule, so select the Icon set and click on Edit Rule.

#5) We will edit the rule to show icons only from 24 to 32 and change the Icon style to an arrow.

The resulting table will be as shown below.

Frequently Asked Questions

Q #1) Can you group columns in a pivot table?

Answer: Yes, data can be grouped in both rows and columns. You need to select the items to be grouped, right-click, and select Group in the context menu.

Q #2) Why can’t you group in Pivot Table?

Answer: If you see an error while grouping, then it is mostly because of the below errors:

• The table might have an invalid date. Example 29/02/2018 which is not a valid date.
• Blank cells in the table are also considered to be invalid. Fill with dummy data.
• The date or number cell might have a text value that will lead to an error.
• While creating a pivot table if you have checked “Add this data to the data model” then you won’t be allowed to perform grouping. Create a new pivot table by checking the data model checkbox.

Q #3) How do you remove grouping from the pivot table?

Answer: Select the group that you want to remove from the group, right-click, and select Ungroup from the context menu.

Q #4) How do you group by month in the pivot table?

Answer: You can group dates by months or quarters.

• Click on any cell in the Date column in the pivot table.
• Go to Analyze –> Group –> Group Selection.
• From the Grouping dialog, select Months and Years.
• Click Ok

Q #5) How do you keep conditional formatting in the pivot table after refreshing?

Answer: When you add new records to the data source and update the pivot table, conditional formatting sometimes might not have an effect on new entries.

Follow the below steps to apply the format for the new records.

• Go to Conditional formatting -> Manage rules
• Select the appropriate rule and edit the Range of cells under Applies to.
  • Example =$B$5:$C$10

Q #6) How do you highlight the top 3 in the pivot table?

Answer:

• Select the column in which you want to find the top 3 values.
• Go to Home -> Conditional Formatting -> Top/ Bottom Rules -> Top 10 items
• On the dialog, change the list box number to 3 and select the appropriate format color.
• This will highlight the top 3 values in the table.

Conclusion

In this Pivot Table Grouping tutorial, we saw how to group together similar kinds of data for text, dates, and numbers. Ungrouping the same was also discussed.

We also learned conditional formatting methods on pivot tables that allows us to format Pivot Table dynamically.

=> Visit Here For Simple Excel Training Tutorials