How to Clear Multiple Cells in Excel: 4 Ways

Using Context Menu

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

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

Using Clear Options

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

   

Based on the selection, the cells will be cleared.

Using the Find and Replace Feature

1. Press Ctrl+H to open the Find and Replace dialog box.
2. 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 Format.
     The Replace Format dialog box will appear.
   • Select a color to highlight the cleared cells.
   • Click OK to close the dialog box.
     
3. Click Replace All in the Find and Replace dialog box.
   
4. 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

Method 1 – Creating the VBA Macro

1. Press Alt+F11 to open the Microsoft Visual Basic for Applications window.
   Or, go to the Developer > Code group > Visual Basic.
   
2. Click Insert > Module.
   
3. Write the following VBA code and close the window.

   Sub ClearMultipleCells()
   Selection.Delete shift:=xlShiftUp
   End Sub

   

4. Save the macro and return to the Excel window.

Method 2 – Creating the Button

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

Method 3 – Assigning Macro to the Button

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

Method 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 also modify the VBA code to clear specific cell properties.

Download Practice Workbook

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

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 whether any of these criteria are applied to the selected cells. 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 press Alt + H > E > F on the keyboard. This will clear the formatting of the selected cells.

Related Articles

• How to Clear Recent Documents in Excel
• How to Clear Contents in Excel Without Deleting Formulas
• How to Clear Cells with Certain Value in Excel

<< Go Back to Clear Contents in Excel | Learn Excel