How to Find and Select All (Partial) Strikethrough Cells in Excel!

This is a common scenario: You have sent an Excel file to a coworker and received the revised version back. Unfortunately, your colleague has only highlighted the changes using the strikethrough formatting. Even worse, in many cases only parts of cells are crossed out. But don’t worry – help is here! In this article you will learn many simple methods to find and select all Excel cells having strikethrough formatting.

Introduction and preparation

Basic Rule

One of my basic rules (before we start solving the problem):

Never transport information with formatting in Excel!

Professor Excel

Most common is probably to use color codes or currency information in cell formatting. But also, strikethrough is unfortunately used quite often. Formatting is very difficult to evaluate. I have already described in this article how to evaluate the number format.

Instead of using cell formatting, the proper way would be to insert another column (or row) and write down the comments there (also the comments and notes functions have their disadvantages).

Overview of methods

The bad news first: Excel does not have a comfortable built-in functionality to find and select all strikethrough cells in Excel. There is only one workaround using the Find window (see option 1 below) but for all other options it means, we have to switch to VBA. So, in this article you will find multiple VBA code snippets that you have to copy and paste into a VBA module.

The options to identify strikethrough Excel cells can be separated into two dimensions:

	Select all cells with strikethrough formatting	Insert function (“UDF”) and return TRUE or FALSE
Entire cell has strikethrough	Find & Replace VBA	VBA
Entire cell or only parts of the cell have strikethrough formatting	VBA Professor Excel Tools	VBA Professor Excel Tools

The first differentiation is if you want to select cells with strikethrough formatting or if you want to rather insert a function that checks if another cell is crossed out. Recommendation: If you only want to gain a quick overview, select all strikethrough cells. On the other hand, if you repeatedly want to evaluate strikethrough or you need to keep it dynamic, use a function.

The second differentiation is about if the whole cell has strikethrough formatting or if you also need to know if only parts a cell is crossed out.

How to insert VBA macros

Because you will find a lot of VBA codes below, here is how use them in your Excel file:

1. Open the VBA editor. The fastest way to do this would be to press Alt + F11 on the keyboard.
   Alternatively, go to the Developer ribbon (if you don’t see the developer ribbon, you must activate it first) and click on Editor.
2. Right-click on the Excel file VBA name (or any other item below, for example the “Microsoft Excel Objects” folder icon as in the screenshot).
3. Go to Insert and click on “Module”.

4. Now it should look similar to number 4 and you see the white code area in which you can paste the VBA source codes from below.
5. Press Start to run the macro.

(For more help with VBA macros please refer to this article).

Option 1: Select all cells with strikethrough formatting on the entire cell

With this first method you select all cells within your selected cell range that are entirely crossed out.

Examples:

• Cell with content “abc” will be selected.
• Cells with content “abc” will not be selected.

Use the Find window to select all strikethrough cells

For selecting all cells that are entirely crossed out, you can use the Find window and search for the formatting:

1. Open the Find and Replace window by pressing Ctrl + F on the keyboard.
2. Click on “Format” to open the “Find Format” window.
3. Go to “Font”.
4. Set the checkmark at “Strikethrough”.
5. Confirm with OK.
6. Back in the “Find and Replace” window, start searching by clicking on “Find All”.
7. Select all search results by holding down the Shift (or Ctrl) key and clicking on the search results.

Use a VBA macro to select all strikethrough cells

Copy the following code into your VBA model, select a larger range of cells and press start in the VBA editor.

Sub selectStrikethroughEntireCells()
    On Error Resume Next
    Dim cellRange As Range
    Dim tempRange As Range
    
    
    For Each cellRange In Selection
        If cellRange.Font.Strikethrough Then
            Set tempRange = addRangeToSelection(tempRange, cellRange)
            Resume Next
        End If
    Next
    tempRange.Select
    
End Sub

Function addRangeToSelection(firstRange As Range, secondRange As Range)
    If Not firstRange Is Nothing Then
        Set firstRange = Union(firstRange, secondRange)
    Else
        Set firstRange = secondRange
    End If
    addRangeToSelection = firstRange
End Function

Option 2: Select all cells with only partial strikethrough

With our next option number 2 you can select all cells within your selected cell range that are have at least one character crossed out.

Examples:

• Select cell with content and formatting “abc“.
• Select cells with content and formatting “abc“.

Sub selectStrikethrough()
On Error Resume Next
Dim cellRange As Range
Dim tempRange As Range

For Each cellRange In Selection
    Dim n As Long

    For n = 1 To cellRange.Characters.Count
        With cellRange.Characters(n, 1)

            If cellRange.Characters(n, 1).Font.Strikethrough Then
                Set tempRange = addRangeToSelection(tempRange, cellRange)
                Resume Next
            End If
        End With
    Next

Next
tempRange.Select
End Sub


Function addRangeToSelection(firstRange As Range, secondRange As Range)
    If Not firstRange Is Nothing Then
        Set firstRange = Union(firstRange, secondRange)
    Else
        Set firstRange = secondRange
    End If
    addRangeToSelection = firstRange
End Function

Option 3: Return TRUE or FALSE if entire cell has strikethrough

With this next option, you return TRUE, if a cell is entirely crossed out. Example:

• =CellEntirelyStrikethrough(A1) returns TRUE if content of cell A1 is “abc“.
• =CellEntirelyStrikethrough(A1) returns FALSE if content of cell A1 is “abc“. (the letter “a” doesn’t have strikethrough formatting here)

Function CellEntirelyStrikethrough(cellRange As Range)
    Dim strikethrough As Boolean
    strikethrough = False
    
    If cellRange.Font.strikethrough Then
            strikethrough = True
    End If

    CellEntirelyStrikethrough = strikethrough
    
End Function

Option 4: Return TRUE or FALSE if only parts of cell have strikethrough formatting

This method returns TRUE, if your referred cell has at least one character crossed out. Example:

• =CellEntirelyStrikethrough(A1) returns TRUE if content of cell A1 is “abc“.
• =CellEntirelyStrikethrough(A1) returns TRUE if content of cell A1 is “abc“.

Function PartOfCellStrikethrough(cellRange As Range)
    Dim strikethrough As Boolean
    strikethrough = False
    
    Dim n As Long
    
    For n = 1 To cellRange.Characters.Count
        If cellRange.Characters(n, 1).Font.strikethrough Then
            strikethrough = True
        End If
    Next

    PartOfCellStrikethrough = strikethrough
    
End Function

Expert Tip: Use Professor Excel Tools to do all this very comfortably

You don’t want to bother with VBA macros? Use Professor Excel Tools. It has these functions built-in and you can use them very comfortably.

Select all strikethrough cells (including if only parts have strikethrough) by clicking on “Selection Tools” on the Professor Excel ribbon and then on “Select all strikethrough cells”:

Or insert the function =PROFEXHasStrikethrough(B7) in order to return TRUE or FALSE depending if cell B7 has at least one character with strikethrough formatting:

Download

Please feel free to download all examples above in one Excel workbook. Just click here to start the download.

Image by Gerd Altmann from Pixabay