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.

How to Clear Multiple Cells in Excel

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:

  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.

clearing cells using the context menu

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:

  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: 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.

    Selecting Multiple Cells

Based on the selection, the cells will be cleared.

Output after Clearing Cell Formats

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:

  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.
      Replacing by Columns
    • 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.
      Changing Format of Cells
  3. Click on Replace All in the Find and Replace dialog box.
    Giving Replace All Command
  4. Finally, click OK in the message box containing the number of changes made.
    Output of Deleting Multiple Cells

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

Read More: How to Clear Cell Contents Based on Condition in Excel

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

  1. Press Alt+F11 to open the Microsoft Visual Basic for Applications window.
    Or, go to the Developer > Code group > Visual Basic.
    Go to Visual Basic Option
  2. Click on Insert Module.
    Openning a module
  3. Write the following VBA code and close the window.
    Sub ClearMultipleCells()
    Selection.Delete shift:=xlShiftUp
    End Sub

    VBA Code for Deleting Multiple Cells

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

Step 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.Inserting Shapes
  3. Adjust the size and text of the button.Creating a Button

Step 3: Assigning Macro to the Button

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

Step 4: Using the Button to Clear Cells

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

Clicking on 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.

Output after Clearing

Read More: How to Clear Contents in Excel Without Deleting Formatting

Download Practice Workbook

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


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!
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF