Show Only Blocked Tasks in a Google Sheets Project Tracker

What formula shows only unfinished tasks with blockers in Google Sheets?

Use Google Sheets FILTER with two conditions, wrapped in IFNA, to return tasks whose status is not “Done” and whose blocker cell contains a value.

With task, status, owner, and blocker in columns A through D, enter:

=IFNA(FILTER(A2:D100,B2:B100<>"Done",D2:D100<>""),"No blocked tasks")

The result updates when source values change. Marking a task “Done” or clearing its blocker removes it from the view without hiding the source row.

“Blocked” means column D contains a value, not that column B says “Blocked”. An “In progress” or “Not started” task qualifies when its blocker is filled in. The output includes all four columns.

What source data does the blocked-task formula expect?

The Google Sheets blocked-task formula expects headers in A1:D1 and task records beneath them, with status in column B and current blockers in column D.

Enter this sample in A1:D8. The owner names are fictional; leave empty table cells genuinely empty in Sheets.

Task	Status	Owner	Blocker
Draft onboarding guide	In progress	Pixel Finch	Waiting for access checklist
Approve homepage copy	Done	Nova Widget	Approval pending
Update support macros	Not started	Moxie Quill
Connect form to CRM	Blocked	Nova Widget	Need test account
Publish team schedule	In progress	Pixel Finch
Confirm launch checklist	Not started	Moxie Quill	Waiting for launch date
Archive kickoff notes	Done	Pixel Finch

The completed homepage task deliberately retains an old blocker but must be excluded. The launch checklist must appear because “Not started” is not “Done” and its blocker is filled in.

Write actionable blocker descriptions. “Waiting for launch date” identifies the missing input; “Yes” qualifies but provides little context.

How do you set up the blocked-task view beside the tracker?

Create the Google Sheets blocked-task view in F:I, leaving A:D as the editable source and column E as a gap.

1. Confirm the source layout. Put headers in row 1 and records in rows 2 through 100. Use A for task, B for status, C for owner, and D for blocker. Adjust references if the tracker uses different columns.

2. Reserve the output area. Clear F2:I100, including formulas that display blank results. FILTER needs empty cells for expansion. Do not place the formula inside its source range, A2:D100.

3. Add output headers. Enter Task, Status, Owner, and Blocker in F1:I1. The formula excludes source row 1, so these headers must be entered separately.

4. Insert the formula in F2. Paste the complete FILTER formula from this article once. Google Sheets fills matching rows across four columns automatically; do not drag the formula down.

5. Format the output. Enable text wrapping in column I and widen column F for task names. Output formatting does not modify source records.

6. Edit only the source records. Update statuses and blockers in A:D, not individual output cells. The result is a calculated view rather than an independently editable task list.

This layout keeps the full tracker visible and does not change which source rows collaborators can see.

Which tasks should the sample tracker return?

The sample Google Sheets tracker returns three tasks: Draft onboarding guide, Connect form to CRM, and Confirm launch checklist.

With the sample in A1:D8 and the formula in F2, the output beneath F1:I1 is:

Task	Status	Owner	Blocker
Draft onboarding guide	In progress	Pixel Finch	Waiting for access checklist
Connect form to CRM	Blocked	Nova Widget	Need test account
Confirm launch checklist	Not started	Moxie Quill	Waiting for launch date

FILTER preserves source order; it does not rank tasks by urgency or owner.

“Approve homepage copy” is excluded because its status is “Done”, despite the blocker text. “Update support macros” and “Publish team schedule” are excluded because their blockers are empty.

When no rows qualify, F2 displays No blocked tasks, with no returned values in the remaining output cells. Unfinished tasks without blockers may still exist.

Why does FILTER wrapped in IFNA return only unfinished tasks with blockers?

FILTER keeps rows that satisfy both the unfinished-status and nonempty-blocker checks, while IFNA supplies readable text when no rows match. The inner calculation uses A2:D100 as the return range, bringing back each task’s status, owner, and blocker rather than only its name. Starting at row 2 leaves the source headers out of the results.

