To delete gaps in Excel, select the relevant cells, use Home > Find & Select > Go To Special > Blanks, then delete the blank cells and choose whether to shift cells up or remove their rows. Choose carefully: shifting cells can misalign related data. Before you begin, save a copy of the workbook and decide whether you mean empty cells, entire blank rows, or extra spaces inside text.
What You’ll Need
- An Excel workbook with the cells or rows you want to clean up.
- A clear idea of which columns belong together. This is important if a row contains related information such as a name, date, and amount.
- A backup copy of the file, especially if it contains important or hard-to-recreate data.
The steps below are for Excel for Microsoft 365 on Windows. In Excel for Mac, look for the same commands on the Home ribbon; keyboard shortcuts can differ. Excel for the web has fewer selection tools, so use the desktop app for the Go To Special method if you cannot find it online.
Step-by-Step
- Save a backup and identify the data area. Save the workbook with a new name or make a copy before deleting anything. Identify the exact columns and rows that make up your data, and note whether a blank cell is genuinely unwanted. A blank in a notes column may be correct, while a blank in a list of values may be a gap you want to close. Avoid selecting entire worksheet columns if you only need to clean a small table.
- Choose the right kind of cleanup. If one standalone list has empty cells between its entries, you can delete those cells and shift the entries below them upward. If each row is a record with information across several columns, keep the records together and remove only rows that are entirely empty. Do not shift cells in one column when the cells alongside it belong to the same records; doing so can pair names with the wrong dates or amounts.
- Select the range you want to check. Drag across the data range, or click its first cell and use Shift with the arrow keys to extend the selection. Include the cells where gaps may occur, but leave out unrelated data and, usually, the header row. For a single list, select that list’s cells. For checking fully blank records with a helper column, select the data columns you intend to test, not the helper column.
- Select blank cells with Go To Special. On Windows, choose Home > Find & Select > Go To Special. In the dialog, select Blanks and choose OK. Excel highlights the empty cells in the selected range. Check that the highlighted cells are the gaps you intend to remove. A cell containing a formula that displays an empty string is not a truly empty cell, so this command will not select it.
- Close gaps in a standalone list. If the selected range is one independent list, keep the blank cells selected and press Ctrl+- (Control and the minus key). In the Delete dialog, choose Shift cells up, then choose OK. Excel moves the entries below each selected gap upward within the selected column or range. Inspect the resulting list and make sure that no adjacent, related columns were left behind and no values were moved into the wrong records.
- Remove a row when a key column identifies the gaps. If a column that every valid record must have—such as an ID or product name—is blank on the rows you want to remove, select the data cells in that column and use Go To Special > Blanks. Confirm the selected cells are only on rows you mean to delete. Then choose Home > Delete > Delete Sheet Rows. This removes the whole worksheet rows, including values in other columns. Do not use this method if a blank in that key column can be legitimate or if the row contains information you need.
- Find rows that are completely blank across a table. When a row should be deleted only if every relevant field is empty, a helper column is safer than selecting blanks in all the data columns. For example, with headers in row 1 and data in A2:Z100, enter this in a new helper column on row 2:
=COUNTA(A2:Z2)=0. It returns TRUE when A2:Z2 contains no entries and FALSE when at least one cell contains a value. Fill the formula down alongside the data. Check a few TRUE and FALSE results before proceeding. COUNTA treats a cell containing a formula as nonempty, even if that formula displays nothing. - Filter and delete only the confirmed empty rows. Turn on filters by selecting the table, then choosing Data > Filter. Use the helper column’s filter arrow to show only TRUE rows. Review the visible rows to confirm they are entirely blank in the fields that matter. Select their row headers, choose Home > Delete > Delete Sheet Rows, and then clear the filter. Remove the helper column if you no longer need it. If you are uncertain about a row, leave it in place until you have checked its contents.
- Check the result and save. Look at the first and last entries around each cleaned area and verify that related columns still line up. If the result is wrong and you have not made other changes since, use Ctrl+Z on Windows to undo. Save the workbook after confirming the data is correct. On Mac, use Edit > Undo or the Undo button if the keyboard shortcut differs in your setup.
Common Mistakes
Shifting only one column in a multi-column table. This is the most serious risk. If a row contains a customer and an order date, moving the customer column upward alone can attach names to the wrong dates. Shift cells up only for a genuinely standalone list. For records, delete whole rows only after confirming they are meant to go.
Deleting every row with any blank field. A record can be valid even if one optional field is empty. Selecting blanks across a full table and deleting entire rows can remove those valid records. Use a key column only when a blank in that column reliably marks a row for removal, or use the helper-column check to identify rows with no content across the relevant fields.
Including headers or unrelated cells. Go To Special works within the selected range. If the selection includes a title, notes, totals, or adjacent tables, the selected blanks may not all belong to the cleanup. Select the smallest suitable range and check the highlighted cells before deleting.
Assuming a visually empty cell is truly empty. A cell may contain a formula that returns an empty string, spaces, or other invisible characters. Go To Special > Blanks finds genuinely empty cells, not every cell that looks blank. Check the formula bar or use a helper test before taking broad action.
Tips
Work on a copy when the cleanup affects a large table. If you use filters, confirm that the filter is showing the intended rows before deleting them; selecting visible row headers is safer than assuming every row in the sheet is in view. After removing gaps, check totals, formulas, and references that depend on the affected range. Deleting rows can change what formulas include, and a sorted or filtered view can make it harder to see the full context.
For a quick review, use Find or the filter controls to inspect the area after cleanup, rather than relying only on the first few rows. If you have a structured Excel table, keep the table boundaries intact and remove rows through the table or sheet-row commands as appropriate. If you only need to hide blank rows temporarily, use a filter instead of deleting data; filtering is reversible and does not remove the underlying records.
If by gaps you mean extra spaces within text rather than empty cells, do not delete cells or rows. In a spare column, use Excel’s TRIM function to clean ordinary leading and trailing spaces and reduce repeated spaces between words, then review the results before replacing the original text. Keep a copy of the source values until you have checked names, codes, and other text where spaces may be meaningful.
Troubleshooting
Go To Special did not select the cells that look blank. The cells may contain formulas returning an empty string, spaces, or invisible characters. Click a cell and inspect the formula bar. If the cell contains a formula, decide whether to keep or remove that formula; deleting it is not the same as removing a truly empty cell. If spaces are present, clean the text in a separate column and review it first.
The Delete dialog did not appear. Make sure a cell selection is active, then use Home > Delete > Delete Cells or press Ctrl+- on Windows. If you selected complete row headers, Excel may delete those rows directly rather than asking how to shift cells. Use the ribbon command when you want to see the available deletion choices.
Other columns no longer match the list. Undo immediately with Ctrl+Z on Windows, or use the Undo command on Mac. Repeat the cleanup on a copy and delete complete rows only when those whole records should be removed. If the changes have already been saved or more edits were made afterward, restore the backup or use workbook version history if it is available.
The helper formula marks an apparently empty row as FALSE. Check every column in the formula’s range, including columns beyond the visible part of the table. A space, a value in a hidden column, or a formula counts as content for COUNTA. Adjust the range to cover exactly the fields you consider part of a record, and verify the formula on a few rows before filtering.
You cannot find Go To Special in Excel for the web. Open the workbook in the desktop version of Excel and follow the ribbon path above. If you must work in a browser, use the available filters to isolate rows based on a reliable key column, review them, and delete only confirmed records. Do not substitute a broad delete action for a selection you cannot verify.
Frequently Asked Questions
How do I delete blank cells and move the data up?
For a standalone list, select its range, choose Home > Find & Select > Go To Special > Blanks, then press Ctrl+- and select Shift cells up. Do not use this for one column of a multi-column record table unless the related columns should move too.
How can I delete blank rows in Excel?
If a required key field is blank on every row you want removed, select the cells in that field, use Go To Special > Blanks, verify the selected rows, and choose Home > Delete > Delete Sheet Rows. If only completely empty rows should go, use a helper column such as the COUNTA check above and review the TRUE rows before deleting.
Can I remove gaps without changing the order of my data?
Yes. Deleting rows does not sort the remaining records, so their relative order stays the same. Shifting cells up in a single column also retains the order of the entries in that column, but it may break the relationship between that column and other columns.
Why does Excel not recognize a cell as blank?
It may contain a formula, a space, or an invisible character. Go To Special selects truly empty cells, not every cell that appears empty on screen. Inspect the cell contents and choose a cleanup method that matches what is actually stored there.
How do I remove extra spaces inside text instead?
Use TRIM in a spare column to clean ordinary spaces, then check the output before copying it back as values. TRIM also reduces repeated spaces between words, so review the result if repeated spacing has a purpose. Some nonbreaking spaces require additional cleanup.
Can I undo deleting gaps?
Use Ctrl+Z on Windows immediately after the deletion, or the Undo command in Excel for Mac. Undo may not be available after closing the workbook or after a chain of later changes. That is why saving a separate copy before a large cleanup is a useful safeguard.
Related
- How to Copy a Whole Column in Excel
- How to Copy an Entire Sheet in Excel: Steps and Tips
- How to Copy a Tab in Excel Without Losing Formatting
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text Join Range: How to Combine Cell ValuesTo join the text in an Excel range, use TEXTJOIN: with values in A2:A5, for example, enter =TEXTJOIN(",…
- Excel Keyboard Shortcut to Remove Decimal PlacesIn Excel for Microsoft 365 on Windows, select the cells and press Alt+H, 9 to show one fewer…
- Box Plot in Excel: Create and Read One Step by StepTo create a box plot in Excel for Microsoft 365 on Windows, place each group of raw numeric…
- How to Freeze Rows in Google Sheets: A Quick GuideTo freeze rows in Google Sheets in a web browser, open View > Freeze and choose 1 row…
- Excel AutoSum Shortcut: How to Use Alt+=In Excel for Microsoft 365 on Windows, the AutoSum shortcut is Alt+=: select the blank cell where you…
- Insert an Electronic Signature in WordTo insert an electronic signature in Word, add a picture of your handwritten signature, draw one with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.