SOLVED

Group Auto Outline greyed out

Copper Contributor

I have asked this elsewhere but noone has been able to offer a solution as yet.  Another Microsoft support site recommended I post the details here in cas anyone can help (hence why this may seem to be a duplicate from that site).

 

I am trying to change the Collapse/Expand column showing the "-" or "+" symbol from the right (default) side to the left. However, the Auto Outline option under Group on the ribbon's Data tab is greyed out -

LudwigWN_3-1706385621230.png

However, it generates an error message. When the outline columns are selected, it has an OK / Cancel dialog saying "Modify existing outline?" -

 

LudwigWN_4-1706385646844.png

If I select Cancel .. it cancels as one would expect. If I select OK it says "Cannot create an outline." -

 

LudwigWN_5-1706385669815.png

I have a column Group on another worksheet in the same workbook and it is not greyed out and in the past I was able to alter this worksheet's grouping to the left (& still shows on the left). But now it also just reports this "Cannot create an outline." message.


This error also occurs if I try to create a new Group of columns and select "Auto Outline" on any worksheet, even a newly inserted one. I can Group and Ungroup columns and rows, but cannot alter the Auto Outline settings. If in a new workbook I immediately go to Data, Group then select Auto Outline, which is not greyed out, I get the "Cannot create an outline." message.

 

I can easily recreate this problem by opening a new, blank, workbook. Enter some data, then select columns (or rows) and go to Data ribbon tab. Then try to select Auto Outline - I get the above two errors every time.

 

I have Excel [365] installed on my desktop *and* my tablet. i.e. both these installations exhibit the same problem in the existing *and* a newly created (& not yet saved) workbook. Both installations have the latest updates applied - currently showing as Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20118) 32-bit.

How can I modify the Auto Outline on any worksheet in a workbook? Any help most welcome. Thanks.

5 Replies

@LudwigWN Without seeing more of your data, including how it's setup and structured, as well as the formulas used (or lack thereof), I can't say for sure what the specific problem is in your case. Having said that, here's what I know about using the Auto Outline feature in Excel...

  • The data range must contain at least one formula row or column (ie: SUBTOTAL, AGGREGATE or SUM)
  • When using a structured Excel table, the active cell must be a single cell outside of the table range

 

Example 1: no aggregate formulas present (Cannot create an outline) [FAILED]

 

auto_outline1.png

 

Example 2: with formulas in the subtotal rows and columns [SUCCESS]

 

auto_outline2.png

 

Example 3: with structured table selected (Auto Outline greyed out) [FAILED]

 

auto_outline3.png

 

Example 4: with a single cell selected outside of the structured table [SUCCESS]

 

auto_outline4.png

 

There may be other scenarios where the Auto Outline feature is greyed out or fails altogether. I'm afraid this is the extent of my knowledge on the subject, though. If none of the above tips apply to your specific situation, please provide additional details that may help in the troubleshooting process. Kind regards.

best response confirmed by LudwigWN (Copper Contributor)
Solution

Thanks for your post @djclements.  After reading it thru & some more thinking, I realised my original post was misleading.

My real problem is best explained in my one sentence buried inside my initial post -

I am trying to change the Collapse/Expand column showing the "-" or "+" symbol from the right (default) side to the left.

I then followed that with lots more words about Auto Outline not allowing me to do that, something it is not designed to do.  Even my subject is misleading :facepalm:

 

Solution:

After some more digging, I have now discovered how to do what I wanted - forget Auto Outline, instead click on the bottom right portion of the "Group" section of the ribbon to get into Settings and there are the options I wanted.  See screenshot below -

LudwigWN_0-1706464196108.png

 

The only thing here is .. don't have a cell within a table selected because then access to Settings will also be greyed out (don't understand why, but that's what I see, & I can avoid it by selecting a suitable cell).

 

Thanks @djclements for getting me to the right place to do what I wanted!

 

 

@LudwigWN Glad to hear you figured it out! Yes, I missed that sentence somehow (must've been skim reading and only saw the references to Auto Outline... sorry). You solved it, though. Congrats! :)

Yes, teaches me to be more careful how I word something in future.

I have a similar issue where I have used outline on two sections of the data already and when I captured more it won't let me outline that data the same way. It worked perfectly the first two times giving me totals of sections I wanted but no any cell within the table just greys out the commands.  @LudwigWN 

1 best response

Accepted Solutions
best response confirmed by LudwigWN (Copper Contributor)
Solution

Thanks for your post @djclements.  After reading it thru & some more thinking, I realised my original post was misleading.

My real problem is best explained in my one sentence buried inside my initial post -

I am trying to change the Collapse/Expand column showing the "-" or "+" symbol from the right (default) side to the left.

I then followed that with lots more words about Auto Outline not allowing me to do that, something it is not designed to do.  Even my subject is misleading :facepalm:

 

Solution:

After some more digging, I have now discovered how to do what I wanted - forget Auto Outline, instead click on the bottom right portion of the "Group" section of the ribbon to get into Settings and there are the options I wanted.  See screenshot below -

LudwigWN_0-1706464196108.png

 

The only thing here is .. don't have a cell within a table selected because then access to Settings will also be greyed out (don't understand why, but that's what I see, & I can avoid it by selecting a suitable cell).

 

Thanks @djclements for getting me to the right place to do what I wanted!

 

 

View solution in original post