Skip to main content

Easily remove blank cells in Excel – Full tutorial

Removing blank cells in Excel is a common task that can help streamline your data, making it easier to analyze, understand, and present. Blank cells can disrupt your data analysis, cause errors in formulas, and make your datasets look incomplete or unprofessional. In this tutorial, we'll explore several methods to efficiently remove or manage blank cells in Excel, such as Go To Special feature, formulas, Filter function. Each method serves different needs and scenarios, so you can choose the one that best fits your requirements.

Remove blank cells with Go To Special feature

Extract all data ignoring blank cells with formulas

Copy and paste data from a list ignoring blank with Filter feature

Delete empty cells after the last cell with data

Additional Operations:


Remove blank cells with Go To Special feature

The Go To Special feature in Excel is a powerful tool for quickly selecting and managing specific types of cells, including blanks. Here's how you can use it to remove blank cells:

Note: To ensure your data's security, it's crucial to create a backup of your worksheet before using this method.

Step 1: Apply the Go To Special feature to select all blank cells

  1. Select the data range where contains blank cells you want to remove.
  2. Then, click Home > Find & Select > Go To Special, see screenshot:
  3. In the Go To Special dialog box, select Blanks and click OK.
  4. Excel will now highlight all blank cells in your selected range. See screenshot:

Step 2: Delete blank cells

  1. With the blank cells selected, right-click on one of the highlighted cells, choose Delete from the context menu, see screenshot:
  2. And then, in the Delete dialog box, based on your data arrangement, decide whether to move cells leftward or upward, then confirm your choice by clicking OK. In this example, I will choose Shift cells up, see screenshot:

Result:

Now, the blank cells in the selected range have been removed successfully, see screenshot:

Considerations for Using the Go To Special:
  • If your data is structured in a way where columns and rows are interdependent, indiscriminately deleting blank cells can disrupt the relationship between data points. In such cases, it's safer to remove entire blank rows instead of individual blank cells. To delete entire blank rows, please view this 6 easy ways to remove blank rows article.
  • It is not possible to delete any individual cells in an Excel table directly. If you're working within an Excel table, consider converting it to a range.
  • Deleting blank cells can affect formulas that reference a range of cells, as well as named ranges. This can lead to errors or incorrect calculations if the formulas or named ranges expect a continuous block of data.
  • Before using this feature to delete blank cells, make sure to back up your worksheet. This ensures that you can restore your original data in case the deletion process does not go as planned or if it adversely affects your dataset.

Extract all data ignoring blank cells with formulas

You can use formulas to extract and list data from a range, ignoring any blank cells. This method is useful when you want to create a clean list without altering the original data set.

Step 1: Select the data range

  1. Please enter or copy the following formula into a blank cell where to put the data list:
    =IFERROR(INDEX(A$2:A$11,SMALL(IF(A$2:A$11<>"",ROW(A$2:A$11)),ROWS(A$2:A2))-1),"")
  2. Then, press Ctrl + Shift + Enter keys simultaneously to get the first result. Then, copy the formula down to the necessary number of cells to ensure that all non-blank items from your list are included. See screenshot:
  3. Next, continue by dragging the fill handle to the right to extract items from additional columns. See screenshot:
Tips: Formula for Excel 365 / 2021

In Excel 365 or Excel 2021, you can extract all data while ignoring blank cells using the Filter function, this is much simple than the above formula.

  1. Please enter or copy the following formula into a blank cell and press the Enter key. The result will then automatically spill over into the adjacent cells. See screenshot:
    =FILTER(A2:A11, A2:A11<>"")
  2. And then, select the first formula cell, drag the fill handle to the right to extract items from additional columns, see screenshot:

Copy and paste data from a list ignoring blank with Filter feature

The Filter feature in Excel can be used to hide blank cells temporarily, allowing you to copy and paste only the cells that contain data.

Step 1: Apply the Filter feature

  1. Click on the header of the column that contains blanks you wish to ignore.
  2. Then, click Data > Filter, this will add a dropdown arrow in the header cell, see screenshot:
  3. Click the dropdown arrow, uncheck the (Blanks) option, and then, click OK. See screenshot:
  4. Now, all blank cells in this column are hidden at once, see screenshot:

