logo
Archive

Can You Group Data In A Pivot Table

author
Ellen Grant
• Tuesday, 03 November, 2020
• 30 min read

For example, you may want to group an unwieldy list of dates or times (date and time fields in the Portable) into quarters and months, like this image. Note: The time grouping feature is new in Excel 2016.

excel pivot grouping table entries
(Source: www.excel-aid.com)

Contents

With time grouping, relationships across time-related fields are automatically detected and grouped together when you add rows of time fields to your Portables. In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed.

For numerical fields, enter a number that specifies the interval for each group. You might want to group columns or rows when you need to segregate data in a way that isn’t explicitly supported by your Excel table.

Excel creates a new grouping, which it names in numerical order starting with Group1. Excel still displays detailed individual information about Oregon and Washington in the pivot table.

However, the pivot table also groups the Oregon and Washington information into a new category: Group1. About the Book Author Stephen L. Nelson is an author and CPA who provides accounting, business advisory, tax planning, and tax preparation services to small businesses.

Pivot Tables allow you to easily summarize, analyze and present large amounts of data. The grouping and ungrouping features of Pivot Tables allow you to easily do this.

month excel date pivot table dates grouping quarter specific function
(Source: www.extendoffice.com)

Knowing how to quickly group data within a Portable report can help you immensely. One of the most useful features of a pivot table is the ability to combine items into groups.

Finally, I explain how to solve some of the most common problems and challenges you may encounter when trying to groupPivotTabledata. This Pivot Tutorial is accompanied by an Excel workbook example.

If you want to follow each step of the way and see the results of the processes I explain below, toucan get immediate free access to this workbook by subscribing to the Power Spreadsheets Newsletter. I use the following source data for all the examples within this Portable Tutorial.

The grouping option that's more suitable for a situation depends on the type of datayou're working with. The types of Fields that you can usually group automatically are those that hold the following data : Numeric.

If you want to add a Calculated Item, proceed in the following 3 steps: Ungroup the Field. Even though this Pivot Table Tutorial doesn't focus on Online Analytical Processing (Lap) sources, there are certain important restrictions/issues to consider.

pivot table date excel exceljet tutorial
(Source: exceljet.net)

In the following sections, I provide a detailed explanation of each of the different ways of grouping data in a Portable. In Excel 2016, Microsoft introduced the time grouping feature.

If your data spans a short period within one month, Subgroup does not take any action. The Fields are grouped based on the relationships identified in step #1 above.

This in turn, results in the following: Excel adds calculated columns or rows to group the Field data. The data is automatically arranged so that the highest-level date or time period is displayed first.

Toucan automatically group date or time Fields in an Excel 2016 Portable in 1 single easy step : Add a date or time Field to the Rows or Columns Areas of the Pivot Table.

Assume you have the following Portable report based on the example source data I explain above. Toucan both (i) add the Date Field to the Rows or Columns Area, and (ii) automatically group the Date Field in a single step.

excel data date pivot table grouped into pivottable grouping week amount
(Source: www.ablebits.com)

In this case, I add the Date Field to the Columns Area. Notice that, in this case, Excel displays the data at the higher-level date.

Adds the following 3 columns to the Rows Area: Years, Quarters and Date. Organizes the added columns in such a way that the highest-level date period is displayed first.

Add the date or time Field to the relevant Area of the Pivot Table. As an example, I add the Date Field to the Rows Area of the Portable report above.

The Date Field shows months instead of individual days. If you're working with version of Excel prior to 2016, you won't have access to the time grouping feature I explain in the previous section.

Even if toucan use time grouping, there are cases where this feature won't be the right tool your job. Therefore, in this section, I explain the general process for automatic Field grouping.

pivot table excel grouping data 2007 quarter microsoft totals sub want
(Source: answers.microsoft.com)

Generally, toucan automatically group Items in a Portable in the following 6 easy steps : Toucan also automatically group Items by using commands in the Ribbon or keyboard shortcuts.

In this case, you group the Items in 5 simple steps, as follows : Let's look at each of the steps and processes above in practice, and some details toucan consider when grouping Fields automatically.

