How to Clear Cells with Certain Value in Excel (2 Ways)

Clearing cells with a certain value means finding a specific value and erasing the cell content. While working in a large data set, clearing certain values by going through the cells one by one is time-consuming and tedious. You can face it for the same values or multiple types of values.

In this Excel tutorial, we will clear cells with a certain value in Excel using the Find and Replace feature and VBA code.

Here we have a dataset containing First Name, Last Name, and Age. There are 3 cells containing “Wood”. We cleared those cells by using the Find & Replace feature.

Overview of clearing cells with certain value in Excel

Here are the 2 ways to clear cells with certain values in Excel:

Using Find and Replace Feature

The Find & Replace feature of Excel can look for a certain value after giving input and then will either replace them one by one or all of them at once. So the drawback of using it is- it can’t handle multiple type values at a time. Now, let’s clear cells containing “Wood” from the following dataset using this feature.

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

  1. Go to the Home tab > Editing group > Find & Select > Replace.
    Or, press Ctrl+H to open the Find and Replace box.Opening Find and Replace tool
  2. In the ‘Find What‘ box, insert the value that you want to clear.
  3. Keep the ‘Replace with‘ box empty.
  4. Click on ‘Replace All‘.

All the cells containing the certain value will be cleared and a notification box will show the count.

Replacing certain values using Find and Replace tool

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

Using VBA Code

When you are working with a large worksheet and several sheets, you can use VBA to clear cells with certain values rapidly.

Here are the steps to clear certain values using VBA:

  1. Go to the Developer tab > Visual Basic. Or, you can press Alt + F11.Opening VBA window
  2. Click on Insert tab > Module to insert a new module.Inserting new module in the VBA window
  3. Copy the following code and paste it into the Module:
    Option Explicit
    Sub Clear_Cells_with_Certain_Value()
    Dim xRange As Range
    Dim eRange As Range
    Set xRange = Worksheets("VBA").Range("B5:C10")
    For Each eRange In xRange
        If eRange.Value = "Wood" Then
        End If
    End Sub
  4. Press F5 or the Run button to execute the code.Inserting VBA Code to Clear Cells with Certain Value in Excel

After running the code, the cells containing the specified value will be cleared from the specified range.

Showing final result after applying VBA code to clear cells with certain value in Excel

Read More: How to Clear Multiple Cells in Excel

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


The Find and Replace tool will particularly help you to clear values one by one and the VBA method will be effective for large and multiple sheets. Overall, the two straightforward methods will enable you to maintain accuracy and enhance your data management efficiency, ultimately contributing to a more organized and effective workflow. Applying this skills will undoubtedly save time and streamline your Excel experience.
Please share any further queries or recommendations with us in the comments section below.

Frequently Asked Questions

Can I clear cells based on a condition in Excel?

Yes, you can use conditional formatting to highlight cells that meet specific criteria, and then manually clear or delete those highlighted cells.

Can I use a formula to clear cells with certain values?

No, Excel formulas are primarily designed for calculations and data manipulation, not for directly clearing or deleting cells. However, you can use formulas to mark cells for deletion and then perform the deletion manually.

How do I clear cells with certain values across multiple sheets in Excel?

To clear cells with specific values across multiple sheets, you can use the Find and Replace feature individually on each sheet or use VBA (Visual Basic for Applications) for a more automated approach.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF