Pivot Table | Chapter 4 | Grouping Ungrouping

Grouping and un-grouping with pivot table data. 

 

Grouping & Ungrouping Buttons

Download the datasheet that we are going to use for demonstration in this post.

Basic Pivot Table from the Datasheet

Explanation:

• Region field is placed on rows
• Rep (Representatives) are placed on column.
• Value total is displayed.

Now lets introduce Order Date On rows.

Order date Grouped

Explanations: 

• As usual two group fields has generated - 'Quarters' and 'Years'.
• Region, Years, Quarters and Order dates are incorporated into Rows area.

Lets ungroup years. 

•  Select any year cell.
•  Click Ungroup under Analyze tab

Ungrouped into basic order dates

Explanations: 

• Years has been ungrouped into basic ordering dates.

Now we are going to group dates as per Qtrs and Months and years.

• Select any cell with dates.
• Click the button Group Selection.
• Check the relevant date range. 
• Highlight Months, Quarters, Years.
• Ignored Days else one can highlight Days too. 

 

Grouping window

Grouped

Explanations:

• Negative buttons can be clicked  to integrate the rows.

We can create our own group with existing groups. 

One of the most useful features of Pivot table is the ability to create groups

Above is the default table. Lets Create Group 1 with Central and East Region. 

• Select Central and East Region (Including years)
• Under Analyze Tab click 'Group Selection'.

Explanations:

•  New Group 1 emerges. 
• One can ungroup this group.

Timeline 

 Instead of adjusting filters to deal with dates, one can use Timeline.

Timeline is a filter option that one can use to zoom a particular date/time period in the pivot table. It is handy.

Click inside the pivot table > Under the Analyze tab click Time line

In the instance Timeline will deal with 'Order Date'.

Explanations: 

• Display name (Order Date) can be changed. Change the Time Line Caption (Ribbon Left).
• The Timeline window comprises of two sliders one (colored) to highlight the zoom option that is particular month, year, qtr etc, and another slider helps to navigate the zoom bar.
• In the Timeline window there is a zoom bar, here by default it is zoomed in months. If one clicks on the sliding bar at a particular point of the month the Pivot Table will change accordingly.
• Again Month option can be changed to Qtrs and so on.
• You can change the Timeline styles.
• One can reset the Timeline by clicking at the top right corner icon.