Grouping & task filters

A kitchen refit with sixty rows is hard to read on a Monday morning. Gantt Excel gives you two separate tools for that, and they live in two separate sections of the ribbon. Filters is about progress — hide everything already finished so only live work is on screen. Grouping is about structure — collapse “Demolition” down to a single summary row and stop looking at its eight children. They are independent features, they are switched on in different places, and switching one on switches the other off.

01 Filters and grouping are not the same thing

It is worth being clear about the difference before you use either, because they hide rows for opposite reasons.

Both settings are stored with the chart, not with Excel, so a chart you filtered last week is still filtered when you reopen the workbook. If a chart looks like it has lost tasks, these two toggles are the first place to look.

02 The three task filters

Go to Gantt Excel → Filters. There are three tickboxes, and on a new chart all three are ticked, so nothing is hidden:

• Show Completed — tasks that are 100% complete.
• Show In Progress — tasks on anything above 0% and below 100%.
• Show Not Started — tasks still on 0%.

Untick one and every task in that state is hidden from the sheet. Tick it again and they come straight back — nothing is deleted, and no dates or links are touched. The filter only changes what you can see.

The three states are read from % Complete, not from the Status dropdown you set on the Task Form. That is not an oversight: the Status column is itself calculated from % Complete on every refresh, so the two always agree. Set a task to 50% and its status reads In Progress; that same task is the one that disappears when you untick Show In Progress.

Summary tasks follow their children

A parent row is not judged on its own roll-up percentage. It stays visible as long as at least one of its children is still visible, and vanishes only when the filter has hidden every child underneath it. That keeps the outline honest — you never get an orphaned summary row heading an empty section.

Worked example

It is Mon 13 July and your kitchen refit has 24 tasks: 9 finished, 4 under way and 11 not begun. Untick Show Completed and the 9 finished tasks disappear, leaving 15 rows. The “Demolition” summary row goes with them — its four children all wrapped up by Fri 10 July, so the whole section is complete. “Fitting” stays: it runs to Fri 24 July and two of its five children are still in progress. What is left on screen is the 15 rows of work that still needs your attention this week.

03 Why everything went grey

This is the single most common question about filters, so it gets its own section. The moment any one of the three tickboxes is off, a whole block of the Gantt Excel tab goes unavailable. The message Gantt Excel shows names these:

• Add Task, Add Milestone, Duplicate, Delete
• Make Parent, Make Child, Move Up, Move Down
• Row Height, Project Dashboard and Enable Grouping
• Expand Groups and Collapse Groups

That is the list the message reads out, not a promise that nothing else is affected. The rule underneath it is the one to remember, and it is simpler: anything that inserts, moves or removes a row is unavailable while the list is filtered.

Nothing is broken. Every one of those commands works on rows, and a filtered sheet has hidden rows sitting invisibly between the ones you can see. Insert a task “below the selection” on a filtered list and it lands somewhere you cannot see; move a task down and it jumps past hidden neighbours. Rather than let you reorder a list you are only half looking at, Gantt Excel switches those buttons off until the view is honest again.

Hover one of the greyed-out buttons and its tooltip says so: Not available while the task list is filtered. Turn Show Completed, Show In Progress and Show Not Started back on to use this. Only the buttons carry that tooltip — the tickboxes, Enable Grouping among them, show nothing when you hover them, so there is no hint on the tickbox itself. What you do get is a message box, the first time a filter restricts the menu in an Excel session, naming exactly which options are off and what they have disabled.

The fix is always the same: Gantt Excel → Filters, tick all three boxes, and the ribbon comes back to life.

04 Excel’s own column filter

Separately from the three tickboxes, you can put Excel’s standard AutoFilter arrows on the task grid’s header row and filter by any column — resource, priority, task name, anything. Two ways to switch it on:

Switching the column filter on resets the other two settings: all three status tickboxes are set back on and Enable Grouping is set off, so the ribbon reports only the column filter. It greys out the same task-editing buttons as a status filter, and the three status tickboxes with them. The greyed buttons carry a tooltip — Not available while a column filter is active. Switch the Filter button off to use this. — but the tickboxes, as ever, show nothing on hover. The “Filter button” the tooltip means is the grey arrow on the Task header, or Ctrl Shift L; there is no Filter button on the Gantt Excel tab itself.

Because those settings are reset, the sheet is put back to match them: any rows a status filter had hidden are unhidden, outline handles left by grouping are cleared away, and the add-task row and dependency arrows come back. You start from the full task list, with the column filter as the only thing hiding anything.

One thing a column filter does not change is the right-click menu: the swap to the greyed-out notice in section 03 is driven by the three status tickboxes only. With a column filter on, all ten Gantt commands stay on the menu and each one refuses on its own if you pick it — a message rather than a silent no-op.

05 Grouping

Grouping turns your indent levels into Excel’s outline bars — the little + and − handles down the left-hand margin — so a summary task can be folded up and its children put out of sight.

Go to Gantt Excel → Grouping and tick Enable Grouping. Gantt Excel reads the indent level of every task and builds a matching outline level: a top-level task becomes level 1, its children level 2, their children level 3, and so on. Untick it and the outline is cleared away again.

Grouping depends entirely on your outline, so it is worth getting the indents right first — that is covered in Summary tasks, indenting & the WBS. Excel supports a maximum of 8 outline levels; a task indented deeper than that keeps its indent on the sheet, but it is left sitting at outline level 1 — so it stays put when you fold its parent away instead of folding with it.

Expanding and collapsing

Two buttons sit next to the tickbox and act on the whole sheet at once:

• Expand Groups — opens every group, so all tasks are visible. Its tooltip reads Expand all task groups.
• Collapse Groups — folds every group up to its summary rows. Its tooltip reads Collapse all task groups.

Expand Groups does one thing beyond its name: it also clears any criteria you have applied with Excel’s column filter, so “everything visible” really does mean everything. The filter arrows stay on the header row — only the criteria go.

Both stay greyed out until Enable Grouping is ticked — there is nothing for them to act on before that. You can also click an individual + or − handle in the margin to fold just one section, which is usually what you want day to day.

06 Collapsed groups and the critical path

One behaviour surprises people, so it is worth stating on its own: while any group is collapsed, the critical-path highlight is switched off. The red bar outlines and red dependency lines disappear until you expand the groups again — even though Show Critical Path is still ticked in Settings.

The reason is drawing, not scheduling. The critical path is painted as a red border on task bars and a red recolour on the connecting arrows, and both of those are attached to individual task rows. Collapse a group and those rows are hidden, so a highlight drawn against them would either land on the wrong row or stretch across a fold in the sheet. Rather than paint a misleading picture, Gantt Excel leaves it off until every row is visible again.

The same applies to the dependency arrows themselves: a link whose predecessor or successor is inside a collapsed group is not drawn, because there is no visible bar for it to point at. Nothing has changed in your schedule — the critical path is still calculated, the links are still stored, and expanding the groups brings the whole picture back. There is more on how the highlight is worked out in The Critical Path & float.

07 On a Mac

The ribbon tickboxes and buttons behave identically on Mac — Filters, Grouping, Expand Groups and Collapse Groups all work the same way. The one real difference is the right-click menu: Gantt Excel does not install its Gantt items on the sheet’s context menu on Mac at all, so the greyed-out Gantt options unavailable – the task list is filtered line described in section 03 never appears there. Use the ribbon buttons instead.

If you use Excel’s column filter on a Mac, select the task data including its header row before switching filtering on.

08 Common questions

Related: Summary tasks, indenting & the WBS · The Critical Path & float · Views, timeline & zoom · Adding, editing & deleting tasks