How to Find Blank Rows in Excel

How-to basics · Updated November 20, 2026

To find rows with no data in a particular range, add a helper column with =COUNTBLANK(A2:F2)=COLUMNS(A2:F2), fill it down, and filter the helper column for TRUE. This checks every cell in each row, rather than flagging a row just because one cell is empty. You can also use Go To Special to locate individual blank cells, but that method does not by itself confirm that an entire row is blank.

Before you start, decide which columns define a complete record. A row can be empty in columns A:F but still contain information in column G, so include all relevant columns in your check. If you only want to locate gaps, keep the rows in place; if you intend to remove them, review the results and save a copy first.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows.
  • A data range with a clear header row and a known set of columns to check.
  • An unused column next to the data if you plan to use the helper-column method. You can remove the helper column after you finish.

The steps below work on ordinary worksheet ranges. If your data is formatted as an Excel table, add the helper column to the table or use a temporary column beside it. Excel tables may automatically copy a formula down the column.

Step-by-Step

  1. Choose the range that defines a blank row. Identify the first and last columns that should contain a record, such as A through F. Include every field that matters. If column F contains optional notes but column G contains an ID, for example, include column G too; otherwise, a row with an ID could be incorrectly classified as blank. Do not include the header row in the formula range.
  2. Add a helper-column heading. In the first empty column next to your data, type a heading such as Blank row? in the header row. For example, if headers are in row 1 and your data starts in row 2, enter the heading in G1. A heading helps Excel treat the helper values as part of a filterable table.
  3. Enter the check for the first data row. With the fields you want to check in A2:F2, enter =COUNTBLANK(A2:F2)=COLUMNS(A2:F2) in G2. COUNTBLANK counts cells Excel considers blank, and COLUMNS returns the number of cells in the range. The comparison returns TRUE only when every cell in A2:F2 is blank. It also treats a formula that displays an empty string as blank; a cell containing a space is not blank.
  4. Copy the formula down the data. Select G2 and use the fill handle, the small square at the lower-right corner of the selection, to drag the formula down to the last data row. Alternatively, copy G2, select the remaining helper cells, and paste. Check that the formula references shift by row: the next row should check A3:F3, then A4:F4, and so on.
  5. Confirm the result on a known row. If row 4 has no content in A4:F4, its helper result should be TRUE. If row 5 has a value in any of those cells, its result should be FALSE. This is a useful check before filtering, especially when the range contains formulas, optional fields, or pasted data.
  6. Turn on filtering. Select a cell in the data and choose Data > Filter. Excel adds filter arrows to the header row. If arrows already appear, filtering is already on. Make sure the helper heading is included in the filtered range; otherwise its TRUE and FALSE results may not filter alongside the records.
  7. Show only blank rows. Open the filter arrow on the helper-column heading, clear Select All, select TRUE, and confirm. Excel now displays rows that are blank across the columns checked by the formula. The row numbers at the left may skip because the other records are hidden, not deleted.
  8. Review the filtered records before acting. Check the displayed rows against the original fields and confirm that your chosen range was broad enough. If you only need to find the gaps, note their row numbers or leave them visible. To return to the full list, open the helper-column filter and choose Clear Filter, or select all values again.
  9. Remove temporary work if appropriate. Once you no longer need the results, clear the filter and delete the helper column if it was only for checking. If you decide to delete blank rows, first save a copy, then select the verified rows by their row numbers and use Home > Delete > Delete Sheet Rows. Do not select individual blank cells and choose Delete Sheet Rows unless you have verified what Excel will remove.

Example: Suppose row 2 has a customer name in A2 and an order number in B2, while C2:F2 are empty. The helper formula returns FALSE because the row contains data. If every cell from A3 through F3 is empty, it returns TRUE. This distinction matters: finding blank cells in a record is not the same as finding a fully blank row.

Common Mistakes

  • Checking only one column. Filtering a column for empty cells finds rows where that field is empty, not rows where the whole record is empty. Use the helper formula across all relevant columns when you mean fully blank rows.
  • Including the header in the check. A header contains labels, so it will never be classified as blank. Start the formula in the first data row and use the header only for the helper-column label.
  • Deleting rows based on selected blank cells. Go To Special selects empty cells, not just rows where every cell is empty. Deleting entire rows from that selection could remove records that have a blank in one field but useful information elsewhere.
  • Forgetting data outside the checked range. A row may look blank in A:F but contain a note, formula, or ID farther right. Expand the formula range to include all columns that should count as part of the record.
  • Assuming a space is empty. A cell with one or more spaces contains characters, so COUNTBLANK does not count it as blank. Similarly, invisible characters copied from another system can make a cell appear empty while still containing content.
  • Confusing hidden rows with deleted rows. A filter hides rows that do not match. It does not remove them. Clear the filter to display the full dataset again.

