How to Filter Strikethrough Data in Excel

Formulas & functions · Updated November 20, 2026

Excel’s regular filter menu can’t filter rows by strikethrough formatting. To show those rows, add a helper column that marks cells with strikethrough as Yes, then filter that column. The VBA method below checks a selected data column, writes the results beside it, and works in Excel for Microsoft 365 on Windows.

What You’ll Need

  • Excel for Microsoft 365 desktop on Windows. The macro also works in the desktop version for Mac, though its menu names and shortcuts can differ. Excel for the web cannot run VBA macros.
  • A workbook saved as a macro-enabled file (.xlsm) if you want to keep the macro in the workbook.
  • A blank helper column immediately to the right of the column you want to check. The macro writes Yes or No into that column, so do not use a column containing data you need to keep.
  • Permission to run macros. If your organization blocks them, ask your administrator about an approved alternative.

Step-by-Step

  1. Make a backup and identify the range. Save a copy of the workbook before running code. Find the column whose cells are crossed out, note the header row, and check which rows contain records. The method checks the formatting of each cell in one column and writes a marker beside each selected cell. If you only want to filter on one column, select that column’s data cells, not the entire table.
  2. Prepare the helper column. Make sure the column immediately to the right is blank for all the rows you plan to check. Add a header such as Strikethrough? in the header row beside your data. Leave the rows beneath the header blank; the macro will fill them. If the adjacent column contains information, insert a new worksheet column or choose a different layout before proceeding.
  3. Save a macro-enabled copy. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) as the file type. If you keep a macro in an ordinary .xlsx file, Excel will not preserve the VBA code when you save.
  4. Open the VBA editor and add a module. In Windows Excel, press Alt+F11. In the editor, choose Insert > Module. On Mac, use Developer > Visual Basic or Tools > Macro > Visual Basic Editor, depending on your Excel build. If the Developer tab is missing in Windows, enable it at File > Options > Customize Ribbon by checking Developer.
  5. Paste the macro into the module. Copy all of the code below into the blank module. It checks the selected cells for cell-level strikethrough and, for text cells, checks individual characters too. It places Yes or No in the cell immediately to the right of each selected cell.
Option Explicit

Sub MarkStrikethroughCells()
    Dim dataRange As Range
    Dim cell As Range
    Dim fontFlag As Variant
    Dim charFlag As Variant
    Dim cellValue As Variant
    Dim textValue As String
    Dim hasStrike As Boolean
    Dim i As Long

    If TypeName(Selection) <> "Range" Then
        MsgBox "Select one column of data cells first."
        Exit Sub
    End If

    Set dataRange = Selection

    If dataRange.Columns.Count <> 1 Then
        MsgBox "Select cells in one column only."
        Exit Sub
    End If

    If dataRange.Column = dataRange.Worksheet.Columns.Count Then
        MsgBox "The selected column has no column to its right."
        Exit Sub
    End If

    For Each cell In dataRange.Cells
        hasStrike = False
        fontFlag = cell.Font.Strikethrough

        If Not IsNull(fontFlag) Then
            If fontFlag = True Then hasStrike = True
        End If

        If Not hasStrike Then
            cellValue = cell.Value2
            If Not IsError(cellValue) Then
                If VarType(cellValue) = vbString Then
                    textValue = CStr(cellValue)
                    For i = 1 To Len(textValue)
                        charFlag = cell.Characters(i, 1).Font.Strikethrough
                        If Not IsNull(charFlag) Then
                            If charFlag = True Then
                                hasStrike = True
                                Exit For
                            End If
                        End If
                    Next i
                End If
            End If
        End If

        If hasStrike Then
            cell.Offset(0, 1).Value = "Yes"
        Else
            cell.Offset(0, 1).Value = "No"
        End If
    Next cell

    MsgBox "Helper column updated."
End Sub
  1. Select only the data cells to check. Return to the worksheet and select the cells below the header in the target column. For example, if the column header is in B1 and records run from B2 to B100, select B2:B100. Do not include the header: the macro treats every selected cell as a record and writes a result beside it.
  2. Run the macro. In Windows, press Alt+F8, select MarkStrikethroughCells, and choose Run. On Mac, use Developer > Macros and run the same macro. If prompted about macros, only enable them for a workbook and code you trust. The macro overwrites the corresponding cells in the adjacent helper column, which is why that column must be blank or safe to replace.
  3. Turn on filtering for the complete data range. Include the original table columns and the helper column, including both header rows and records. Choose Data > Filter, or use Home > Sort & Filter > Filter. If your existing filter does not include the new helper column, clear and reapply the filter to the full range. For an Excel Table, confirm that the helper field is included in the table.
  4. Show the strikethrough rows. Open the filter arrow in the Strikethrough? header, clear Select All, check Yes, and select OK. Excel hides rows marked No and displays rows where the selected cells contain strikethrough formatting. Choose No instead to see the other rows, or Select All to restore the complete list.
  5. Refresh the helper results after formatting changes. The Yes/No values are a snapshot; changing a cell’s formatting does not automatically recalculate them. Clear the filter, select the same data range, and run the macro again whenever you add or remove strikethrough. Then filter Yes again.

