Excel’s standard filter cannot filter directly by strikethrough formatting. In Microsoft 365 for Windows, the practical solution is to add a helper column that labels marked cells, then filter that column; you can fill the helper manually or use a short VBA macro.
What Excel’s filters can and cannot detect
AutoFilter is designed to filter on cell values, not every kind of font formatting. You can use a column’s dropdown to filter by certain formatting, including font color, fill color, and cell icon, but there is no built-in “strikethrough” choice. The same limitation applies if you open the menu from Data > Filter or Home > Sort & Filter > Filter.
That distinction matters because a strikethrough is usually a visual signal, not data that Excel can evaluate in an ordinary worksheet formula. A formula such as IF or COUNTIF can test a cell’s value, but it cannot ask whether the cell’s font has a line through it. Sorting by format does not provide a strikethrough-specific solution either.
For a short list, manually recording the formatting in a helper column is often simplest. For a longer list that needs a repeatable check, a macro can read the font property and write a filterable label. Either way, the filter works on the helper values, not on the strikethrough appearance itself.
Fastest no-code workaround: add a helper column
Add a column to the data table with a clear heading, such as “Strike status.” For each row, enter “Yes” if the relevant cell is struck through and “No” if it is not. Then use the dropdown on the helper column to show only Yes. In Excel for Windows, you can turn filters on from Data > Filter; Ctrl+Shift+L is the keyboard shortcut for toggling them. On Mac, use Data > Filter if the shortcut differs in your keyboard setup.
This is a good choice when you have a manageable number of rows, when the formatting changes infrequently, or when you want the status to be explicit for other people using the workbook. It also avoids macros, which may be blocked by an organization’s security settings.
- Keep the helper column inside the table’s filter range. If the data is an Excel table, add the helper as a table column so its heading receives a filter dropdown.
- Use consistent labels, such as Yes and No, rather than several variations like “struck,” “done,” and “crossed out.”
- After changing a cell’s formatting, update its helper value too. The manual label does not update automatically when you apply or remove strikethrough.
For example, suppose a task list has task names in column A and owners in column B, while the strikethrough is applied to completed task names in column A. Add “Strike status” in column C, label each task row, and filter column C to Yes. Excel then hides the No rows and displays the marked tasks.
Find cells with strikethrough formatting
Excel’s Find dialog can search for formatting, which can help you identify marked cells without inspecting every row. In desktop Excel for Windows, press Ctrl+F, select Options, then use Format to specify strikethrough on the Font tab. Search within the relevant worksheet or selected range, and use Find All to list matching cells. The exact placement of the Format controls can vary slightly by desktop version.
Finding is useful for checking where strikethrough appears, but it is not the same as filtering records. Find All selects or lists matching cells; it does not create a persistent row-level filter, and it may find struck-through cells in unrelated parts of the sheet. You still need a helper column if you want to show only complete records whose chosen field is struck through.
On a Mac, use the Find command from the Edit menu or the available keyboard shortcut, then look in the expanded search options for formatting criteria. Excel for the web has fewer formatting-search and automation options than the desktop application, so the helper-column method is generally more dependable there.
Automate the helper column with VBA
If many rows need to be checked, a VBA macro can read each selected cell’s direct font formatting and write Yes, No, or Review into the cell immediately to its right. Select only the data cells in one column, not the heading, and make sure the adjacent column is empty or that overwriting it is safe. Add a heading to the helper column and include it in the table’s filter range.
In desktop Excel, open the Visual Basic Editor, choose Insert > Module, and paste the code below. Run the macro with the source cells selected. It checks direct formatting: a fully struck-through cell becomes Yes, a cell without strikethrough becomes No, and a cell with mixed character formatting becomes Review because the whole cell does not have one unambiguous strikethrough state.
Sub MarkStrikethrough()
Dim sourceRange As Range
Dim sourceCell As Range
Dim helperCell As Range
Dim mark As Variant
' Use a single selected column of data cells.
If TypeName(Selection) <> "Range" Then Exit Sub
Set sourceRange = Selection
If sourceRange.Columns.Count <> 1 Then
MsgBox "Select data cells in one column."
Exit Sub
End If
For Each sourceCell In sourceRange.Cells
Set helperCell = sourceCell.Offset(0, 1)
mark = sourceCell.Font.Strikethrough
If IsNull(mark) Then
helperCell.Value = "Review"
ElseIf CBool(mark) Then
helperCell.Value = "Yes"
Else
helperCell.Value = "No"
End If
Next sourceCell
End Sub
Save a copy of the workbook before running a macro. Macro changes cannot be undone with Ctrl+Z, and a macro-enabled workbook generally needs the .xlsm file format to retain its VBA code. Excel for the web cannot run VBA macros; use desktop Excel for this method. Excel for Mac supports VBA in its desktop app, although editor menus and keyboard shortcuts may differ.
Direct formatting versus conditional formatting
The macro above checks direct formatting—the strikethrough applied through the Font dialog or the Home tab’s Font controls. A cell may also look struck through because a conditional-formatting rule applies that effect when a condition is met. In that case, checking the underlying direct font property may report No even though the displayed cell appears crossed out.
If the visual effect comes from conditional formatting, the best solution is usually to filter on the condition that drives the rule. For example, if a task is struck through when its Status value is “Complete,” filter the Status column for Complete. That filters the underlying meaning rather than trying to infer it from how the cell looks.
If you specifically need to capture the displayed result, a VBA procedure can use the displayed-format property instead of the direct-format property. In the macro, replace the line mark = sourceCell.Font.Strikethrough with mark = sourceCell.DisplayFormat.Font.Strikethrough. DisplayFormat can read formatting as displayed by conditional formatting in a macro, but it is not a substitute for a normal worksheet formula and does not make the standard filter understand strikethrough on its own.
Keep the filtered results accurate
A helper column is a snapshot or a manually maintained label, not a live connection to font styling. If someone strikes through a task after the macro has run, the existing Yes/No value will be stale until the macro runs again. The same is true if someone removes the formatting. Rerun the macro after a batch of edits, or agree on a process for keeping the helper column current.
For a table, place the helper column within the table rather than beside it outside the table’s range. If it is outside, its dropdown may not control the same set of rows, and filtering one range can leave neighboring data visible in a misleading way. Also check that the data has a single header row and no blank rows splitting the list.
“Review” results deserve attention rather than being treated automatically as Yes or No. A cell containing rich text can have only part of its text struck through, so the whole-cell formatting state may be mixed. Decide whether partial strikethrough should count as marked, then record that decision consistently. If the mark is intended to mean a status such as “cancelled,” storing that status in a dedicated data field is more reliable than using font formatting as the only record.
Alternatives and when to use them
For a one-time check, Find by Format may be enough to locate marked cells, followed by manual review. For recurring row filtering, use a helper column. For a large range with consistent direct formatting, the VBA macro can populate that column quickly. If the workbook already has a status, category, or completion field that explains why the text is struck through, filter that field instead.
Power Query and worksheet formulas are generally better suited to filtering and transforming values than reading font effects. A refreshable data process should rely on an explicit field such as Status rather than formatting alone. This also makes the information easier to sort, summarize, export, and understand for people who use different themes, print settings, or assistive technology.
Strikethrough is often used to show completed, cancelled, or superseded items, but those meanings are not interchangeable. Before building a workflow around the appearance, decide what the mark means in the data. A field with values such as Open, Complete, or Cancelled can drive both filtering and conditional formatting, keeping the status and its visual cue aligned.
Frequently Asked Questions
Can Excel filter by strikethrough directly?
No. Excel’s standard AutoFilter does not offer strikethrough as a formatting criterion. Use a helper column containing labels such as Yes and No, then filter that column.
Can I use a formula to detect strikethrough?
Not with a standard worksheet formula. Formulas evaluate values, not font formatting. Use manual labels or VBA to read the formatting and write a value that the filter can use.
Will filtering work if strikethrough comes from conditional formatting?
The filter still cannot filter directly by the displayed strikethrough effect. If a rule is based on a status or value, filter that underlying field. A VBA macro using DisplayFormat can read the displayed effect, but the helper values must be refreshed when the formatting result changes.
Does Excel for the web support the macro method?
No. Excel for the web does not run VBA macros. Use desktop Excel to run the macro, or maintain the helper column manually in the browser.
Why does the macro return Review for a struck-through cell?
Review indicates mixed character formatting within the cell, such as only part of the text being struck through. Decide how partial formatting should be classified and inspect those cells rather than assuming the entire cell is marked.
Related
- Excel CSV vs XLSX: Which File Should You Use?
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for a List of Numbers: 10 Useful OptionsTo create a list from 1 to 10 in Excel, enter =SEQUENCE(10) in an empty cell; it spills…
- SUMPRODUCT in Excel: What It Contains and How It WorksExcel’s SUMPRODUCT contains one required array argument and optional additional arrays; it multiplies corresponding entries across the arrays,…
- VLOOKUP Across Sheets in Excel: Formula and TipsYes. If the value to look up is in A2 on an Orders sheet, and a Product List…
- Excel SUM and SUBTOTAL: Formulas, Filters, and ExamplesTo add a range in Excel, use =SUM(B2:B10). To add only the rows currently shown after filtering, use…
- Excel Time Formulas: Calculate Overnight PM-to-AM HoursIf A2 contains a PM start time and B2 contains the following AM finish time, enter =MOD(B2-A2,1) to…
- Excel Table Sort by Two Columns: A Step-by-Step GuideTo sort an Excel table by two columns in Microsoft 365 for Windows, click a cell in the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.