How-To

Excel Cannot Group That Selection: 5 Fixes

By Adam Davidson

Published May 2, 2024

If you’re having trouble grouping dats in an Excel pivot table, here’s what to try if you’re getting a “cannot group that selection” error.

Pivot tables. Just the name is enough to send shivers down the spine of some Excel users. These innocent-looking tables are often avoided by many as they seem far too complex to understand. However, once you get to know them, Excel pivot tables can be very simple to create and incredibly useful. Like all Excel features, however, sometimes things may not go according to plan. You may find that when you try to group the data for your pivot tables, you get an error telling you it isn’t possible. Here’s what to try if you’re seeing an Excel “cannot group that selection” error.

1. Ensure That You’re Not Using an OLAP Pivot Table

As well as creating standard pivot tables, Excel allows you to create a more powerful type of pivot table known as an Online Analytical Processing (OLAP) Power Pivot table. Instead of creating a pivot table from a single table of data, an OLAP pivot table uses a data model that can include data from multiple sources. However, using a pivot table of this kind means that you may see an Excel “cannot group that selection” error.

You can try creating a standard pivot table from your data instead, which may solve your issue.

1. Select the data that you want to use for your pivot table.
2. Click Insert.
   
3. In the ribbon, click the Pivot Table button.
   
4. Make sure that Add This Data To The Data Model is not checked.
   
5. Click OK.
6. Try grouping a selection in your pivot table.

2. Search For Invalid Dates

If you’re certain that you’re using a standard pivot table and not an OLAP-based pivot table, but you’re still unable to group a selection, it may be that some of the dates in your data are not valid.

For example, you may have a date down as February 29, 2017. Since 2017 was not a leap year, this date does not exist, so it will be invalid even if it is entered in a valid date format. If you try to group dates that include an invalid date, you’ll get the Excel cannot group that selection error. Finding the invalid date in a large table of data can be challenging, but there is a way to make it easier.

1. If there isn’t one already, insert a blank column to the right of your dates column in your data by clicking the row header of the column where you want the blank column to appear.
   
2. Right-click in any of the highlighted cells and select Insert.
   
3. In the cell next to your first date cell, type = and then click the date cell to the left, then type +1.
   
4. Press enter, and you should see a valid date as the result.
5. Select this cell, click and hold the grab handle in the bottom right corner, and drag down to apply the formula to all of your other dates.
   
6. All valid dates should generate a new valid date, but invalid dates should generate a #VALUE error.
   
7. Fix any invalid dates that are found.
8. Return to your pivot table, right-click any cell, and select Refresh.
   
9. You should now be able to group your dates without any errors.

3. Search For Text Data

If your data contains some text entries among numerical data or dates, this will also cause the Excel cannot group that selection error when you try to group this data. You can use the Find & Select tool to locate any text hidden in your data.

1. Select the column of data that you want to search by clicking the column header.
   
2. In the Home ribbon, click the Find & Select button.
   
3. Select Go To Special.
   
4. Click the Formulas radio button and check Text.
   
5. Select the Constants radio button and then click OK.
   
6. Any text entries in your selected column should be highlighted.
   
7. Ensure that any text entries found are replaced with valid dates.
8. Return to your pivot table, right-click a cell in the table, and choose Refresh.
   
9. Try grouping your data again.

4. Search For Seemingly Blank Cells

If any of your cells are blank, Excel will still be able to group your data. However, some of your cells may appear blank but are not truly blank. This is often the case when you use a formula that enters "" into a cell if it meets certain criteria. While the cell looks blank, it contains an empty string, so it is not recognized as blank by your pivot table. You can use the Find & Select tool to spot any “blank” cells that are not actually blank so that you can remove them.

1. Highlight the cells you want to search by clicking the column header.
   
2. Click the Find & Select icon in the Home ribbon.
   
3. Select Go To Special.
   
4. Select the Blanks radio button and click OK.
   
5. Any genuinely blank cells will be highlighted.
6. Any cells that appear blank but are not highlighted are not actually blank. Delete the contents of these cells.
   
7. Go back to your pivot table, right-click, and select Refresh.
   
8. Try grouping your data again.

5. Search For Errors

If your data contains any cells that have errors, then this will also stop you from being able to group data in your pivot table. You can use the Find & Select tool to locate errors in your data and fix them. You should then be able to group data once again.

1. Select the column that contains the data you want to search.
   
2. In the Home ribbon, click Find & Select.
   
3. Choose Go To Special.
   
4. Click the Formulas radio button and check Errors.
   
5. Click OK and any cells with errors will be highlighted.
   
6. Fix any errors and return to your pivot table.
7. Right-click and select Refresh.
   
8. Try grouping your data again.

How to Group Dates in Excel

Grouping data in your pivot tables is an effective way to get the most out of your pivot tables by combining data so that you can analyze trends. For example, you can group your sales data over a number of years by month. This will combine your sales from January 2022, January 2023, January 2024, etc. into one row. You can then easily see how your sales change over a year. Alternatively, you can summarize your data by grouping by year and month. This will group the data for all the months of 2022, then all the months of 2023, then all the months of 2024, etc. This allows you to see the sales trends by month for each individual year.

1. Right-click any date in the Dates column of your pivot table.
2. Select Group.
   
3. Select the period to group by. You can select more than one option.
4. For example, select Months to show all sales across all years split into the month of sale, regardless of year.
   
5. Select Months and Years to group each year’s sales by month.
   
6. If you see the cannot group that selection error, try one of the fixes above.

Fixing an Excel ‘Cannot Group That Selection’ Error

Grouping data in pivot tables in Excel is a very useful tool, so if you’re getting an Excel “cannot group that selection” error, it can be frustrating. Pivot tables are one of those features of Excel that are often seen as mysterious and impossible to use, but once you know what you’re doing, they can be very simple to create and can be incredibly useful. It’s definitely worth getting to know how to take advantage of them; the time spent learning could save you far more time in the long run. Hopefully, one of the fixes above has solved your issues. If you find any other successful fixes, please let us know in the comments.