Today, you’ll learn how to delete highlighted cells in Microsoft Excel using proven methods!

Deleting highlighted cells in Excel might seem simple at first, but it’s trickier than it looks. Think of it like filtering out outdated contacts from a massive client database. If you don’t do it right, you might delete the wrong entries or leave behind empty gaps.

If you’ve ever tried to delete highlighted cells manually, you know how frustrating it can be. But don’t worry! I’ll walk you through the best ways to do it effortlessly. By the end of this Microsoft Excel tutorial, you’ll master this skill effortlessly.

Using the Right Click Context Menu

If there are only a few highlighted cells in your worksheet you can use this manual method to delete those easily.

Select the cell, cell range, row, or column that contains the highlighted cells you wish to delete.

Right-click and select the Delete option from the context menu.

You’ll see the Delete dialog box with the following options:

• Shift cells left: Excel moves the data from the right of the deleted cells to the left, filling the empty space.
• Shift cells up: It moves the data below the deleted cells upward to fill the gap.
• Entire row: Excel deletes the entire row containing the selected cells.
• Entire column: It deletes the entire column (or multiple columns) containing the selected cells.

According to the real-time scenario you need to choose any of the options from the above.

In most cases, you must be using the Shift cells left option since you wouldn’t want to disturb the existing structure of the data.

Sometimes, you might need to manually copy and paste the shifted cells to the right, in their original positions if there are column header texts.

Using Find and Replace

This method allows you to look up the cells containing specific cell formatting styles. Once the source cells are located using the Find and Replace tool, you can delete the content of the target cells. In this method, you can only clear the cell contents. The cell formatting remains the same. Also, you don’t shift or move any cells when using this method to delete highlighted cells in Excel.

Press Ctrl + H to launch the Find and Replace dialog box.

Click on the Format button in the Find what line and go to the Fill tab of the Find Format dialog box.

Select a color from the color picker box that matches the background color of the source cells. Click OK to save it.

You should now see the cell formatting in the Preview button in the Find what line.

Now, simply hit the Replace All button.

Excel will delete the content of the source cells but keep the formatting.

Using Go To Special

You can use the Go To Special tool to delete highlighted cells in Excel if the cell styling or highlighting came from Conditional Formatting rules.

To try this method, go to the source worksheet and press the Ctrl + G keys to bring up the Go To dialog box.

Click on the Special button in the bottom left corner to access Go To Special.

Select the Conditional formats option in the Go To Special toolbox and click on the OK button.

Excel will select all the cells that use some Conditional Formatting rules.

You can now hit the Delete button to get rid of these highlighted cells.

Filter by Color and Delete Rows

Another easy way to remove highlighted cells is to use the Sort & Filter tool. This tool allows you to filter cells based on data and cell formatting, like fill color.

To try it out, go to the active worksheet and select the column header row of your dataset. Press Ctrl + Shift + L to activate the Sort & Filter tool.

Now, click on the filter drop-down arrow in any of the column headers under which the formatted cells exist.

The Sort & Filter context menu will open. Hover the cursor over the Filter by Color option and select the cell fill color by which you wish to filter the cells.

Excel will filter the dataset according to the input cell fill color value.

Now, select the highlighted cells and press the Delete key to remove the highlighted cells.

You’ve deleted the cell contents but not the actual cells so far. The formatting, like cell fill color also remains.

Alternatively, you can select all the rows that have highlighted cells, right-click, and click on the Delete Row option from the context menu.

The filtered dataset will be empty now. To reveal the rest of the data, press Ctrl + Shift + L again to disable the Sort & Filter tool.

You’ll now get back your dataset except for the highlighted cells that you have deleted.

Sort by Color and Delete Selected Rows

The Sort & Filter tool also allows you to sort a dataset based on the color of the cells. So, you can use this method to sort the dataset, bring the highlighted cells on top of the rest of the data, and delete those either by rows or cells.

Let me walk you through the process below with the steps:

Firstly, go to the source worksheet, select the column header of the highlighted cell or cell ranges, and press Ctrl + Shift + L to activate the Sort & Filter tool.

Click on the Sort & Filter drop-down menu in any of the column header cells that contain the highlighted cells below the column.

Hover the cursor over the Sort by Color menu and click on the cell fill color by which you wish to sort the dataset.

You should now see the highlighted cells on top of the rest of the dataset.

Select the cell or cell range and press the Delete key.

This will clear the content in the selected cell range.

Alternatively, you can select the cell range and click on the Clear All option in the Clear command of the Editing block in the Home tab.

This deletes the content in the selected range as well as cell formatting, like cell fill colors, styles, etc.

If you don’t want to keep the rows of the highlighted cells, simply right-click on the sorted rows and click on the Delete option in the context menu.

Excel will delete the highlighted cells as well as the rows.

Use VBA to Delete Highlighted Cells

If you need to delete highlighted cells a lot and wish to automate the process Excel VBA macros are the best method. You can set up one or many macros to achieve different ways to delete highlighted cells and use them continuously for all the workbooks.

Before you can use this method, you must learn how to create a macro using a VBA script. Follow along with this Excel tutorial to understand the process:

📒 Read More: How To Use The VBA Code You Find Online

So far, you have learned how to create a macro in Excel. You can now use the following VBA script to set up your own macro that will delete highlighted cells:

Sub DeleteHighlightedCells()
    Dim ws As Worksheet
    Dim cell As Range
    Dim groupDict As Object
    Dim groupCounter As Integer
    Dim groupName As String
    Dim userInput As String
    Dim deleteOption As String
    Dim cellCount As Integer
    Dim rowCount As Integer
    Dim key As Variant
    Dim cellInGroup As Range
    
    Set ws = ActiveSheet
    Set groupDict = CreateObject("Scripting.Dictionary")
    groupCounter = 1
    
    ' Group cells by their custom fill color
    For Each cell In ws.UsedRange
        If cell.Interior.ColorIndex <> xlNone Then
            groupName = "Group " & cell.Interior.ColorIndex
            If Not groupDict.exists(groupName) Then
                groupDict.Add groupName, New Collection
                groupCounter = groupCounter + 1
            End If
            groupDict(groupName).Add cell
        End If
    Next cell
    
    ' Prompt for group selection
    Dim groupList As String
    groupList = ""
    For Each key In groupDict.keys
        groupList = groupList & key & vbCrLf
    Next key
    
    userInput = InputBox("Enter the group you want to delete:" & vbCrLf & groupList, "Delete Group")
    
    ' Prompt for deletion option
    If userInput <> "" Then
        deleteOption = InputBox("Enter '1' to delete by cell or '2' to delete by row:", "Delete Option")
        cellCount = 0
        rowCount = 0
        
        If groupDict.exists(userInput) Then
            For Each cellInGroup In groupDict(userInput)
                If deleteOption = "1" Then
                    cellInGroup.Clear
                    cellCount = cellCount + 1
                ElseIf deleteOption = "2" Then
                    cellInGroup.EntireRow.Delete
                    rowCount = rowCount + 1
                End If
            Next cellInGroup
        End If
        
        ' Display result
        If deleteOption = "1" Then
            MsgBox cellCount & " cells have been removed.", vbInformation, "Deletion Complete"
        ElseIf deleteOption = "2" Then
            MsgBox rowCount & " rows have been removed.", vbInformation, "Deletion Complete"
        End If
    End If
End Sub

When the macro is ready, press Alt + F8 to launch the Macro dialog box.

Select the DeleteHighlightedCells macro and hit the Run button.

The VBA script will show a list of groups of cell ranges with similar cell formatting styles to identify different types of highlighted cells in the worksheet.

Another input box will show up where you can type in the group name, like Group 6 for deletion.

You’ll see another dialog box where you can choose if you wish to delete the cell contents or the rows.

According to your input, Excel will either delete the content of the cells or the entire rows.

Conclusions

Now you know how to delete highlighted cells in Microsoft Excel using several methods, like the right-click menu, the Find and Replace tool, the Go To Special dialog box, the Sort & Filter tool, and Excel VBA.

You can choose a suitable method from any of the above depending on the dataset size and frequency of use.

If you’ve liked the tutorial and wish to learn any new Excel skills drop a comment below.