Step 2: Copy and paste the data list

Select the visible cells, copy them by pressing Ctrl+C, and paste (Ctrl+V) them into a new location without the blanks. See screenshot:

Note: Use the Filter feature to copy and paste non-blank cell values is suitable for single-column data. If you have data spanning multiple columns, you will need to remove the filter from the current column, then reapply the filter to other columns. This ensures that you can efficiently extract all non-blank cell values from each column, allowing for comprehensive management and analysis of multi-column data.

Delete empty cells after the last cell with data

Blank cells in Excel that look empty but have hidden formatting or unseen characters can cause unexpected issues. For instance, they can make your Excel file much larger than it should be, or cause blank pages to print out. To avoid these problems, it's a good idea to get rid of these empty rows and columns, especially if they have formatting, extra spaces, or characters you can't see.

To thoroughly clear all content and formatting that is located after the last cell with data in your Excel worksheet, follow these steps:

Step 1: Select and delete blank columns

  1. Click to select the first blank column to the right of your data. Then, Press Ctrl + Shift + End. This will select a range of cells from the current position to the last used cell on the worksheet.
  2. Then, right-click on the selected columns, choose Delete, and select Entire column in the Delete dialog box. And then, click OK. See screenshot:

Step 2: Select and delete blank rows

  1. Click to select the first blank row below your data. Again, press Ctrl + Shift + End to select all blank rows below your data up to the last used cell.
  2. Then, right-click on the selected rows, choose Delete, and select Entire row in the Delete dialog. And then, click OK. See screenshot:

Step 3: Save the workbook

Press Ctrl + S to save the changes you've made to your workbook.Now, any unnecessary content and formatting that lies beyond the active data in your sheet has been eliminated at once.


Additional Operations

In addition to deleting blank cells, you might sometimes need some other operations for the blank cells. Such as highlighting them for easy identification and filling them with a specific value, such as 0 or any other value. This section will introduce how to perform these additional operations in Excel.

Highlight blank cells

Highlighting blank cells makes them easily visible, which is especially helpful in large datasets. You can use Excel's Conditional Formatting feature to achieve this.

  1. Select the data range that you want to highlight the blank cells. And then click Home > Conditional Formatting > New Rule, see screenshot:
  2. In the New Formatting Rule dialog box, do the following operations:
    1. Select Format only cells that contain from the Select a Rule Type list box; 
    2. Choose Blanks from the Format only cells with drop-down list;
    3. Finally, click Format button.
  3. In the Format Cells dialog box, under the Fill tab, choose one color to highlight the blank cells you need, see screenshot:
  4. Then, click OK > OK to close the dialogs. And now, the blank cells are highlighted as following screenshot shown:

Fill blank cells with 0 or any other specific value

Filling blank cells in Excel with a specific value, such as 0, can be a tedious task, especially when dealing with large datasets. Fortunately, Kutools for Excel simplifies this process, offering an efficient and user-friendly way to quickly fill blank cells with any value you choose. With its Fill Blank Cells feature, you can easily accomplish the following operations: (Click to download Kutools for Excel Now!)

  • Fill blank cells with 0 or any other specific value

  • Fill blank cells with a series of linear values

  • Fill blank cells with value above/down/left/right


    By following these methods and considerations, you can efficiently remove or manage blank cells in Excel, making your data cleaner and more accessible for analysis. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!


    Related Articles:

    • Fill blank cells with value above or 0 in Excel
    • In this guide, we will focus on how to fill blank cells either with the value in the cell directly above or with 0 or any other specific value. This can be incredibly useful for handling data sets that contain empty cells, ensuring that calculations and visualizations are accurate and meaningful.
    • Check if cell or range is blank or not in Excel
    • Dealing with blank cells or ranges in Excel can be a crucial part of data management and analysis. Whether you need to identify, fill, or skip blank cells, understanding how to check for them efficiently is essential. This guide provides simple yet effective methods to determine if a cell or range is blank in Excel, with practical tips to enhance your data handling skills.
    • Auto fill serial numbers and skip blanks in a list
    • If you have a list of data which contains some blank cells, now, you want to insert serial numbers for the data, but skip the blank cells as below screenshot shown. In this article, I will introduce a formula for solving this task in Excel.