how to remove blank rows in excel

To remove blank rows in Excel, you have a few reliable options, from quick manual fixes to more advanced, “set it and forget it” methods.

Fastest method: Filter and delete

Use this when you have a big list and want to get rid of rows that are completely empty.

Select your data
- Click any cell in your table, then press Ctrl + A to select the region, or drag to select the range you care about.

Turn on filters
- Go to Data > Filter. Little dropdown arrows appear in the header row.

Filter for blank rows
- Click the dropdown on any column that should always have data (like “Name” or “ID”).
- Click Select All to clear everything, then select only (Blanks) and click OK / Apply.

Delete the blank rows
- You should now see only blank rows (or rows that are blank in that key column).
- Select those rows by clicking the row numbers, right‑click, choose Delete , then Entire row / Rows.

Remove the filter
- Click the filter icon again and choose Select All , or click Data > Filter to turn filters off.

This method keeps non‑blank rows intact while quickly clearing out the visibly empty ones.

Precise method: Helper column with COUNTA/COUNTBLANK

Use this when some rows have partial data and you only want to remove rows that are completely blank.

Add a helper column
- Insert a new column at the end of your data, e.g., “CheckBlank”.

Enter a formula to test the row
- If you want “TRUE/FALSE” for blank vs non‑blank:
  - In the helper column on the first data row (say row 2), enter:
    =COUNTA(A2:D2)=0 (adjust A2:D2 to cover your row).

   * Copy the formula down the column.

 * Alternatively, to count blanks only:
   * Use `=COUNTBLANK(A2:D2)`, then compare that to the number of columns (for example, 4).

Filter to find blank rows
- Turn on Data > Filter.
- Filter the helper column to show only TRUE (for the COUNTA test) or only rows where the blank count equals the total number of columns.

Delete the filtered rows
- Select the visible rows, right‑click, choose Delete row / Delete Sheet Rows.

Clear the helper setup
- Remove the filter and delete the helper column if you no longer need it.

Very quick manual methods

Good for small sheets or when you only have a few gaps.

Delete specific blank rows
- Hold Ctrl (Windows) or Command (Mac) and click each row number you want to delete.
- Right‑click > Delete > Entire row / Rows.

Use Go To Special for blank cells , then delete rows
- Select your range.
- Press F5 (or Ctrl + G) > Special… > Blanks.
- Right‑click a selected blank, choose Delete… and then Entire row.

* This removes any row that has at least one selected blank cell, so be careful if blanks can be legitimate values.

Power user options (for recurring cleanup)

If you clean similar files repeatedly, these can save time.

Power Query (Get & Transform)
- Load your data with Data > From Table/Range.
- In Power Query: Home > Remove Rows > Remove Blank Rows.

* Close & Load to get a cleaned table, and refresh next time you have new data.

Formula-based “no‑blank view”
- Use a formula with FILTER to spill only non‑empty rows into a clean area, for example:
  - =FILTER(B5:E16, BYROW(B5:E16, LAMBDA(row, SUM(--(row<>""))>0))) in newer Excel versions.

* This does not delete rows, but gives you a dynamic, blank‑free version of your table.

Small FAQ

Does “deleting” the rows remove Excel's extra million rows?
No. Excel always has those rows in the sheet; you’re just clearing or hiding the unused ones so they don’t interfere with sorting, filtering, formulas, or exports.

What if only the bottom of my sheet looks blank but keeps acting “used”?
There may be formatting or invisible content. Deleting those rows (or clearing formats) from the bottom up until your real data ends can reset the used range.

Simple HTML table example (steps overview)

html

<table>
  <thead>
    <tr>
      <th>Method</th>
      <th>Best for</th>
      <th>Key steps</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Filter + Delete</td>
      <td>Many blank rows in a list</td>
      <td>Filter on (Blanks) in a key column, select visible rows, delete rows.</td>
    </tr>
    <tr>
      <td>Helper column (COUNTA)</td>
      <td>Only delete fully empty rows</td>
      <td>Use COUNTA across row, filter where result shows blank rows, delete those rows.</td>
    </tr>
    <tr>
      <td>Go To Special</td>
      <td>Quick removal with some risk</td>
      <td>Select range, Go To Special → Blanks, delete entire rows.</td>
    </tr>
    <tr>
      <td>Power Query</td>
      <td>Repeatable cleaning</td>
      <td>Load to Power Query, Remove Blank Rows, load cleaned table.</td>
    </tr>
  </tbody>
</table>