The macro does not delete records or remove formatting. It only writes markers in the helper column, and filtering hides rows temporarily. However, VBA changes cannot be reversed with Ctrl+Z, so save a backup first. On Mac, the undo behavior is also unavailable for macro actions.

Common Mistakes

  • Selecting the header row with the data. This can put a Yes or No beside the header and leave the actual records out of sync with the intended range. Select only the cells below the header, then put the helper heading in the corresponding header row yourself.
  • Running the macro on several columns. This code deliberately accepts one column only. Select one target column so each helper result clearly means “this row’s target cell is struck through.”
  • Leaving important data in the helper column. The code replaces the values immediately to the right of the selection. Check that area first, and save a copy before running the macro.
  • Filtering a range that excludes the helper field. The filter dropdowns appear only for columns inside the filtered range. Select the whole dataset, including the helper column, when applying or reapplying Data > Filter.
  • Sorting only the original data column. If you sort one column separately, the Yes/No markers may no longer match their records. Sort the complete table or filtered range so each row stays together.
  • Expecting a live result. Strikethrough is formatting, not a cell value that a standard worksheet formula can read. Re-run the macro after changing formatting; filtering the helper column alone will not detect a new change.

Tips

Use a clear helper heading, and keep the helper column visible while you verify the results. Before relying on the filter for a large list, test a few known cases: one cell that is struck through, one that is not, and—if applicable—one where only part of the text is crossed out. The macro marks a cell Yes if any character in its text has strikethrough.

If your data is an Excel Table, a helper field is especially convenient because it has its own filter arrow. Make sure the helper column is actually part of the table, not just next to it. You can check whether its header has a filter arrow and whether the table styling extends into that column. If not, resize the table using Table Design > Resize Table, or apply a filter to the full range instead.

For repeated work, keep the code in a trusted macro-enabled workbook and give the helper column a consistent name. If you share the workbook with someone who cannot run macros, run the macro first and save the resulting Yes/No values. Those values remain available as a snapshot even if the recipient cannot run the code, but they will not update when formatting changes.

Excel’s Find dialog can search for formatting, but finding matching cells is not the same as filtering entire records by that format. A helper column connects the formatting check to each row, which makes it possible to use ordinary AutoFilter controls and restore the full list later.

Troubleshooting

  • The macro does not appear in the macro list: Confirm that the code is in a standard module, that the procedure is named MarkStrikethroughCells, and that the workbook is saved as .xlsm. If macros are disabled, check Excel’s security prompt or your organization’s macro policy.
  • You see a message asking for one column: Return to the sheet and select a single vertical range of data cells. A selection spanning more than one column is rejected to prevent ambiguous results.
  • Every result says No: Check that the target cells really use Excel’s strikethrough font formatting, rather than a line drawn with a shape or a border. Verify that you selected the correct column and run the macro again. If the formatting is applied only to part of a text cell, test that cell separately; the macro checks individual characters in text values.
  • The filter arrow does not show Yes: Check the helper cells to see whether the macro wrote Yes, and confirm that the helper header and data are inside the filtered range. Remove the current filter and apply Data > Filter to the entire dataset again.
  • The results became incorrect after editing or sorting: Re-run the macro after formatting edits. If rows were sorted, sort the whole dataset, including the helper column, so the markers travel with their original records.
  • You are using Excel for the web: Excel for the web can display and filter saved Yes/No helper values, but it cannot run this VBA macro. Open the workbook in desktop Excel to run it, or ask someone with desktop Excel to create the markers.

Frequently Asked Questions

Can Excel filter strikethrough directly without a helper column?
No. The standard AutoFilter menu does not offer strikethrough as a filter condition. A helper column turns the formatting into a filterable Yes/No value.

Can I use a normal formula to detect strikethrough?
No built-in worksheet formula reads font formatting such as strikethrough. A VBA macro or another process that examines formatting is needed to create the helper values.

Does this work when only part of a cell’s text is crossed out?
The macro checks each character in text cells and marks the row Yes if it finds any struck-through character. For numbers and other non-text values, it checks the cell’s overall font formatting.

Will the helper values update as soon as I change formatting?
No. They are ordinary text values written by the macro, not live formula results. Run the macro again after changing strikethrough formatting.

Can I run this in Excel for Mac?
Yes, the desktop Mac version supports VBA. The editor and macro menus may be in different places than in Windows, and keyboard shortcuts can depend on your keyboard. Excel for the web does not run VBA.

Can I undo the macro with Ctrl+Z?
No. VBA actions clear Excel’s normal undo history. Save a separate copy before running the macro, and check the helper column before you overwrite any existing contents.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.