Next, B2:B100<>"Done" creates a row-by-row condition that accepts statuses other than “Done”. This is broader than checking for “Blocked”, so an unfinished task can qualify regardless of its current workflow label. Then D2:D100<>"" checks for blocker content, using the dedicated blocker field rather than inferring a blocker from status alone.

FILTER combines these separate conditions with AND logic and returns qualifying rows in source order. That includes the sample launch checklist because its status is “Not started” and its blocker is filled in. Finally, IFNA replaces a no-match #N/A result with No blocked tasks; it targets that error type rather than applying the broader handling of IFERROR.

How does the formula decide which tasks are blocked?

The Google Sheets formula returns a source row only when both the status and blocker conditions pass.

Formula part	Effect
FILTER(A2:D100,...)	Returns matching rows across all four source columns.
B2:B100<>"Done"	Excludes “Done”; other statuses, including blank statuses, pass.
D2:D100<>""	Requires a value other than an empty string.
IFNA(...,"No blocked tasks")	Replaces the no-match #N/A result with readable text.

A genuinely empty blocker cell and a formula returning "" both fail the blocker check. Spaces count as text and can qualify despite appearing blank.

The formula checks values, not formatting or comments. A red cell or a blocker described only in a comment does not qualify.

IFNA does not catch every spreadsheet error. It can also mask source-data #N/A errors, so inspect the source if the message contradicts visible qualifying rows.

How do you fix errors or misleading results in the blocked-task view?

Fix Google Sheets blocked-task results by checking output space, blocker contents, status labels, and matching range boundaries.

The output shows #REF!

Symptom: Sheets reports that the array result cannot expand because it would overwrite data.

Exact fix: Preserve the formula in F2 and headers in F1:I1. Clear G2:I2 and F3:I100, and unmerge cells in F2:I100. Move needed information elsewhere first.

A formula displaying an empty string can still prevent expansion.

A blocker looks empty but its task appears

Symptom: An unfinished task appears with an apparently blank blocker.

Exact fix: Select the task’s source cell in column D and press Delete. Do not replace its contents with a space.

Clear placeholders such as “-” or “None” when they mean no blocker. Reserve empty cells for unblocked tasks.

A completed task remains visible

Symptom: A returned task’s status looks like “Done”.

Exact fix: Replace the source status with exactly Done, without leading or trailing spaces. Standardize completed records labeled “Complete” or “Closed” to “Done” for this formula.

Configure a status dropdown through Data > Data validation to reduce inconsistent labels.

Tasks below row 100 are missing

Symptom: A qualifying task in row 101 or later does not appear.

Exact fix: Extend all three ranges together:

=IFNA(FILTER(A2:D1000,B2:B1000<>"Done",D2:D1000<>""),"No blocked tasks")

Ensure the sheet has at least 1,000 rows and sufficient empty output space. Changing only the return range creates a range-size mismatch; all ranges must start and end on matching rows.

How do you keep the blocked-task view accurate during updates?

Keep the Google Sheets blocked-task view accurate by clearing resolved blockers and marking completed tasks “Done” in the source tracker.

Stale blocker text keeps unfinished tasks visible. Completed work with an unfinished status also remains visible if its blocker is filled in.

Consider protecting the output formula against accidental edits while leaving appropriate source cells editable. Protection controls editing; it does not make source information confidential.

Do not attach manual notes beside individual output rows. Filtered rows shift when tasks disappear, but adjacent notes do not follow their original tasks. Keep discussion attached to source records.

When should you avoid this blocked-task formula?

Avoid this Google Sheets formula when you need direct editing of matching rows, permanent blocker history in column D, or multiple completed-state labels.

• Direct editing: Use a filter view on the source table, excluding “Done” in column B and requiring nonempty cells in column D.
• Historical blockers: Move resolved history to another field and keep only current blockers in D, or filter a dedicated current-blocked field.
• Multiple terminal statuses: Define how completed, canceled, and archived tasks should behave before changing the formula. Excluding only “Done” does not exclude those other labels.