How to delete cells in excel. Delete scattered and empty cells, 3 ways to delete a cell in Excel

In the process of working with Excel documents, users regularly need to not only insert cells, but also delete them. The process itself is quite easy, but there are certain ways to implement this procedure that can speed up and simplify it. In this article, we will examine in detail all the methods for removing cells from a document.

Procedure for deleting cells

The considered elements of the table can be of 2 types: those that contain information and empty ones. With this in mind, the process of deleting them will differ, since the program itself provides the option of automating the procedure for selecting and further deleting unnecessary cells.

It should also be said here that in the process of deleting one or more elements of the table, the information in them may change its own structure, since as a result of the steps taken, some parts of the table may be displaced. In this regard, before deleting unnecessary cells, it is necessary to consider the adverse consequences and, for safety, make a backup copy of this document.

Important! In the process of deleting cells or several elements, and not entire rows and columns, the information inside the Excel table is shifted. Therefore, the implementation of the procedure in question must be carefully considered.

Method 1: context menu

First, you need to consider the implementation of the procedure in question through the context menu. This method is one of the most common. It can be used for filled cells and for empty table elements.

1. It is necessary to select 1 cell or several elements to be deleted. Clicking on the selection with the right mouse button. Next, you should launch the context menu. In it, you need to select the “Delete …” checkbox.
   

   

2. A window with 4 functions will be displayed on the monitor. Since we need to remove the cells directly, and not entire rows or columns, then 1 of 2 actions is selected – to remove elements with an offset to the left side or with an offset up. The choice of action should be based on the specific tasks facing the user. Then, when a certain option has been selected, the action is confirmed by pressing the “OK” key.
   

   

3. As planned, all marked elements are removed from the document. The 2nd option (shift up) was chosen, because the group of cells that is under the marked area was shifted up by as many lines as were in the selected gap.
   

   

4. If you choose the 1st option (shift to the left), each cell that is to the right of the deleted ones will be shifted to the left. This option would be optimal in our situation, since there were empty elements to the right of the specified range. In view of this, outwardly it seems that the information of the marked interval was simply cleared while maintaining the integrity of the document structure. Although, in fact, a similar effect is achieved directly due to the fact that the elements of the table that replaced the initial ones do not have data in them.
   

   

Method 2: Ribbon Tools

You can also delete cells in Excel tables using the tools provided on the ribbon.

1. Initially, you need to mark in some way the element that you want to delete. Then you should switch to the main tab and click “Delete” (located in the “Cells” menu).
   

   

2. Now you can see that the checked cell has been removed from the table, and the elements below it have moved up. In addition, it should be emphasized that this method will not allow you to determine the direction in which the elements will be displaced after the removal itself.
   

   

When it becomes necessary to remove a horizontal group of cells using this method, it is worth taking note of the following recommendations:

• A range of horizontal cells is selected. Click “Delete” on the “Home” tab.
  

  

• As in the previous case, the specified elements are removed with an upward offset.
  

  

When a vertical group of cells is removed, the shift occurs in the other direction:

• A group of vertical elements is highlighted. Click on “Delete” on the ribbon.
  

  

• You can see that at the end of this procedure, the marked elements are deleted with a shift to the left.
  

  

Now that the basic operations have been covered, it is possible to use a more flexible method for removing elements. It involves working with tables and ranges of horizontal and vertical cells:

• The required data interval is highlighted and the delete button located on the ribbon is pressed.
• The selected array is removed and adjacent cells are shifted to the left.

Important! Using the Delete key found on the tool ribbon will be less functional than deleting via the context menu, as it does not allow the user to adjust cell offsets.

Using the tools on the ribbon, it is possible to remove elements by preselecting the direction of the shift. You should study how this is implemented:

• The range to be deleted is highlighted. However, now in the “Cells” tab, it is not the “Delete” button that is clicked, but the triangle, which is located on the right side of the key. In the pop-up menu, click “Delete Cells…”.
  

  

• Now you can notice a window already appearing with options for deleting and shifting. The one that will suit specific purposes is selected, and the “OK” key is pressed to get the final result. For example, it will be an upward shift.
  

  

• The removal process was successful, and the shift occurred directly upwards.
  

  

Method 3: using hotkeys

It is also possible to carry out the procedure in question using a set of hotkey combinations:

1. Select the range in the table that you want to delete. Then you need to press the combination of buttons “Ctrl” + “-” on the keyboard.
   

   

