How to delete empty cells in excel. 3 Methods to Remove Blank Cells in Excel

When transferring a table from an external source to Excel, situations often arise with the shift of cells with information and the formation of voids. When using formulas, further work is not possible. In this regard, the question arises: how can you quickly remove empty cells?

Cases in which it is possible to delete blank cells

During the operation, data shift may occur, which is not desirable. Removal is performed only in some cases, for example:

• There is no information in the entire row or column.
• There is no logical connection between cells.

The classic method for removing voids is one element at a time. This method is possible if you work with areas that need minor adjustments. The presence of a large number of empty cells leads to the need to use the batch deletion method.

Solution 1: delete by selecting a group of cells

The easiest way is to use a special tool for selecting groups of cells. Execution process:

1. Select the problem area where empty cells have accumulated, then press the F5 key.

2. The screen should open the following command window. Click on the interactive Select button.
3. The program will open another window. Select “Empty Cells”. Check the box and click OK.
4. There is an automatic selection of unfilled places. Right-clicking on any non-information area activates the opening of a window where you need to click “Delete”.
5. Next, “Delete Cells” will open. Put a tick next to “Cells with a shift up.” We agree by pressing the “OK” button.

6. As a result, the program will automatically remove the places that need to be corrected.
7. To remove the selection, click LMB anywhere in the table.

Note! The deletion method with a shift is selected only in cases where there are no lines after the selection area that carry any information.

Solution 2: Apply Filtering and Conditional Formatting

This method is more complicated, therefore, before proceeding with the implementation, it is recommended that you first familiarize yourself with the detailed plan for the implementation of each action.

Attention! The main disadvantage of this method is that it is used to work with a single column that does not contain formulas.

Consider a sequential description of data filtering:

1. Select an area of ​​one column. Find the item “Editing” on the toolbar. By clicking on it, a window with a list of settings will appear. Go to the “Sort and Filter” tab.

2. Select the filter and activate LMB.

3. As a result, the top cell is activated. A square-shaped icon with a down arrow will appear on the side. This indicates the possibility of opening a window with additional functions.
4. Click on the button and in the tab that opens, uncheck the box next to the “(Empty)” position, click “OK”.

5. After the done manipulations, only filled cells will remain in the column.

Expert advice! Removing voids using filtering is only suitable if there are no filled cells around, otherwise, when performing this method, all data will be lost.

Now let’s look at how to perform conditional formatting along with filtering:

1. To do this, select the problem area and, having found the “Styles” toolbar, activate the “Conditional Formatting” button.

2. In the window that opens, find the line “More” and follow this link.
3. Next, in the window that appears in the left field, enter the value “0”. In the right field, select the color fill option you like or leave the default values. We click “OK”. As a result, all cells with information will be painted in the color you choose.
4. If the program removes a previously made selection, we make it again and turn on the “Filter” tool. Hover over the value “Filter by cell color” or by font and activate one of the positions.
5. As a result, only cells that are colored with color, and therefore filled with data, will remain.

6. Re-select the zone colored with color and find the “Copy” button at the top of the toolbar, press it. It is represented by two sheets superimposed on each other.
7. By selecting another area on this sheet, we make another selection.
8. Right-click to open the menu, where we find “Values”. The icon is presented in the form of a tablet with the digital enumeration 123, click.

Note! When selecting a zone, it is necessary that the upper part is located below the bottom line of the highlighted list.

9. As a result, the copied data is transferred without applying a color filter.

Further work with data can be done locally or by transferring it to another area of ​​the sheet.

Solution 3: apply the formula

Removing empty table cells in this way has some difficulties and is therefore less popular. The difficulty lies in using the formula, which must be stored in a separate file. Let’s go through the process in order:

1. Select the range of cells that need to be adjusted.
2. Then we right-click and find the command “Assign a name.” Assign a name to the selected column, click OK.

4. In any place on the sheet, select the free zone, which corresponds to the size of the area where the adjustment is made. Right-click and enter a different name.

5. After you need to activate the topmost cell of the free area and enter the formula into it: =IF(ROW() -ROW(Adjustment)+1>NOTROWS(LastNames)-COUNTBLANK(LastNames);””;INDIRECT(ADDRESS(LOW((IF(LastNames<>“”,ROW(LastNames);ROW() + ROWS(Surnames)));ROW()-ROW(Adjustment)+1);COLUMN(Surnames);4))).

Note! The names for the areas are chosen arbitrarily. In our example, these are “Surnames” and “Adjustment”.

6. As soon as these formulas are entered, press the key combination “Ctrl + Shift + Enter”. This is necessary because there are arrays in the formula.

Stretch the top cell down to the borders of the previously defined area. A column with the transferred data should be displayed, but without empty cells.

Conclusion

Removing empty cells is possible in several ways, each of them differs in the level of complexity, so that both an inexperienced and an advanced spreadsheet user can choose the most suitable option for themselves.