Report #1: Displays the following data for each day and item: Sum of Units Sold. To a certain extent, the Portable reports above are already summarizing the 20,000 rows of raw data we're working with.

Report #1: Group the Date Field by months, quarters and years. Report #2: Group the Unit Price Field in $1,000 intervals.

As I explain above, toucan automatically groupPivotTable items in different ways. In the following sections, I look at the process of automatically grouping Portable Items by using a contextual menu.

pivot table dates grouping field date data tables excel versus columns source fields rows cell
(Source: www.excelcampus.com)

By: Your entry in the By input field depends on the type of data you're working with, as follows: Numeric Fields: Number that represents the group's interval size. This allows you to group date Fields by a certain number of days.

If you group dates by a certain number days and use the Number of days field (#4 above), toucan 't group by other time periods (months, quarters, years) at the same time. Toucan use the process I explain there to, for example, group by (i) weeks and (ii) months, quarters or years.

Report #1: I group all the available data in quarters and years, by specifying the following conditions: Starting at: January 1, 2017 (1/1/2017). Report #2: I group the Unit Price in $1,000 intervals, as follows: Starting at: 0.

Step #6: Click On The OK Button To confirm your grouping settings, click on the OK button in the lower section of the Grouping dialog box or press the Enter key. Step #3: Excel Displays The Grouping Dialog Box The look of the Grouping dialog box differs slightly depending on the type of Field you work with.

You generally encounter 1 of the following versions, depending on the Field: Step #4: Use The Grouping Dialog To Specify Grouping Conditions This is the same as step #5 of the process to automatically groupPivotTable Items through a contextual menu (above).

ungroup data pivottable pivot excel table dates grouped months quarters fields together
(Source: support.office.com)

In the Grouping dialog box, you get to specify the following conditions: If you work with a numeric Field, the smallest and largest numbers to group by.

Element #3 above (By) also differs slightly depending on whether you work with a date/time or numeric Field, as follows: Report #1: My purposes is to group data in quarters and years.

Report #2: My goal is to group the Unit Price Field in $1,000 intervals. Step #5: Click The OK Button After you enter the grouping conditions in the Grouping dialog, confirm your input by clicking on the OK button in the lower right corner of the dialog box.

The results I obtain in the examples we're working with are the same regardless of which process of automatic grouping (through a contextual menu vs. the Ribbon) I use. Excel assigns default names and labels to any newly created Fields or groups.

Toucan easily modify either of these by following the processes that I explain further below. The process to automatically group by dates that I explain in the previous section covers most situations.

week pivot table excel data grouped doc productivity extendoffice
(Source: www.extendoffice.com)

A common situation where this restriction can be annoying is if you want to group by weeks (7 days) and months, quarters or years. The most common solution to this problem is to add a helper column to the source data.

In other words, toucan group by weeks (or other number of days) and months, quarters and/or years in the following 6 easy steps : In each helper column, add a formula to calculate grouping levels/intervals.

Expand the data source of your Pivot Table to include the helper column(s). I explain how to group the data in months, quarters and years in the previous section(s).

A Portable report resulting from that process looks roughly as follows: The purpose of this(these) helper column(s) is to help you calculate the levels or intervals of the additional group (s) you want to add to the Portable.

In the example we're working with, I add a single helper column. As I mention above, the purpose of the helper column(s) you add to the source data is to calculate the grouping levels/intervals you need.

table pivot ungroup excel data selection grouping worksheet
(Source: www.datanumen.com)

ISOWEEKNUM: Calculates the ISO week number for a date. You can, therefore, nest MONTH within the TEXT Function to convert the number to a string.

You can specify that the week begins on Sunday by setting this argument to 1. Depending on your situation, you may have to manually expand the data source of the PivotTableyou're working with to include the helper column(s).

In some cases, Excel automatically expands the data source. This is the case if (i) your data source range is formatted as a Table, and (ii) the Portable data source is specified as that Table.

In such cases, toucan usually refresh the Portable in one of the following 4 ways: Right-click on the Pivot Table and select “Refresh” within the contextual menu displayed by Excel.

Go to the Change Portable Data Source dialog box. Adjust the reference to the source range within the Table /Range input field.

excel pivot table age range data tables results things voting basic exceljet ranges should know into source numeric many layout
(Source: exceljet.net)

Step #1: Go To The Change Portable Data Source Dialog Box Toucan make Excel display the PivotTableData Source using either of the following methods: Step #2: Adjust The Reference To The Source Range Within The Table /Range Input Field Within the Change Portable Data Source dialog, check the Table /Range input field.

Modify this specification to extend the data range and include the helper column(s). Step #3: Click OK Once the data source range specification includes the helper column(s), click the OK button in the lower right side of the dialog box.

Toucan complete the process of filtering by week, month, quarter and year by adding the Field(s) to the appropriate Area (Rows or Columns). In the example below, I add the newly-added Week Field at the bottom of the Rows Area.

The following alternative process allows you to manually group Items in 2 simple steps : After you group Items, Excel creates a new Portable Field.

Appears immediately within the Pivot Table Field List. Let's go through each of the steps of the processes I explain above to understand how this works in practice.

data excel grouping multiple manually groups table pivot names field pivottable row create select into office ablebits
(Source: www.ablebits.com)

Throughout the explanation below, I work with the following Portable report example. As I mention above, there are different ways to manually groupPivotTable Items.

In this section, I explain the first process I describe above: how to groupPivotTable Items through a contextual menu. Non-Contiguous Items: Maintain the Ctrl key pressed while making your selection.

Once you complete the simple 4-step process above, Excel groups the selected Items. The second way of grouping Portable Items that I describe above relies on the Ribbon.

As I explain above, toucan select Items with the mouse or keyboard. After you complete this quick 2-step process, Excel groups the selected Items.

Once you complete either of the processes to manually group Items I explain above (through contextual menu vs. Ribbon or keyboard shortcut), Excel creates a new Field (Item2 in the screenshot below). The Item2 Field appears automatically in the Rows' area of the Pivot Table.

excel pivottable data table row grouping pivot value office each orderid represented individual ablebits
(Source: www.ablebits.com)

Strictly speaking, this completes the process of manually grouping Portable Items. However, the default names that Excel assigns to the new Field and Items may not be the most meaningful.

Generally, toucan change the default name of a Portable Field in the following 4 easy steps : Excel displays the Field Settings dialog box.

Finally, in recent Excel versions, toucan change the default name of a Portable Field in the following 2 easy steps : In this section, I explain how toucan change a Field name through a contextual menu.

This is the Portable report that I create in the section about time grouping in Excel 2016 (above). Notice that the Field containing months is labeled, by default, “Date”.

Step #4: Excel Displays The Field Settings Dialog Box The Field Settings dialog box that Excel displays looks roughly as follows: The OK button is on the lower right section of the Field Settings dialog box.

pivot excel table example tables sample data tutorial using portfolio productivity create pdf information count field organize party nine patch
(Source: excelhelpsblog.wordpress.com)

Once you complete the easy 6-step process I describe above, Excel changes the Field name. In this section, I explain all the details of how toucan change a default Field name using the Ribbon or a keyboard shortcut.

This report is the result of automatically grouping date Fields using the process I describe in a previous section. Notice how the Field holding quarters is labeled “Date” by default.

In the following sections, I show you how I change that default label to “Quarter”. In the following screenshot, I select the Field header (Date).

Step #2: Go to Ribbon > Analyze > Active Field Or Use The Keyboard Shortcut “Alt, JT, M” The Ribbon has a Pittsfield Name input field. Toucan find this under Ribbon > Analyze > Active Field.

Toucan also get to the Pittsfield Name input field by using the keyboard shortcut “Alt, JT, M”. The following screenshot shows the results I obtain in the Portable example.

pivot excel table tables range data age create chart things department exceljet employee numbers breakdown should know count filter grouping
(Source: exceljet.net)

In this section, I go through a third method of changing a default Portable Field name. This is the result of manually grouping Items using the process I describe in a previous section.

Once you complete this simple process, Excel modifies the name of the Field. In the screenshot below, toucan see the new custom Field Name (Category instead of Item2).

Toucan change the default names of Portable Groups in the following 2 easy steps : To change the default name of a PivotTablegroup, start by selecting the cell.

If you prefer using the Ribbon or a keyboard shortcut, toucan ungroup PivotTabledata in these 2 simple steps : The effects of ungrouping a single group vary slightly depending on the Field you work with.

If you work with Excel 2016 and take advantage of the time grouping feature that I explain in a previous section, there's an additional consideration: the effects of undoing (“Ctrl + Z” keyboard shortcut) after time grouping is triggered. You add a date or time field to the Rows or Columns Area of a Pivot Table report.

excel grouping ranges consolidation pivottable pivot table data multiple row column groups labels label into field filter fields report creating
(Source: www.ablebits.com)

To understand how undoing works in the case of time grouping, let's look at the following 3-step process: Notice how Excel displays the data grouped by year, quarter and month.

The Date Field continues to appear within the Rows Area in the Portable report. However, notice that the data is organized by individual days (vs. higher-level periods such as month).

In the following sections, I go through each of the steps required to ungroup these Fields both manually and with the applicable keyboard shortcut. Example #2: Ungroup Date/Time Portable Field Through Contextual Menu In this section, I explain the process to ungroup a Field using a contextual menu.

Step #2: Excel Displays A Contextual Menu After you right-click on a Pivot Field Item, Excel displays a contextual menu. After you select Ungroup, Excel usually removes all grouping for the automatically-grouped Field.

Notice how a single call to the ungrouping command results in the removal of the groupings in years and quarters. Example #3: Ungroup Numeric Portable Field Through Ribbon Or With Keyboard Shortcut In this section, I show how toucan easily ungroup a Portable Field through the Ribbon or using a keyboard shortcut.

pivot table controls wpf devexpress sales sum
(Source: documentation.devexpress.com)

In other words, ungroup a single manually-grouped group of Items in these 2 simple steps : Let's go through the 3 steps of the basic process to ungroup a single manually-grouped group of Items.

In the example below, I ungroup the Items within the Surface group in the Portable below. Step #2: Excel Displays A Contextual Menu After right-clicking on an Item within the applicable group, Excel displays a contextual menu.

When you create a Portable, Excel generally makes a copy of the entire source data. This data is stored in a memory area known as the Pivot Cache.

Even though this has some practical advantages, it uses up memory and increases the size of your files. If you create several Pivot Tables based on the same source data, but each working with a separate Pivot Cache, your workbook may be bloated and slow due to the amount of (repeated) data.

The topic of the Pivot Cache exceeds the scope of this Tutorial. Most notably, as I mention above, it reduces memory requirements and file size vs. the scenario where the Pivot Cache isn't shared.

pivot excel table step tutorial ungroup data
(Source: powerspreadsheets.com)

However, Pivot Cache sharing has an important consequence on the behavior of Portable grouping : If needed, toucan force Excel to create a new Pivot Cache for the same source data in several ways.

Paste the Pivot Table in a separate (helper) workbook. Modify the grouping settings of the Pivot Table in the helper workbook.

When selecting the PivotTableyou want to copy, make sure that it's based on the source data you want the new Portable to use. Step #2: Paste The Portable In A Separate (Helper) Workbook Create a new workbook using either of the following methods: The keyboard shortcuts “Ctrl + N” or “Alt, F, N”.

Paste the Pivot Table using either of the following: The keyboard shortcuts “Ctrl + V”, “Alt, H, V, P” or “(Shift + F10), P”. Step #3: Modify The Grouping Settings Of The Portable In The Helper Workbook I explain several ways of specifying Portable grouping settings throughout this Tutorial.

There are, however, other alternatives to force Excel to create a new Pivot Cache. Step #4: Copy The Portable From The Helper Workbook Go to the helper workbook and copy the Portable that you pasted in step #2 above.

pivot table excel tables sample tutorial microsoft example data productivity portfolio pivottable field pdf fields working age take same
(Source: www.timeatlas.com)

The result of the process is that the newly-pasted Portable has its own separate Pivot Cache. In Step 2 of 3 of the Pivot Table Wizard, confirm the Range of your source data and click Next.

Step #2: Use The Keyboard Shortcut “Alt, D, P” However, in any case, toucan access the Portable Wizard with the keyboard shortcut “Alt, D, P”. Step #4: In Step 1 of 3 Of The Portable Wizard, Click Next The Portable Wizard begins by asking you about the (i) the type of source data you work with, and (ii) the report you want to create.

Data Source: Microsoft Excel list or database. Therefore, toucan generally click on Next button on the lower right side of the dialog box.

Step #5: In Step 2 of 3 of the Portable Wizard, Confirm The Range Of Your Source Data And Click Next In the second screen of the Portable Wizard, you're asked about the location of the source data you want to use. As I mention in step #1 above, Excel should determine the range automatically.

Once you're sure that the range is correct, click Next on the lower right side of the dialog box. Step #6: Excel Displays A Dialog Box Indicating That Toucan Use Less Memory If The New Report Is Based On The Previously-Existing Portable Report If the workbook you're working on has a previously-existing Portable report based on the same source data, Excel displays a dialog box.

pivot tables cannot excel fields table powerpivot description unlink
(Source: stackoverflow.com)

As explained in the dialog box (and above), this results in lower memory requirements and smaller file size. No: Results in Excel creating a separate Pivot Cache.

Once your choice is selected, click Finish in the lower right corner of the dialog box. After you complete the 8 steps above, Excel creates a new Portable report.

Toucan, however, use certain variations of these 3 processes to force Excel to separate Pivot Caches when (both) (i) creating a new Portable, or (ii) modifying an existing Portable. Go to the Change Portable Data Source dialog box.

This box is usually located on the upper left section of the screen. After you complete the process above, Excel defines the new name and assigns it to the selected data range.

Excel displays the Name Manager dialog box. Click the Close button in the lower right corner of the Name Manager dialog.

pivot table excel tables data sample example spreadsheet organize tutorial field range portfolio age row options create pivottable worksheet productivity
(Source: sites.google.com)

After you go through the 6 steps above, Excel creates the name and assigns it to the range. Step #3: Repeat Step #2 As Required Under this method, you get Excel to create a new Pivot Cache by defining different names for the same data range.

The Change Portable Data Source dialog looks as follows: Step #6: Within The Change Portable Data Source Dialog, Enter One Of The Newly-Defined Names In The Table /Range Input Field Enter 1 of the newly-defined names in the Table /Range input field of the dialog box.

I also discuss some other options toucan explore if blanks or data type inconsistencies aren't the cause of they cannot group that selection error or the greyed-out grouping buttons. As I mention above, a common best practice is to avoid working with source data that has empty rows or columns.

This is because Pivot Tables have some issues handling blank cells. In my experience, Excel 2016 handles blank cells better than previous versions.

Blank cells are generally errors waiting to happen. There are cases where determining the data you used to fill the blanks is easy.

pivot table excel data timeline tables productivity month slicer using sort increase summarize slicers instead average animated calculate pretty count
(Source: reviewhomedecor.co)

In those situations, toucan simply enter the appropriate value in the blank cell. Inappropriate Field formatting can be the cause of several Portable issues, including grouping problems.

Make sure that all the data within the column(s) that serve as source for the Field(s) you want to group is of the same type. Excel may be able to handle data type inconsistencies in several situations.

If Excel is interpreting values a text, there are several ways to solve the problem. The topic of text-to-value conversion exceeds the scope of this Portable Tutorial.

If you want to be informed whenever I publish new material in Power Spreadsheets, please make sure to register for our Newsletter by entering your email address below: You may encounter some (rare) cases, where they cannot group that selection error or the greyed-out group buttons aren't caused by (i) blank cells, or (ii) inconsistent data types within a Field.

Following the grouping processes I describe throughout this Pivot Table Tutorial; and Ensuring your data and Pivot Table meet the applicable conditions for grouping. This can theoretically happen when you're working with manual grouping of Items.

pivot table data source grouping dates field fields versus excel tables bad
(Source: www.excelcampus.com)

You're trying to group Fields that aren't in the Rows or Columns Areas of the Pivot Table. Excel generally disables the Group command when you select cells within the Filters or Values Areas.

In some cases, you may be able to group Items within the Filters or Values Areas in the following 3 steps : Group the Items within the Field, using the methods I describe in previous sections.

Move the Field back to the Area (Filters or Values) where you want to display it. In these cases, check your Field List and the Pivot Table Areas.

In some cases, the grouping causing the issue may be at the source data. As I explain in the section about automatic grouping, Fields must usually be date/time or numeric for you to use this feature.

In these situations, you can generally apply manual grouping by following the easy process I describe in a previous section. Microsoft introduced the time grouping feature in Excel 2016.

pivot excel tables things table microsoft create project myexcelonline charts most management few data format powerful feature analyze lots computer
(Source: www.myexcelonline.com)

The process to turn off Portable time grouping changes depending on which Excel 2016 version you have, as follows: The only way to turn off time grouping in a stand-alone version of Excel 2016 is by adding a new key to the Windows Registry.

Making changes to the Windows Registry is a sensitive matter. Check out, for example, the warning Microsoft makes at the beginning of the explanation of how to add the new registry key in the webpage I link to above.

If you choose to edit the registry, make sure to take any necessary precautions prior to doing it. This includes backing up the Registry and valuable data prior to making the modifications.

Step #3: Within The Data Section Of The Advanced Tab, Select The Option To Disable Automatic Grouping Of Date/Time Columns In Portables The contents of the Advanced tab are divided in several sections. Within the Data section, toucan find the setting to Disable automatic grouping of Date/Time columns in Portables.

Click the box on the left side of this option to add a checkmark. Once you complete the quick 4-step process above, Excel disables time grouping.

pivot table data step steps
(Source: www.wikihow.com)

Compare these results with those I show in the example within the section on how to automatically group date or time Fields in an Excel 2016 Portable. If you work with Lap sources, there are some grouping limitations to be aware of.

The following are the 3 main aspects to consider if you want to group data while working with Lap sources : You can 't group Items for an Lap “source that doesn't support the CREATE SESSION CUBE statement”.

Even if the CREATE SESSION CUBE statement is supported, you may want to avoid using it due to the disk usage issues this can generate. You can 't use the Convert to Formulas command (within Lap Tools) with grouped Items.

If you want to use Convert to Formulas, proceed as follows: Ungroup the Items. You can 't create Slicers for an Lap hierarchy that has grouped Fields.

You've also read about the time grouping feature that Microsoft added in Excel 2016. The information and examples provided in that section allow you to handle and troubleshoot the most common grouping problems.

pivot table know dont presentations don
(Source: www.everydayexcel.com)

Finally, I include a basic introduction to the limitations when grouping Items in Pivot Tables based on Lap sources. Remember that toucan get immediate free access to the Excel workbook example that accompanies this Portable Tutorial by subscribing to the Power Spreadsheets Newsletter.

United States of America: Pearson Education Inc. Helen, Bill (2015). United States of America: Pearson Education Inc. Hollenbach, John (2015).

Related Videos

Other Articles You Might Be Interested In

01: Excel Grouper Des Colonnes
02: Excel Grouper Des Donnees
03: Excel Grouper Intervalle Graphique
04: Warsaw Grouper Can You Eat
05: Wat Is Een Grouper Vis
06: Wat Is Grouper Vis
07: Wat Is Grouper Voor Vis
08: Spotted Grouper Aquarium
Sources
1 www.aquariumcreationsonline.net - https://www.aquariumcreationsonline.net/Grouperfish_saltwaterfish_2.html
2 animal-world.com - https://animal-world.com/encyclo/marine/groupers/BlueSpottedGrouper.php
3 www.aquariumcreationsonline.net - https://www.aquariumcreationsonline.net/Grouperfish_saltwaterfish.html
4 www.theledger.com - https://www.theledger.com/story/sports/2020/12/24/saltwater-gag-grouper-biting-offshore-and-around-inshore-structures/4024564001/
5 en.wikipedia.org - https://en.wikipedia.org/wiki/Brownspotted_grouper
6 www.youtube.com - https://www.youtube.com/watch
7 www.liveaquaria.com - https://www.liveaquaria.com/category/32/