To filter rows by strikethrough in Excel, add a helper column that checks the formatting of the cell beside it, then use Excel’s regular filter on that helper column. Excel’s built-in filter menu does not offer strikethrough as a direct condition. This method is for Microsoft Excel for Microsoft 365 on Windows; it uses a legacy Excel 4 macro function in a defined name, so it may be blocked by your organization’s security settings and does not work in Excel for the web.
What You’ll Need
- A desktop copy of Excel for Microsoft 365 on Windows, with permission to use Excel 4 macro functions in defined names.
- A worksheet with a header row and one record per row. The cells you want to check should be in one column, such as column A.
- An empty helper column immediately to the right of the cells you want to check. This matters because the defined name will look one column to its left.
Save a copy of the workbook before changing its structure, especially if it is shared or used for reporting. The helper column identifies formatting; it does not alter the original text or remove its strikethrough.
Step-by-Step
- Check which cells the filter should inspect. Decide whether you want to find strikethrough in one particular field, such as a task name in column A, or in a different column. This method checks one column at a time. If the crossed-out formatting can appear in several columns, make a separate helper column for each one, or decide which column determines whether the whole row counts as crossed out. A row may have strikethrough in one field but not another.
- Make sure your data has a clear header row. For example, put a heading such as Task in A1 and Helper in B1, with records starting in row 2. Avoid blank rows inside the list, since they can interrupt the range Excel recognizes when you turn on filtering. If your data is already an Excel table, use its existing header row and add the helper field as a table column.
- Insert a helper column next to the formatting you want to check. If the target cells are in A, use B for the helper. Enter a clear heading such as Has strikethrough? in B1. The helper will report whether the cell immediately to its left has strikethrough formatting. If another column contains the relevant text, place the helper directly to the right of that column instead.
- Create a defined name for the formatting check. Select a cell in the helper column, such as B2. On the ribbon, choose Formulas > Defined Names > Name Manager, then choose New. In the Name box, enter
HasStrike. Set Scope to Workbook. Assuming the cells to check are in column A and the helper is directly beside them in column B, enter this in Refers to:=GET.CELL(23,INDIRECT("rc[-1]",FALSE)). Choose OK, then close Name Manager. The relative referencerc[-1]means the cell one column to the left of the helper formula. Keep the helper immediately beside the target column; moving it elsewhere changes what the name checks. - Put the name in the first helper cell. With the same layout—target cells in A2:A… and the helper in B2:B…—enter
=HasStrikein B2 and press Enter. A result of 1 indicates that A2 has strikethrough; 0 indicates that it does not. If you see an error instead, go to Troubleshooting before filling the column. - Fill the helper down for every record. Select B2 and drag its fill handle down to the last data row, or copy B2 and paste into the remaining helper cells. Check several results against the source formatting before filtering. Do not fill the helper into the header row, and do not leave gaps if you want every record included.
- Turn on AutoFilter. Click a cell in the data range. Choose Data > Filter. Excel adds a dropdown to each header. If the range is an Excel table, its header dropdowns may already be visible. Confirm that the filter range includes both the source column and the helper column.
- Choose which rows to show. Open the dropdown in the helper header. Clear (Select All), select 1, and choose OK to show rows whose checked cell has strikethrough. To hide those rows and show only non-struck entries, select 0 instead. The source values remain unchanged; filtering only hides rows that do not match.
- Clear or change the filter when you are finished. Reopen the helper dropdown and select both values, or use Data > Sort & Filter > Clear to clear active filters. Keep the helper column if you will repeat the task. If you no longer need it, first clear the filter and then remove the helper column, taking care not to delete the original data.
Important refresh note: Excel does not always recalculate a formatting check just because you applied or removed strikethrough. After changing the formatting, force a calculation with F9, then check the helper results again before filtering. If the values remain stale, select the affected helper cells, re-enter the formula, and fill it down again. Formatting changes are not the same as changes to cell contents, so automatic updates are not guaranteed.
Common Mistakes
- Expecting a strikethrough option in the filter dropdown. The regular AutoFilter menu offers filters for values and some formatting options, but it does not provide a reliable strikethrough condition. Filtering by font color is not equivalent: a struck-through cell can use the same font color as other cells.
- Putting the helper in the wrong place. The defined name above checks one column to the left. If there is an empty column between the source and helper, or the helper is on the left, the check will inspect a different cell. Keep the two columns adjacent and verify a known struck-through example.
- Checking the wrong field. A record can contain several text fields, and only one may have strikethrough. Be explicit about which field determines the row’s status. A helper next to a description column will not detect formatting in a separate status column.
- Filtering before checking the helper results. A filter can make records disappear, but that does not prove the formatting test is correct. Compare the 1 and 0 results with a few cells you can inspect visually before relying on the filtered list.
- Assuming the formatting check updates immediately. Changing only a font format may not trigger recalculation. Press F9 after formatting edits and confirm the helper column has refreshed before using its filter.
- Using this method in Excel for the web. Excel for the web does not support this legacy GET.CELL approach in the same way as desktop Excel. Open the workbook in the Windows desktop app, or use a different workflow such as maintaining an explicit status field that you can filter normally.
Tips
- Use a status value for ongoing work. If strikethrough means “complete,” “cancelled,” or “reviewed,” add a Status column with values such as Complete and In progress. Filter that column instead of treating visual formatting as the source of truth. Status values are easier to audit, share, and use in charts or formulas.
- Keep the helper heading descriptive. A heading such as Strikethrough in Task makes it clearer what the 1 and 0 values mean, particularly when another person opens the workbook.
- Make the filter range a table. Select your data and choose Insert > Table if it is not already a table. Tables make it easier to keep the filter range and headers together as rows are added. Add the helper as a table column and confirm the new records receive helper entries; still check results after adding rows.
- Use the helper to show either side of the result. Selecting 1 isolates struck-through records. Selecting 0 excludes them from the visible list. This is useful when you want to review active items without deleting completed or cancelled records.
- Keep a backup of important source data. Filtering hides rows, but deleting visible rows after filtering can remove records permanently. If you need a separate list, copy the filtered results to another sheet instead of deleting rows from the source.
- Remember that the check is about cell formatting. It identifies font strikethrough applied to the cell. It is not a text search for a special character, and it does not infer meaning from words such as “done” or “cancelled.”
Troubleshooting
The defined name returns #NAME? or Excel rejects it. Check that the name is spelled HasStrike in both Name Manager and the helper cell, and that the defined name was created with Workbook scope. Confirm that the formula was entered in the Refers to box, not in a worksheet cell. GET.CELL is a legacy Excel 4 macro function, not a standard worksheet function. Some Microsoft 365 installations or organizational policies disable Excel 4 macros; if that is the case, do not lower security settings without approval. Ask your administrator about an approved method, or use a regular status column instead.
Every row shows 0, including cells that clearly have strikethrough. First confirm the checked cells are immediately left of the helper formulas. Then check that the strikethrough is applied to the cell’s font, not merely displayed by another visual effect or a separate overlay. Re-enter the named formula exactly, recalculate, and compare a few cells. If the formatting was applied to only part of a cell’s text, this cell-level method may not identify the partial formatting as expected; treat the result as a whole-cell check, not a character-by-character scan.
The helper results do not change after you edit formatting. Press F9 and inspect the helper values before opening the filter. If that does not refresh them, re-enter the helper formula in the affected rows. A formula based on formatting can be less dependable for live updates than a helper value based on cell contents. For frequently changing records, use a dedicated status field and update it when the status changes.
The filter dropdown does not show 1 and 0. Check that formulas in the helper cells have calculated to numeric values, that the helper header is included in the filtered range, and that the column does not contain blanks or error values. Clear the current filter and turn it on again after confirming the range. If the data is a table, verify that the helper column is part of the table rather than an adjacent range.
The wrong rows appear after filtering. Clear the filter, inspect the helper values alongside the original cells, and verify that the source column and helper column are aligned on the same rows. A copied formula can be displaced if a row was inserted or the helper was moved. Correct the alignment, refill the helper, recalculate, and apply the filter again.
You are using a Mac or Excel for the web. The steps and availability of legacy Excel 4 macro functions can differ by platform and security configuration. Excel for the web is not a suitable place to create this GET.CELL-based check. If you cannot use the named formula in your desktop version, use a manually maintained status column or ask an administrator for an approved alternative; do not assume that a filter by font color will find strikethrough.
Frequently Asked Questions
Can Excel filter strikethrough directly?
Not with a dedicated strikethrough option in the standard AutoFilter menu. Use a helper column to identify the formatting, then filter the helper values.
Does filtering out strikethrough delete those rows?
No. Selecting 0 in the helper filter hides rows whose checked cells are struck through. The hidden records remain in the worksheet and can be shown again by clearing or changing the filter.
Can I filter strikethrough in more than one column?
Yes, but the example checks only the cell immediately left of its helper. Add a helper beside each relevant column if you need separate checks. If a row should count as struck when any one of several fields is struck, you will need to combine those checks using a suitable approved method or track the row’s status in a dedicated field.
Will the helper update automatically when I apply strikethrough?
Not reliably. Formatting changes may not trigger recalculation. Press F9, inspect the helper results, and re-enter formulas if needed before trusting the filter.
Can I do this in Excel for the web?
Not with the GET.CELL method described here. Open the workbook in desktop Excel if your installation permits the legacy function, or use an explicit status column that works with ordinary filters.
Is filtering by font color the same thing?
No. A font-color filter matches color formatting, not the line through the text. Strikethrough can be present regardless of font color, so use the helper check or a status field instead.
Related
- How to Duplicate an Excel File on Windows
- How to Duplicate an Excel File Shortcut on Windows
- How to Duplicate a Page in Excel and Keep Its Layout
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel XLOOKUP Sort Order: How Search Direction WorksIn Excel, XLOOKUP does not have a separate sort-order argument: its sixth argument, search_mode, controls the direction or…
- How to Filter Dates in Excel by Day, Month, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
- Excel MATCH Arguments: What Each One DoesExcel’s MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The first two say what to look for and…
- Excel Formula for Calculating Overtime HoursTo calculate daily overtime in Excel, subtract unpaid break hours and the regular-hours threshold from the shift length,…
- Data Validation List Formula: Excel Examples and SetupIn Excel, a data validation list formula goes in Data > Data Validation > Settings > Allow: List…
- Excel Function to Check If a Cell Contains TextTo check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.