How to Clear Multiple Cells in Excel (4 Ways)

Clearing multiple cells in Excel defines the removal of contents, formatting, comments, notes, hyperlinks, and so on from cells in Excel. As part of organizing Excel data in the shortest possible time, we select multiple cells at a time and clear the contents on those cells.

In this Excel tutorial, you will learn different ways to clear multiple cells in Excel using the context menu, Find and Replace feature, Clear options, etc.

In the image below, we have cleared the data in the range B7:G12 with the Clear Content command from the context menu.

Here are the 4 ways to clear multiple cells in Excel:

Using Context Menu

You can clear multiple cells/ranges using the Clear Contents command in the context menu. This will delete the cell data but keep the cell formatting.

To clear cells using the context menu, follow the steps below:

Select the cells/ranges to clear.
Right-click on the selection for the context menu.
Select the Clear Contents command.

This will clear all the cell data in the selected cells.

Shortcut: After selecting the cells/range, pressing the Delete or Backspace button on the keyboard also clears the content. This works like the Clear Contents command.

Using Clear Options

If you want to clear any specific cell element such as formatting, contents only, hyperlinks, or comments, you can use these Excel-provided options. This is efficient to clear only one element keeping the others.

For example, we will clear formatting in selected cells in the dataset. There are several formatting in the table such as number format, fill color, cell border, etc.

To clear cells using the Clear options, follow the steps below:

Select the cells to clear.
Go to the Home tab > Editing group > Clear drop-down.
From the Clear dropdown, you can select any of the commands:
- Clear All: Clears everything in the selected cells including formatting, content, links, etc.
- Clear Formats: Clears the cell formatting only keeping the contents.
- Clear Contents: Clears the cell contents keeping the formatting.
- Clear Comments and Notes: Clears any comments and notes in the selected cells.
- Clear Hyperlinks: Clears any hyperlinks in the selected cells.

Based on the selection, the cells will be cleared.

Note: If you want to remove cells/range from the worksheet and fill up the blanks with nearby cells, select the cells to delete > right-click on the selection > Delete. In the Delete dialog box, select how you want to fill up those cells and click on OK.

Read More: How to Clear Excel Temp Files

Using the Find and Replace Feature

If you want to clear specific data in an entire worksheet and it’s time-consuming to manually search for it, you can use the Find and Replace feature in such cases. Replacing any data with blank or nothing will clear the cells.

While clearing data using the Find and Replace feature, you can also change the formatting and other cell properties. However, if no format is specified, it will keep the existing format in the cell but delete the data only.

In this example, we want to clear the cells having the data “Eric Hoffmann” in them and highlight the cleared cells with color to locate them.

To clear cells with the Find and Replace feature, follow the steps below:

Press Ctrl+H to open the Find and Replace dialog box.
In the Find and Replace dialog box:
- Write the data to be cleared in the Find what box.
- Keep the Replace with box empty.
- Change the Search option to By Columns if needed.
- Click on Format.
  As a result, the Replace Format dialog box will appear.
- Select a color to highlight the cleared cells.
- Click on OK to close the dialog box.
Click on Replace All in the Find and Replace dialog box.
Finally, click OK in the message box containing the number of changes made.

This will clear all cells with specific data and highlight them with a color.

Using VBA Code

When working with a large amount of data, it might be inefficient to manually select and clear the cells. In such cases, we can use the VBA code to develop a macro that will clear selected data. We can also make this process easier by creating a button with the macro for a one-click solution.

In this example, we will create a button with a VBA macro to clear selected cells and use it.

To clear cells with VBA code, follow the steps below:

Step 1: Creating the VBA Macro

Press Alt+F11 to open the Microsoft Visual Basic for Applications window.
Or, go to the Developer > Code group > Visual Basic.
Click on Insert > Module.
Write the following VBA code and close the window.
```
Sub ClearMultipleCells()
Selection.Delete shift:=xlShiftUp
End Sub
```
Save the macro and return to the Excel window.

Step 2: Creating the Button

Go to the Insert tab > Illustration group > Shapes drop-down.
Select a suitable shape to create a button from the dropdown.
Adjust the size and text of the button.

Step 3: Assigning Macro to the Button

Select the button > right-click on it.
Select the Assign Macro… option from the context menu.
In the Assign Macro dialog box, select the previously created macro.
Click on OK to assign the macro to that button.

Step 4: Using the Button to Clear Cells

Select the range or cells to be cleared and click on the Button.

This will clear the cells based on the operations mentioned in the VBA code. You can modify the VBA code to clear specific cell properties as well.

Download Practice Workbook

You can download the practice workbook from the following download button.

Clear Multiple Cells.xlsm

Conclusion

In this article, we have discussed different ways to clear multiple cells that are both adjacent and non-adjacent with the use of the Delete, Find and Replace, and Clear commands. We also discussed the way to create a dedicated button to create a one-click solution using the VBA code. If you have any queries regarding this topic, please leave your questions in the comment section. Thank you!

Frequently Asked Questions

How do I clear multiple blank cells at once in Excel?

Select the Range with blank cells > Press F5 > click on Special. In the Go To Special dialog box, choose the option for Blanks and click OK. This will select the blank cells in that range. Right-click on any of the selected blank cells, and select Delete from the context menu. Select the required action in the Delete dialog box and click OK.

Why can’t I clear multiple cells in Excel?

There may be multiple reasons: permission issues, merged cells, data validation, cell locking, etc. Check if any of these criteria are applied to the selected cells or not. Try unlocking the cells or remove the conditional validation and try again.

Is there a keyboard shortcut to clear the formatting of cells in Excel?

Select the cell range/cells and on the keyboard, press Alt + H > E > F. This will clear the formatting of the selected cells.

Related Articles

<< Go Back to Clear Contents in Excel | Entering and Editing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!