Show Only Blocked Tasks in a Google Sheets Project Tracker
How do I show only unfinished tasks with blockers in a Google Sheets project tracker without hiding the original rows?
Put =IFNA(FILTER(A2:D100,B2:B100<>"Done",D2:D100<>""),"No blocked tasks") in a separate output area. It returns rows with a status other than "Done" and a filled blocker cell, leaving the original tracker rows in place.
Enter =IFNA(FILTER(A2:D100,B2:B100<>"Done",D2:D100<>""),"No blocked tasks") in a separate output area. The formula lists unfinished tasks with blockers and leaves the source rows in place. FILTER returns rows that match the conditions you set. When no rows match, IFNA replaces the resulting #N/A with a message.
How do I show only unfinished tasks with blockers in Google Sheets?
Combine a status check with a blocker check: the status must differ from Done, and the blocker cell must contain a value. If columns A through D hold the task, status, owner, and blocker, enter:
=IFNA(FILTER(A2:D100,B2:B100<>"Done",D2:D100<>""),"No blocked tasks")
The FILTER function returns rows from the range that satisfy both conditions. Starting at row 2 keeps the source headers out of the results. The status does not have to be Blocked. Tasks marked In progress or Not started also appear when their blocker cells contain a value.
The result shows all four columns, not only the task name. Marking a task Done or clearing its blocker makes the row fail one of the checks. The source row remains in the tracker.
What source data does the blocked-task formula expect?
The formula expects task records in A:D, with statuses in column B and current blockers in column D. Put the headers in A1:D1, then enter task records below them.
Paste this sample into A1:D8. The owner names are fictional. Leave cells without a value genuinely empty in Google 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 homepage task has blocker text, but its Done status excludes it from the results. The launch checklist qualifies because its status differs from Done and its blocker cell contains a value.
Describe the missing input or action in each blocker. Waiting for launch date tells someone what needs to happen. Yes also counts as a value, though it gives the person clearing the blocker little useful information.
How do you set up the blocked-task view beside the tracker?
Place the formula in F2, keep the source data in A:D, and leave room for the results in F:I. That setup keeps the source tracker and calculated view visible side by side.
-
Confirm the source layout. Put headers in row 1 and task records in rows 2 through 100. Use column A for the task, B for status, C for owner, and D for blocker. Adjust the formula references if your tracker uses different columns.
-
Reserve the output area. Clear F2:I100, including formulas that return blank results. Leave enough cells for the output to expand. Keep the formula outside its source range, A2:D100.
-
Add output headers. Enter Task, Status, Owner, and Blocker in F1:I1. The formula skips source row 1, so enter these headers yourself.
-
Insert the formula in F2. Paste the full FILTER formula from this article once. Matching rows will fill four columns. Do not drag the formula down.
-
Format the output. Turn on text wrapping in column I and widen column F to fit task names. Formatting the output leaves the source records unchanged.
-
Edit only the source records. Change statuses and blockers in A:D, rather than individual output cells. FILTER calculates the results, so make task edits in the source tracker. Use a filter view on the source table if you need to edit matching rows directly.
Why isn’t my Google Sheets filter working?
If rows are missing or an error appears, check the output space, blocker values, status text, and range boundaries. The return range and each condition range must cover the same rows.
With the sample tracker, the formula returns three tasks: Draft onboarding guide, Connect form to CRM, and Confirm launch checklist. When the sample sits in A1:D8 and the formula is in F2, the output below F1:I1 should look like this:
| 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 |
Approve homepage copy remains out because its status is Done, even though its blocker cell contains text. Update support macros and Publish team schedule remain out because their blocker cells are empty. FILTER keeps the source order. It does not sort tasks by urgency or owner.
The formula parts work as follows:
| 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 fails the check. A formula that returns "" fails it too. Cells containing spaces or placeholders such as - or None pass, even when they look empty. Clear the source cell with Delete when no current blocker exists.
If a task appears even though it looks complete, check that the status is exactly Done, without spaces before or after it. Labels such as Complete or Closed do not match this condition. Set a consistent status rule before changing the formula.
For tasks below row 100, extend the return range and both condition ranges together. For example:
=IFNA(FILTER(A2:D1000,B2:B1000<>"Done",D2:D1000<>""),"No blocked tasks")
The sheet needs at least 1,000 rows, and the output area needs enough empty cells for the results. Extending only the return range causes a range-size mismatch because the return and condition ranges must cover corresponding rows.
A #REF! error may mean existing content or merged cells are blocking the output from expanding. Keep the formula in F2 and the headers in F1:I1. Move anything you need to retain before clearing the output area.
When no rows match, IFNA displays No blocked tasks. FILTER found no rows that meet both conditions. That result does not prove the project is complete; unfinished tasks without blockers may still remain in the tracker. IFNA handles #N/A results, so it can also hide a #N/A error in the source data. Check the source if the message conflicts with the rows you expect to qualify.
How do you keep the blocked-task view accurate during updates?
Update blocker details in the source tracker and mark completed work Done there. An unfinished task with stale blocker text will remain in the output after the issue is resolved. Completed work can also appear if its status differs from Done and its blocker cell still contains a value.
Keep manual notes out of the rows beside the output. Matching rows can shift as source data changes, while notes entered beside them stay in place. Keep discussion with the source record instead.
For a recurring operations handoff, use the view to prepare a daily blocked-task summary. See how to post a daily blocked-tasks summary from Google Sheets to Slack. To review workload by owner, count open tasks by owner with Google Sheets QUERY covers that separate task. Both workflows fit within remote operations, where a shared view can help people across locations review work status.
When should you avoid this blocked-task formula?
Skip this formula if you need to edit results directly, keep a history of resolved blockers in column D, or account for several labels for completed work. It checks only whether the status differs from Done and the blocker cell is nonempty.
Use a filter view on the source table when you need to edit rows directly. Exclude Done in column B and require nonempty cells in column D. FILTER creates calculated output, so change source records rather than treating the output as a second task list.
To keep blocker history, store resolved items in a separate field and reserve column D for current blockers. You can also add a dedicated field for current blocked status. Otherwise, old blocker text can make an unfinished task appear blocked after the issue has been resolved.
If you use several terminal statuses, decide how to handle completed, canceled, and archived tasks before editing the condition. Excluding only Done leaves those other labels in the results.