Your password has been reset
※ Download: Excel 2016 pivot table dates
I love reading and sharing success stories from amazing members of the Power Spreadsheets community, like you. Even Power-BI falls on its face trying to do this. To remove a slicer from your pivot table, click it to select it and then press the Delete key.
Since you don't want to share the Pivot Cache between the Pivot Tables, select No. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. Notice how the new name Months appears in both the Pivot Table and the Pivot Table Fields task pane. If you are using an older version of Excel this is on the Advanced tab.
How to Filter Pivot Table Data in Excel 2016 - What steps do I need to take to make this happen?
Pivot Tables allow you to easily summarize, analyze and present large amounts of data. However, to appropriately do this, you must be able to organize the data into adequately-sized and organized subsets. The grouping and ungrouping features of Pivot Tables allow you to easily do this. You see… Knowing how to quickly group data within a PivotTable report can help you immensely. This is because it allows you easily group a huge amount of disparate data into a few groups or subsets. Fewer groups allow you to simplify your analysis and focus on the grouped Items that matter the most. As explained by Excel guru John Walkenbach in the Excel 2016 Bible: One of the most useful features of a pivot table is the ability to combine items into groups. This Pivot Table Tutorial explains all the details you need to know to group and ungroup data in a Pivot Table. I focus on showing how you can easily group different types of Fields in different circumstances. You can also find a thorough explanation of how to ungroup data. Finally, I explain how to solve some of the most common problems and challenges you may encounter when trying to group Pivot Table data. The following table of contents lists the main contents I cover in the blog post below. If you want to follow each step of the way and see the results of the processes I explain below, you can 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 Pivot Table Tutorial. This is similar to the data in other Pivot Table Tutorials, such as. The table contains 20,000 rows. The grouping option that's more suitable for a situation depends on the type of data you're working with. If you're working with Excel 2016, there's an additional grouping feature you can use: and time column grouping. Pivot Table grouping is quite flexible. It allows you to group several different types of Fields. You can create many groups and you can group previously existing groups create groups of groups. Despite its flexibility, Pivot Table grouping has some restrictions. I provide some more comments about these in an individual section below. In the following sections, I provide a detailed explanation of each of the different ways of grouping data in a Pivot Table. How To Automatically Group Date Or Time Fields In An Excel 2016 Pivot Table In Excel 2016, Microsoft introduced the time grouping feature. Time grouping is generally triggered when you add a date or time Field to either the Rows or Columns Areas of a Pivot Table report. For example, as explained by Excel MVP Bill Jelen Mr. Excel in Excel 2016 in Depth: If your data spans a short period within one month, AutoGroup does not take any action. If your data spans several months but does not fall outside of one year, AutoGroup groups to months. The above may sound difficult. The example below shows how this looks in practice. Let's see how this looks in practice: Assume you have the following PivotTable report based on the example source data I explain above. It displays the Sum of Units Sold and Sum of Sales Amount for each item. No information from the Date Field is displayed because the Field isn't yet in any Area. You can both i add the Date Field to the Rows or Columns Area, and ii automatically group the Date Field in a single step. In this case, I add the Date Field to the Columns Area. The resulting Pivot Table report looks as follows. Notice that, in this case, Excel displays the data at the higher-level date. In this case, that's years. Once I expand the groups, the Pivot Table looks as in the screenshot below. Years appears before Quarters. Quarters is before Date. If you're working with data model Pivot Tables, consider the following restriction explained at : If you drag a date Field that has more than 1,000 rows of data from the Field List to a Pivot Table Area, the Field is removed from the Field List. This allows Excel to display a Pivot Table overriding the 1 million records limitation. If you automatically group Fields with time grouping, Excel assigns default names and labels to the newly created Fields and groups. I explain how you can modify either of these in a separate section below. Automatically Group Date Or Time Fields With Time Grouping When Field Already Appears In Pivot Table You can take advantage of the time grouping feature even if you've already added date or time Fields to the same Area. To understand the situation, consider the following Pivot Table. This is the Pivot Table that appears above after I ungroup the Date Field. In such situations, you can anyway use time grouping. As an example, I add the Date Field to the Rows Area of the Pivot Table report above. The resulting Pivot Table report below is the same as that which I show above. The Date Field shows months instead of individual days. How To Automatically Group Items In A Pivot Table 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 you can 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. The process above works through a contextual menu. You can also automatically group Items by using commands in the Ribbon or. Let's look at each of the steps and processes above in practice, and some details you can consider when grouping Fields automatically. To a certain extent, the PivotTable reports above are already summarizing the 20,000 rows of raw data we're working with. However, you may want to group your data further. How To Automatically Group Pivot Table Items Through Contextual Menu As I explain above, you can automatically group Pivot Table items in different ways. In the following sections, I look at the process of automatically grouping Pivot Table Items by using a contextual menu. Step 1: Right-Click On A Field That Is Suitable For Automatic Grouping As I explain above, you can't automatically group absolutely all Fields. Step 2: Excel Displays A Contextual Menu After your right-click on a Pivot Table Field suitable for automatic grouping, Excel displays a contextual menu. Step 3: Select Group In the contextual menu that Excel displays, select Group. Step 4: Excel Displays The Grouping Dialog Box After you select Group, Excel displays the Grouping dialog box. This type of grouping is commonly used for frequency distributions. You can usually select more than 1 time period for grouping. 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 , you can't group by other time periods months, quarters, years at the same time. I explain how to get around this restriction in a separate section below. You can use the process I explain there to, for example, group by i weeks and ii months, quarters or years. 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. Excel groups the Fields accordingly I show this below. How To Automatically Group Pivot Table Items Through The Ribbon Or With A Keyboard Shortcut In this section, I look at a second way to automatically group Pivot Table Items. In this case, you work with the Ribbon. Step 1: Select The Field You Want To Group Automatically This step is substantially the same as step 1 I describe above for automatically grouping Pivot Table Items through a contextual menu. The difference is that, instead of right-clicking on the Field, you select it. 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. Element 4 Number of days applies when you group by days. You use it to specify the number of days used to group the data into. 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. Results Of Automatically Grouping Items In A Pivot Table 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. Excel assigns default names and labels to any newly created Fields or groups. You can easily modify either of these by following the processes that I explain further below. However, as I explain above, you can't group by i a certain number of days, and ii the other grouping periods months, quarters or years. 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 the following sections, I show you how to group by weeks, months, quarters and years following this process: Step 1: Group The Date Field, To The Extent Possible, Using The Automatic Grouping Process I explain how to group the data in months, quarters and years in the previous section s. A Pivot Table report resulting from that process looks roughly as follows: Step 2: Add Helper Column s To The Source Data Once your data is grouped, to the extent possible, using Excel's grouping feature, go back to the source data. Add 1 or more helper column s to the source data. 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 Pivot Table. In the example we're working with, I add a single helper column. Therefore, may vary depending on your objective. Week 1 is the one containing the first Thursday of the year. Generally, the week containing January 1 is week 1 of the year. MONTH returns a number between 1 January and 12 December. You can, therefore, nest MONTH within to convert the number to a string. In our example, I use the WEEKNUM Function. My purpose is to group by weeks. You can specify that the week begins on Sunday by setting this argument to 1. Step 4: Expand The Data Source Of Your Pivot Table To Include The Helper Column s Depending on your situation, you may have to manually expand the data source of the Pivot Table you'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 is formatted as a Table, and ii the PivotTable data source is specified as that Table. This field displays the source data range. 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. This confirms the changes you've made. Step 5: The Pivot Table Field List Displays The New Field s That Correspond To The Helper Column s You Added After completing the previous 4 steps, as required, Excel displays the newly added Field s to the Pivot Table Field List. This These Field s correspond to the helper column s. In the example we work with, this looks as follows: Step 6: Add The Newly-Added Field s To The Rows Or Columns Areas Once Excel adds Field s to the Pivot Table Field List, you can work with them as usual. This includes moving them to the Rows or Columns Areas. You can 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 resulting Pivot Table report groups items by week, month, quarter and year. How To Manually Group Items In A Pivot Table In some cases, automatic grouping isn't the best solution for your challenge. Fortunately, you don't always have to rely on automatic Field grouping. Excel allows you to manually group selected Items. If you're working with Fields that are organized in levels, you're only allowed to group Items that are at the same level. After you group Items, Excel creates a new Pivot Table Field. Let's go through each of the steps of the processes I explain above to understand how this works in practice. Manual Grouping Of Pivot Table Items Example Throughout the explanation below, I work with the following Pivot Table report example. The Pivot Table is based on the source data that I explain above. How To Manually Group Pivot Table Items Through Contextual Menu As I mention above, there are different ways to manually group Pivot Table Items. In this section, I explain the first process I describe above: how to group Pivot Table Items through a contextual menu. Step 1: Select The Items Of The Pivot Table That You Want To Group You can select the Items you want to group using the mouse or the keyboard. Step 2: Right-Click Your Selection Once you've selected the Items to group, right-click the selected Items. Step 3: Excel Displays A Contextual Menu After you right click, Excel displays a contextual menu. Step 4: Select Group Within the contextual menu that Excel displays, choose Group. Once you complete the simple 4-step process above, Excel groups the selected Items. How To Manually Group Pivot Table Items Through Ribbon Or Keyboard Shortcut The second way of grouping Pivot Table Items that I describe above relies on the Ribbon. Let's look at its 2 simple steps: Step 1: Select The Items You Want To Group This step is the same as the first step to manually group of Pivot Table Items through a contextual menu. As I explain above, you can select Items with the mouse or keyboard. After you complete this quick 2-step process, Excel groups the selected Items. Results Of Manually Grouping Pivot Table 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. This new Field is based on the grouped Items. Thereafter, you can work with that new Field in the same way as with regular Fields. In the example we're working with, Excel creates 1 Field Item2. The results are shown in the image below. Strictly speaking, this completes the process of manually grouping Pivot Table Items. However, the default names that Excel assigns to the new Field and Items may not be the most meaningful. Let's look at how you can change these. The following sections also apply to automatic grouping and time grouping, which I explain in previous sections. How To Change Default Pivot Table Field Names There are several ways to change Pivot Table Field names. I explain some of these in this section. The above process relies on a context menu. But you can also use the Ribbon or keyboard shortcuts to achieve the same effect. Let's go through each of the processes I explain above in more detail: How To Change Default Pivot Table Field Names Through A Contextual Menu In this section, I explain how you can change a Field name through a contextual menu. As an example, I use the following Pivot Table. This is the Pivot Table report that I create in the section about time grouping in Excel 2016 above. Step 1: Right-Click On The Field To begin the process, right-click on the Field you want to change. In the example we work with, I right-click on the Field header. You can also right-click on other cells within the Field. Step 4: Excel Displays The Field Settings Dialog Box The Field Settings dialog box that Excel displays looks roughly as follows: Step 5: Enter The New Field Name In The Custom Name Input Field The Custom Name input field is on the upper section of the Field Settings dialog. This is where you can specify the Field name you want to use. Step 6: Click OK After you've entered the new Field name, click OK to confirm the changes. The OK button is on the lower right section of the Field Settings dialog box. Once you complete the easy 6-step process I describe above, Excel changes the Field name. The screenshot below shows the results in the Pivot Table I use as example. Notice how the new name Months appears in both the Pivot Table and the Pivot Table Fields task pane. How To Change Default Pivot Table Field Names Through The Ribbon Or A Keyboard Shortcut In this section, I explain all the details of how you can change a default Field name using the Ribbon or a keyboard shortcut. As an example, I work with the following Pivot Table. This report is the result of automatically grouping date Fields using the process I describe in a previous section. Step 1: Select The Field Begin the process by selecting a cell in the Field whose name you want to modify. In the following screenshot, I select the Field header Date. You can also select other cells within the same Field. Step 3: Enter The New Field Name And Press Enter Type the new Field name in the PivotField Name input field. Confirm your entry by pressing the Enter key. Because of the process above, Excel updates the Field name. The following screenshot shows the results I obtain in the Pivot Table example. Notice the new Field name Quarter in the Pivot Table, Pivot Table Fields List and Rows Area. How To Change Default Pivot Table Field Names Directly In The Cell In this section, I go through a third method of changing a default Pivot Table Field name. As an example, I use the following Pivot Table report. This is the result of manually grouping Items using the process I describe in a previous section. Notice the default name Item2. Once you complete this simple process, Excel modifies the name of the Field. In the screenshot below, you can see the new custom Field Name Category instead of Item2. How To Change Default Pivot Table Group Names In addition to changing the default names of the Fields that result from grouping, you can modify the default names of the groups themselves. In the following sections, I explain these 2 simple steps. As an example, I work with the following Pivot Table report. This is the same report that appears in the screenshot above. Step 1: Select A Cell Containing The Group Name To change the default name of a Pivot Table group, start by selecting the cell. In the example we're working with, I separately select the cells of both Group1 and Group2. Once you edit the name of the group within the cell, Excel updates all the group names within the Pivot Table. If you want to ungroup a manually-grouped Field, right-click on the Field header. The process above works with a contextual menu. To entirely ungroup a manually-grouped Field, select the Field header. The effects of ungrouping a single group vary slightly depending on the Field you work with. Therefore, the Field that Excel created when you manually group items doesn't disappear until you ungroup all groups within that Field. Basically, you can immediately ungroup the Fields that time grouping groups by undoing the last action. This triggers time grouping. This results in the removal of the calculated columns or rows the time grouping featured added. Therefore, the only Field left is the one you originally added. In other words, this first undo only undoes the time grouping 2 above. This second undo is the one that undoes everything within this process. Let's go back to the examples used in previous sections of this Tutorial to see how each of the 4 scenarios above looks like in practice: Example 1: Ungroup Date Or Time Fields Automatically Grouped By Time Grouping In Excel 2016 I show how the time grouping feature works in Excel 2016 in a previous section. Let's go through each of the steps in more detail: Step 1: Move The Date Field From The Pivot Table Field List Into The Rows Area As I explain above, this is the single step you take to automatically group date or time fields in an Excel 2016 Pivot Table. As expected, this triggers time grouping. Notice how Excel displays the data grouped by year, quarter and month. Step 2: Undo 1 Time The first time you undo, Excel undoes the automatic grouping. The Date Field continues to appear within the Rows Area in the Pivot Table report. However, notice that the data is organized by individual days vs. Step 3: Undo A Second Time The second time you undo, Excel removes the date Field added in step 1 above from the Pivot Table. In other words, the whole process is undone. I use this report in example 2 below. This is the report I use in example 3 below. In the following sections, I go through each of the steps required to ungroup these Fields both manually and with the applicable keyboard shortcut. Step 1: Right-Click On An Item Within The Group You Want To Ungroup The Item you right-click on depends on the group you want to ungroup. In the example we're looking at, I can right-click on any Item within the Years or Quarters Fields. 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. In the Pivot Table report example, the results look as follows. Notice how a single call to the ungrouping command results in the removal of the groupings in years and quarters. Example 3: Ungroup Numeric Pivot Table Field Through Ribbon Or With Keyboard Shortcut In this section, I show how you can easily ungroup a Pivot Table Field through the Ribbon or using a keyboard shortcut. Step 1: Select 1 Of The Items Within The Group The Item you select depends on the group you want to ungroup. In this example, I can select any Item within the Unit Price Field. In the following sections, I show how both ungrouping methods. Let's look at the basic 3-step process to ungroup a manually-grouped Field. I illustrate the steps in the second process in the following section. Step 1: Right-Click On The Field Header To ungroup a manually-grouped Field, start by right-clicking on the Field Header. In the example below, I right-click on the Category Field header. Step 2: Excel Displays A Contextual Menu Because of step 1 above, Excel displays a contextual menu. Step 3: Select Ungroup Within the contextual menu, choose Ungroup. The following image shows the results I obtain in the case of the Category Field. Notice how, as expected, Excel has eliminated the whole Field from both the Pivot Table report and the Field List. Ungroup A Single Manually-Grouped Group Of Items The process to ungroup a single manually-grouped group of Pivot Table Items is like that of ungrouping the whole Field. You can achieve the same result using keyboard shortcuts. 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 Pivot Table below. Step 1: Right-Click On An Item Within The Group You Want To Ungroup If you're ungrouping manually-grouped Pivot Table Items, you must click on 1 of the Items within the relevant group. For example, as I explain above, I only ungroup one of the groups: Surface. Therefore, I right-click 1 of the Items within this group. Step 2: Excel Displays A Contextual Menu After right-clicking on an Item within the applicable group, Excel displays a contextual menu. Step 3: Select Ungroup To confirm that you want to ungroup the Items, select Ungroup. Excel immediately ungroups the Items within the group. How To Create Multiple Pivot Tables Based On The Same Source Data But With Different Groups When you create a Pivot Table, Excel generally makes a copy of the entire source data. This data is stored in a memory area known as the Pivot Cache. By storing the data in the Pivot Cache, Excel creates an additional copy of the source data. 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. If you're interested in this topic, you can read more it at the websites of Excel MVP's and. I include this brief discussion about the Pivot Cache because a common way to reduce the size of workbooks that have several Pivot Tables based on the same source data is to share the Pivot Cache. In fact, as mentioned in Excel 2016 Pivot Table Data Crunching: Each time you create a new pivot table in Excel 2016, Excel automatically shares the pivot cache. Pivot Cache sharing has several benefits. Most notably, as I mention above, it reduces memory requirements and file size vs. In other words, if you work with several Pivot Tables that share a Pivot Cache and you group certain Fields in any of those Pivot Tables, those grouping settings affect and apply to that same Field in all the other Pivot Tables. Therefore, if you have several Pivot Tables and want to apply different Field-grouping criteria, you want to avoid sharing the Pivot Cache. If your Pivot Tables are based on different source data, you don't have to worry about the Pivot Cache sharing issue I describe above. In such cases, the Pivot Tables can't share the Pivot Cache. Pivot Tables based on different sources of data use different Pivot Caches. If your Pivot Tables are based on the same source data, you may have to ensure that if required they're not sharing the Pivot Cache. If needed, you can force Excel to create a new Pivot Cache for the same source data in several different ways. The most appropriate method of forcing Excel to create separate Pivot Caches generally varies depending on the situation you're in. There are other ways in addition to the 3 I explain here to achieve this same objective. When selecting the Pivot Table you want to copy, make sure that it's based on the source data you want the new Pivot Table to use. Step 3: Modify The Grouping Settings Of The Pivot Table In The Helper Workbook I explain several ways of specifying Pivot Table grouping settings throughout this Tutorial. You can, basically, specify the grouping settings of your new Pivot Table here without influencing the Pivot Table that you originally copied. This is the key step within the process. There are, however, other alternatives to force Excel to create a new Pivot Cache. Step 4: Copy The Pivot Table From The Helper Workbook Go to the helper workbook and copy the Pivot Table that you pasted in step 2 above. I explain the process to copy a Pivot Table in step 1 above. Step 5: Paste The Pivot Table In The Original Source Workbook To finish the process, go back to the original workbook and paste the Pivot Table. The result of the process is that the newly-pasted Pivot Table has its own separate Pivot Cache. Now, let's look at the 8 easy steps I describe above: Step 1: Select A Cell Within The Source Data You can select your source data in Step 2 of 3 within the Pivot Table Wizard step 5 below. However, if you select a cell within the source data prior to launching the Pivot Table Wizard, Excel is usually able to select the entire range of your source data by default. Step 3: Excel Displays The Pivot Table Wizard The first dialog box of the Pivot Table Wizard PivotTable and PivotChart Wizard — Step 1 of 3 looks as follows: Step 4: In Step 1 of 3 Of The Pivot Table Wizard, Click Next The Pivot Table Wizard begins by asking you about the i the type of source data you work with, and ii the report you want to create. These settings are usually appropriate. Therefore, you can generally click on Next button on the lower right side of the dialog box. Step 5: In Step 2 of 3 of the Pivot Table Wizard, Confirm The Range Of Your Source Data And Click Next In the second screen of the Pivot Table 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. If there are any mistakes, you can correct them. 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 You Can Use Less Memory If The New Report Is Based On The Previously-Existing Pivot Table Report If the workbook you're working on has a previously-existing Pivot Table report based on the same source data, Excel displays a dialog box. This dialog explains that your new Pivot Table report uses less memory if you base it on your existing report, which was created from the same source data. This means, as I explain above, that you can reduce the memory required by sharing the Pivot Cache between the Pivot Tables. As explained in the dialog box and above , this results in lower memory requirements and smaller file size. Since you don't want to share the Pivot Cache between the Pivot Tables, select No. Step 8: In Step 3 of 3 Of The Pivot Table Wizard, Specify Where You Want To Put The Pivot Table Report And Click Finish In the last step of the Pivot Table Wizard, Excel allows you to choose the location of the new Pivot Table report. Once your choice is selected, click Finish on the lower right corner of the dialog box. After you complete the 8 steps above, Excel creates a new Pivot Table report. This Pivot Table has its own unshared Pivot Cache. How To Force Excel To Create A New Pivot Cache By Using Different Range Names The examples for the 2 methods to create separate Pivot Caches I explain above show the process for creating new Pivot Tables. In this section, the example and description explain how to force Excel to create separate Pivot Caches for Pivot Tables that already exist. You can, however, use certain variations of these 3 processes to force Excel to separate Pivot Caches when both i creating a new Pivot Table, or ii modifying an existing Pivot Table. You want to have 1 name per required Pivot Cache. Change the defined name every time. Change the defined name you use as source every time. Let's go through each of these 10 steps in more detail. In the example that I show below, I work with 2 Pivot Tables. As I mention above, you may apply this process to more Pivot Tables. Step 2: Define A Name For The Source Data Range You can define a name for the source data range in different ways. 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. 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. You then use those different names as the sources for the Pivot Tables. Therefore, you must have 1 defined name for each Pivot Cache. To achieve this, repeat step 2 above as many times as required. Make sure that you're not repeating names in the process of assigning names. Step 4: Select 1 Of The Pivot Tables Based On The Source Data You Selected In Step 1 Once you've created the defined names you need, you can assign them as a source for existing Pivot Tables. To begin this process, select a cell within 1 of the Pivot Tables that needs a separate Pivot Cache. Step 7: Click OK Once you've entered the appropriate defined name as source, click the OK button on the lower right section of the dialog box. Step 8: Repeat Steps 4 To 7 For Each Pivot Table That Needs A Separate Pivot Cache You force Excel to create separate Pivot Caches by using the different defined names to specify the source of each Pivot Table. Therefore, you must repeat steps 4 to 7 above for all the affected Pivot Tables. Once you go through the steps I explain above, each of the Pivot Tables whose data source you modify will have a separate Pivot Cache. Pivot Table Cannot Group That Selection Error Or Grouping Buttons Greyed-Out: Causes And Solutions From time-to-time, Excel may display a message box stating the following: Cannot group that selection. These problems generally caused by certain inconsistencies in the underlying data. Both inconsistencies are, therefore, caused by inappropriate source data. Let's look at these 2 scenarios. I also discuss some other options you can explore if blanks or data type inconsistencies aren't the cause of the cannot group that selection error or the greyed-out grouping buttons. This implies that, when possible, you should have as few blanks as possible. This is because Pivot Tables have some issues handling blank cells. In my experience, Excel 2016 handles blank cells better than previous versions. But as explained by Excel authorities Bill Jelen Mr. Excel and Mike Alexander in Excel 2016 Pivot Table Data Crunching: Blank cells are generally errors waiting to happen. The solution to a cannot group that selection error or greyed-out group buttons caused by blanks is not surprisingly to fill in the blank cells. There are cases where determining the data you use to fill the blanks is easy. In those situations, you can simply enter the appropriate value in the blank cell. There are other situations where you may have a harder time figuring out which value to use. For these cases, consider using a dummy value. The idea is of a dummy value is to have a missing value code that has some logical meaning. If necessary, you can usually filter the dummy values out. One of the main ways to solve this issue is ensuring that all the data within a source column is of the same type. Excel may be able to handle data type inconsistencies in several situations. If you have text data within a value Field, the solutions is simple: replace the text data with numerical data. 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 Pivot Table Tutorial. I may write about this topic in the future. Cannot Group That Selection Or Greyed Out Group Buttons Because Of Other Issues You may encounter some rare cases, where the 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. In such situations, the solutions I explain in the previous sections may not work. Excel may display the Cannot group that selection message box or grey-out the grouping buttons because of other reasons. This can theoretically happen when you're working with manual grouping of Items. If you choose a single Item vs. The solution is to make sure that you select several Items to group. Excel generally disables the Group command when you select cells within the Filters or Values Areas. This may occur when Fields are organized in levels for example City and Region and you try to manually group Items from different levels. Generally, Excel is only able to group Items at the same level. Depending on the applicable grouping settings and organization of the Pivot Table report, you may be trying to group in a way that Excel can't handle. In these cases, check your Field List and the Pivot Table Areas. If there are unnecessary grouped Fields, ungroup them before trying to group again. In some cases, the grouping causing the issue may be at the source data. Therefore, you may also want to check the source table. In these situations, you can generally apply manual grouping by following the easy process I describe in a previous section. How To Turn Off Pivot Table Time Grouping In Excel 2016 Microsoft introduced the time grouping feature in Excel 2016. You may, however, prefer disabling this feature. In this section, I explain how you can do it. Let's look at each of these options: How To Turn Off Time Grouping In A Stand-Alone Version Of Excel 2016 As I'm writing this post, 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. The process of adding this registry key is explained by Microsoft. According to the comments of Dan Battagin from the Microsoft Excel Team at , Microsoft may add the possibility to turn off time grouping from the Excel Options dialog which I explain below to the stand-alone version of Excel. I suggest you check the status to confirm whether the feature has been added. Making changes to the Windows Registry is a sensitive matter. Certain incorrect editions may damage your operating system. 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. How To Turn Off Time Grouping In An Office 365 Version Of Excel This section applies if you work on an Excel version where the option to disable time grouping from the Excel Options dialog exists. The Excel Options dialog box looks roughly as follows: Step 2: Go To The Advanced Tab Of The Excel Options Dialog Once you're within the Excel Options dialog box, select the Advanced tab on the left side of the dialog. One of the last sections is Data. Click the box on the left side of this option to add a checkmark. Step 4: Click The OK Button To confirm your settings, click the OK button on the lower right side of the Excel Options dialog. Once you complete the quick 4-step process above, Excel disables time grouping. The image below shows an example of how this looks like. Notice that, after I add the Date Field to the Rows Area of the Pivot Table, Excel doesn't time group the newly-added Field. 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 Pivot Table. OLAP Sources And Pivot Table Grouping If you work with OLAP sources, there are some grouping limitations to be aware of. 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. For further information, I suggest you read Microsoft MVP Chris Webb's posts , and. Conclusion After reading this Pivot Table Tutorial, you have the knowledge to easily group or ungroup data in a Pivot Table. You can do this in different contexts and with different types of data. You've also read about the time grouping feature that Microsoft added in Excel 2016. If you don't like this feature, I explain how you can disable it. We've also gone through several common problems that arise when trying to group Pivot Table Fields. The information and examples provided in that section allow you to handle and troubleshoot the most common grouping problems. Finally, I include a basic introduction to the limitations when grouping Items in Pivot Tables based on OLAP sources. Remember that you can get immediate free access to the Excel workbook example that accompanies this Pivot Table Tutorial by subscribing to the Power Spreadsheets Newsletter. Books Referenced In This Excel Pivot Table Tutorial Click on any of the links below to purchase the book at Amazon now. I love reading and sharing success stories from amazing members of the Power Spreadsheets community, like you. If this or any other Tutorial has helped you, please share your success story below. This only takes few seconds and, by doing it, you help the future development of Power Spreadsheets. Any improvements I make to this or the other free Tutorials in Power Spreadsheets based on your feedback will benefit you too. I'd like to highlight your amazing work if there's a suitable opportunity. Therefore, I may publish your success story so other members of the Power Spreadsheets community can learn and be inspired by your success. If there's any data about you or your success story you don't want me to publish, please expressly specify this below.
My scenario uses the pivot as a template and the data is updated regularly. Instead, it uses the relationships join created in Power Pivot to perform the grouping on the fly. After you complete this quick 2-step process, Excel groups the selected Items. Another issue I had once had is with imported data, you see a date, it is formated as a date but it is NOT a date. I don't believe there is any right or wrong answer here. I'm seeing the same issue.
.png?full=1 "Get serial number from cmd")
