Pull the Rows That Have Only 1 Value in Excel

What this does

Pulling rows with only one value is a row-level completeness check. Instead of counting a single column, the formula counts filled cells across each row and keeps the row only when the count equals one. It is useful for cleaning partially loaded exports, finding orphan records and separating incomplete rows before analysis. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “pull the rows that have only 1 value in excel”. Start on a copy or a tiny sample, keep the affected rows visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a layout choice that keeps the sheet readable and sortable, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

A2:D6 contains customer records. Row 2 has only an email, row 3 has name plus email, row 4 has only an ID. =FILTER(A2:D6, BYROW(A2:D6, LAMBDA(r, COUNTA(r)=1))) returns rows 2 and 4, because each has exactly one nonblank cell across the selected columns. Incomplete rows are easy to miss visually. A row-level filter turns the cleanup into an auditable rule instead of manual scanning. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Treat “pull the rows that have only 1 value in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Counting only one column instead of all fields that define row completeness.
• Including headers in the tested range.
• Using COUNT instead of COUNTA when text values should count as filled.
• Forgetting that formulas returning an empty string may still behave differently from truly blank cells.

Frequently asked questions