Remove blank rows

What you will build

Create a clean view that excludes rows with a blank Order ID while preserving the original import on the Raw tab. This lesson uses fictional sample data so every step can be reproduced safely.

Learning objective

Use FILTER to return only rows whose Order ID is not blank, understand each argument, verify the spill result, and recognize when a partially filled row needs manual review instead of silent removal.

Prepare the sample data

Open the source workbook below or make your own copy. The Raw tab contains the sample table; keep Raw unchanged while you work in Clean.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Source workbook in Google Sheets with the Raw tab selected, showing Order and Amount plus the two blank rows that should be excluded from the clean result.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: Keep the imported table on Raw so the original data remains available for checking and recovery.

The _result workbook in Google Sheets with the Raw tab selected, showing the exact Order and Amount table referenced by the FILTER formula on Clean.

Step 2: On your copy, open Clean. Enter Order and Amount as headers in A1:B1, then select A2 for the filtering formula.

Formula to try

In Clean!A2, enter the formula below. The matching rows spill into columns A:B automatically. Do not drag the fill handle or copy the formula down; one formula in A2 is enough. Leave the spill area clear.

=FILTER(Raw!A2:B6,Raw!A2:A6<>"")

The _result workbook in Google Sheets with Clean!A2 selected; the formula bar shows FILTER and the spilled result contains O01, O02, and O03.

How this formula works=FILTER(Raw!A2:B6,Raw!A2:A6<>"")

Overall meaning of the whole formula
- Return data from Raw!A2:B6 only for rows where column A is not blank, removing empty rows from the result.
FILTER(range, condition1, [condition2, ...])
- `range` is required; it accepts the source range to return. Here it is `Raw!A2:B6`, containing five source rows: O01/120, one blank row, O02/240, one blank row, and O03/80.
- `condition1` is required; it accepts a TRUE/FALSE array whose row count is compatible with `range`. Here it is `Raw!A2:A6<>""`.
- That condition evaluates to `{TRUE; FALSE; TRUE; FALSE; TRUE}` because A2, A4, and A6 contain Order IDs while A3 and A5 are blank.
- Both `Raw!A2:B6` and `Raw!A2:A6` are five rows tall, so each TRUE/FALSE value aligns with the corresponding source row.
- [condition2, ...] are optional additional conditions; this lesson does not pass any because checking for a nonblank Order ID is sufficient.
Whole-expression evaluation
- Raw!A2:B2 is O01/120; A2 is nonblank, so the condition is TRUE and this row is kept.
- Raw!A3:B3 is blank; A3 equals the empty string, so the condition is FALSE and this row is excluded.
- Raw!A4:B4 is O02/240; A4 is nonblank, so the condition is TRUE and this row is kept.
- Raw!A5:B5 is blank; its condition is FALSE, so the row is excluded.
- Raw!A6:B6 is O03/80; A6 is nonblank, so the condition is TRUE and this row is kept.
- FILTER returns the three TRUE rows in source order; it does not delete or modify Raw.
Why the displayed final result appears
- The three TRUE rows form a 3×2 array: `{O01,120; O02,240; O03,80}`.
- The formula is in `Clean!A2`, so the 3×2 array spills into `Clean!A2:B4`; the headers in `Clean!A1:B1` are entered separately and are not part of FILTER’s output.
- The spill area must remain clear. If any value blocks A2:B4, Sheets cannot place the complete array result.
- The audit changed Raw!B2 from 120 to 121 and Clean updated to O01/121; after restoring 120, the checkpoint returned to O01/120, O02/240, and O03/80.
- Because FILTER returns a spilled array from anchor cell A2, there is no fill-handle or copy-down step.

Checkpoint

Order	Amount
O01	120
O02	240
O03	80

The clean result contains three rows. The two completely blank source rows do not appear.

Common mistakes and fixes

If FILTER returns an error or an unexpected row count, confirm that the formula is in Clean!A2, the spill area is empty, and the source range is exactly Raw!A2:B6 with the condition Raw!A2:A6<>"".

Independent practice

Without editing Raw, add an audit column on your copy that flags rows where Order ID is blank but Amount is present. This separates truly blank rows from incomplete records that need review.

Key takeaways

✓Keep the original imported data on Raw and build the clean result separately.
✓FILTER returns only source rows whose Order ID is not blank.
✓The expected clean result is O01/120, O02/240, and O03/80.
✓Investigate partially filled rows instead of treating them as harmless blanks.

Continue learning

Previous lesson: Find duplicates in a column. Next lesson: Extract first and last names.

What you will build

Create a clean view that excludes rows with a blank Order ID while preserving the original import on the Raw tab. This lesson uses fictional sample data so every step can be reproduced safely.

Learning objective

Use FILTER to return only rows whose Order ID is not blank, understand each argument, verify the spill result, and recognize when a partially filled row needs manual review instead of silent removal.

Prepare the sample data

Open the source workbook below or make your own copy. The Raw tab contains the sample table; keep Raw unchanged while you work in Clean.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: Keep the imported table on Raw so the original data remains available for checking and recovery.

Step 2: On your copy, open Clean. Enter Order and Amount as headers in A1:B1, then select A2 for the filtering formula.

Formula to try

=FILTER(Raw!A2:B6,Raw!A2:A6<>"")

How this formula works=FILTER(Raw!A2:B6,Raw!A2:A6<>"")

Overall meaning of the whole formula
- Return data from Raw!A2:B6 only for rows where column A is not blank, removing empty rows from the result.
FILTER(range, condition1, [condition2, ...])
- `range` is required; it accepts the source range to return. Here it is `Raw!A2:B6`, containing five source rows: O01/120, one blank row, O02/240, one blank row, and O03/80.
- `condition1` is required; it accepts a TRUE/FALSE array whose row count is compatible with `range`. Here it is `Raw!A2:A6<>""`.
- That condition evaluates to `{TRUE; FALSE; TRUE; FALSE; TRUE}` because A2, A4, and A6 contain Order IDs while A3 and A5 are blank.
- Both `Raw!A2:B6` and `Raw!A2:A6` are five rows tall, so each TRUE/FALSE value aligns with the corresponding source row.
- [condition2, ...] are optional additional conditions; this lesson does not pass any because checking for a nonblank Order ID is sufficient.
Whole-expression evaluation
- Raw!A2:B2 is O01/120; A2 is nonblank, so the condition is TRUE and this row is kept.
- Raw!A3:B3 is blank; A3 equals the empty string, so the condition is FALSE and this row is excluded.
- Raw!A4:B4 is O02/240; A4 is nonblank, so the condition is TRUE and this row is kept.
- Raw!A5:B5 is blank; its condition is FALSE, so the row is excluded.
- Raw!A6:B6 is O03/80; A6 is nonblank, so the condition is TRUE and this row is kept.
- FILTER returns the three TRUE rows in source order; it does not delete or modify Raw.
Why the displayed final result appears
- The three TRUE rows form a 3×2 array: `{O01,120; O02,240; O03,80}`.
- The formula is in `Clean!A2`, so the 3×2 array spills into `Clean!A2:B4`; the headers in `Clean!A1:B1` are entered separately and are not part of FILTER’s output.
- The spill area must remain clear. If any value blocks A2:B4, Sheets cannot place the complete array result.
- The audit changed Raw!B2 from 120 to 121 and Clean updated to O01/121; after restoring 120, the checkpoint returned to O01/120, O02/240, and O03/80.
- Because FILTER returns a spilled array from anchor cell A2, there is no fill-handle or copy-down step.

Checkpoint

Order	Amount
O01	120
O02	240
O03	80

The clean result contains three rows. The two completely blank source rows do not appear.

Common mistakes and fixes

If FILTER returns an error or an unexpected row count, confirm that the formula is in Clean!A2, the spill area is empty, and the source range is exactly Raw!A2:B6 with the condition Raw!A2:A6<>"".

Independent practice

Without editing Raw, add an audit column on your copy that flags rows where Order ID is blank but Amount is present. This separates truly blank rows from incomplete records that need review.

Key takeaways

✓Keep the original imported data on Raw and build the clean result separately.
✓FILTER returns only source rows whose Order ID is not blank.
✓The expected clean result is O01/120, O02/240, and O03/80.
✓Investigate partially filled rows instead of treating them as harmless blanks.

Continue learning

Previous lesson: Find duplicates in a column. Next lesson: Extract first and last names.

Remove blank rows

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Remove blank rows

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments