You try to add a grouping to selected rows or columns in Excel but it is not working? Maybe even the buttons are greyed out on the data ribbon? There are a couple of different reasons for that. Let’s take a look at it.
The problem
You want to insert groupings but the buttons on the Data ribbons look something like this?
There are several potential reasons. Let’s get started
Solution 1: Only select one worksheet to group rows or columns
Have you selected several worksheets at the same time? In such case, you can’t add groupings. You can only do it sheet by sheet.
So, simply select one worksheet only. If that is not the reason, proceed with solution number 2 below.
Solution 2: You are editing a cell – just leave the cell to insert grouping
Are you inside an Excel cell, for example for typing a formula? If yes, you have to leave the cell first in order to add groupings. It’s also possible that you are editing a cell in a different Excel file (although in newer Excel versions that should not be a problem).
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Solution 3: Unprotect your worksheet or workbook to add grouping
The worksheet is protected? In that case you’d usually receive an error message. So, you have to unprotect a worksheet. In order to do that go to the Review ribbon and click on “Unprotect Sheet”.
Similar for downloaded Excel files: Excel opens these workbooks in the “Protected View” mode. You can see this with the yellow bar on top of your worksheet:
If you click on “Enable Editing”, you should be able to add a grouping.
Just a quick side note: Do you know that you can change the direction of groupings in Excel? Here is how to do that!
Solution 4: Show outline symbols within the Excel options
There also might be another reason. Within the Excel options, you can choose if you want to display groups. So maybe, the grouping works, but is just not shown?
- Click on “File” and the “Options”.
- Navigate to “Advanced” on the right-hand side and scroll down.
- Make sure the box “Show outline symbols if an outline is applied”.
- Confirm by clicking on “OK”.
Image by Michal Jarmoluk from Pixabay
I am able to group but when i click “group selection” and try to use the “number of days option”, it won’t let me and is grayed out. do ye know why this is?
Missed one and I just figured out.
If a large pivot table is up into row 1, I could not group. I added a row, then it worked.
Solution 5: Outline options button is greyed out if the active cell is in a table. Just click outside of the table area. Took me some time to figure this out…
Thank you, solution 4 made the trick for me!
My worksheet is not protected and the “show outline symbols if an outline is applied” option is checked…the Group is still greyed out. Any other suggestions?