To delete genuinely empty cells in Excel for Microsoft 365 on Windows, select the range, choose Home > Find & Select > Go To Special > Blanks, then choose Home > Delete > Delete Cells and pick whether to shift remaining cells up or left. If you need to preserve rows of related data, delete whole blank rows instead of shifting individual cells. Save a copy first if the workbook contains important information.
What You’ll Need
- An Excel workbook with the blank cells you want to remove.
- A clear idea of whether you want to close gaps within a range, remove entire rows, or remove entire columns. These actions have different effects.
- Permission to edit the worksheet. If the sheet is protected, you may need its password or an unprotected copy.
- Optionally, a backup copy of the workbook, especially if the data is large or difficult to restore.
Step-by-Step
- Decide what should happen to the surrounding data. Deleting a cell and shifting cells up or left changes the layout. It can be appropriate for a single list, such as names in column A, but it can scramble records if each row contains related fields such as a name, date, and amount. For a table of records, remove entire blank rows only when the rows are genuinely empty across the relevant data.
- Save a working copy. Use File > Save As to create a copy, or make sure the current workbook is saved before changing it. Excel’s Undo can reverse many ordinary deletions, but a copy makes it easier to recover if you save, close, or discover the wrong cells were selected.
- Select the exact area to clean. Drag over the cells or click the first cell and Shift-click the last cell. You can also click the Name Box to the left of the formula bar, type a range such as
A2:A100, and press Enter. For one-column lists, select only that list. For a multi-column record table, select the full data range or the particular key column, depending on whether you plan to remove rows. - Open Go To Special. On Windows, use Home > Find & Select > Go To Special. In the dialog, select Blanks and click OK. Excel highlights all genuinely empty cells in the selected range at once. You can also open Go To with
Ctrl+G, choose Special, and then select Blanks. On Mac, use the Go To dialog (commonlyControl+GorF5) and choose Special; ribbon labels and shortcut behavior can vary slightly by Excel version. - Check the selection before deleting. Look at the highlighted cells and confirm they are the blanks you intend to remove. Go To Special selects blank cells throughout the range, including intentional gaps. If the highlighted pattern does not match your goal, press Escape and select a narrower or more appropriate range. This check is particularly important in a table with partially filled records.
- Delete blank cells in a single list. With the blank cells selected, choose Home > Delete > Delete Cells. In the dialog, select Shift cells up when you want the values below each blank to move up and close the gaps in that column. Select Shift cells left only when you are cleaning a horizontal sequence and want cells to the right to move left. Click OK. Avoid shifting cells in a rectangular table when columns belong together.
- Remove entire blank rows when that is the goal. For a reliable row cleanup, select the column that must contain a value for every valid record, such as an ID column. Run Go To Special > Blanks on that column, then choose Home > Delete > Delete Sheet Rows. Excel removes the whole worksheet rows containing those blank key cells, keeping the other values in each record together. Before confirming, make sure a blank in that key column really means the entire record should be removed.
- Remove wholly empty columns carefully. Go To Special > Blanks is usually not the right way to identify empty columns: most columns in a selected area contain at least some blank cells, so deleting columns based on any blank selection could remove useful data. Inspect column headings and contents, select only columns that are completely empty and safe to remove, then use Home > Delete > Delete Sheet Columns. If columns contain formulas, formatting, or data outside the visible range, check those too.
- Review the result. Check the top, bottom, and edges of the cleaned range, and compare a few rows with the original. If related values no longer line up, immediately use
Ctrl+Zon Windows orCommand+Zon Mac, then retry with entire-row deletion or a narrower selection. Save the workbook after you are satisfied.
Common Mistakes
Choosing Shift cells up for a multi-column table. Suppose a row has a customer in column A, a date in B, and an amount in C. If you shift only blank cells in column B upward, dates can become associated with the wrong customers. Keep records intact by deleting entire rows based on a key field that should never be blank.
Selecting too much of the worksheet. If you select a large block, Go To Special finds every blank in that block, including spaces that separate sections or unused cells inside the data. A broad selection can produce a surprising number of highlighted cells. Limit the selection to the actual list or table before running the command.
Confusing clearing with deleting. Pressing Delete or choosing Home > Clear > Clear Contents removes cell contents but leaves the cells in place. That does not close a gap or remove a row. To close gaps, use Delete Cells and specify a shift direction; to remove a worksheet row or column, choose Delete Sheet Rows or Delete Sheet Columns.
Assuming a formula that displays nothing is empty. A cell containing a formula such as one that returns an empty text string may look blank, but it is not an empty cell. Go To Special > Blanks generally selects truly empty cells, not cells containing formulas that display nothing. Deleting or replacing those formulas requires a separate review.
Deleting rows because one cell is blank. In a broad table selection, a blank in any one field does not necessarily mean that the entire record is empty. If you delete sheet rows based on those blanks, you may lose valid records. Use a required identifier column as the selection, or filter and inspect rows before removing them.
Tips
- Use a key column to define incomplete records. An order ID, employee number, or other required field provides a clearer rule for row deletion than checking whether any cell in a record is blank.
- Use filters for review before deletion. Turn on filters with Data > Filter, open the filter menu for the key column, and display blank values if available. Inspect the visible records first. Filtering does not itself delete data; select and delete the intended worksheet rows only after checking them.
- Check hidden rows and columns. Hidden data may not be obvious when you inspect the sheet. Unhide relevant rows or columns temporarily, or check the full range before deleting so you do not overlook information outside the visible area.
- Expect formatting to remain in some cases. Deleting cell contents is different from deleting cells, and formatting may remain around a cleaned range. If the goal is only to remove visible formatting, use Home > Clear > Clear Formats rather than deleting cells.
- Use Undo immediately if the result is wrong. Undo is most dependable before you perform other actions or close the workbook. For a high-impact cleanup, work on a copy and inspect the result before saving over the original.
- Be cautious with Excel tables and merged cells. Table layouts and merged cells can restrict how cells may be shifted. If Excel blocks the operation, consider deleting complete table rows through the table’s row controls or unmerging cells only if doing so is appropriate for the layout.
Troubleshooting
Go To Special selects nothing. The range may contain formulas, spaces, or other content that only looks empty. Click a suspected cell and inspect the formula bar. If it contains a formula or invisible characters, it is not a genuinely empty cell for this command. You may need to correct the formula or remove the unwanted characters first, after confirming they are not meaningful.
Excel says it cannot shift cells in a table. Structured Excel tables limit some cell-shifting operations. If you want to remove a record, select the table row and use the table’s Delete > Table Rows option, or right-click the row and choose Delete > Table Rows. For a blank in a single table column, decide whether the whole record should be removed rather than trying to shift one cell independently.
The delete command is unavailable or has no effect. Confirm that the worksheet is not protected and that the intended cells are selected. A protected sheet may prevent edits; use Review > Unprotect Sheet if you have authorization and any required password. Also distinguish between selecting a cell and selecting an entire row or column: the Delete Cells dialog offers shift choices, while worksheet row and column deletion use their respective commands.
There are still blank-looking cells afterward. Some may contain spaces, nonprinting characters, or formulas returning empty text. Some may also be outside the range you selected. Check the formula bar, expand the selection if appropriate, and inspect hidden rows or columns. Do not remove contents in bulk until you know what they contain.
The data no longer matches across columns. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Then select the relevant records and delete whole rows based on a suitable key column. If Undo is no longer available, restore the saved copy or use the workbook’s version history if it is available.
Blank cells appear to return after cleanup. A formula, data refresh, or later paste may recreate blank-looking cells. If the range is fed by an external query or formula-driven output, change the source or transformation rather than repeatedly deleting cells from the result. Otherwise, repeat the cleanup after confirming that the new blanks are truly unwanted.
Frequently Asked Questions
Can I delete all blank cells in a worksheet at once?
Go To Special can select blanks in a selected range, but selecting an entire worksheet may include vast unused areas and can make the result difficult to control. Select only the data you mean to change. For records, remove rows using a key column rather than shifting individual blanks across the sheet.
How do I delete blank cells without shifting data?
You cannot remove a cell from the grid without some change to the layout. If you do not want neighboring values to shift, clear the cell contents instead, or delete the entire row or column when that matches the data structure. Choose the action based on whether the cell’s position matters.
Does Go To Special identify cells with formulas that return an empty string?
Generally, no. Those cells contain formulas, even if they display as blank. Go To Special > Blanks is intended for genuinely empty cells. Inspect formulas before clearing or replacing them so you do not remove calculations that are needed later.
Can I use this method in Excel for the web?
Excel for the web has different command availability and interface details from the Windows desktop app, and Go To Special options may not be identical. If the Blanks command is unavailable, open the workbook in the desktop app for the steps above, or use filters to identify and review blank records before deleting rows.
What is the safest way to remove blank rows from a list?
Select a column that every valid record must fill, find its blanks, and delete the corresponding sheet rows only after confirming they are records you want removed. This keeps the remaining fields aligned. Avoid treating every partially blank row as an empty row unless that is your intended rule.
Will deleting blank cells affect formulas?
It can. Shifting cells changes positions, and formulas that refer to affected cells may update or produce different results. Review formulas and dependent totals after cleanup, and use a copy of the workbook when the relationships are complex.
Related
- How to Count in Excel While Skipping Specific Cells Manually
- Count Cells by Color in Excel: Practical Methods
- Count Green Cells in Excel: 3 Reliable Ways to Do It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Dependent Dropdown List: Group Choices by CategoryYes. In Excel for Microsoft 365, make a group dropdown and a second dropdown that changes to show…
- Import Excel to Jira CSV: Sample File and Import StepsTo import Excel issues into Jira, arrange one issue per spreadsheet row, save the sheet as a CSV…
- How to Insert a Dividing Line in WordTo insert a dividing line in Word, place the cursor where the line should go and type three…
- Google Sheets Conditional Formatting: How to Add RulesTo add conditional formatting in Google Sheets, select the cells, choose Format > Conditional formatting, set a condition…
- Invoice Template Excel South Africa: VAT and SetupFor an invoice template in Excel for South Africa, create a workbook with supplier and customer details, a…
- Conditional Formatting Between Two Values in ExcelIn Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.