ASM Arman
The date grouping feature in Excel is a very powerful tool that will save you a lot of hassle. It helps you analyze the data in a large worksheet while grouping the data by dates, weeks, months, quarters, and years. The date grouping feature, however, doesn’t always work. Sometimes, you will see that the Group Field button on the Analyze/Options tab of the PivotTable Tools ribbon is disabled or grayed out. You might get an error, “Cannot group that selection”. In this tutorial, we will take a look at why we cannot group dates in pivot table and how we can solve the problem.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
4 Easy Methods to Solve Group Dates Problem in Pivot Table
Let’s assume we have an Excel large worksheet that contains the information about various fruits and vegetables that a country has imported to three different countries in Europe. The Excel file has the Product Name, Exported Amount, Importer Country, Order Date, and Ship Date. We will use the Order Date and Ship Date column of this Excel to solve the group dates problem in the pivot table. We will use Filter, Go To Special, and VBA Macro to solve the problem as to why we cannot group dates in pivot table. The image below shows we are getting the error “Cannot group that selection” while trying to group dates for this Excel worksheet.
Method 1: Replace Missing or Distorted Date Values to Group Dates in Pivot Table
Step 1:
- The only way to avoid this error while trying to use the Group Field feature for dates is that all cells in the date columns in the source data must contain dates or the cells could be blank. If there are any cells in the date field of the source data that contain text or errors, then the group feature will NOT work. For example, we have some error date values like the image below in our source data.
- In most cases, due to these faulty date values, we can not group dates in pivot tables in Excel.
- To solve this problem, we have to insert dates on those cells that do not have a date value. If the cells have error date values like the image above, we have to correct the errors.
- For this example, we have corrected the 3 error dates that we are seeing above.
Step 2:
- Now, to group dates, we will create a pivot table first. We will drag the cells like the image below to create the pivot table.
- Our pivot table will be like the image below.
Step 3:
- Now, we will select any cell containing a year. We will then will right-click on the cell. A window will appear. We will click on Group from that window.
- Another window titled Grouping will now appear. We can select how we want to group our data. We have selected Quarters and Years to group our data by.
- Then, we will click on OK.
- Now, we will see that our information has been grouped by Quarters and Years.
Read more: How to Use Excel Pivot Table to Group Dates by Month and Year
Method 2: Filter to Find Out Distorted Date Values to Group Dates in Pivot Tables
We will now look into some ways to find out the distorted or error date values. There are 3 easy ways to find out such dates. The first one is using the filter to find the date values with errors. Follow these steps to use the filter to find the dates with errors.
Step 1:
- Select all the cells in the data range. Then, click on the Filter option under the Data.
- Now, click on the small downward arrow on the right side of the Ship Date You will see the filter drop-down menu has grouped all the date values in this column by Year, and Date. All text and error date values are listed at the bottom of the list.
Step 2:
- We can see that this column contains some dates that were entered in the incorrect format. Excel did not consider these as dates when they were entered in the cell and so it identified them as text.
- To filter the Text and ERROR values, uncheck all the date items Or remove the tick beside the Select All option. Then select the text and error items.
- Click OK.
- The column will now be filtered to only display the text and error values.
Step 3:
- The next step is to determine why the values are not in the correct date format and fix them. We have corrected all the filtered dates with errors. See the image below.
- Now, we will click on the Filter option again.
- The data range will be returned to the initial form with the faulty dates being corrected this time.
- Now, we can group the dates as there is no distorted date in our data.
Read more: How to Group Pivot Table by Month in Excel
Similar Readings
- How to Group by Year in Excel Pivot Table (3 Easy Methods)
- How to Group Dates by Filter in Excel (3 Easy Methods)
Method 3: Find Out Error Date Values Using the GoTo Special Menu to Group Dates in Pivot Table
The GoTo Special menu in Excel is a wonderful feature that allows us to select cells containing different types of data such as constants, blanks, formulas, comments, etc. We can also use it to select the cells that contain data of specific types.
Steps:
- First, we will select the entire column of the date field. We can press the CTRL+SPACE together.
- Go to Find & Select under the Home tab. Select Go To Special from the drop-down menu.
- We will Select the Constants radio button.
- Then Uncheck the Numbers checkbox.
- Click OK.
- All cells that contain text or error values will be selected. We can then apply a different fill color to highlight these cells so that we can fix them in the next step. For this example, we have selected Red as the fill color to flag the dates with error values.
Read more: Excel Pivot Table Group by Week
Method 4: Find Out Error Date Values With VBA to Group Date Values in Excel
We can also use VBA to determine the data type of a cell. There are several ways to do this. The easy way is to use the CellType function that will take a cell value as an argument and return the data type of that cell.
- Click ALT+F11 to open the Visual Basic You can also open it from the Developer tab.
- Click on the Insert button and select Module.
- Write down the following code in the window that appears.
Public Function CellType(c)
Application.Volatile
Select Case True
Case IsEmpty(c): CellType = "Blank"
Case Application.IsText(c): CellType = "Text"
Case Application.IsLogical(c): CellType = "Logical"
Case Application.IsErr(c): CellType = "Error"
Case IsDate(c): CellType = "Date"
Case InStr(1, c.Text, ":") <> 0: CellType = "Time"
Case InStr(1, c.Text, "%") <> 0: CellType = "Percentage"
Case IsNumeric(c): CellType = "Value"
End Select
End Function- Click on the File tab and save the Excel file.
- Now, we will go back to our source worksheet and write the following function like below in cell G5:
=CellType(F5)
- Upon pressing ENTER, we will get the data type of cell F5. The function will return Date as data type.
- We will drag the fill handle downward to apply the function to the rest of the cells in the Ship Date We will find out that it is showing data type as Text for the dates with error values.
- We will highlight the cell with data type as Text as we have done before. In the next step, we will correct those cells.
- After we have corrected those cells, we can group dates as we have done in Method 1.
Things to Remember
- You can also use the VBA CellType function to determine the type of other data in a cell.
- Select New Worksheet when you are creating a pivot table. If you select Existing Worksheet, a pivot table will be created in your existing sheet that contains the data. There is a substantial risk of data being distorted if we create the pivot table in our existing worksheet.
Conclusion
In this article, we have learned how to solve the problem when we cannot group dates in a pivot table in Excel. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