2. Then you need to open the already familiar window for deleting cells in the table. The desired offset direction is selected and the OK button is clicked.
   

   

3. As a result, you can see that the selected cells are deleted with the offset direction specified in the last paragraph.
   

   

Method 4: Removing disparate elements

There are situations where you want to delete multiple ranges that are not considered contiguous, located in different places in the document. They can be removed using one of the above methods, by manipulating each cell separately. However, this often takes quite a lot of time. There is an option to remove scattered elements from the table, which helps to cope with the task much faster. However, for this purpose, they must first be identified.

1. The first cell is selected by the standard method, holding down the left mouse button and circling it with the cursor. Next, you need to hold down the “Ctrl” key and click on the remaining scattered elements or circle the ranges using the cursor with the left mouse button pressed.
2. Then, when the necessary cells are selected, it is possible to carry out the removal by any of the above methods. After that, all the necessary cells will be deleted.
   

   

Method 5: Deleting Empty Cells

When a user needs to delete empty cells in a document, it is possible to automate the procedure in question and not select each of the elements individually. There are several options for solving the problem, but the easiest way to do this is through the selection tool.

1. A table or some other range on the sheet is selected where deletion is required. After that, the function key “F5” is clicked on the keyboard.
   

   

2. The transition window is enabled. In it, you need to click the “Select …” button, which is located at the bottom left.
   

   

3. Then a window for selecting groups of elements will open. In the window itself, the switch is set to the “Empty Cells” position, and then the “OK” button is clicked at the bottom right.
   

   

4. After that, you can notice that after the last action, empty cells in the marked range will be highlighted.
   

   

5. Now the user will only have to carry out the removal of the cells in question by any of the options indicated above.

Method 1. Rough and fast

To delete unnecessary cells in an Excel table in a similar way, follow these steps:

1. Select the desired range.
2. Then the functional button “F5” is pressed, after the “Select (Special)” key. In the menu that appears, select “Blanks” and click “OK”. Then all empty elements in the range should be selected.
   

   

3. After that, the menu gives a command to delete the specified elements of the RMB table – “Delete Cells (Delete Cells) with an upward shift”.

Method 2: Array Formula

To simplify the procedure for deleting unnecessary cells in a table, you should assign names to the required working ranges using the “Name Manager” on the “Formulas” tab, or – in Excel 2003 and older – “Insert window” – “Name” – “Assign”.

For example, the range B3:B10 will have the name “IsEmpty”, the range D3:D10 – “NoEmpty”. The gaps must have the same size, and can be located anywhere.

After the performed operations, the first element of the second interval (D3) is selected and the following formula is entered: =IF(ROW()-ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);”«;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“«;ROW(ThereEmpty);ROW() + ROWS (There are Empty))); LINE ()-ROW (No Empty) + 1); COLUMN (There are Empty); 4))).

It is entered as an array formula, after inserting, you need to click “Ctrl + Shift + Enter”. After that, the formula in question can be copied down using automatic filling (a black plus sign stretches in the bottom right corner of the element) – after this, the original range will be obtained, but without empty elements.

Method 3. Custom function in VBA

When the user has to regularly repeat the operation in question to remove unnecessary cells from the table, it is recommended to add such a function to the set once and use it in each subsequent case. For this purpose, the Visual Basic Editor is opened, a new empty module is inserted, and the text of the function is copied.

It is important not to forget to save the file and return from the Visual Basic Editor to Excel. To use the function in question in a specific example:

1. The required range of empty elements is highlighted, for example F3:F10.
2. Open the “Insert” tab, then “Function”, or press the “Insert Function” button in the “Formulas” section in the new version of the editor. In User Defined mode, NoBlanks is selected.
3. As a function argument, specify the initial range with spaces (B3:B10) and press “Ctrl + Shift + Enter”, this will allow you to enter the function as an array formula.

Conclusion

Based on the article, a considerable number of methods are known, using which it is possible to delete unnecessary cells in Excel tables. It should be noted that the implementation of many of them is similar, and in some situations the procedure is actually identical. Therefore, users can choose the method that will enable them to solve a specific problem more quickly and efficiently. In addition, the editor directly for the function of deleting table elements provides “hot buttons” that allow you to save time on the operation in question. In addition, when the document contains empty cells, there is no need to select each separately for further deletion. For these purposes, it is possible to use the grouping tool, which automatically selects elements that do not contain data. After that, the user only needs to delete them by any of the above methods.