Tips

  • Use a helper column when accuracy matters. It gives each row a clear TRUE or FALSE result, which you can filter, sort, or inspect before making changes.
  • Choose whether formula results count as blank. The COUNTBLANK check treats a formula that returns an empty string as blank. If you instead want any formula to make a row count as occupied, even when its displayed result is empty, use COUNTA for that test: with the same A2:F2 layout, =COUNTA(A2:F2)=0 returns TRUE only when the cells contain no values or formulas. Choose one definition and apply it consistently.
  • Keep a backup before deleting. Save a separate copy of the workbook or use Save As before removing rows. Deleting rows can affect formulas, references, charts, and linked data elsewhere in the workbook.
  • Use Go To Special for a quick visual scan of empty cells. Select the data cells, not the headers, then choose Home > Find & Select > Go To Special > Blanks. Excel selects empty cells in the chosen range. You can apply a temporary fill color to make them easier to inspect, but this highlights individual cells and is not a reliable test for entirely empty rows on its own.
  • Apply a consistent scope to every row. If one record is considered complete when columns A:D are filled, use A:D for every row. Do not change the checked columns partway down the list, since the TRUE and FALSE results would no longer mean the same thing.

Troubleshooting

The formula marks a row FALSE even though it looks empty. Check each cell in the selected range for spaces, nonprinting characters, or formulas. Click a cell and inspect the formula bar. A formula returning an empty string is counted as blank by COUNTBLANK, but a space or other character is not. If you need to clean imported text, do that carefully in a copy of the data rather than deleting rows based on appearance alone.

Rows with blank-looking formulas are not being treated as expected. Confirm you used COUNTBLANK for the definition where formula results that display nothing should count as blank. If you used COUNTA instead, a formula in the cell counts as content even when the result appears empty. Pick the test that matches your task and adjust the formula consistently.

The filter arrow is missing or does not include the helper results. Click inside the data and choose Data > Filter. If the filter range stops before the helper column, remove the filter and select the complete range, including the headings and helper values, before turning it on again. Check that there is one header row and no completely separate block of data immediately beside the list.

Filtering for TRUE shows no rows. First inspect the helper results without filtering. Confirm the formula refers to the intended columns and that it was copied through the last data row. Check that the filter is set to TRUE rather than FALSE, and clear any filters on other columns that could also hide matching rows.

Go To Special selects more cells than expected. That is normal: it selects each empty cell within the selected range. It does not group those cells into verified blank rows. Press Esc to cancel the selection, then use the helper-column method to identify rows that are blank across every chosen column.

You are using Excel for Mac. The helper formula and filter logic are the same. The ribbon labels can differ slightly by Excel build; look for Find & Select and Go To Special on the Home tab, or use the Go To command to open its Special options. Control+G opens Go To in many Mac Excel configurations; use the ribbon if your keyboard settings intercept that shortcut. In Excel for the web, the formula and filters also work, though some selection options may be in different places.

Frequently Asked Questions

How do I find completely blank rows without deleting them?
Use the helper formula across the columns that define a complete record, then filter the helper column for TRUE. The matching rows stay in the worksheet and can be reviewed safely.

Does Go To Special find blank rows?
It finds blank cells in the selected range. Because it can select a blank cell in a row that contains other data, use a helper formula to confirm that every relevant cell in the row is blank.

Can I find rows that are blank in only one column?
Yes. Filter that specific column for blank values, or use Go To Special on the column. Keep in mind that this identifies missing values in that field, not empty records across the whole row.

Will the formula count a cell with a formula returning nothing as blank?
Yes, COUNTBLANK treats a formula result of an empty string as blank. A cell containing a space or another character is not blank for this test.

Can I delete all the blank rows after finding them?
Yes, but review the filtered results first, save a copy, and delete only the verified rows. Clear the filter afterward to confirm that the records you intended to keep are still present.

Why does a blank row remain after I filter for TRUE?
It may contain content outside the columns checked, or the row may not be included in the filter range. Expand the formula and filter range if those additional columns should count, then check the row again.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.