Microsoft Excel provides its users with the feature of Pivot table. It is basically a summary tool which is used by the users to summarize particular data or information from a large datasheet. You can summarize or manage data from your worksheet by creating groups using the pivot table which may include sums or averages or other sort of statistics which will make your work cleaner, easier and quicker.
Here we will show you how you can group pivot table items. You can group the data based on Name, Date and Number. A detailed tutorial on this topic is given below.
Group Names/Products/Items
You have 7 items in the worksheet given below – GPU, Desktop table, Desktop chair, Power bank, PSU, Keyboard and mouse. Among them GPU, Power bank, PSU, Keyboard and mouse are electronic items and Desktop chair, and table are furniture items.
From the above data table, we create the following Pivot Table:
You can separate these two sort of items in two groups following the steps below.
Step 1: Press and hold Ctrl command key and select Desktop chair and Desktop table one by one and right click on your mouse. Now click on Group.
Step 2: Group 1 is created.
Step 3: Follow the same process for selection or click and drag from GPU to Mouse, then right click and select Group to create another group for the electronic items which will be Group 2.
Step 4: You can also change the names of the groups. Simply click on the name of a group and then change the name from Formula bar.
Step 5: If you want to minimize the groups just click on the minus sign.
Group Dates
The worksheet contains many dates like 12-Oct-2022, 7-Nov-2022, 8-Dec-2022 etc. You can create groups of these dates too for your work purposes.
Step 1: Click on any cell that have dates and then right click on your mouse. Click on Group.
Step 2: Select Days. Type any number in the Number of days box to select the range of dates you want to create. The dates are grouped based on 15 days range in the example below.
Step 3: Here we can see 11/11/22 – 25/11/22 is the range of dates with most Sum of Product Value.
Group Numbers
Here we will show you how you can group the data on your worksheet based on numbers. A pivot table is given below which is representing the Store no, Product value and Product amount. To create group numbers using this pivot table, follow the steps bellow.
Step 1: Click on any cell which has Product Value in it.
Step 2: Now select Pivot Table Analyze and Select Group Selection.
Step 3: A Grouping dialogue box will pop-up on your screen. Specify the Starting at, Ending at and By with your desired values as shown below in the picture based on your worksheet data and click OK.
Step 4: The data in this worksheet are Grouped based on the number range of product’s value. Here we can see Store 3 has the highest valued transaction between range $917-$1017.
Step 5: You can also create a matrix by dragging the grouped Product Value in column area.
This table can be used to analyze which stores does the high value transactions and which stores need to improve on its strategy for better sales.
How to Ungroup Numbers in Pivot table
These number groups created above can also be Ungrouped if needed. Simply click on any grouped number and then select Pivot Table Analyze and click Ungroup.
You may be interested:
