Documentation / Views & output / Grouping & task filters

Grouping & task filters

Two ways to make a long task list readable: Filters hide tasks by their progress or by a value in one column, and Grouping folds sub-tasks away under their summary rows. This page covers both, and why parts of the Gantt Excel tab go grey while one of them is on.

A kitchen refit with sixty rows is hard to read on a Monday morning. Gantt Excel gives you two kinds of tool for that, in two sections of the ribbon. Filters decides which tasks are on screen: hide everything already finished, or show only the work given to one person. Grouping is about structure: collapse “Demolition” to a single summary row and stop looking at its eight children. They are switched on in different places, and switching one on switches the other off.

01 Filters and grouping are not the same thing

They hide rows for different reasons, so it helps to know which one you are using.

FiltersGrouping
Hides byA task’s progress, or its value in one columnWhere a task sits in the outline
Ribbon sectionFiltersGrouping
ControlsThree tickboxes, plus Filter Tasks and Clear FilterOne tickbox plus two buttons
Kept when you reopen the fileThe three tickboxes: yes. Filter Tasks: noYes

The three tickboxes and Enable Grouping are stored with the chart, so a chart you filtered by progress last week is still filtered when you reopen the workbook. If a chart looks like it has lost tasks, check those first. Filter Tasks is the exception: it is not saved, and a chart saved with it on opens with every task showing.

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. 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 column. Status is itself calculated from % Complete on every refresh, so the two always agree: set a task to 50% and its status reads In Progress, and it is the task that disappears when you untick Show In Progress.

100%  =  Completed  ·  above 0% and below 100%  =  In Progress  ·  0%  =  Not Started

Summary tasks follow the tasks beneath them

A summary row is not judged on its own rolled-up percentage. It stays visible while at least one task somewhere beneath it is visible, and disappears only when the filter has hidden every one of them. You never get a 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, because its four children all wrapped up by Fri 10 July. “Fitting” stays: it runs to Fri 24 July and two of its five children are still in progress. The 15 rows left are the work that still needs your attention this week.

The ribbon with Show Completed unticked and Show In Progress and Show Not Started ticked; the sheet below skips row numbers where finished tasks are hidden, and the Tasks section of the ribbon is greyed out.
Untick Show Completed and finished work drops out of the list.

03 Filter one column with Filter Tasks

Filter Tasks shows only the tasks that have a particular value in one column: everything given to one person, every high-priority task, or everything due to start in March. It can keep the summary rows above each task on screen, so you can still see which phase a task belongs to.

1

Go to Gantt Excel → Filters and click Filter Tasks. On Windows, Ctrl Shift L opens the same form.

2

In Filter on this column: pick the column. You can filter on Resource, Priority, Status, % Complete, the start and end dates of the Estimated, Baseline and Actual bands, Critical and Total Float from the critical path columns, and any custom column you have shown. Columns you have hidden are not offered, and each one is listed under its own heading, so a column you have renamed keeps its name.

3

Choose what to keep. For a text column, tick the values in the list. The Search: box narrows a long list, and Select All and Deselect All act on the values showing. For % Complete or a date, type a range in From: and To:; both ends count.

4

Under Summary rows: choose Show matching tasks and their summary rows to keep the outline, or Show matching tasks only for a flat list.

5

Check the count at the bottom of the form, then click Apply.

📷 Screenshot needed The Filter Tasks form on the kitchen-refit chart: Resource chosen in “Filter on this column:”, Electrician ticked in the list, “Show matching tasks and their summary rows” selected, and the count line at the bottom reading “Tasks that match: 3. Summary rows shown with them: 2.”
Pick a column, tick the values to keep, and check the count before you apply.

Worked example

Three tasks in the kitchen refit are given to the electrician: “First-fix wiring” under “Services”, and “Second-fix sockets” and “Test and certify” under “Finishing”. Open Filter Tasks, pick Resource, tick Electrician and leave Show matching tasks and their summary rows selected. The form reads Tasks that match: 3. Summary rows shown with them: 2. Click Apply and five rows are left, each summary row with the electrician’s tasks beneath it. Choose Show matching tasks only instead and the same filter leaves just the three tasks.

A task with several people on it matches when any one of them is ticked. Blank cells are listed as (blank), which is a quick way to find every task nobody has been given yet.

With the three tickboxes

Filter Tasks and the three tickboxes work together: a task is shown only when it passes both. Untick Show Completed while filtering on the electrician and you see only the electrician’s unfinished work. The count on the form takes the tickboxes into account, so it is the number of tasks Apply will really leave.

You can still type into the tasks that are showing. A task you change stays on screen until you apply the filter again, even if it no longer matches.

Showing everything again

Click Clear Filter, on the ribbon or on the form, and every task comes back. The three tickboxes are left as they were. Clear Filter is greyed out while there is nothing to clear, and opening Filter Tasks on a chart that is already filtered starts from the filter in force, so you can adjust it rather than build it again.

04 Why everything went grey

