To delete blank cells in Excel, select the range, choose Home > Find & Select > Go To Special > Blanks, then use Home > Delete > Delete Cells and choose whether to shift the remaining cells up or left. If you mean to remove whole empty rows, use a method that checks whether each entire row is blank; selecting blanks across a table and deleting rows can remove records that still contain data.
What You’ll Need
- Excel for Microsoft 365 on Windows for the menu paths below. The ribbon method is also available in many desktop versions of Excel.
- A saved copy of the workbook, especially if the blanks are in an important or shared data set.
- A clear idea of what should happen to the cells beside the blanks: shift up, shift left, or remove a whole row.
Step-by-Step
- Save a copy and identify the target. Save the workbook, or duplicate the worksheet before making a bulk change. Then decide whether you want to remove gaps from one column, a rectangular range, or entire empty rows. These are different operations. For example, removing a blank cell from a single list and shifting values up is not the same as deleting the full worksheet row that contains it.
- Select only the range where blanks are safe to remove. Click and drag over the cells you want to clean, including the headers only if you intend to affect them. If you are cleaning a single list in column A, select just its data cells, such as A2:A100, rather than the entire worksheet. For a rectangular block of related values, select that whole block. Avoid selecting unrelated columns: shifting cells within a block can make values no longer line up with their original records.
- Open Go To Special. On the Windows ribbon, choose Home > Find & Select > Go To Special. In the dialog, select Blanks and click OK. Excel highlights the genuinely empty cells within your selected range; it does not search the entire worksheet unless the entire sheet was selected. If the selection contains no empty cells, there will be nothing to delete.
- Choose the deletion command, not Clear Contents. With the blank cells still selected, go to Home > Delete > Delete Cells. In the dialog, choose Shift cells up to close gaps in a vertical list, or Shift cells left to close gaps in a horizontal row. Click OK and inspect the result. The direction matters: Excel moves neighboring cells into the spaces, so check that the remaining values still match the right labels or records.
- Use the right approach for entirely blank rows. Do not select a multi-column data table, find blanks, and delete entire rows unless you are certain every selected row is empty. A row with a missing phone number but a valid name and order is not an empty row. For a reliable check, add a temporary helper column beside the data. With the row’s data in A2:D2, enter
=COUNTA(A2:D2)=0in E2. The formula returns TRUE only when all four cells contain no values; a row with any entry returns FALSE. Fill the formula down through the records. - Filter for the rows marked TRUE, then delete those rows. Select the table, choose Data > Filter, and open the filter arrow in the helper column. Clear Select All, select TRUE, and apply the filter. Review the visible rows to confirm they are entirely empty within the columns you checked. Select their worksheet row numbers, right-click, and choose Delete Row. If Excel offers Delete Sheet Rows, use it for the selected rows. Clear the filter, check the remaining records, and remove the temporary helper column when you are satisfied.
- Check formulas, totals, and the surrounding layout. Confirm that data has not shifted into the wrong fields, formulas still refer to the intended cells, and totals or charts still make sense. If the result is wrong, immediately use Ctrl+Z on Windows to undo the deletion, then repeat the process with a more precise selection or a different deletion direction.
In Excel for Mac, the same general process is available through the Home ribbon’s Find & Select and Delete controls, although labels and dialog layout can vary slightly by release. In Excel for the web, some desktop selection tools, including Go To Special, may not be available. If you cannot find the command, open the workbook in the desktop app or use a helper column and filter to identify the rows you want to remove.
Common Mistakes
- Deleting entire rows because one cell is blank. Go To Special identifies blank cells, not records that are completely empty. Deleting rows based on blanks in a selected data set can erase valid information. Check every relevant column before removing a whole row.
- Choosing the wrong shift direction. Shift cells up is usually appropriate for a vertical list; Shift cells left is for a horizontal sequence. In a table with several fields per record, shifting only one column can detach values from their associated names, dates, or IDs. For structured records, remove complete rows only after verifying they are empty.
- Selecting entire columns or the whole sheet unnecessarily. A broad selection can include notes, totals, or other data far from the visible table. Limit the selection to the specific data range you intend to clean.
- Confusing deleting with clearing. Pressing Delete or choosing Clear Contents removes cell contents but leaves the empty cells in place. The rows and columns do not move, so gaps remain. Use Delete Cells when you want surrounding cells to shift.
- Assuming a cell that looks empty is truly blank. A formula that displays an empty string, a cell containing spaces, or a cell with formatting can look blank but behave differently. Go To Special > Blanks selects empty cells, not cells containing formulas or spaces.
- Cleaning a filtered list without checking what is selected. A filter hides rows; it does not remove them. Before deleting, confirm that the visible TRUE rows are the records you intend to remove and that the command targets sheet rows rather than just clearing cell contents.
Tips
- Keep records together. For data where each row is one record, avoid shifting individual cells within the table. Filter out fully empty rows and delete those complete rows instead.
- Make a backup before a large cleanup. Ctrl+Z can reverse a recent deletion, but it is not a substitute for a saved copy, especially after you have made several other changes.
- Use a small test range first. If you are uncertain whether to shift up or left, try the operation on a copied sample. Check that the result preserves the order and relationships you need.
- Check for spaces when apparent blanks are missed. Click a cell that looks empty and look at the formula bar. If it contains spaces or an apostrophe, it is not an empty cell. Remove the unwanted contents deliberately rather than assuming Go To Special will select it.
- Turn a data range into a table when appropriate. Excel tables make filtering and reviewing records easier. They do not decide which rows are safe to delete, though; inspect the record first and remove whole rows when appropriate.
- Keep a record of the columns used for your blank-row test. If a table has data outside A:D, a helper formula checking only A:D might classify a row as empty even though another column contains information. Include every column that defines a non-empty record.
Troubleshooting
Go To Special is unavailable or difficult to find: Confirm that a worksheet range is selected, then look on the Home tab under Find & Select. Ribbon layouts differ between desktop and web editions. If you are using Excel for the web and cannot access the command, use the desktop app or filter with a helper column.
No cells are selected: The chosen range may have no truly empty cells. Look for formulas that return an empty display, spaces, or other hidden contents. Click a suspected cell and inspect the formula bar. If it contains a formula or characters, it is not a blank for this command.
Values moved into the wrong columns: Undo immediately with Ctrl+Z on Windows, or use the Undo command on the Quick Access Toolbar. Reopen Go To Special with a narrower selection. If the values belong to complete records, use the helper-column method to remove fully empty rows rather than shifting individual cells.
Some empty rows remain: Check whether the rows are empty across all columns included in your selection or helper formula. Also look for spaces or formulas that return an empty string. Decide whether those cells should count as empty for your task, and verify each row before deleting it.
The filtered results include rows you want to keep: Cancel or undo the deletion, then inspect the helper formula’s range. It must cover all columns that can contain data. For example, if your table runs from A to F, checking only A:D can mark a row TRUE even when E or F contains a value. Update the range, fill the corrected formula down, and review the filtered results again.
Undo no longer restores the original layout: Excel’s undo history can be lost after some actions, closing the workbook, or making many subsequent edits. Reopen the saved copy if you made one. If the file is stored in OneDrive or SharePoint, check its version history for an earlier version.
Frequently Asked Questions
How do I delete blank cells and move everything up?
Select the range, choose Home > Find & Select > Go To Special > Blanks, then choose Home > Delete > Delete Cells > Shift cells up. Use this only when the cells below can safely move upward without separating related data.
How do I delete blank rows in Excel?
To remove rows that are completely empty, use a helper column that checks all the data columns, filter for rows with no entries, review them, and delete those worksheet rows. Do not delete a whole row just because one field is missing.
Does pressing Delete remove blank cells?
No. The Delete key generally clears contents while keeping the cell and its position. To close a gap, use Delete Cells and select a shift direction, or delete the entire row if the whole record is empty.
Why does Go To Special skip cells that look blank?
Those cells may contain a formula that displays nothing, spaces, or other content. Go To Special > Blanks selects genuinely empty cells. Inspect the formula bar to find content that is not visible in the grid.
Can I delete blank cells in Excel without affecting other columns?
Yes, select only the column or range you intend to compact and choose the appropriate shift direction. However, if columns are parts of the same records, shifting one column alone can misalign the data. Use whole-row deletion only for rows confirmed to be empty.
Can I use this method in Excel for the web?
Go To Special may not be available in the web app. Open the workbook in desktop Excel for that selection command, or identify fully blank rows with a helper column and filter if those tools are available in your web version.
Related
- How to Copy Multiple Sheets in Excel Without Errors
- How to Copy Only Filtered Data in Excel Without Hidden Rows
- How to Copy Only Selected Cells in Excel Without Extra Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Schedule an Email Reminder in Outlook: Step-by-StepTo get a reminder to follow up on an Outlook email, flag the message and set a reminder…
- How to Schedule an Email in Outlook on Desktop and MobileTo schedule an email in Outlook, write your message, choose the arrow beside Send, select Schedule send or…
- Copy Only Visible Cells in Excel Without Hidden DataTo copy only visible cells in Excel for Microsoft 365 on Windows, select the range, choose Home >…
- How to Change Pane View in OutlookTo change the pane view in Outlook, open View, choose Layout if your version shows it, then select…
- Calculate Age Between Two Dates in ExcelTo calculate a person’s completed age in years between two dates in Excel, enter the birth date in…
- Circular Cell Reference in Excel: Causes and FixesA circular cell reference in Excel occurs when a formula refers to its own cell, either directly or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.