This is the most common question about filters. While any of the three tickboxes is off, or Filter Tasks is on, a block of the Gantt Excel tab is 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 gives. The rule behind it is simpler: anything that inserts, moves or removes a row is unavailable while the list is filtered.

Each of those commands works on rows, and a filtered sheet has hidden rows 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. Gantt Excel switches those buttons off until every row is showing again. The dependency arrows are not drawn while a filter is on either, and they return when it is cleared.

Hover a greyed-out button and its tooltip says why. While a tickbox is off it reads Not available while the task list is filtered. Turn Show Completed, Show In Progress and Show Not Started back on to use this., and while Filter Tasks is on, Not available while Filter Tasks is on. Click Clear Filter to use this. The tickboxes themselves, Enable Grouping among them, show nothing when you hover them. You also get a message box the first time a filter restricts the tab in an Excel session, naming what is on and what it has disabled.

The fix: tick all three boxes in Gantt Excel → Filters, and click Clear Filter if Filter Tasks is on.

05 Sorting, and Excel’s column filter

Sorting breaks a Gantt chart. The bars are drawn against the timeline and do not move with the rows, so after a sort every row would show a different task’s bar. A task’s place in the outline is also its position in the list (a sub-task is a sub-task because of the row it sits under), so a sort would rearrange the plan itself. That is why Gantt Excel offers no sort, and why it does not use Excel’s own column filter, whose drop-downs carry Sort A to Z and Sort Z to A. To filter, use Filter Tasks and the three tickboxes.

A chart can still arrive with Excel’s filter arrows on it: a file saved by an older version of Gantt Excel, or arrows added from Excel’s own Data → Filter on a Mac. Gantt Excel switches them off. When the chart opens it does this without a message. Otherwise it happens the next time you select a cell, type into the chart or use a Gantt Excel command on it, before the change is made, and the first time in an Excel session you see: Gantt Excel has switched off Excel’s column filter on this chart. Its sort options move tasks away from their Gantt bars and their summary tasks. To filter your tasks, use Filter Tasks on the Gantt Excel tab. Nothing is lost: the rows the arrows had hidden come back.

06 Grouping

Grouping turns your indent levels into Excel’s outline bars, the + 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 get the indents right first (see Summary tasks, indenting & the WBS). Excel supports at most 8 outline levels. A task indented deeper than that keeps its indent on the sheet but sits at outline level 1, so it stays put when you fold its parent away.

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.

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

The task sheet with Enable Grouping on: Excel outline handles in the left margin, two phases folded shut to their summary rows and one left expanded.
With grouping on, each summary task gets a fold handle in the margin.

07 Collapsed groups and the critical path

One behaviour surprises people: 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 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. Gantt Excel leaves it off until every row is visible again.

The same applies to the dependency arrows: 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. Your schedule has not changed. 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.

08 On a Mac

The Filters and Grouping controls are on the ribbon on Mac too, Filter Tasks included. The one real difference is the right-click menu: Gantt Excel does not add its Gantt items to the sheet’s context menu on Mac, so the greyed-out Gantt options unavailable – the task list is filtered line described in section 04 never appears there. Use the ribbon buttons instead.

Excel’s own Data → Filter is still on the Mac menu bar, but Gantt Excel switches it off on a chart (section 05). Use Filter Tasks instead.

09 Common questions

Half my ribbon has gone grey. What did I do?
Something is hiding rows: one of the three tickboxes under Gantt Excel → Filters is off, Filter Tasks is on. Every greyed-out button is one that works on rows: Add Task, Delete, Move Up, Make Parent, Row Height, Dashboard, Enable Grouping. Tick all three boxes back on and click Clear Filter, and they come back.
My tasks have disappeared. Have I lost them?
No. Filtering and grouping only hide rows; nothing is deleted and no dates, links or costs are changed. Check the Filters tickboxes and Clear Filter first, then Enable Grouping (a collapsed group hides its children). Turning them all off restores the full list exactly as it was.
Can I see one person’s tasks and still see which phase each one is in?
Yes. Click Filter Tasks, pick Resource, tick the person and keep Show matching tasks and their summary rows selected. Their tasks stay on screen with every summary row above them, and everyone else’s work is hidden. Section 03 has a worked example.
Where has my critical path gone?
Collapse any group and the red critical-path highlight is suppressed until you expand again, because it is painted on bars and arrows that are no longer on screen. Click Expand Groups and it returns. The calculation never stopped; see The Critical Path & float.
Enable Grouping does nothing when I tick it.
Grouping is built from your indent levels, so a flat list of tasks with no parents and children has nothing to group. Indent some tasks first (see Summary tasks, indenting & the WBS) and the handles appear.
Can I filter and group at the same time?
No, and deliberately. Switching Enable Grouping on greys out the Filters section; switching a filter on greys out Enable Grouping. Only one feature is ever left in charge of hiding rows. The three tickboxes and Filter Tasks are the exception: they are both filters, and they work together.

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

From the makers of Gantt Excel Rather not work in Excel? See Desktop Plan, our project planning software for Windows and Mac